关于加密:技术分享-详解SQL加密函数AESENCRYPT

作者：岳明强

爱可生北京分公司 DBA 团队成员，人称强哥，负责数据库治理平台的运维和 MySQL 问题解决。善于对 MySQL 的故障定位。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

最近因为客户对于 MySQL 数据加密有一些要求，顺便对于 MySQL 的数据加密钻研了一下。以后 MySQL 原生的数据加密有动态加密，即加密数据库的物理文件，避免间接拖库后读取敏感数据，还有 SQL 级别的加密，只加密局部字段，即便获取到数据，也无奈进行解读。上面次要是对于 SQL 加密函数 AES_ENCRYPT() 的一些阐明

参数阐明

解密：AES_DECRYPT()：AES_DECRYPT(crypt_str,key_str,init_vector,salt)

加密：AES_ENCRYPT(str,key_str,init_vector,salt)

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 原生的加密伎俩，能够加密一些相似于身份证、银行卡等隐秘信息。业务中批量应用会造成肯定的性能损耗，集体还是倡议这些简单的函数操作还是在应用层实现，升高数据库的压力。