数据库敏感数据加密后模糊查询方案
wuhming 收录于 Springboot 生产问题数据库敏感数据加密后模糊查询方案
在做系统设计时，考虑到系统的安全性，需要对用户的一些个人隐私信息，比如：登录密码、身份证号、银行卡号、手机号等，做加密处理，防止用户的个人信息被泄露。
1.一次加载到内存
实现起来比较简单，成本非常低。如果个人隐私数据非常多的话，应用服务器的内存不一定够用，可能会出现OOM问题。如果用户修改了手机号，数据库更新成功了，需要同步更新内存中的缓存，否则用户查询的结果可能会跟实际情况不一致。
2. 使用数据库函数
SELECT
DES_DECRYPT('U2FsdGVkX1+q7g9npbydGL1HXzaZZ6uYYtXyug83jHA=', '123'); 该方案中保存数据时，只对单个用户的数据进行操作，数据量比较小，性能还好。
但模糊查询数据时，每一次都需要通过DES_DECRYPT函数，把数据库中用户某个隐私信息字段的所有数据都解密了，然后再通过解密后的数据，做模糊查询。
如果该字段的数据量非常大，这样每次查询的性能会非常差。
3. 采用分段保存的方式
我们可以将一个完整的字符串，拆分成多个小的字符串。
以手机号为例：15780016354，按每3位为一组，进行拆分，拆分后的字符串为：[“157”,“578”,“780”,“800”,“001”,“016”,“163”,“635”,“354”]，这9组数据。
- 用户在写入手机号的时候，同步把拆分之后的手机号分组数据，也一起写入，可以保证在同一个事务当中，保证数据的一致性。
- 模糊查询手机号，可以直接通过
sys_extension_mapping的encrypt_value模糊查询出用户表的user_id，再通过user_id查询用户信息。
创建两张测试表
create table sys_user
(
id bigint auto_increment comment '用户ID' primary key,
user_code varchar(128) null comment '用户编码',
user_name varchar(64) null comment '用户姓名',
user_pwd varchar(128) null comment '用户密码;用于后台管理端登录',
user_phone varchar(64) null comment '联系电话',
delete_yn int null comment '删除标志',
create_id bigint null comment '创建人ID',
create_time datetime null comment '创建时间',
update_id bigint null comment '更新人ID',
update_time datetime null comment '更新时间',
remark varchar(900) null comment '备注'
)
comment '账号密码信息表' row_format = DYNAMIC;
create table sys_extension_mapping
(
id bigint auto_increment comment '主键' primary key,
user_id bigint null comment '用户id',
encrypt_value varchar(255) not null comment '加密后的字符串',
delete_yn int null comment '删除标志',
create_id varchar(32) null comment '创建人ID',
create_time datetime null comment '创建时间',
update_id varchar(32) null comment '更新人ID',
update_time datetime null comment '更新时间',
remark varchar(900) null comment '备注'
)
comment '分段加密映射表' collate = utf8mb4_bin;创建测试数据
我已经用mybatis-flex代码生成器生成好了对应的实体类，mapper和service，可参考[Mybatis Flex代码生成器抽象类章节](Mybatis Flex代码生成器抽象类 - 我的博客 (wuhm.com.cn))实现代码生成器
@Test
void test4(){
String aesKey = "zpqeuex3jo8s8i9ef8v06x9typtb5koa";
String iv = "aelu8ouqo9hxgu2a";
AESUtil aesUtil = new AESUtil(AESUtil.Model.CBC);
String phone = "15692681589";
//String phone = "15892887589";
User user = new User();
user.setUserCode("wuhming1");
user.setUserPhone(aesUtil.encoded(aesKey, iv, phone));
userService.save(user);
List<ExtensionMapping> extensionMappingList = new ArrayList<>();
List<String> strSequenceGroup = StringUtilLang3.getStrSequenceGroup(phone, 3);
for(String str : strSequenceGroup){
ExtensionMapping extensionMapping = new ExtensionMapping();
extensionMapping.setUserId(user.getId());
extensionMapping.setEncryptValue(aesUtil.encoded(aesKey, iv, str));
extensionMappingList.add(extensionMapping);
}
extensionMappingService.saveBatch(extensionMappingList);
}执行两次后：数据库数据
模糊查询结果：
@Test
void test6(){
String aesKey = "zpqeuex3jo8s8i9ef8v06x9typtb5koa";
String iv = "aelu8ouqo9hxgu2a";
AESUtil aesUtil = new AESUtil(AESUtil.Model.CBC);
String key = "158";
QueryWrapper queryWrapper = QueryWrapper.create()
.select(USER.ID, USER.USER_CODE, USER.USER_PHONE)
.from(USER.as("u"))
.leftJoin(ExtensionMappingTableDef.EXTENSION_MAPPING).as("em").on(USER.ID.eq(ExtensionMappingTableDef.EXTENSION_MAPPING.USER_ID))
.where(ExtensionMappingTableDef.EXTENSION_MAPPING.ENCRYPT_VALUE.like(aesUtil.encoded(aesKey, iv, key)));
List<UserExtensionDto> userExtensionDtos = userService.getMapper().selectListByQueryAs(queryWrapper, UserExtensionDto.class);
for(UserExtensionDto userExtensionDto : userExtensionDtos){
userExtensionDto.setUserPhone(aesUtil.decoded(aesKey, iv, userExtensionDto.getUserPhone()));
System.out.println(JSON.toJSONString(userExtensionDto));
}
}结果：
{"extensionMappingList":[],"id":1,"userCode":"wuhming","userPhone":"15892887589"}
{"extensionMappingList":[],"id":2,"userCode":"wuhming1","userPhone":"15692681589"}测试：key=288
{"extensionMappingList":[],"id":1,"userCode":"wuhming","userPhone":"15892881589"}==缺点==：如果业务表中的数据量很大，一个手机号就需要保存9条数据，一个身份证或者银行卡号也需要保存很多条数据，这样会导致sys_extension_mapping表的数据急剧增加，可能会导致这张表非常大。最后的后果是非常影响查询性能。
4.采用分段保存的方式+增加模糊查询字段(推荐)
可以在用户表中，在手机号旁边，增加一个
encrypt_phone字段，然后我们在保存数据的时候，将分组之后的数据拼接起来。[“157”,“578”,“780”,“800”,“001”,“016”,“163”,“635”,“354”]，这9组数据。加密后拼接的结果：
EHcc8tNW/EyIxEbFVKrThQ==,kmxElCDXGYqOkUjKiRO32g==,KX2r7cZ/C1JNh9502UfbGg==,jbKYeJVzjhjJnrFjmWRJBg==,JJkjlEgheDZyCzCrs6yw+w==,v0rTrcv2+yp+fTyXa6+BPA==,IcSI8+r+7xh47L0CyU58KA==,EHcc8tNW/EyIxEbFVKrThQ==,kmxElCDXGYqOkUjKiRO32g==
以后可以直接通过sql模糊查询字段encrypt_phone了：
select id,user_name,user_phone
from sys_user where encrypt_phone like '%U2FsdGVkX19Se8cEpSLVGTkLw/yiNhcB%'
limit 0,20;==注意一定要使用逗号分割，是为了防止直接字符串拼接，在极端情况下，两个分组的数据，原本都不满足模糊搜索条件，但拼接在一起，却有一部分满足条件的情况发生==