For anyone who's interested, here's some functions I was able to come up with to encrypt/decrypt within PHP (rather than using Mysql AES functions). These functions require that the PHP is compiled with the mcrypt library.
$key= "some key";
function hex2bin($data)
{
$len = strlen($data);
return pack("H" . $len, $data);
}
function encryptString($theString){
global $key;
$cipher_alg = MCRYPT_RIJNDAEL_128;
$iv = mcrypt_create_iv(mcrypt_get_iv_size($cipher_alg, MCRYPT_MODE_ECB), MCRYPT_RAND);
$encrypted_string = mcrypt_encrypt($cipher_alg, $key, $theString, MCRYPT_MODE_CBC,$iv);
return bin2hex($iv) . bin2hex($encrypted_string);
}
function decryptString($theString){
global $key;
$iv=substr($theString,0,32);
$theStringwoiv=substr($theString,32);
$cipher_alg = MCRYPT_RIJNDAEL_128;
$theStringBin = hex2bin($theStringwoiv);
return mcrypt_decrypt($cipher_alg, $key, $theStringBin, MCRYPT_MODE_CBC,hex2bin($iv));
}
The $iv is an intialization vector and will get stored as a 32 character value along with each column (make sure your varchars have enough characters to hold 32 + encrypted data - typically atleast 150 chars. Works surprisingly fast.
For good security, the $key should be stored in a separate file and should be encoded using Zend Encoder or similar tool. Also, the $iv could be manipulated before storing with the column data (ie, could be reversed, or split, etc - for better security - but probably not really necessary).
Why is this better than using MySQL functions?
In the event of a root compromise, the intruder would be able to sniff packets going into mysql from PHP (unless stunnel is used), and thus the intruder could view the packets. This method (using PHP for encryption) is only more secure if you are able to encode the PHP files with Zend or similar (else they would be able to view the PHP files). This method borders on paranoid, but does provide good security, and to anyone who's system has been compromised, it offers a little comfort.
Lastly, be sure not to encrypt any columns on which you will be doing SQL comparisons.
