MySQL 5.6.17 – now with better encryption

Joro wrote recently about MySQL 5.6.17‘s new support for AES-256 encryption, and it’s a great improvement for people need to encrypt their data at rest.  The new session block_encryption_mode variable controls what variant of AES is used, and for backwards compatibility, the default behavior remains 128-bit key length with ECB block cipher mode.  If you’re happy with that level of encryption, nothing changes – your existing code will work the same on 5.6.17 as it has on earlier versions (note that users of statement-based replication will experience new warnings).  There are good examples of how to use the new functionality in the updated public test cases, but this blog post is intended to help orient users to the new options as well.

block_encryption_mode

The block_encryption_mode session variable defines which which cipher, key length and block cipher mode will be used in the encryption/decryption routine.  The cipher in the current implementation is always “aes”, and the key length can be 128, 192 or 256.  The block cipher mode is well-described in Joro’s post, and the documentation lists the possible values, which vary based on underlying SSL library used:

  • For OpenSSL, permitted mode values are: ECB, CBC, CFB1, CFB8, CFB128, OFB
  • For yaSSL, permitted mode values are: ECB, CBC

A simple example of changing the encryption key length to use 256-bit keys instead of 128-bit keys is shown below:

mysql> SELECT @@session.block_encryption_mode;
+---------------------------------+
| @@session.block_encryption_mode |
+---------------------------------+
| aes-128-ecb                     |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key'));
+----------------------------------+
| HEX(AES_ENCRYPT('test', 'key'))  |
+----------------------------------+
| 9E9CE44CD9DF2B201F51947E03BCCBE2 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key'));
+----------------------------------+
| HEX(AES_ENCRYPT('test', 'key'))  |
+----------------------------------+
| 9E9CE44CD9DF2B201F51947E03BCCBE2 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'), 'key');
+---------------------------------------------------------------+
| AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'), 'key') |
+---------------------------------------------------------------+
| test                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.block_encryption_mode = 'aes-256-ecb';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key'));
+----------------------------------+
| HEX(AES_ENCRYPT('test', 'key'))  |
+----------------------------------+
| F9F124D037CDE3DD44084A9189D727A0 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('F9F124D037CDE3DD44084A9189D727A0'), 'key');
+---------------------------------------------------------------+
| AES_DECRYPT(UNHEX('F9F124D037CDE3DD44084A9189D727A0'), 'key') |
+---------------------------------------------------------------+
| test                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

Content encrypted with a 128-bit key cannot be decrypted when the block_encryption_mode is set to an incompatible key length (or block cipher mode). The following demonstrates this, using the 256-bit key encrypted value in trying to decrypt while block_encryption_mode is set to use 128-bit keys, and vice-versa:

mysql> SET @@session.block_encryption_mode = 'aes-128-ecb';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('F9F124D037CDE3DD44084A9189D727A0'), 'key');
+---------------------------------------------------------------+
| AES_DECRYPT(UNHEX('F9F124D037CDE3DD44084A9189D727A0'), 'key') |
+---------------------------------------------------------------+
| NULL                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.block_encryption_mode = 'aes-256-ecb';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'), 'key');
+---------------------------------------------------------------+
| AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'), 'key') |
+---------------------------------------------------------------+
| NULL                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

That’s important in that it implies you’ll need to be intentional about how data is encrypted in your system.  You may want to convert existing AES128-encrypted data to AES256 before changing the block_encryption_mode.

Block cipher mode

Joro does a good job explaining block cipher mode options.  All but ECB mode require an additional argument in AES_ENCRYPT() and AES_DECRYPT() – the initialization vector.   Using AES_ENCRYPT() or AES_DECRYPT() with block_encryption_mode set to a block cipher other than ECB will produce an error if the IV is not provided:

mysql> SET @@session.block_encryption_mode = 'aes-256-cbc';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'aes_encrypt'
mysql> SELECT HEX(AES_ENCRYPT('test', 'key', RANDOM_BYTES(16)));
+---------------------------------------------------+
| HEX(AES_ENCRYPT('test', 'key', RANDOM_BYTES(16))) |
+---------------------------------------------------+
| 2EFBA8708925C1DF8B661E57938FAE5E                  |
+---------------------------------------------------+
1 row in set (0.00 sec)

Note that the IV isn’t itself stored in the resulting encrypted output – it’s an artifact that you’ll have to track separately to get the decrypted values back:

mysql> SET @iv = RANDOM_BYTES(16);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key', @iv));
+--------------------------------------+
| HEX(AES_ENCRYPT('test', 'key', @iv)) |
+--------------------------------------+
| 650CE9E699ECA922E09E80CEBE51BFC7     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('650CE9E699ECA922E09E80CEBE51BFC7'), 'key', @iv);
+--------------------------------------------------------------------+
| AES_DECRYPT(UNHEX('650CE9E699ECA922E09E80CEBE51BFC7'), 'key', @iv) |
+--------------------------------------------------------------------+
| test                                                               |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Practically, this means you’ll likely want to add a column to tables to store the IV used for each row.

