Create Sha 1 hash for a table

[saadi06]

Hi,
I have a table in database and I want to create a hash value string that will be based on the entries of that table. Is it possible in php that I can create sha1 value of the whole table and how can I compare the sha1 value that the values are not changed.
Thanks in advance.

[diafol]

Is it possible in php that I can create sha1 value of the whole table

That's a bit odd. You could have a last_modified field in your table and then query:

SELECT last_modified ORDER BY last_modified DESC

To track the date of the last change. Alternatively, you could have a separate table to log the last change of each table, with fields like:

table_id
table_name
last_modified

[broj1]

One approach would be to read the contents of the table in a giant string, hash that string and store the hash. Later you can repeat the procedure and compare the computed hash with the stored hash. But there are few considerations:

• the string that you read rows into, should not be too big not to exceed the memory and time limits of the server
• the contents of all the fields in the table should be convertible to string uniquely (what happens with the binary data, true/false etc?)

Maybe there is a better method (maybe hashing the MYD file). Anyway, what is the purpose of that?

[cereal]

If there aren't any NULL values you could use CONCAT(). For example you have a table like this:

create table posts(
    id auto_increment primary key not null,
    title varchar(255) not null,
    body text not null,
    created_at datetime not null,
    updated_at datetime not null
) engine = myisam default charset=utf8;

Now if you want to save the hash to the same table you can add a field hash:

alter table posts add hash varchar(40) not null;

And perform a query like this:

update posts set hash = sha1(concat(id, title, body, created_at, updated_at));

This will run the hash against all the rows.

You can change method and use ARCHIVE engine instead, you cannot modify the entries once inserted, nor you can delete them. So it depends on your goals. You can also save the hash to another table (with archive engine) which will work as logger, in this case you will need an ID as foreing key, the hash and the date of when the check was performed, so you can perform multiple checks and compare results.

As I wrote it depends on you, if you have to hash existing data you don't need to pull it to PHP, you can run the queries directly in a MySQL client, otherwise if you're inserting data through a form, you can process the hash before sending everything to the database.

Or you can create a trigger. There are many solutions.