前言
最近在工作中遇到一個(gè)需求是這樣的：需要在使用AES_ENCRYPT()函數(shù)將明文加密，存儲(chǔ)在MySQL中，但是遇到了一些問(wèn)題……下面就來(lái)詳細(xì)介紹下。
說(shuō)將加密后的密文，解密取出來(lái)是NULL。
看了一下，她發(fā)過(guò)來(lái)的表結(jié)構(gòu)：
再看了她通過(guò)AES_DECRYPT()函數(shù)加密了一個(gè)字符串，然后insert進(jìn)去了，執(zhí)行成功后，顯示了一個(gè)warning：
Query OK, 1 row affected, 1 warning (0.00 sec)
（沒(méi)有報(bào)錯(cuò)而是warning，大概是sql_mode的緣故）
此時(shí)她忽略了這個(gè)warning，再通過(guò)AES_DECRYPT()解密后，發(fā)現(xiàn)取出來(lái)的明文為NULL。
再回看表結(jié)構(gòu)，發(fā)現(xiàn)其字段屬性為“varchar” 字符集是ut8，檢查warning為下：
mysql> show warnings;
+---------+------+------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE3f767\x12...' for column 'passwd' at row 1 |
+---------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
查了一下文檔，看一下這兩個(gè)函數(shù)的使用：
-- 將'hello world'加密，密鑰為'key'，加密后的串存在@pass中
mysql> SET @pass=AES_ENCRYPT('hello world', 'key');
Query OK, 0 rows affected (0.00 sec)
-- 看一下加密后串的長(zhǎng)度（都為2的整數(shù)次方）
mysql> SELECT CHAR_LENGTH(@pass);
+--------------------+
| CHAR_LENGTH(@pass) |
+--------------------+
| 16 |
+--------------------+
1 row in set (0.00 sec)
-- 使用AES_DECRYPT()解密
mysql> SELECT AES_DECRYPT(@pass, 'key');
+---------------------------+
| AES_DECRYPT(@pass, 'key') |
+---------------------------+
| hello world |
+---------------------------+
1 row in set (0.00 sec)
那么到底該如何存呢？
方法①：
將字段屬性設(shè)置為varbinary/binary/四個(gè)blob類(lèi)型，等二進(jìn)制字段屬性。
創(chuàng)建三個(gè)字段，屬性分別為varbinary、binary、blob。
并將'明文1'，'text2'，'明文_text3'加密，密鑰為key，存入表中。
最后取出。
mysql> CREATE TABLE t_passwd (pass1 varbinary(16), pass2 binary(16), pass3 blob);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t_passwd VALUES (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 row in set (0.00 sec)
當(dāng)然，屬性括號(hào)內(nèi)的長(zhǎng)度要取決于明文的長(zhǎng)度，此處明文較短，故只給了16。
方法②：
將密文十六進(jìn)制化，再存入varchar/char列。
此處需要用到HEX()來(lái)存入，用UNHEX()取出。
創(chuàng)建一個(gè)字符串屬性的字段。
將'hello world'先用密鑰'key2'進(jìn)行AES加密，再將加密后的串通過(guò)HEX函數(shù)十六進(jìn)制化。
最后先將加密后的串通過(guò)UNHEX取出，再通過(guò)AES據(jù)密鑰'key2'解密：
mysql> CREATE TABLE t_passwd_2(pass1 char(32));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t_passwd_2 VALUES (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 row in set (0.00 sec)
同樣，根據(jù)明文的長(zhǎng)度不同，AES_ENCRYPT加密后的串長(zhǎng)度也會(huì)有所變化，所以HEX后的字符串長(zhǎng)度也會(huì)有所變化。
實(shí)際使用時(shí)，需要據(jù)業(yè)務(wù)評(píng)估出一個(gè)合理值即可。
方法③：
直接存入varchar中，不做十六進(jìn)制化。
回溯到問(wèn)題的一開(kāi)始，將加密后的串，存到utf8字符集并且屬性為varchar中，是不行的。
實(shí)際上，將字符集改成latin1就可以了：
在insert的時(shí)候也不會(huì)報(bào)warning了。
mysql> CREATE TABLE t_passwd_3(pass varchar(32)) CHARSET latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t_passwd_3 SELECT 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 row in set (0.00 sec)
這樣的方法雖然美，只需將字段字符集設(shè)置為latin1就可以了，但可能會(huì)帶來(lái)隱患：
文檔上寫(xiě)了這樣的一句：
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類(lèi)型中，在做字符轉(zhuǎn)換的時(shí)或空格被刪除時(shí)，可能會(huì)帶來(lái)潛在的影響。
所以如果一定要存在char/varchar/text中，那么還是參考方法②，十六進(jìn)制化一下吧。
或者如同方法①，直接存在二進(jìn)制字段中。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
參考文檔：
Chapter 12 Functions and Operators - 12.13 Encryption and Compression Functions
您可能感興趣的文章:- The MySQL server is running with the --read-only option so it cannot execute this statement
- mysql數(shù)據(jù)庫(kù)mysql: [ERROR] unknown option ''--skip-grant-tables''
- mysql視圖之確保視圖的一致性（with check option）操作詳解
- MySQL存儲(chǔ)表情時(shí)報(bào)錯(cuò)：java.sql.SQLException: Incorrect string value:‘\xF0\x9F\x92\xA9\x0D\x0A...’的解決方法
- Mysql中的NULL和Empty String
- javascript連接mysql與php通過(guò)odbc連接任意數(shù)據(jù)庫(kù)的實(shí)例
- ubuntu下apt-get安裝和徹底卸載mysql詳解
- mysql server is running with the --skip-grant-tables option
- 利用pt-heartbeat監(jiān)控MySQL的復(fù)制延遲詳解
- MySQL pt-slave-restart工具的使用簡(jiǎn)介