Converting existing data

If you have existing data encrypted using AES128, you may want to convert it to use AES256.  That’s can be a little tricky, since you have to decrypt using aes-128-ecb and re-encrypt using a different block_encryption_mode (and, potentially, an IV).  Because the block_encryption_mode control is set at the session level, you’ll need some tools to do an in-place conversion (and nobody wants to decrypt their sensitive data to a temporary staging area).  The following SQL stored function makes this possible:

DROP FUNCTION IF EXISTS aes_encrypt_with_blockmode;
DELIMITER ||
CREATE FUNCTION aes_encrypt_with_blockmode(
 str BLOB,
 enc_key TEXT,
 block_mode VARCHAR(128),
 iv TINYBLOB
) RETURNS BLOB
  DETERMINISTIC
BEGIN
 SET @old_block_mode = @@session.block_encryption_mode;
 SET @@session.block_encryption_mode = block_mode;
 SET @ret = AES_ENCRYPT(str, enc_key, iv);
 SET @@session.block_encryption_mode = @old_block_mode;
 RETURN @ret;
END||
DELIMITER ;

DROP FUNCTION IF EXISTS aes_decrypt_with_blockmode;
DELIMITER ||
CREATE FUNCTION aes_decrypt_with_blockmode (
 str BLOB,
 enc_key TEXT,
 block_mode VARCHAR(128),
 iv TINYBLOB
) RETURNS BLOB
  DETERMINISTIC
BEGIN
 SET @old_block_mode = @@session.block_encryption_mode;
 SET @@session.block_encryption_mode = block_mode;
 SET @ret = AES_DECRYPT(str, enc_key, iv);
 SET @@session.block_encryption_mode = @old_block_mode;
 RETURN @ret;
END||
DELIMITER ;

Using these functions to toggle the block_encryption_mode variable, we can decrypt data that was encrypted using some other block_encryption_mode setting, or encrypt it with something other than the current session variable state:

mysql> SET @@session.block_encryption_mode = 'aes-128-ecb';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @t = AES_ENCRYPT('test', 'key');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(@t);
+----------------------------------+
| HEX(@t)                          |
+----------------------------------+
| 9E9CE44CD9DF2B201F51947E03BCCBE2 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.block_encryption_mode = 'aes-256-ecb';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT AES_DECRYPT(@t, 'key');
+------------------------+
| AES_DECRYPT(@t, 'key') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT_WITH_BLOCKMODE(@t, 'key', 'aes-128-ecb', NULL);
+------------------------------------------------------------+
| AES_DECRYPT_WITH_BLOCKMODE(@t, 'key', 'aes-128-ecb', NULL) |
+------------------------------------------------------------+
| test                                                       |
+------------------------------------------------------------+
1 row in set (0.00 sec)

This enables in-place conversion, including when you want to use non-ECB block cipher modes. An example of an in-place conversion is shown below:

mysql>  DROP TABLE IF EXISTS aes_test;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE aes_test (
    ->  id INT NOT NULL PRIMARY KEY,
    ->  enc_val BLOB
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.26 sec)

mysql>
mysql> SET @k = 'test-key';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # Use legacy AES128 with ECB:
mysql> SET @@session.block_encryption_mode = 'aes-128-ecb';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO aes_test VALUES
    ->  (1, AES_ENCRYPT('test1', @k)),
    ->  (2, AES_ENCRYPT('test2', @k)),
    ->  (3, AES_ENCRYPT('test3', @k)),
    ->  (4, AES_ENCRYPT('test4', @k)),
    ->  (5, AES_ENCRYPT('test5', @k));
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT id, AES_DECRYPT(enc_val, @k) val, HEX(enc_val) hex_val FROM aes_test;
+----+-------+----------------------------------+
| id | val   | hex_val                          |
+----+-------+----------------------------------+
|  1 | test1 | FB5F7EB4C31BBB92EBEFE62855EACC61 |
|  2 | test2 | A24D421EFB03E378B9806C6974B44C9E |
|  3 | test3 | 134B0C23E5E6F12EFFCFB949ED14F264 |
|  4 | test4 | F2E069C6FE09F6655B0545626F05FDBC |
|  5 | test5 | D4385180F4927DB7117661F3C482F1EA |
+----+-------+----------------------------------+
5 rows in set (0.00 sec)

