MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势
遇到一个需求是这样的：
需要在使用AES_ENCRYPT()函数将明文加密，存储在MySQL中，但是遇到了一些问题……
说将加密后的密文，解密取出来是NULL。
看了一下，她发过来的表结构：
再看了她通过AES_DECRYPT()函数加密了一个字符串，然后insert进去了，执行成功后，显示了一个warning：
Query OK, 1 row affected, 1 warning (0.00 sec)
（没有报错而是warning，大概是sql_mode的缘故）
此时她忽略了这个warning，再通过AES_DECRYPT()解密后，发现取出来的明文为NULL。
再回看表结构，发现其字段属性为“varchar”&&字符集是ut8，检查warning为下：
查了一下文档，看一下这两个函数的使用：
那么到底该如何存呢？
方法①：
将字段属性设置为varbinary/binary/四个blob类型，等二进制字段属性。
创建三个字段，属性分别为varbinary、binary、blob。
并将'明文1'，'text2'，'明文_text3'加密，密钥为key，存入表中。
最后取出。
当然，属性括号内的长度要取决于明文的长度，此处明文较短，故只给了16。
方法②：
将密文十六进制化，再存入varchar/char列。
此处需要用到HEX()来存入，用UNHEX()取出。
创建一个字符串属性的字段。
将'hello world'先用密钥'key2'进行AES加密，再将加密后的串通过HEX函数十六进制化。
最后先将加密后的串通过UNHEX取出，再通过AES据密钥'key2'解密：
同样，根据明文的长度不同，AES_ENCRYPT加密后的串长度也会有所变化，所以HEX后的字符串长度也会有所变化。
实际使用时，需要据业务评估出一个合理值即可。
方法③：
直接存入varchar中，不做十六进制化。
回溯到问题的一开始，将加密后的串，存到utf8字符集并且属性为varchar中，是不行的。
实际上，将字符集改成latin1就可以了：
在insert的时候也不会报warning了。
这样的方法虽然美，只需将字段字符集设置为latin1就可以了，但可能会带来隐患：
文档上写了这样的一句：
大意是，如果用方法③那样，直接将加密后的串存入char/varchar/text类型中，在做字符转换的时或空格被删除时，可能会带来潜在的影响。
所以如果一定要存在char/varchar/text中，那么还是参考方法②，十六进制化一下吧。
或者如同方法①，直接存在二进制字段中。
参考文档：
Chapter 12 Functions and Operators -12.13 Encryption and Compression Functions
网站栏目：MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势
当前URL：http://cdiso.cn/article/iecdoe.html
需要在使用AES_ENCRYPT()函数将明文加密，存储在MySQL中，但是遇到了一些问题……
说将加密后的密文，解密取出来是NULL。
看了一下，她发过来的表结构：
再看了她通过AES_DECRYPT()函数加密了一个字符串，然后insert进去了，执行成功后，显示了一个warning：
Query OK, 1 row affected, 1 warning (0.00 sec)
（没有报错而是warning，大概是sql_mode的缘故）
此时她忽略了这个warning，再通过AES_DECRYPT()解密后，发现取出来的明文为NULL。
再回看表结构，发现其字段属性为“varchar”&&字符集是ut8，检查warning为下：
-
mysql> show warnings;
-
+---------+------+------------------------------------------------------------------------+
-
| Level | Code| Message |
-
+---------+------+------------------------------------------------------------------------+
-
| Warning| 1366| Incorrect string value:'\xE3f767\x12...' for column'passwd' at row 1|
-
+---------+------+------------------------------------------------------------------------+
- 1 rowin set (0.00 sec)
查了一下文档，看一下这两个函数的使用：
-
-- 将'hello world'加密，密钥为'key'，加密后的串存在@pass中
-
mysql> SET @pass=AES_ENCRYPT('hello world', 'key');
-
Query OK, 0 rows affected(0.00 sec)
-
-
-- 看一下加密后串的长度（都为2的整数次方）
-
mysql> SELECT CHAR_LENGTH(@pass);
-
+--------------------+
-
| CHAR_LENGTH(@pass) |
-
+--------------------+
-
| 16 |
-
+--------------------+
-
1 rowin set(0.00 sec)
-
-
-- 使用AES_DECRYPT()解密
-
mysql> SELECT AES_DECRYPT(@pass, 'key');
-
+---------------------------+
-
| AES_DECRYPT(@pass, 'key') |
-
+---------------------------+
-
| hello world |
-
+---------------------------+
- 1 rowin set(0.00 sec)
那么到底该如何存呢？
方法①：
将字段属性设置为varbinary/binary/四个blob类型，等二进制字段属性。
创建三个字段，属性分别为varbinary、binary、blob。
并将'明文1'，'text2'，'明文_text3'加密，密钥为key，存入表中。
最后取出。
-
mysql> CREATE TABLE t_passwd(pass1varbinary(16), pass2binary(16), pass3 blob);
- Query OK, 0 rows affected(0.00 sec)
-
-
mysql> INSERT INTO t_passwdVALUES (AES_ENCRYPT('明文1', 'key'), AES_ENCRYPT('text2', 'key'), AES_ENCRYPT('明文_text3', 'key'));
-
Query OK, 1 row affected(0.01 sec)
-
-
mysql> SELECT AES_DECRYPT(pass1, 'key'), AES_DECRYPT(pass2, 'key'), AES_DECRYPT(pass3, 'key') FROM t_passwd;
-
+---------------------------+---------------------------+---------------------------+
-
| AES_DECRYPT(pass1, 'key') | AES_DECRYPT(pass2, 'key') | AES_DECRYPT(pass3, 'key') |
-
+---------------------------+---------------------------+---------------------------+
-
| 明文1 | text2 | 明文_text3 |
-
+---------------------------+---------------------------+---------------------------+
- 1 rowin set (0.00 sec)
当然，属性括号内的长度要取决于明文的长度，此处明文较短，故只给了16。
方法②：
将密文十六进制化，再存入varchar/char列。
此处需要用到HEX()来存入，用UNHEX()取出。
创建一个字符串属性的字段。
将'hello world'先用密钥'key2'进行AES加密，再将加密后的串通过HEX函数十六进制化。
最后先将加密后的串通过UNHEX取出，再通过AES据密钥'key2'解密：
-
mysql>CREATE TABLE t_passwd_2(pass1char(32));
-
Query OK, 0 rows affected(0.01 sec)
-
-
mysql>INSERT INTO t_passwd_2VALUES (HEX(AES_ENCRYPT('hello world', 'key2')));
-
Query OK, 1 row affected(0.00 sec)
-
-
mysql>SELECT AES_DECRYPT(UNHEX(pass1), 'key2') FROM t_passwd_2;
-
+-----------------------------------+
-
| AES_DECRYPT(UNHEX(pass1), 'key2') |
-
+-----------------------------------+
-
| hello world |
-
+-----------------------------------+
- 1 rowin set (0.00 sec)
同样，根据明文的长度不同，AES_ENCRYPT加密后的串长度也会有所变化，所以HEX后的字符串长度也会有所变化。
实际使用时，需要据业务评估出一个合理值即可。
方法③：
直接存入varchar中，不做十六进制化。
回溯到问题的一开始，将加密后的串，存到utf8字符集并且属性为varchar中，是不行的。
实际上，将字符集改成latin1就可以了：
在insert的时候也不会报warning了。
-
mysql> CREATE TABLE t_passwd_3(passvarchar(32)) CHARSET latin1;
-
Query OK, 0 rows affected(0.00 sec)
-
-
mysql> INSERT INTO t_passwd_3SELECT AES_ENCRYPT('text', 'key3');
-
Query OK, 1 row affected(0.00 sec)
-
Records: 1 Duplicates: 0 Warnings: 0
-
-
mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;
-
+---------------------------+
-
| AES_DECRYPT(pass, 'key3') |
-
+---------------------------+
-
| text |
-
+---------------------------+
- 1 rowin set (0.00 sec)
这样的方法虽然美，只需将字段字符集设置为latin1就可以了，但可能会带来隐患：
文档上写了这样的一句：
- 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://cdiso.cn/article/iecdoe.html
