Database encryption made easy

Posted on December 7th, 2006 in Encryption,Programming by Brandon

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));

What could be easier?

4 Responses to 'Database encryption made easy'

Subscribe to comments with RSS or TrackBack to 'Database encryption made easy'.

  1. Shogo Yahagi said,

    on January 1st, 2007 at 7:00 pm

    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?

  2. Brandon said,

    on January 1st, 2007 at 11:20 pm

    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

  3. alex said,

    on June 11th, 2009 at 10:46 am

    not so great, actually. enable query logging in mysql and you can see all password used to encrypt data.

  4. Brandon said,

    on June 11th, 2009 at 12:38 pm

    @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.


  5. on March 19th, 2012 at 9:45 am

    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!

Post a comment

Please copy the string VjBnkg to the field below: