MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正确姿势-成都快上网建站

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)

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

  12. -- 使用AES_DECRYPT()解密
  13. mysql> SELECT AES_DECRYPT(@pass, 'key');
  14. +---------------------------+
  15. | AES_DECRYPT(@pass, 'key') |
  16. +---------------------------+
  17. | hello world              |
  18. +---------------------------+
  19. 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)

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

  5. mysql> SELECT AES_DECRYPT(pass1, 'key'), AES_DECRYPT(pass2, 'key'), AES_DECRYPT(pass3, 'key') FROM t_passwd;
  6. +---------------------------+---------------------------+---------------------------+
  7. | AES_DECRYPT(pass1, 'key') | AES_DECRYPT(pass2, 'key')  | AES_DECRYPT(pass3, 'key') |
  8. +---------------------------+---------------------------+---------------------------+
  9. | 明文1                    | text2                    | 明文_text3                |
  10. +---------------------------+---------------------------+---------------------------+
  11. 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)

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

  5. mysql>SELECT AES_DECRYPT(UNHEX(pass1), 'key2') FROM t_passwd_2;
  6. +-----------------------------------+
  7. | AES_DECRYPT(UNHEX(pass1), 'key2') |
  8. +-----------------------------------+
  9. | hello world                      |
  10. +-----------------------------------+
  11. 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)

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

  6. mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;
  7. +---------------------------+
  8. | AES_DECRYPT(pass, 'key3') |
  9. +---------------------------+
  10. | text                      |
  11. +---------------------------+
  12. 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://kswjz.com/article/iecdoe.html
扫二维码与项目经理沟通

我们在微信上24小时期待你的声音

解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流