I’ve always wondered how one would securely store sensitive information in a MySQL database. A recent project has given me the opportunity to work on it, and I’ve been impressed on how easy it is to implement. MySQL provides an easy interface for encrypting data before storing it in the database. Simply use the AES_ENCRYPT and AES_DECRYPT functions when reading or writing to a table.
Simply make your column a blob field, then use something like this to write to the table
(using a PEAR::DB syntax)
$db->query("
UPDATE sometable
SET some_col = AES_ENCRYPT( ?, ?)
WHERE something_else = ?
" array( $sensitive_value, $encryption_key, $index));
and something like this to read it back out
$value = $db->getOne("
SELECT AES_DECRYPT( some_col, ?)
FROM sometable
WHERE something_else = ?
", array( $encryption_key, $index));
I get NULL when I use that. I even googled and found out that it must be stored in a blob and I still get NULL. Does this really work? I have the latest MySQL..
Are you using a blob?
Yes, my database column is a blob. The MySQL documentation can be found here:
http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html
Exact debugging steps will depend on how you are calling the query. If using PEAR, I will often do a print_r($db) after the database call. There is a lot of stuff that it outputs, but it will contain the exact call it made to the database. Make sure variable substitutions happened correctly. If you want to send me some more specific info, I’d be happy to look at it for you.
Thanks,
Brandon
not so great, actually. enable query logging in mysql and you can see all password used to encrypt data.
@alex
You have a lot more potential problems if somebody has access to enable query logging. They could likely tcpdump the traffic as well, and likely have access to the encryption key as well….. Security is only as good as the weakest link.
Yea ! i Agree in ‘I even googled and found out that it must be stored in a blob and I still get NULL.’ Thanks for sharing!