MySQL敏感数据加密的实现方案！
MySQL敏感数据加密的实现方案！
一、准备工作（环境要求）
1、版本要求
mysql必须是mysql5.6以上版本,通过以下命令查看是否版本支持
|
1
2
3
4
5
6
|
mysql> select version();+-----------+| version() |+-----------+| 8.0.24 |+-----------+ |
2、字段类型要求
数据表字段类型必须是以下类型中的其中一个
|
1
|
varbinary、binary、blob |
二、数据库演示
1、创建演示表
|
1
2
3
4
5
6
7
|
mysql> CREATE TABLE `user` (`id` int unsigned NOT NULL AUTO_INCREMENT,`username` varchar(20) DEFAULT NULL,`mobile` varbinary(50) NOT NULL,`id_card` varbinary(155) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; |
注意： mobile（手机号） 和 id_card（身份证号） 的字段类型为varbinary，我们将对这两个字段进行加密存储。
2、插入加密数据
|
1
2
3
4
|
mysql> insert into user(username, mobile, id_card) values('Tom', aes_encrypt('15101123111', 'test_key'), aes_encrypt('372522195710100019','test_key'));mysql> insert into user(username, mobile, id_card) values('Mary', aes_encrypt('15101123111', 'key'), aes_encrypt('372522195710100019','key')); |
注意：这里在使用**insert** 插入时，将需要加密的字段使用aes_encrypt函数并指定秘钥key。
3、查看插入数据
|
1
2
3
4
5
6
7
8
|
mysql> select * from user;-----------++----+----------+------------------------------------+---------------------------------------------------------| id | username | mobile | id_card |+----+----------+------------------------------------+--------------------------------------------------------------------+| 1 | Tom | 0x28E415A075D38ECFEE0AFB86027231BD | 0x45397D31A49C50EEFE669D2145110F134A90D6E834084FBA38E4B5098F2EDAED || 2 | Mary | 0xC932282B9EBC4FC82A225A56FB12BB63 | 0xF748DFDB3C0994DB9C06F4FB863AAAE5DC1685EA49D8AAA5C28952D5BFDD8A35 |+----+----------+------------------------------------+--------------------------------------------------------------------+ |
**提示：**此处是直接查询，返回的是加密后的值，神仙也看不出是啥，如果猜测的话首先要猜出加密方式，其次是秘钥。
4、解密数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> select id, username, cast(aes_decrypt(mobile, 'test_key') as char charset utf8 ) as mobile from user where id = 1;+----+----------+-------------+| id | username | mobile |+----+----------+-------------+| 1 | Tom | 15101123111 |+----+----------+-------------+mysql> select id, username, cast(aes_decrypt(id_card, 'key') as char charset utf8 ) as id_card from user where id = 2;+----+----------+--------------------+| id | username | id_card |+----+----------+--------------------+| 2 | Mary | 372522195710100019 |+----+----------+--------------------+ |
注意：
1）、id为1的那条数据的加密密钥为key 为 test_key,id 为2的加密密钥为key 为 key 。 解密时如果密钥不对将查询结果为null。
2）、解密函数aes_decrypt 外层需要使用cast 进行处理，否则将返回二进制值而不是解密后的值。
三、Java + mybatis 实现加解密
1、Java代码中什么都不需要动
提示： 此处java使用的是mybatis持久化数据，如果使用hibernate或mybatis-plus等框架，请自行研究，理论上通用但需要变通一下
2、mybatis中插入和更新用法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
<!-- 插入 --><insert id="saveCertificate" parameterType="com.test.candidate.bean.Certificate">insert into certificate (oa_serial,begin_date,end_date,certificate_type,certificate_code,certificate_code_again,address,country,created_at,updated_at) values (#{oaSerial},#{beginDate},#{endDate},#{certificateType},AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo'),AES_ENCRYPT(#{certificateCodeAgain,jdbcType=VARCHAR},'perinfo'),#{address},#{country},#{createdAt},#{updatedAt});</insert><!-- 更新 --><update id="updateCertificate" parameterType="com.test.candidate.bean.Certificate">update certificate setbegin_date = #{beginDate},end_date = #{endDate},certificate_type = #{certificateType},certificate_code = AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo'),certificate_code_again = AES_ENCRYPT(#{certificateCodeAgain,jdbcType=VARCHAR},'perinfo'),address = #{address},country = #{country},updated_at = #{updatedAt}where oa_serial = #{oaSerial} and id = #{id}</update> |
注意：插入和更新是加密：AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo')
3、mybatis中查询用法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<!-- 查询 --><select id="findList" resultMap="PartCertificateMap">SELECTbegin_date,end_date,certificate_type,cast(AES_DECRYPT(certificate_code,'perinfo') as char charset utf8) certificate_code,cast(AES_DECRYPT(certificate_code_again,'perinfo') as char charset utf8) certificate_code_again,address,countryFROM certificatewhereoa_serial = #{oaSerial}</select> |
**注意：**查询时解密：cast(AES_DECRYPT(certificate_code,'perinfo') as char charset utf8) certificate_code,
四、其他说明
网上有人说无需更改数据类型，直接将加密数据存储到carchar类型的字段中，然后将字符集改成latin1, 虽然此种方式确实也能实现加解密，但是mysql中insert 时，会报warning， 并且可能会带来隐患，使用以下命令查看警告信息
|
1
|
mysql> show warnings(); |
并不推荐此种方式：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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: 0mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;+---------------------------+| AES_DECRYPT(pass, 'key3') |+---------------------------+| text |+---------------------------+1 row in set (0.00 sec) |
MySQL官方给出的描述信息如下：
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类型中，在做字符转换的时或空格被删除时，可能会带来潜在的影响。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
共有 0 条评论