技术分享 | 详解SQL加密函数:AES_ENCRYPT()-程序员宅基地

技术标签: 技术分享  安全  加密  

作者:岳明强

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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ActionTech/article/details/127690657

智能推荐

分布式系统理论学习总结_分布式系统及其理论-程序员宅基地

文章浏览阅读3.7k次。分布式理论CAPCAP定理讲的是三个性。consistency数据一致性,availability可用性,partition tolerance分区容错性。三者只能选其中两者。为什么呢,看看这三个性质意味着什么吧。首先看看分区容错性,分区容错性指的是网络出现分区(丢包,断网,超时等情况都属于网络分区)时,整个服务仍然可用。由于网络分区在实际环境下一定存在,所以必须首先被考虑..._分布式系统及其理论

qt 登陆界面账号和密码的验证_qt ui 设计 怎么做槽 密码认证-程序员宅基地

文章浏览阅读1.6w次。这几天一直研究怎么让界面账号和密码验证的问题特别想用数据库,一直有QSQLlitdatabase cant find 的问题 找了很多方法没成功 要交课设 所以放弃用其他方法 用文件存吧 比较省事但是有个更简单的办法 哈哈   先看 转到槽函数user那是显示的信息 头文件加上QMessageBox  最后那行 获得焦点 倒数第二行清楚输入 结合我之前发的登陆界面到主界_qt ui 设计 怎么做槽 密码认证

串口通讯以及MPAndroidchart画图的使用(kotlin)_int count=linedatasets.get(0).getentrycount();-程序员宅基地

文章浏览阅读3.9k次,点赞3次,收藏9次。目录一.Kotlin串口通讯实现... 21.1导入基础库... 21.2 效果展示... 31.3布局界面... 412.1 代码... 41.4 Activity实现代码... 913.1 实列化... 91.3.2 适配波特率以及获取本机串口号,进行串口监听... 9二.MPAndroidChart图表使用... 132.1导入基础库... 132.1.1.引入开源库... 132.1.2在app根目录的buil.gradle文件中加入依赖... 1.._int count=linedatasets.get(0).getentrycount();

【Kubernetes】Pod调度到指定Node_双实例pod 调度到不同node-程序员宅基地

文章浏览阅读2.4w次。问题描述   Kubernetes有着自己特定的调度算法与策略,有Master中的Scheduler组件来实现,根据Node资源使用情况自动调度Pod的创建,通常可以满足我们大部分的需求。但是有时我们希望可以将某些Pod调度到特定硬件节点上,这里采用目前最为简单的nodeName和nodeSelector来实现Pod调度。  假设以下场景:有三个Node,分别为107、108、109,创建De_双实例pod 调度到不同node

angular6使用ngx-bootstrap/modal_ngx-bootstrap modal-程序员宅基地

文章浏览阅读6.4k次。首先下载ngx-bootstrap:npm install ngx-bootstrap --save下载后,在module.ts中导入ngx-bootstrap/modal类:import { ModalModule } from 'ngx-bootstrap'; imports: [ ... ModelModule, ModalModule.forRoot(..._ngx-bootstrap modal

Centos7利用rpm升级OpenSSH到openssh-8.1p1版本_openssh8.1升级使用rpm-程序员宅基地

文章浏览阅读3.4k次。参考:https://www.cnblogs.com/fsckzy/p/10834550.html Centos 升级至 OpenSSH 8 rpm包制作RPM打包使用的是rpmbuild命令,这个命令来自rpm-build软件包,这个是必装的。yum install rpm-build -y #安装rpm-build软件,以提供rpmbuild命令 69 ssh -V 70 rpm -qa openssh 71 yum install rpm-..._openssh8.1升级使用rpm

随便推点

训练网络报错device-side assert triggered_device-site-程序员宅基地

文章浏览阅读225次。出现以下:​Assertion 'indexValue>=0 && indexValue < src.sizes[dim]​应该是分类数和路径所指向的类别数不匹配。_device-site

关于Unity中的UGUI优化,你可能遇到这些问题-程序员宅基地

文章浏览阅读181次。https://blog.uwa4d.com/archives/QA_UGUI-1.html关于Unity中的UGUI优化,你可能遇到这些问题作者:admin/时间:2016年11月08日/浏览:23818 次/分类:厚积薄发关键字界面制作网格重建界面切换加载相关字体一、界面制作Q1:UGUI里的这个选项 ,应该是ET..._ugui 文字过多 很卡

一看就会(废)的最小二乘法推导_最小二乘拟合流程图-程序员宅基地

文章浏览阅读1.2k次,点赞2次,收藏9次。一、预备知识:方程组解的存在性及引入  最小二乘法可以用来做函数的拟合或者求函数极值。在机器学习的回归模型中,我们经常使用最小二乘法。我们先举一个小例子来走进最小二乘法。  某次实验得到了四个数据点(x,y):(1,6)、(2,5)、(3,7)、(4,10)(x,y):(1,6)、(2,5)、(3,7)、(4,10)(x,y):(1,6)、(2,5)、(3,7)、(4,10) (下图中红色的点)。我们希望找出一条与这四个点最匹配的直线 y=θ1+θ2xy = \theta_{1} + \theta_{_最小二乘拟合流程图

nodejs 控制台美化 console-color-mr_node控制台打印美化-程序员宅基地

文章浏览阅读4.1k次。console-color-mr插件可以让node控制台输出带有颜色,是一个不错的插件,通过颜色可以更直观的分析程序bug一、使用npm install -D console-color-mr// 方法一import 'console-color-mr'console.log(`.....`.red);// 方法二import _console from 'consol..._node控制台打印美化

WIN10删除微软拼音输入法,设置默认输入法为英文_微软拼音输入法删除后没有纯英文输入怎么办-程序员宅基地

文章浏览阅读1k次。WIN10删除微软拼音输入法,设置默认输入法为英文删除微软拼音输入法设置英文为默认输入方式删除微软拼音输入法在安装好自己熟悉的输入法后,我通常会将系统自带的微软拼音输入法删除,但系统又总是会自动重装上去。解决的办法很简单,删除以后,再手动添加一次微软拼音输入法,然后再一次删除,以后系统就不会再次自动重装了。设置英文为默认输入方式然后点高级键盘设置,选择默认输入法..._微软拼音输入法删除后没有纯英文输入怎么办

java打印一个三角形_java使用float函数打印三角形-程序员宅基地

文章浏览阅读192次。java打印一个三角形public class Day1 { //打印一个5行三角形 public static void main(String[] args) { for (int i = 1; i <= 5; i++) { //设置好需要打印的行数 for (int i1 = 5; i1 >=i; i1--) { //嵌套的第一个for循环,作用从5到1打印空格,打印5个空格后, _java使用float函数打印三角形