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 documentationlists 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.
网友评论