Notes on the AES encryption in MySQL

Oracle has improved the AES encryption/decryption functions in MySQL 5.6.17. They improved it a lot and posted a blog which explains all the details.

If you would like to know more about encryption there are two resources I would recommend:

• The Code Book by Simon Singh. This is about the history of cryptography, but it also includes a lot of information about crypto which is currently in use. This is also a very entertaining read.
• Crypto 101, a free/opensource book which gives a intro to crypto. The webpage also has a video of the talk on which the book is based.

And if you're going to use the AES encryption functions in MySQL there are some things you should consider:

• Connections with a UNIX socket (or shared memory on Windows) should be safe. Local TCP/IP connections are also safe in most circumstances.
• Remote connections which use the AES encryption should use a protected connection (SSL, VPN, etc). Otherwise someone might be able to sniff the network traffice which contains your encryption key.
• Replication traffic should also be protected (especially if you're using statement based replication. Mixed or row based might be safer).
• There is a trick to prevent the key to be logged:
• SET @encrypted := AES_ENCRYPT('foo', 'bar');
• INSERT INTO t1(c1) VALUES(@encrypted);
• MySQL 5.6 prevents logging of passwords, but this doesn't include the AES functions (yet). (Bug #72158). So be careful with your logging.
• It is possible to use the AES_DECRYPT in a view definition, but then your probably storing your key with your data, which defeats the purpose. The same is true for storing your key in a stored procedure, function or a virtual column in case of MariaDB.

And there are alternatives to using encryption within MySQL: Your application could encrypt the data before it's sent over the network. And depending on why you need encryption it might also be possible to encrypt the whole data directory. This can be achieved with LUKS or eCryptFS if you're on Linux or ZFS if you're on Solaris. On Windows you might use the EFS feature of NTFS or use Bitlocker.

The AES functions in MySQL are a good solution if you only want to encrypt specific data (which is often the case) or if you need to search on the decrypted data (WHERE AES_DECRYPT(..)='foo')