MySQL利用AES_ENCRYPT()與AES_DECRYPT()加解密的正確方法示例
前言
最近在工作中遇到一個需求是這樣的：需要在使用AES_ENCRYPT()函數(shù)將明文加密，存儲在MySQL中，但是遇到了一些問題……下面就來詳細介紹下。
說將加密后的密文，解密取出來是NULL。
看了一下，她發(fā)過來的表結構：
再看了她通過AES_DECRYPT()函數(shù)加密了一個字符串，然后insert進去了，執(zhí)行成功后，顯示了一個warning：
Query OK, 1 row affected, 1 warning (0.00 sec)
（沒有報錯而是warning，大概是sql_mode的緣故）
此時她忽略了這個warning，再通過AES_DECRYPT()解密后，發(fā)現(xiàn)取出來的明文為NULL。
再回看表結構，發(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)
查了一下文檔，看一下這兩個函數(shù)的使用：
-- 將'hello world'加密，密鑰為'key'，加密后的串存在@pass中
mysql> SET @pass=AES_ENCRYPT('hello world', 'key');
Query OK, 0 rows affected (0.00 sec)
-- 看一下加密后串的長度（都為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)
那么到底該如何存呢？
方法①：
將字段屬性設置為varbinary/binary/四個blob類型，等二進制字段屬性。
創(chuàng)建三個字段，屬性分別為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)
當然，屬性括號內的長度要取決于明文的長度，此處明文較短，故只給了16。
方法②：
將密文十六進制化，再存入varchar/char列。
此處需要用到HEX()來存入，用UNHEX()取出。
創(chuàng)建一個字符串屬性的字段。
將'hello world'先用密鑰'key2'進行AES加密，再將加密后的串通過HEX函數(shù)十六進制化。
最后先將加密后的串通過UNHEX取出，再通過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ù)明文的長度不同，AES_ENCRYPT加密后的串長度也會有所變化，所以HEX后的字符串長度也會有所變化。
實際使用時，需要據(jù)業(yè)務評估出一個合理值即可。
方法③：
直接存入varchar中，不做十六進制化。
回溯到問題的一開始，將加密后的串，存到utf8字符集并且屬性為varchar中，是不行的。
實際上，將字符集改成latin1就可以了：
在insert的時候也不會報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)
這樣的方法雖然美，只需將字段字符集設置為latin1就可以了，但可能會帶來隱患：
文檔上寫了這樣的一句：
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
- The MySQL server is running with the --read-only option so it cannot execute this statement
- mysql數(shù)據(jù)庫mysql: [ERROR] unknown option ''--skip-grant-tables''
- mysql視圖之確保視圖的一致性（with check option）操作詳解
- MySQL存儲表情時報錯：java.sql.SQLException: Incorrect string value:‘\xF0\x9F\x92\xA9\x0D\x0A...’的解決方法
- Mysql中的NULL和Empty String
- javascript連接mysql與php通過odbc連接任意數(shù)據(jù)庫的實例
- ubuntu下apt-get安裝和徹底卸載mysql詳解
- mysql server is running with the --skip-grant-tables option
- 利用pt-heartbeat監(jiān)控MySQL的復制延遲詳解
- MySQL pt-slave-restart工具的使用簡介
相關文章
相信大家在日常的開發(fā)中經(jīng)常會碰到榜單類的活動需求，本文主要介紹了MySQL窗口函數(shù)實現(xiàn)榜單排名，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-04-04
Linux下面因為從MySQL 5.5開始使用cmake來做config了，所以編譯安裝的會和5.1版本有些區(qū)別。不過總體來說還是差別不大2015-08-08
這篇文章主要為大家詳細介紹了基于Windows安裝MySQL 8.0.12圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
這篇文章主要介紹了MySQL中的臨時表與內存表,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
iOS開發(fā)runloop運行循環(huán)機制學習
這篇文章主要為大家介紹了iOS開發(fā)runloop運行循環(huán)的機制學習，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-07-07
這篇文章主要介紹了PostgreSQL 正則表達式 常用函數(shù)的總結的相關資料,對那些需要進行復雜數(shù)據(jù)處理的程序來說，正則表達式無疑是一個非常有用的工具，這里就介紹下如何使用，需要的朋友可以參考下2017-08-08
MySQL的安裝分為兩種,一種是安裝版本,一種是免安裝解壓版本,一般老師都會推薦免安裝解壓版本,用起來更方便些,下面這篇文章主要給大家介紹了關于Mysql?8.0解壓版下載安裝以及配置的相關資料,需要的朋友可以參考下2022-01-01
麒麟系統(tǒng)上安裝?MySQL?8.0.24的詳細步驟(避坑指南)
這篇文章主要介紹了麒麟系統(tǒng)上安裝MySQL8.0.24的詳細步驟，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-08-08
對于從來自事計算機專業(yè)工作的人而言,MySQL并不陌生,下面這篇文章主要給大家介紹了關于cmd進入mysql以及常用的mysql操作的相關資料,需要的朋友可以參考下2023-11-11
MySQL5.7.27-winx64版本win10下載安裝教程圖解
這篇文章主要介紹了MySQL5.7.27-winx64版本win10下載安裝教程,本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2019-09-09