mysql>
mysql> # Add a column for the IV:
mysql> ALTER TABLE aes_test ADD COLUMN iv BINARY(16);
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> # Populate the IV:
mysql> UPDATE aes_test SET iv = RANDOM_BYTES(16) WHERE iv IS NULL;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql>
mysql> # Do the conversion:
mysql> UPDATE aes_test
    ->  SET enc_val = AES_ENCRYPT_WITH_BLOCKMODE(
    ->    AES_DECRYPT_WITH_BLOCKMODE(enc_val, @k, 'aes-128-ecb', NULL),
    ->    @k,
    ->    'aes-256-cbc',
    ->    iv
    ->  );
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql>
mysql> # confirm the update:
mysql>
mysql> SET @@session.block_encryption_mode = 'aes-256-cbc';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, AES_DECRYPT(enc_val, @k, iv) val, HEX(enc_val) hex_val FROM ae
s_test;
+----+-------+----------------------------------+
| id | val   | hex_val                          |
+----+-------+----------------------------------+
|  1 | test1 | CC467FE6A04B2E039343BB857D002D83 |
|  2 | test2 | 5C5040BAF0DB835CEC53F0FA344D50D4 |
|  3 | test3 | CB47A06C96D922CA3AB1A5453AA2F0ED |
|  4 | test4 | 83358D068965D825F87C6860A5CC11F7 |
|  5 | test5 | 4404AA118AEC7979E1CDF1D2116BCFC1 |
+----+-------+----------------------------------+
5 rows in set (0.00 sec)

mysql>

Note on performance

The above SQL stored function is meant for one-time conversion processes, not ongoing production deployment.  The performance of the stored function is not fast.

7 thoughts on “MySQL 5.6.17 – now with better encryption

  1. how can i set the block_encryption_mode to cbc permanently? and how to set the aes-256-cbc permanently in my database.im using xampp..plz help me

    1. Hi Neil,

      The block_encryption_mode variable can be set using the MySQL Server configuration file. Including the following in my configuration file ensure aes-256-cbc is the default block mode:

      [mysqld]
      ...
      block_encryption_mode=aes-256-cbc
      

      Note that this sets the default for each session, but each session can override the block_encryption_mode setting for their own use – it does not enforce use of this specific mode.

  2. SELECT HEX(AES_ENCRYPT(‘test’, ‘key’));

    Result:

    Error Code: 1582. Incorrect parameter count in the call to native function ‘aes_encrypt’

    1. Hi Jason,

      That error means that the block_encryption_mode session variable has been set to a mode requiring an IV as the third argument. See the section above under the “Block cipher mode” header, where this exact condition is shown.

    1. Hi Michael,

      Good question – you’re basically asking whether the IV should be considered secret material like the key. It serves a different purpose, and it’s not necessary to keep the IV secret. If the key is kept secret, knowing just the IV won’t help attackers decrypt the encrypted content. So what’s the value of the IV? Used properly, it establishes semantic security (quoting Wikipedia):

      … knowledge of the ciphertext (and length) of some unknown message does not reveal any additional information on the message that can be feasibly extracted.

      What does this look like in practice? Here’s an example – with the same (or no) IV, encrypted values can be compared to see if they are the same:


      mysql> SELECT HEX(AES_ENCRYPT('test', 'key', @iv));
      +--------------------------------------+
      | HEX(AES_ENCRYPT('test', 'key', @iv)) |
      +--------------------------------------+
      | BD4F99D6AA34EECD348DCB91ABCB8DB3 |
      +--------------------------------------+
      1 row in set (0.00 sec)

      mysql> SELECT HEX(AES_ENCRYPT('test', 'key', @iv));
      +--------------------------------------+
      | HEX(AES_ENCRYPT('test', 'key', @iv)) |
      +--------------------------------------+
      | BD4F99D6AA34EECD348DCB91ABCB8DB3 |
      +--------------------------------------+
      1 row in set (0.00 sec)

      Using the same (or no) IV, I can compare ciphertext values and deduce which plaintexts are the same. This is the principle which drives rainbow tables. Although use of rainbow tables to find the plaintext equivalents of ciphertext is made impractical by use of an encryption key, when the comparing ciphertext reveals which row values are equal, information about the plaintext values is leaked.

      Say you are encrypting web account passwords and using no IV, and I have access to the encrypted values. I observe that 25 unique ciphertexts are used by 30% of your web users. I have no reason to believe your web users are a unique population, so I Google “most common passwords” and get a nice list of the top 25 passwords for 2014. Yes, many of these stupid passwords would be quickly broken in any brute-force attempt, but the point remains: People with access to just the ciphertext (and IV) should not be able to infer anything about the corresponding plaintext. Using an IV helps prevent that. Here is an example where the same input as above produces two very different ciphertexts because the IV is random:


      mysql> SELECT HEX(AES_ENCRYPT('test', 'key',
      -> RANDOM_BYTES(16))) AS ciphertext;
      +----------------------------------+
      | ciphertext |
      +----------------------------------+
      | 89EDB3AB356D0242A54B9EADC278FBC5 |
      +----------------------------------+
      1 row in set (0.00 sec)

      mysql> SELECT HEX(AES_ENCRYPT('test', 'key',
      -> RANDOM_BYTES(16))) AS ciphertext;
      +----------------------------------+
      | ciphertext |
      +----------------------------------+
      | 6182350E55E51CA7C7F8B317D8714646 |
      +----------------------------------+
      1 row in set (0.00 sec)

      I hope that helps!

Leave a Reply to Todd Farmer Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.