技术分享 | 详解SQL加密函数:AES_ENCRYPT()

作者:岳明强

爱可生北京分公司 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 原生的加密手段,可以加密一些类似于身份证、银行卡等隐秘信息。业务中批量使用会造成一定的性能损耗,个人还是建议这些复杂的函数操作还是在应用层实现,降低数据库的压力。

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

368 声望
180 粉丝
0 条评论
推荐阅读
技术分享 | 一文了解 MySQL Optimizer Trace 的神奇功效
对于 MySQL 5.6 以及之前的版本来说,查询优化器就像是一个黑盒子一样,你只能通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划,却无法知道它为什么做这个决策。这对于一部分喜欢刨根问底的⼩伙伴来说简直...

爱可生云数据库

安全地在前后端之间传输数据 - 「3」真的安全吗?
在「2」注册和登录示例中,我们通过非对称加密算法实现了浏览器和 Web 服务器之间的安全传输。看起来一切都很美好,但是危险就在哪里,有些人发现了,有些人嗅到了,更多人却浑然不知。就像是给门上了把好锁,还...

边城31阅读 7.1k评论 5

封面图
🖼️ 如何解决 SVG 图片中字体失效的问题
如果你喜欢我的文章,希望点赞👍 收藏 📁 评论 💬 三连支持一下,谢谢你,这对我真的很重要!「SVG 图片中字体失效」的修复方案很简单,只想看答案翻到最后看结论就行。如果想看我的排查思路和具体原因可以从头开始...

卤代烃6阅读 1.4k

http 和 https 的通信过程及区别
🎈 两者的区别端口: http 端口号是80, https 端口号是443传输协议: http 是超文本传输协议,属于明文传输; https 是安全的超文本传输协议,是经过 SSL 加密后的传输协议安全性: https 使用了 TLS/SSL 加密,...

tiny极客2阅读 3k评论 2

封面图
JWT 登录认证
🎈 Token 认证流程作为目前最流行的跨域认证解决方案,JWT(JSON Web Token) 深受开发者的喜爱,主要流程如下:客户端发送账号和密码请求登录服务端收到请求,验证账号密码是否通过验证成功后,服务端会生成唯一...

tiny极客3阅读 1.3k评论 1

封面图
前端代码安全与混淆
作者:京东零售 周明亮一、友商网页分析1.1 亚马逊亚马逊商详地址: [链接]所有交互事件在页面初始化时,不进行下发,等待通过 js 请求后下发 具体点击事件js内容采用自执行方式,防止代码格式化。【无法调用 Chr...

京东云开发者1阅读 1.1k

封面图
前端安全13条,除了XSS/CSRF你还知道哪些?
大家好,我是沐华。最近面了一些前端,发现每次问到安全相关的问题,一些面试者只能说上来 XSS、CSRF、中间人攻击,就没了,于是写了这篇文章,总结了前端安全相关的点,给大家查缺补漏

沐华2阅读 257

封面图

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

368 声望
180 粉丝
宣传栏