Interview preparation--案例加密后数据的模糊查询

加密数据的模糊查询实现方案

• 我们知道加密后的数据对模糊查询不是很友好，本篇就针对加密数据模糊查询这个问题来展开讲一讲实现的思路，希望对大家有所启发。
• 为了数据安全我们在开发过程中经常会对重要的数据进行加密存储，常见的有：密码、手机号、电话号码、详细地址、银行卡号、信用卡验证码等信息，这些信息对加解密的要求也不一样，比如说密码我们需要加密存储，一般使用的都是不可逆的慢hash算法，慢hash算法可以避免暴力破解（典型的用时间换安全性）。
• 因此在作完加密之后的字符串之后，加密算法会抹去原来字符的一些特性，让结果字符串没有可识别的特点，这样目的是确保安全性，即使被截获还是无法轻易的破解。但是这样也就造成了查询的复杂性。
• 一般我们在加密数据查询都是用如下方式，利用等值查询来获取：

select * from t_test_aes where message_id = '0xF3F4127D4D1AEC1DC5A620CE5E8C7F4F';

• 以上手段都无法做到模糊查询，例如银行用户的真实用户名，是一个敏感数据，如何模糊查询用户名字是一个难点

加密数据模糊查询方案一

• 数据库中实现加密，解密，有如下SQL

drop table if exists t_test_aes;
CREATE TABLE `t_test_aes` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',`message_id` BLOB(20) NOT NULL COMMENT '消息Id',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='aes测试表';
## 添加数据
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message2', 'test_key'));
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message3', 'test_key'));
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message4', 'test_key'));

• 有如上数据库表，我们定义message_id用来做模糊查询，在添加数据时候，不在代码中加密，而是在mysql数据库中加密，利用数据库自己的加密算法完成脱敏，如下insert

## 添加数据
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message2', 'test_key'));
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message3', 'test_key'));
insert into t_test_aes( message_id) values ( AES_ENCRYPT('test_message4', 'test_key'));

• 同样在查询的时候，也可以用这个方法进行模糊查询

## 查询数据
select * from t_test_aes where AES_DECRYPT(message_id, 'test_key') like '%2%';
select * from t_test_aes where message_id = '0xF3F4127D4D1AEC1DC5A620CE5E8C7F4F';

优缺点

• 优点是实现成本低，开发使用成本低，只需要将以往的模糊查找稍微修改一下就可以实现
• 缺点也很明显，这样做无法利用数据库的索引来优化查询，甚至有一些数据库可能无法保证与程序实现一致的加解密算法，但是对于常规的加解密算法都可以保证与应用程序一致

加密数据模糊查询方案二

• 对密文数据进行分词组合，将分词组合的结果集分别进行加密，然后存储到映射表，查询时通过key like ‘%partial%’，这是一个比较划算的实现方法，我们先来分析一下它的实现思路
• 先对字符进行固定长度的分组，将一个字段拆分为多个，比如说根据4位英文字符（半角），2个中文字符（全角）为一个检索条件，举个例子：

test_message_one 使用4个字符为一组的加密方式, 第一组test，第二组est_，第三组st_m，第四组t_mes … 依次类推。

• 如果需要检索所有包含检索条件4个字符的数据比如：test，加密字符后通过 key like “%partial%” 查库。

• 重要：要实现以上加密算法的模糊查询，必须有固定的加密算法满足以下需求：

假如原序列 A 加密后的 值是X
那么存在 A的连续子序列 B 加密后的值是Y
必须保证 Y 也是X 的连续子序列
例如如下加密算法的结果久满足需求：
test_message_one 加密后值为 whvwbphvvdjhbrqh
test 加密之后的值为  whvw

• 依据以上分析，我们有如下Sql案例做具体说明：

drop table if exists t_test_aes_target;
CREATE TABLE `t_test_aes_target` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',`message_id` varchar(256) NOT NULL COMMENT '消息Id',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='加密测试表';drop table if exists t_test_aes_sub;
CREATE TABLE `t_test_aes_sub` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',`target_id` bigint(20) NOT NULL COMMENT 'id',`origin_msg` varchar(256) NOT NULL COMMENT '消息Id',`message_id_buf` varchar(256) NOT NULL COMMENT '消息Id',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='拆分加密测试表';

• 添加如下加密数据到目标数据表中：

insert into t_test_aes_target(id, message_id) values (1, 'whvwbphvvdjhbrqh');

• 添加如下拆分后的关连数据到如下表中：

insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'test' , 'whvw');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'est_' , 'hvwb');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'st_m' , 'vwbp');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 't_me' , 'wbph');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, '_mes' , 'bphv');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'mess' , 'phvv');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'essa' , 'hvvd');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'ssag' , 'vvdj');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'sage' , 'vdjh');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'age_' , 'djhb');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'ge_o' , 'jhbr');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, 'e_on' , 'hbrq');
insert into t_test_aes_sub(target_id, origin_msg, message_id_buf) values (1, '_one' , 'brqh');

• 加密算法说明：我们可以采用一种简单的方法：将原字符串转换为一个固定大小的数组，然后对数组中的每个元素进行简单的变换（例如，ASCII值加一个固定偏移量）。这样，当子字符串被加密时，由于它是原字符串的一部分，其加密结果自然也是原字符串加密结果的子串

 // 加密方法public static String encrypt(String input) {return shiftString(input, SHIFT);}// 通过ASCII值偏移量来加密字符串private static String shiftString(String input, int shift) {char[] chars = input.toCharArray();for (int i = 0; i < chars.length; i++) {chars[i] = (char) (chars[i] + shift);}return new String(chars);}public static List<String> splitStringByCharacterType(String input, Integer num) {if(StringUtils.isBlank(input)){return Arrays.asList();}if(input.length() <= 4){return Arrays.asList(input);}List<String> result = new ArrayList<>();for (int i = 0; i <= input.length() - 4; i++) {result.add(input.subSequence(i, i+4).toString());}return result;}

• 通过以上加密方式以及数据维护方式来维护的数据，可以用如下查询来完成：
• 第一直接通过子字符串 的加密密文模糊查询

select * from t_test_aes_target where message_id like '%whvw%';

• 第二，通过关联表来查询，关联表通过test加密密文查询出关联id，通过id主键查询完成：

select * from t_test_aes_target where id in (select id from t_test_aes_sub where message_id_buf like '%whvw%');

优缺点

• 优点就是实现起来不算复杂，使用起来也较为简单，算是一个折中的做法，因为会有扩展字段存储成本会有升高，但是可利用数据库索引优化查询速度，推荐使用这个方法。
• 缺点这个方法虽然可以实现加密数据的模糊查询，但是对模糊查询的字符长度是有要求的，以我上面举的例子模糊查询字符原文长度必须大于等于4个英文/数字，或者2个汉字，再短的长度不建议支持，因为分词组合会增多从而导致存储的成本增加，反而安全性降低。而且这种加密算法的安全性不高