设计思路-数据库字段加密
说明
线上运行很久的项目,用户反馈用户手机号需要数据层面做加密,用户场景用户相关字段十分铭感
我们以前就出现过数据库用户订单信息泄露 手机号是明文 然后骗子打电话把客户骗了
现状
代码里面大量,的sql拼接,或者上层透传拼接sql,导致不确定要改的地方有多少,而且回归测试的地方又很多,或者下次用户又要邮件加密呢。代价成本大
而且我们是hibernate和mybatis混用
还涉及到hibernate的级联查询,如果级联返回用户信息手机是密文出问题
方案
1.通过JsqlParse解析sql树,在DataSource层面做替换 比如select * from user where mobile={mobile} mobile参数替换成密文传到数据库, 查询结果返回字段在应用层面也对应解密
2.有些特殊情况要在数据库做解密函数做处理
替换前
select * from ticke where phone in(select phone from user where id=1)
select * from user where phone like '%1312827%'
替换后
select * from ticke where phone in(select aes_decrypt(phone,'salt') from user where id=1)
select * from user where aes_decrypt(phone,'salt') like '%1312827%'
面临问题
各种复杂的sql动态拼接,或者有些没考虑到的复杂sql万一没改到怎么办 这样全局处理不是有风险
SELECT * FROM user u WHERE u.provider_id = ? AND u.valid = true AND u.deleted = false AND u.type = ? AND (u.suspended = ? OR u.suspended IS NULL) AND (CONCAT_WS(?, u.`name`, u.nickname, u.email, u.phone, u.mobile_phone, u.notes, u.signature) LIKE ? OR u.id IN ( SELECT urt.user_id FROM user_r_tag urt LEFT JOIN tag t ON t.id = urt.tag_id WHERE t.provider_id = ? AND t.`name` LIKE ? )) ORDER BY u.created_at DESC LIMIT ?, ?
解决方式
其实换一种思路就可以解决,在dataSource 加个采集所有的程序运行sql,然后根据写好的脚本程序解析替换所有sql跑一遍 验证,不符合预期的再针对性改
这样风险就小很多了
以下是我采集的sql
jqlparse使用
逻辑和考虑场景应该挺复杂的 可以参考开源的一些框架 比如mybatis 支持脱敏 还有sharding的一键脱敏
1.引入pom依赖
<dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>4.3</version> </dependency>
2.例子
public static void main(String[] args) throws JSQLParserException { String sql="\n" + "SELECT sd.name AS \"serviceDesk_name\", t.no AS \"no\", wt.name AS \"workflowTemplate_name\", ru.name AS \"requester_name\", t.subject AS \"subject\"\n" + "\t, eg.nickname AS \"engineer_nickname\", create_user.name AS \"user_name\", t.priority AS \"priority\", via.channel_name AS \"via_channel_name\", eu.name AS \"engineer_user_name\"\n" + "\t, t.created_at AS \"created_at\", t.deleted AS \"deleted\", create_user.email AS \"user_email\", ru.mobile_phone AS \"requester_mobilePhone\", t.id AS \"id\"\n" + "\t, t.status AS \"status\", ru.email AS \"requester_email\", t.updated_at AS \"updated_at\", t.engineer_id AS \"engineer_id\", eu.id AS \"engineer_user_id\"\n" + "\t, t.requester_id AS \"requester_id\", ug.id AS \"requester_userGroup_id\", t.service_desk_id AS \"serviceDesk_id\", t.via_id AS \"via_id\", t.user_id AS \"user_id\"\n" + "\t, t.workflow_template_id AS \"workflowTemplate_id\"\n" + "FROM ticket t\n" + "\tLEFT JOIN engineer eg ON t.engineer_id = eg.id\n" + "\tLEFT JOIN `user` eu ON eg.user_id = eu.id\n" + "\tINNER JOIN `user` ru ON t.requester_id = ru.id\n" + "\tLEFT JOIN user_group ug ON ru.user_group_id = ug.id\n" + "\tLEFT JOIN service_desk sd ON sd.id = t.service_desk_id\n" + "\tLEFT JOIN via via ON via.id = t.via_id\n" + "\tLEFT JOIN user create_user ON t.user_id = create_user.id\n" + "\tLEFT JOIN workflow_template wt ON t.workflow_template_id = wt.id\n" + "WHERE t.provider_id = ?\n" + "\tAND t.status = ?\n" + "\tAND t.engineer_id IS NULL\n" + "\tAND (t.deleted = false\n" + "\t\tOR t.deleted IS NULL)\n" + "\tAND (t.service_desk_id IN (\n" + "\t\t\tSELECT DISTINCT temp.service_desk_id\n" + "\t\t\tFROM (\n" + "\t\t\t\tSELECT ersd.service_desk_id\n" + "\t\t\t\tFROM engineer_r_service_desk ersd\n" + "\t\t\t\tWHERE ersd.engineer_id = ?\n" + "\t\t\t\tUNION\n" + "\t\t\t\tSELECT jsd.joint_service_desk_id\n" + "\t\t\t\tFROM joint_service_desk jsd\n" + "\t\t\t\tWHERE jsd.service_desk_id IN (\n" + "\t\t\t\t\tSELECT ersd2.service_desk_id\n" + "\t\t\t\t\tFROM engineer_r_service_desk ersd2\n" + "\t\t\t\t\tWHERE ersd2.engineer_id = ?\n" + "\t\t\t\t)\n" + "\t\t\t) temp\n" + "\t\t)\n" + "\t\tOR t.engineer_id = ?\n" + "\t\tOR t.user_id = (\n" + "\t\t\tSELECT e.user_id\n" + "\t\t\tFROM engineer e\n" + "\t\t\tWHERE e.id = ?\n" + "\t\t)\n" + "\t\tOR t.id IN (\n" + "\t\t\tSELECT trc.ticket_id\n" + "\t\t\tFROM ticket_r_cc trc\n" + "\t\t\tWHERE trc.user_id = (\n" + "\t\t\t\t\tSELECT e.user_id\n" + "\t\t\t\t\tFROM engineer e\n" + "\t\t\t\t\tWHERE e.id = ?\n" + "\t\t\t\t)\n" + "\t\t\t\tOR trc.service_desk_id = (\n" + "\t\t\t\t\tSELECT e.default_service_desk_id\n" + "\t\t\t\t\tFROM engineer e\n" + "\t\t\t\t\tWHERE e.id = ?\n" + "\t\t\t\t)\n" + "\t\t)\n" + "\t\tOR t.requester_id = (\n" + "\t\t\tSELECT e.user_id\n" + "\t\t\tFROM engineer e\n" + "\t\t\tWHERE e.id = ?\n" + "\t\t))\n" + "ORDER BY t.id DESC"; Statement statement = pm.parse(new StringReader(sql)); int i=0; }