MD5() and AES_DECRYPT() server specific?

[Catfish]

Hi all,

 

I just uploaded my project to my webspace and tried it out online. Everything seems functioning ok, but I have made code that puts/pulls encrypted data to and from the database. To encrypt the data, I use an algorithm that generates the crypt key for the AES_ENCRYPT/DECRYPT() calls and also use MD5() in there for added encryption.

 

Now that I've uploaded my database tables and scripts, they are not decrypting data correctly. Are MD5 and AES_ENC/DEC server specific? (ie: can be enabled/disabled on a server to server basis) Or is it perhaps something else? My crypt key algorithm generates the key from a number of field values of the record that holds the encrypted data field. Would that cause some problems?

[Catfish]

The query I am using is:

 

SELECT IF(isEncrypted='TRUE', AES_DECRYPT(dataField, MD5(CONCAT(UNIX_TIMESTAMP(timestamp),creatorLogin))), dataField) as dataField,id,timestamp,category,creatorLogin,privacyLevel,refs,allowedUsers,allowedGroups,isEncrypted FROM tableName WHERE (condition)

 

The query is working because it retrieved all the other fields correctly only the dataField is not decrypted and returned. condition is a standard where clause condition which is generated by my php script so it can vary depending on user input.

 

sorry for double posting but i couldn't find an edit link?

[fenway]

What not try the simple case to prove it?  Use a basic string on both server.

[Catfish]

What not try the simple case to prove it?  Use a basic string on both server.

 

Hi I followed your advice and checked if MD5() and AES_ENCRYPT/DECRYPT() are working on my webhost and they both are.

 

If I use my SELECT statement as above, the dataField returns NULL even in the event that dataField is encrypted. I stripped down the IF() clause and tried each element seperately. The UNIX_TIMESTAMP() is fine, the CONCAT() is fine, the MD5() works fine, it is only the AES_DECRYPT() that is failing and returning a NULL instead of the plain text version of the encrypted data.

 

I just compared the result of MD5(CONCAT(UNIX_TIMESTAMP(timestamp),creatorLogin)) between my home server and the webhost server and it is giving me differing values - this is where my problem lies.

 

 

Is there an encryption method I can use which will remain constant over all servers? I guess the MD5 encryption must vary from server to server due to some factor I know nothing about. If anyone has some info on this I am very willing to learn about it.

[Mchl]

MD5 is a well described algorithm and it results do not vary across different systems. Otherwise it would be useless as a hashing function.

[Catfish]

UNIX_TIMESTAMP is returning varying results. I can see they are explaining it in the documentation at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp but I don't understand why it returns varying results.

 

My best guess is that, for example in some instances where daylight saving time is possible, you can have two unix_timestamp values that coresspond to two different timestamp values. That's what I understand from the mysql docs anyway. Is this correct? I'd have thought the odds on something like that occuring would be rather slim but I guess not.

 

I suppose I shouldchange the algorithm in my scripts to something else asthis is causing too much headache.

[Mchl]

UNIX_TIMESTAMP returns number of seconds since 1970-01-01 GMT. It is not influenced by time zones.

Be aware that it changes every second, so unless you call it in exactly same moment on both computers AND both computer have their internal clocks synchronised, you will get different results.

[Catfish]

I am calling UNIX_TIMESTAMP with a parameter, which is 'timestamp' - a field name holding a value in timestamp format. This value cannot be changed through my PHP frontends (only directly via the database) therefore I am effectively calling:

 

SELECT UNIX_TIMESTAMP(timestamp) FROM tableName WHERE id=40

 

on both servers, which contain identical tables and record values (I have checked the values are the same on both servers).

'timestamp' can be swapped for a timestamp string value like:

 

SELECT UNIX_TIMESTAMP('2009-01-26 22:00:00')

 

and I am receiving a different value from each server. The values are as follows:

1232971200

1233028800

 

I would suspect something is configured wrong on my server that may cause it, but I also checked the timezone settings on the servers and they differ also:

SHOW VARIABLES LIKE '%time_zone%'

 

returns 'E. Australia Standard Time' for variable 'system_time_zone' on my server and

'CST' on my webhost's. The 'time_zone' variable are set to 'SYSTEM' on both. I don't know if these values have any bearing on the return value of UNIX_TIMESTAMP but in any case I can't change that value on the webhost, so I think I might have to modify my encrypt/decrypt algorithm.

[Mchl]

YOu might be right.

There's some useful information here

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp

Note the link about time zone support (section 9.7)

[fenway]

Yes, timestamps are evil.