MySQL, starting from 4.0.2, had AES encryption and decryption functions. They are compiled with support for pure independent block by block encryption mode (ECB), using a 128 bit key.

128 bits is plenty enough! And sufficient for everybody! And who would even want to go to the trouble of dealing with initialization vectors? At least that’s what they probably thought when introducing these functions back in 2002 in MySQL 4.0.2.

But I believe in giving people a choice. Read below on why choice is important.

Does (key) size matter ?

The biggest threat that longer keys protect against is brute force attacks. Fast forward 12 years since the introduction of these great SQL functions.  Brute-forcing shorter keys doesn’t sound as impossible as it previously did. Especially with modern day GPUs 🙂

Thus today NSA considers 128 bit keys good enough only for data with SECRET designation. For TOP SECRET though, it requires 256 bit keys.

The European Network of Excellence in Cryptology report also recommends 128 bit keys for long term protection, but says 256 bit keys are the only good protection against quantum computers.

Note that bigger isn’t always better! There are known weaknesses that apply to 192 and 256 bit keys due to key schedule if it’s not implemented with enough rounds, as Bruce Schneider points out.

So no (key) size seems to fit every situation; and I believe that people should not have to recompile MySQL in order to change the key size. Finally, since the SSL libraries used provide support for all these key sizes, I was really running out of arguments on “why not” to support 192 and 256 bit key sizes.

What is this block cipher mode about and why should I care ?

AES is a block cipher that always operates on 128 bit blocks, no matter the key length. But we typically need to encrypt more than 16 bytes . So we have to iterate. We take the first 16 bytes from the plain data block, encrypt them, store the result into the encrypted block, get the next 16 bytes and repeat the process until we’re out of plain data.

This method above is called Electronic Code Book (ECB).

But it suffers one serious flaw. If we happen to have repeating blocks of 16 bytes in our plain data block we will get repeating blocks of 16 bytes in our encrypted output too.

Wikipedia famously demonstrates this with a set of pictures:

Original image	Encrypted using ECB image	Modes other than ECB

As you can see the Tux is still there even after you try hiding it.

What is done differently in the last picture is that we factor in the result of encrypting the previous block into encrypting the next one. Obviously we need a little extra info to bind into the first block. This is called an initialization vector. It has to be random. And it needs to be available when decrypting the data.

So how did we factor all this into the MySQL AES functions ?

We decided to keep them backward compatible. This meant no new required arguments to AES_ENCRYPT() and AES_DECRYPT(). And that by default they still need to do 128 bits ECB AES.

So I decided to introduce a new system variable, block_encryption_mode. Why not aes_encryption_mode? Well, because I like to think ahead :). It takes a value in the <cipher>-<key length>-<block cipher> format (currently “aes” is the only valid/accepted cipher value). “key-length” can be 128, 192, or 256. Lastly, “block cipher” depends on what the underlying SSL library supports. And the default is … yes, you’ve guessed it : aes-128-ecb 🙂

Obviously I needed to pass the initialization vector to the AES_ENCRYPT and AES_DECRYPT functions. So I added a 3rd optional argument for that. ECB won’t need it, so your current code will still work.

Other goodies

I needed a come up with a way to generate the initialization vector so it’s a cryptographically strong random value. Using a weak random generator is one of the most popular side channel attacks.

And what better source of cryptographically strong random data than the SSL library itself?

Enter the new function RANDOM_BYTES() that taps into it. It’s an obvious way to generate the initialization vector. I decided not to simply bundle it with the AES functions though, so that people can get creative in how they generate and use their cryptographic data.

When can I get this ?

This was initially implemented in the Nolana DMR (5.7.4), but since I’ve been so careful not to break existing functionality, we also back-ported it to 5.6.17 so that  you can get this functionality even sooner.

Be careful though! The fact that the output of AES_ENCRYPT() and AES_DECRYPT() now depends on something more than their direct arguments makes them unsafe for statement based replication and query cache. As noted, however, the default behavior of these functions is entirely unchanged.