189 8069 5689

MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势

遇到一个需求是这样的：
需要在使用AES_ENCRYPT()函数将明文加密，存储在MySQL中，但是遇到了一些问题……

说将加密后的密文，解密取出来是NULL。

看了一下，她发过来的表结构：

MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势

再看了她通过AES_DECRYPT()函数加密了一个字符串，然后insert进去了，执行成功后，显示了一个warning：
Query OK, 1 row affected, 1 warning (0.00 sec)
（没有报错而是warning，大概是sql_mode的缘故）

此时她忽略了这个warning，再通过AES_DECRYPT()解密后，发现取出来的明文为NULL。

再回看表结构，发现其字段属性为“varchar”&&字符集是ut8，检查warning为下：
  1. mysql> show warnings;
  2. +---------+------+------------------------------------------------------------------------+
  3. | Level   | Code| Message                                                                |
  4. +---------+------+------------------------------------------------------------------------+
  5. | Warning| 1366| Incorrect string value:'\xE3f767\x12...' for column'passwd' at row 1|
  6. +---------+------+------------------------------------------------------------------------+
  7. 1 rowin set (0.00 sec)

查了一下文档，看一下这两个函数的使用：
  1. -- 将'hello world'加密，密钥为'key'，加密后的串存在@pass中
  2. mysql> SET @pass=AES_ENCRYPT('hello world', 'key');
  3. Query OK, 0 rows affected(0.00 sec)

  5. -- 看一下加密后串的长度（都为2的整数次方）
  6. mysql> SELECT CHAR_LENGTH(@pass);
  7. +--------------------+
  8. | CHAR_LENGTH(@pass) |
  9. +--------------------+
  10. | 16                |
  11. +--------------------+
  12. 1 rowin set(0.00 sec)

  14. -- 使用AES_DECRYPT()解密
  15. mysql> SELECT AES_DECRYPT(@pass, 'key');
  16. +---------------------------+
  17. | AES_DECRYPT(@pass, 'key') |
  18. +---------------------------+
  19. | hello world              |
  20. +---------------------------+
  21. 1 rowin set(0.00 sec)

那么到底该如何存呢？


方法①： 
将字段属性设置为varbinary/binary/四个blob类型，等二进制字段属性。

创建三个字段，属性分别为varbinary、binary、blob。
并将'明文1'，'text2'，'明文_text3'加密，密钥为key，存入表中。
最后取出。
  1. mysql> CREATE TABLE t_passwd(pass1varbinary(16), pass2binary(16), pass3 blob);
  2. Query OK, 0 rows affected(0.00 sec)

  4. mysql> INSERT INTO t_passwdVALUES (AES_ENCRYPT('明文1', 'key'), AES_ENCRYPT('text2', 'key'), AES_ENCRYPT('明文_text3', 'key'));
  5. Query OK, 1 row affected(0.01 sec)

  7. mysql> SELECT AES_DECRYPT(pass1, 'key'), AES_DECRYPT(pass2, 'key'), AES_DECRYPT(pass3, 'key') FROM t_passwd;
  8. +---------------------------+---------------------------+---------------------------+
  9. | AES_DECRYPT(pass1, 'key') | AES_DECRYPT(pass2, 'key')  | AES_DECRYPT(pass3, 'key') |
  10. +---------------------------+---------------------------+---------------------------+
  11. | 明文1                    | text2                    | 明文_text3                |
  12. +---------------------------+---------------------------+---------------------------+
  13. 1 rowin set (0.00 sec)

当然，属性括号内的长度要取决于明文的长度，此处明文较短，故只给了16。



方法②：
将密文十六进制化，再存入varchar/char列。
此处需要用到HEX()来存入，用UNHEX()取出。

创建一个字符串属性的字段。
将'hello world'先用密钥'key2'进行AES加密，再将加密后的串通过HEX函数十六进制化。
最后先将加密后的串通过UNHEX取出，再通过AES据密钥'key2'解密：
  1. mysql>CREATE TABLE t_passwd_2(pass1char(32));
  2. Query OK, 0 rows affected(0.01 sec)

  4. mysql>INSERT INTO t_passwd_2VALUES (HEX(AES_ENCRYPT('hello world', 'key2')));
  5. Query OK, 1 row affected(0.00 sec)

  7. mysql>SELECT AES_DECRYPT(UNHEX(pass1), 'key2') FROM t_passwd_2;
  8. +-----------------------------------+
  9. | AES_DECRYPT(UNHEX(pass1), 'key2') |
  10. +-----------------------------------+
  11. | hello world                      |
  12. +-----------------------------------+
  13. 1 rowin set (0.00 sec)

同样，根据明文的长度不同，AES_ENCRYPT加密后的串长度也会有所变化，所以HEX后的字符串长度也会有所变化。
实际使用时，需要据业务评估出一个合理值即可。
 

方法③：
直接存入varchar中，不做十六进制化。

回溯到问题的一开始，将加密后的串，存到utf8字符集并且属性为varchar中，是不行的。

实际上，将字符集改成latin1就可以了：
在insert的时候也不会报warning了。
  1. mysql> CREATE TABLE t_passwd_3(passvarchar(32)) CHARSET latin1;
  2. Query OK, 0 rows affected(0.00 sec)

  4. mysql> INSERT INTO t_passwd_3SELECT AES_ENCRYPT('text', 'key3');
  5. Query OK, 1 row affected(0.00 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;
  9. +---------------------------+
  10. | AES_DECRYPT(pass, 'key3') |
  11. +---------------------------+
  12. | text                      |
  13. +---------------------------+
  14. 1 rowin set (0.00 sec)

这样的方法虽然美，只需将字段字符集设置为latin1就可以了，但可能会带来隐患：

文档上写了这样的一句：

  1. Many encryptionand compression functions return stringsfor which the result might contain arbitrarybyte values. If you want to store these results, use a column with a VARBINARYor BLOB binary string datatype. This will avoid potential problems with trailing space removalor character set conversion that would change data values, such as may occurif you use a nonbinary string datatype (CHAR, VARCHAR, TEXT).

大意是，如果用方法③那样，直接将加密后的串存入char/varchar/text类型中，在做字符转换的时或空格被删除时，可能会带来潜在的影响。

所以如果一定要存在char/varchar/text中，那么还是参考方法②，十六进制化一下吧。
或者如同方法①，直接存在二进制字段中。


参考文档：
Chapter 12 Functions and Operators -12.13 Encryption and Compression Functions


本文题目：MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势
URL网址：http://cdxtjz.cn/article/iecdoe.html

其他资讯