ä½è ï¼å²³æ强
ç±å¯çå京åå ¬å¸ DBA å¢éæåï¼äººç§°å¼ºå¥ï¼è´è´£æ°æ®åºç®¡çå¹³å°çè¿ç»´å MySQL é®é¢å¤çãæ é¿å¯¹ MySQL çæ éå®ä½ã
æ¬ææ¥æºï¼ååæ稿
*ç±å¯çå¼æºç¤¾åºåºåï¼ååå 容æªç»ææä¸å¾éæ使ç¨ï¼è½¬è½½è¯·èç³»å°ç¼å¹¶æ³¨ææ¥æºã
æè¿ç±äºå®¢æ·å¯¹äº MySQL æ°æ®å å¯æä¸äºè¦æ±ï¼ç¹å°å¯¹äº MySQL çæ°æ®å å¯ç 究äºä¸ä¸ãå½å MySQL åççæ°æ®å å¯æéæå å¯ï¼å³å å¯æ°æ®åºçç©çæ件ï¼é²æ¢ç´æ¥æåºå读åæææ°æ®ï¼è¿æ SQL 级å«çå å¯ï¼åªå å¯é¨åå段ï¼å³ä½¿è·åå°æ°æ®ï¼ä¹æ æ³è¿è¡è§£è¯»ãä¸é¢ä¸»è¦æ¯å¯¹äº SQL å å¯å½æ° AES_ENCRYPT() çä¸äºè¯´æ
åæ°è¯´æ
解å¯ï¼AES_DECRYPT()ï¼AES_DECRYPT(crypt_str,key_str[,init_vector][,kdf_name][,salt][,info | iterations])
å å¯ï¼AES_ENCRYPT(str,key_str[,init_vector][,kdf_name][,salt][,info | iterations])
srt:å å¯ä¹åçå符串
crypt_strï¼ç¨æ¥å å¯çå符串ï¼å å¯åçå段é¿åº¦å¯ä»¥ç¨ä»¥ä¸å ¬å¼è®¡ç®ï¼å ¶ä¸ trunc() 表示å°æ°é¨åèå¼ï¼å³å¦æè¾å ¥å个å符ï¼é£ä¹åå¨çå段é¿åº¦å³ä¸ºæçé¿åº¦16
16 * (trunc(string_length / 16) + 1)
key_strï¼å å¯å¯é¥ï¼ä¸å»ºè®®ä½¿ç¨ææå¯é¥ï¼åºè¯¥å ç¨hashå¤çä¸ä¸
init_vector åå§åéï¼ç¨äºåå å¯ç模å¼ï¼block_encryption_modeï¼ï¼é»è®¤çå å¯æ¨¡å¼ä¸ºaes-128-ecbï¼ä¸éè¦åå§åéï¼å ¶å®çå å¯æ¨¡å¼ï¼CBCãCFB1ãCFB8ãCFB128 å OFBï¼é½éè¦åå§åéï¼å ¶ä¸ ecb çå å¯æ¨¡å¼å¹¶ä¸å®å ¨ï¼å»ºè®®ä½¿ç¨å ¶å®çå å¯æ¨¡å¼ï¼ä½¿ç¨ init_vector å å¯å ä¹è¦ä½¿ç¨ç¸åç init_vector 解å¯
kdf_name,salt,info,iterationsï¼ä¸º KDF çç¸å ³åæ°ï¼ç¸å¯¹äºæ´å å®å ¨ï¼å®æ¹å»ºè®®ä½¿ç¨ï¼ä½ç±äºçæ¬è¦æ±è¿é«ï¼5.7.40以å8.0.30ä¹åï¼ï¼è¿éå°±å ä¸èèäº
使ç¨è¯´æ
使ç¨å®æ¹ AESï¼é«çº§å å¯æ åï¼ç®æ³è§£å¯æ°æ®ï¼é»è®¤ä½¿ç¨128-bitä¹å¯ä»¥ä½¿ç¨196æè 256ï¼å¯é¥çé¿åº¦ä¸æ§è½åå®å ¨åº¦æå ³ï¼
ä½¿ç¨ AES_ENCRYPT(ï¼å¯¹äºåºäº statement ç binlog ç±»åæ¯ä¸å®å ¨çï¼å»ºè®®ä½¿ç¨ SSL è¿æ¥ï¼é²æ¢å°å å¯å½æ°çå¯ç åå ¶å®ææå¼ä½ä¸ºææåéå°æå¡å¨ã
ç®å示ä¾ï¼
mysql [localhost:5734] {root} (test) > show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`n` char(200) DEFAULT NULL,
`t` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:5734] {root} (test) > insert into test values(aes_encrypt('b','test'),1);
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5734] {root} (test) > select * from test;
+----------------------------+------+
| n | t |
+----------------------------+------+
| x | 0 |
| y | 0 |
| ùpñU!㿧ßWHÆôò | 1 |
+----------------------------+------+
3 rows in set (0.00 sec)
mysql [localhost:5734] {root} (test) > select aes_decrypt(n,'test') from test where t = 1;
+-----------------------+
| aes_decrypt(n,'test') |
+-----------------------+
| b |
+-----------------------+
1 row in set (0.00 sec)
ç»è¿å å¯åå缩çç»æè¿åäºè¿å¶å符ï¼æ以建议é 置为VARBINARYæBLOBäºè¿å¶å符串æ°æ®ç±»åçåï¼é²æ¢å符é转æ¢ä»è导è´æå ¥å¤±è´¥
mysql [localhost:5729] {msandbox} (test) > create table test (a int ,n varchar(60));
Query OK, 0 rows affected (0.06 sec)
mysql [localhost:5729] {msandbox} (test) > insert into test values(1,AES_ENCRYPT('test','test'));
ERROR 1366 (HY000): Incorrect string value: '\x87\xBD\x908\x85\x94...' for column 'name' at row 1
mysql [localhost:5729] {msandbox} (test) > alter table test MODIFY `n` VARBINARY(180);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5729] {msandbox} (test) > insert into test values(1,AES_ENCRYPT('test','test'));
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > select a,AES_decrypt(n,'test') from test;
+------+--------------------------+
| a | AES_decrypt(n,'test') |
+------+--------------------------+
| 1 | test |
+------+--------------------------+
1 row in set (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > select * from test;
+------+------------------+
| a | n |
+------+------------------+
| 1 | ���8��;�h�c�� |
+------+------------------+
é¿å æå ¥å¤±è´¥ï¼ä¹å¯ä»¥å°å¼è½¬æ¢ä¸º16è¿å¶ï¼ç¶ååè¿è¡åå¨ï¼æ¥ççæ¶åä¹éè¦å ç¨ unhex 解æåºæ¥ï¼ç¶ååè¿è¡è§£å¯
mysql [localhost:5729] {msandbox} (test) > insert into test1 values(1,AES_ENCRYPT('test','test'));
ERROR 1366 (HY000): Incorrect string value: '\x87\xBD\x908\x85\x94...' for column 'name' at row 1
mysql [localhost:5729] {msandbox} (test) > insert into test1 values(1,hex(AES_ENCRYPT('test','test')));
Query OK, 1 row affected (0.02 sec)
mysql [localhost:5729] {msandbox} (test) > select AES_DECRYPT(unhex(n),'test') from test1
-> ;
+---------------------------------+
| AES_DECRYPT(unhex(n),'test') |
+---------------------------------+
| test |
+---------------------------------+
1 row in set (0.00 sec)
å å¯æ¹æ³ç¤ºä¾
mysql [localhost:5729] {msandbox} (test) > SET block_encryption_mode = 'aes-256-cbc';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > SET @key_str = SHA2('mysql passphrase',512);
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > SET @init_vector = 'It is very very safe';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > SET @crypt_str = AES_ENCRYPT('test',@key_str,@init_vector);
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5729] {msandbox} (test) > SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
+-----------------------------------------------+
| AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
+-----------------------------------------------+
| test |
+-----------------------------------------------+
1 row in set (0.00 sec)
ç»è¯
å å¯å½æ°ä¸º MySQL åççå å¯æ段ï¼å¯ä»¥å å¯ä¸äºç±»ä¼¼äºèº«ä»½è¯ãé¶è¡å¡çéç§ä¿¡æ¯ãä¸å¡ä¸æ¹é使ç¨ä¼é æä¸å®çæ§è½æèï¼ä¸ªäººè¿æ¯å»ºè®®è¿äºå¤æçå½æ°æä½è¿æ¯å¨åºç¨å±å®ç°ï¼éä½æ°æ®åºçååã