Understand and satisfy your AES encryption needs with 5.6.17

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.

17 thoughts on “Understand and satisfy your AES encryption needs with 5.6.17

    1. The underlying crypto library depends on how you build your MySQL server.
      We build our enterprise binaries with OpenSSL. For the community binaries we can’t use OpenSSL because of its license not being compatible with GPL. So we build our community binaries with (the built in) yaSSL library.

      The GCM block mode is not currently supported by any other encryption function.

  1. Is the bug regarding the appended NULL encoded block if the encrypted value is a modulous of the block size? I reported this bug back on 2005/2006 and its now no longer showing up in the public bugs database. I can only assume it was flagged for a security item and is no longer publicily viewable.

  2. I have tried to change the encryption mode in MySQL (via phpmyadmin) using:

    SET block_encryption_mode = ‘aes-256-cbc’;

    (and b/c this didn’t work, I used something else I found: SET @@session.block_encryption_mode = ‘aes-256-cbc’ – which didn’t work either)

    I have also tried to permanently set the mode in mysql.cnf under [mysqld] using:


    This didn’t work either.

    My question is 1) why didn’t these methods work? and 2) can you please help?

    I am using PDO with MySQL/php and just cannot find practical help to understand how to change mysql’s encryption mode.

    1. The variable seems to be having an effect in a mysql console (see below)

      I’m guessing the problem is that you’re trying to set a session variable (both “SET @@session.variable” and “SET @@variable” do the same) that’s valid for your phpmyadmin session, but not for the session you’re executing the actual AES_ENCRYPT/AES_DECRYPT calls.
      If blocking mode is properly set to anything else than ecb the function will require the initialization vector as demonstrated below.

      Note also that the example below does not preserve the initialization vector. And it needs to do that to decrypt the data. See http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_aes-decrypt for a slightly more practical example that preserves into a session variable.

      mysql> select @@global.block_encryption_mode;
      select @@global.block_encryption_mode

      | @@global.block_encryption_mode |
      | aes-128-ecb |
      1 row in set (0.00 sec)
      mysql> select hex(aes_encrypt('secret','thekey'));
      select hex(aes_encrypt('secret','thekey'))

      | hex(aes_encrypt('secret','thekey')) |
      | B2F6CB3986B405678CEE94A33366EFCA |
      1 row in set (0.00 sec)

      mysql> set @@session.block_encryption_mode='aes-256-cbc';
      set @@session.block_encryption_mode='aes-256-cbc'

      Query OK, 0 rows affected (0.00 sec)

      mysql> select hex(aes_encrypt('secret','thekey'));
      select hex(aes_encrypt('secret','thekey'))

      ERROR 1582 (42000): Incorrect parameter count in the call to native function 'aes_encrypt'
      mysql> select hex(aes_encrypt('secret','thekey',random_bytes(16)));
      select hex(aes_encrypt('secret','thekey',random_bytes(16)))

      | hex(aes_encrypt('secret','thekey',random_bytes(16))) |
      | C473DDB299AF61A222195D186DD198C6 |
      1 row in set (0.00 sec)

      1. Thanks for replying and offering suggestions to get this working for me. I appreciate you taking time to help, but I think I need to be a little more descriptive about this:

        I have set up apache/mysql/php on my mac OS X laptop, and created my own SSL cert to test my web application on my localhost. Everything is working, but perhaps b/c of the way I set up the environment, I cannot use the console for mysql. I get the following error:

        ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

        (I’m not as skilled as I’d like to be when it comes to sys admin stuff so I did my best…)

        So, b/c of the above, I am using phpmyadmin to enter the sql statements. My mysql version is mysql-5.6.22-osx10.8-x86_64. Since you mentioned it would be available in 5.6.17, I assume its available for my version.

        When I enter in phpmyadmin the sql statement to set the encryption mode, it does not change the mode. And perhaps b/c I can’t start/stop/restart mysql using the console (or mac os x preferences – it just doesn’t work), the change to mysql.cnf is not taking place (although I did restart my computer hoping that would restart mysql to use the new encryption mode – it didn’t ).

        I have researched quite a bit to understand how to apply this awesome new way of using different encryption modes with AES_ENCRYPT and AES_DECRYPT – adding the extra IV argument (and storing the randomized IV in a field for each database row and using that to decrypt the row data). I appreciate your work on making this available – just hoping I can use it!


Leave a Reply