High performance encrypt/decrypt in both PHP AND MySQL

Question

Id like to redesign some aspects of my database/website, and am looking for reasonably strong crypto functions in PHP, which are also supported by MySQL.

I also need the encrypt/decrypt to be 100% portable & compatible

Mostly I will be crypting in PHP, selecting the crypted version from MySQL, and then decrypting in PHP. But occasionally I will need to run a query which decrypts the field in MySQL, for reporting purposes etc

I had a look at mycrypt php library, but its not clear which of these ciphers are supported by MySQL. Any recommendations plase?

Answer 1

After a bit of Google-fu it appears MySQL uses 128-bit AES with Electronic Codebook (ECB) mode. For the key, you'll need to use exactly value that's exactly 16 bytes.

Lets say I use _My-16-byte-key_ as my secret key.

SELECT AES_ENCRYPT('The rooster crows at midnight!', '_My-16-byte-key_')

Result is: 7e41520667dc20457db2f18644bad06dd62a2120be8b93cd5596d8ffea45ef0f

Over in PHP, I can use mcrypt_decrypt to reverse it:

$secret = '7e41520667dc20457db2f18644bad06dd62a2120be8b93cd5596d8ffea45ef0f';
$key = '_My-16-byte-key_';
print mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, pack('H*', $secret), 'ecb');

Result:

The rooster crows at midnight!

I'll leave the reverse flow as an exercise to the reader. =)

Answer 2

Here: http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html
Is a list of all the encryption functions in MySQL.

I recommend to use AES.
All the other encryption options are no longer secure.
AES supports a 128 bit key length (and a 256 bit key length with a recompile of the MYSQL source).
Don't forget to salt everything you encrypt with AES to prevent rainbow table attacks.

If you use the same key to encrypt decrypt everything all the attacker needs to do is get that key, with the hash function (and salt) you don't have to worry about losing the key, with this option you run a huge risk of losing the key and all your passwords with it.

Use a hash function instead: SHA256 with a salt.