AES Encryption Standard PHP vs. MySQL

I recently ran into a problem using AES encryption that took me an annoying amount of time to figure out. Hopefully this post will achieve enough google fame that any other poor soul who runs into the same problem won’t waste as much time as I did. I will spare you the rant on how absurd almost everything about this is, and just get down to brass tacks.

Say you have a rather large MySQL database filled to the brim with your user’s sensitive personal information. As we all know so very well keeping sensitive information stored in plain text is not one of the brightest ideas so you opt to encrypt the data using MySQL’s AES_ENCRYPT(). That way if someone does get their grubby hacker paws on your database it will make it that much more difficult to get the data (unless of course you, like Gawker, lost your source code containing the encryption key).

Here is where things get…interesting. To get the information back you need to use AES_DECRYPT() right? That would be simple though and who would want that? Simplicity is boring, and in this case marginally unsafe considering that the decrypted information is sent plain over the wire. That’s when I got the brilliant idea to use mcrypt. I figured that AES is a standard so mcrypt should be able to decrypt what MySQL has encrypted as long as I have the key. That’s where my “brilliant” idea became an exercise in futility and madness. I created a test table, used a password generator to generate a sufficiently complex key, and wrote up a quick script in PHP to make sure this idea worked.

Which of course it didn’t; not that I expected it to. Any developer worth that title (not that I necessarily am yet) will tell you that most things don’t work the first time. Some pain and frustration are the payment required for results. Hoping that this was some fluke caused by my ignorance on some key part of the process as opposed to incompatible implementations of the same damn standard, I looked to my trusty pal Google to find me the answer.

After what seemed like hours of aimless wandering, I stumbled upon this gem: “MySQL AES Encrypt/Decrypt Problems With PHP MCrypt Compatibility.” Let me say that I know what you’re probably thinking: “There is no possible way it could be as simple as the key length being too long. No. Freaking. Way.” Alas my dear friends, my original key was 20 characters in length, so a measly four characters stood in between me and success!

This time it's personal

You've mocked me for the last time MySQL!

Don’t believe me? Go ahead and try it for yourself; you can take my little test script here and see the madness with your own eyes. Just change the MySQL connection and database info, and you are good to go on the test. Adding one, ten, or 100 characters to the key will cause mcrypt to dump garbage all over your screens.

One last thing to keep in mind when encrypting data in an established database: make sure your columns are large enough to support the data once it is encrypted, as the encrypted string will typically be longer than the data. You can reference MySQL‘s page for specifics or reference this handy equation to figure out what the minimum size the column should be:

16 * (trunc(string_length / 16) + 1)

If this post saves at least one developer from almost smashing their head clean through their desk, then I have done my job as a denizen of the internets.


Google Image searches never fail to entertain.

%d bloggers like this: