MySQL使用AES_ENCRYPT()/AES_DECRYPT()加解密的正確姿勢

神諭丶發表於2017-07-19
MySql解密
遇到一個需求是這樣的：
需要在使用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為下：
  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 row in 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 row in 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 row in set (0.00 sec)

那麼到底該如何存呢？


方法①： 
將欄位屬性設定為varbinary/binary/四個blob型別，等二進位制欄位屬性。

建立三個欄位，屬性分別為varbinary、binary、blob。
並將'明文1'，'text2'，'明文_text3'加密，金鑰為key，存入表中。
最後取出。
  1. mysql> CREATE TABLE t_passwd (pass1 varbinary(16), pass2 binary(16), pass3 blob);
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> INSERT INTO t_passwd VALUES (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 row in set (0.00 sec)

當然，屬性括號內的長度要取決於明文的長度，此處明文較短，故只給了16。



方法②：
將密文十六進位制化，再存入varchar/char列。
此處需要用到HEX()來存入，用UNHEX()取出。

建立一個字串屬性的欄位。
將'hello world'先用金鑰'key2'進行AES加密，再將加密後的串透過HEX函式十六進位制化。
最後先將加密後的串透過UNHEX取出，再透過AES據金鑰'key2'解密：
  1. mysql> CREATE TABLE t_passwd_2(pass1 char(32));
  2. Query OK, 0 rows affected (0.01 sec)

  4. mysql> INSERT INTO t_passwd_2 VALUES (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 row in set (0.00 sec)

同樣，根據明文的長度不同，AES_ENCRYPT加密後的串長度也會有所變化，所以HEX後的字串長度也會有所變化。
實際使用時，需要據業務評估出一個合理值即可。
 

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

回溯到問題的一開始，將加密後的串，存到utf8字符集並且屬性為varchar中，是不行的。

實際上，將字符集改成latin1就可以了：
在insert的時候也不會報warning了。
  1. mysql> CREATE TABLE t_passwd_3(pass varchar(32)) CHARSET latin1;
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> INSERT INTO t_passwd_3 SELECT 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 row in set (0.00 sec)

這樣的方法雖然美，只需將欄位字符集設定為latin1就可以了，但可能會帶來隱患：

文件上寫了這樣的一句：

  1. Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

大意是，如果用方法③那樣，直接將加密後的串存入char/varchar/text型別中，在做字元轉換的時或空格被刪除時，可能會帶來潛在的影響。

所以如果一定要存在char/varchar/text中，那麼還是參考方法②，十六進位制化一下吧。
或者如同方法①，直接存在二進位制欄位中。


參考文件：
Chapter 12 Functions and Operators - 12.13 Encryption and Compression Functions

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2142305/，如需轉載，請註明出處，否則將追究法律責任。

相關文章