Tales from the (PHP and Perl) Crypt - AES Encryption in MySQL

I was looking for a way to share encrypted information between two systems where a table in MySQL was the integration point.

The one system is based on php while the other component is a perl daemon.

Let's get cryptic

My first stab at this was a perl based solution using the Crypt::CBC and Crypt::Blowfish libraries plus a shared secret/key. This meant I had to develop a perl script which I called from php to do the encryption which is a rather inelegant solution.

At first I could not find the right libs in php to get this done but later stumbled upon the Mcrypt suit off php and MCrypt perl functions that allow you to do encryption between the two different subsystems.

Unfortunately this means you have double the amount of hassle when it comes to updates and ensuring things Just Work™.

Move it back to the source
Some more checking brought me to the MySQL AES encryption functions that are built into MySQL. They provide the best cryptographic algorithms MySQL currently has to offer and are pretty respectable from a academic encryption perspective.

This means en/decryption is dealt with at one integration point across all languages involved which is much more elegant.

Tales from the Crypt
The MySQL AES encryption functions allow you to en/decrypt data quite easily. To encrypt a string you simply issue the following, assuming your shared secret is lesser-spotted-mountain-squid:

mysql> INSERT INTO test_table (test_column) VALUES(AES_ENCRYPT('this is a super-secret message', 'lesser-spotted-mountain-squid'));
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test_table;
+----------------------------------+
| test_column                      |
+----------------------------------+
| Aÿ„1
ý#ôärO™é=:Žï   ¼Ñ†kWA |
+----------------------------------+
1 row in set (0.00 sec)

Et voila!

One thing you need to keep in mind is that the field you want to store your encrypted data in must be a MySQL BLOB data type.

Sucking our super secret string back out into a usable form is as simple as:

mysql> SELECT AES_DECRYPT(test_column, 'lesser-spotted-mountain-squid') AS top_secret FROM test_table;
+--------------------------------+
| top_secret                     |
+--------------------------------+
| this is a super-secret message |
+--------------------------------+
1 row in set (0.00 sec)

The security lesson
This is rather obvious but your security is only as strong as the weakest link in the chain. In this specific case I did not want to have clear text data in the db and achieved that amicably.

Because my secret is in clear text in two different systems I am rather exposed if those systems are not as secure as they could be. Lucky for my they are pretty much locked away from daylight so I'm not too concerned.