遇到一个需求是这样的:
需要在使用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://kswjz.com/article/iecdoe.html