文章目录
- 一:需求
- 二:思路演进
- 三:实现功能
- 四:待优化
- 五:小马过河
- 六:代码部分
一:需求
一个面向开发人员使用的sql查询网页端工具,
同时要对展示的敏感字段进行脱敏处理。
例:需要在生产上执行 select * from cust where acct='123';
cust表中存在phone_no 手机号,通过本文中的工具对该字段进行脱敏.
原sql经该工具处理后返回效果select
-- 对phone_no进行脱敏后的效果
case when phone_no is null then null else '***' end as phone_no,
acct
from cust
where acct = '123';二:思路演进
v0: 判断执行sql后的返回结果,如果列名为敏感字段.将敏感字段中的数据替换成 ‘***’ (对列起别名失效)
v1: 匹配前端输入的sql是否存在敏感字段,如果存在.拒绝执行 (不支持select * ,误伤where中条件)
v2: 使用链家开源的d18n工具,但是不支持对列起别名,所以没有深入研究该插件.
v3: 使用sql解析器进行解析,对select -待解析字段1- from -待解析字段2- where … 中的字段进行解析,判断是否存在敏感字段,如果存在则用常量对敏感列进行替代
例: select id ,name ,phone_no phoneNo from (select * from b) a where a.phone_no =‘10086’
当出现复杂的union,subQuery,join等复杂的嵌套查询时, 对红色部分进行穷举法解析,递归处理里面的敏感字段(v3.2).
v3.1: 简单sql:使用druid进行sql解析,druid返回的SQLSelectStatement可以获取到实际使用列名和别名,所以很方便用来进行处理.
在判断该列为敏感列后将该列重新赋值为常量 如 select phone_no from a 经解析后如果存在敏感列则修改为 select ‘***’ as phone_no from a;
或者 select case when phone_no is null then null else ‘***’ end as phone_no from a (推荐该修改方式,展示数据更易读,也可以避免查询不存在的列)
v3.2: 复杂sql:通过解析器获取最底层sql(v3.1 简单sql)的java类型(SQLJoinTableSource(join语句),SQLExprTableSource(常规简单sql语句))
简单来说就是只分析最低层的sql信息,对上层关系进行忽略.因为只有最底层数据包含了敏感信息,只要把最底层的敏感信息去除就可以解决问题.
三:实现功能
1. 对 select * ,select a.*,b.*,c.* ...的脱敏支持
2. 对 函数concat,substr ...的脱敏支持
3. 对 复杂查询语句 union,subquery,join 的支持四:待优化
目前的版本已经支持我实际的使用场景,但仍存在优化空间:
- 针对特定表的特定字段进行脱敏
- 对字段制定个性化脱敏规则
五:小马过河
因为网上druid解析器的资料很少,所以整个过程都是边debug边code,通过druid对sql解析后的返回结果看sql中的哪一个部分属于哪一个类型,然后在进行判断。
sql语句中小元素(各种函数)的复杂性,也就造成了该工具并不能完美的解析所有场景的sql,但是已经包含了日常用到的sql函数,复杂的查询场景。
尤其值得说的是对于 * 的解析支持,这一部分的解决方案我认为比较完美
六:代码部分
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.parser.ParserException;
import org.springframework.util.StopWatch;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* 功能描述: 通过druid SQL解析器处理敏感字段
*
* @author bigHead
* @date 2021/10/13
*/
public class QuietSqlParser {
public static void main(String[] args) {
QuietSqlParser quietSqlParser = new QuietSqlParser();
quietSqlParser.parseSql("database1", "select * from table1_3 a,table1_2 b,table1_1 c");
}
/**
* 敏感列集合
*/
private static final Set<String> SENSITIVE_COLUMNS = new HashSet() {{
add("name");
add("id_card");
add("birthday");
add("phone_no");
}};
/**
* 不同数据库对应表的列属性
*/
private static final Map<String, Map<String, String>> DATABASE_TABLE_MAP = new HashMap() {{
put("database1", new HashMap<String, String>() {{
put("table1_1", "id,name");
put("table1_2", "id,gender");
put("table1_3", "id,id_card");
}});
put("database2", new HashMap<String, String>() {{
put("table2_1", "id,phone_no");
put("table2_2", "id,gender");
put("table2_3", "id,birthday");
}});
}};
/**
* @param dbName 数据库名，支持多数据源
* @param originSql 原sql
* @return
*/
public String parseSql(String dbName, String originSql) {
StopWatch stopWatch = new StopWatch();
stopWatch.start();
List<SQLStatement> list = SQLUtils.parseStatements(originSql, "mysql");
// 1.获取sql解析结果
SQLSelectStatement sqlStatement1 = (SQLSelectStatement) list.get(0);
SQLSelect select = sqlStatement1.getSelect();
// 2.先判断是什么类型的query:union,join,subQuery...
extractedSelect(dbName, select);
// 3.最后,将sql返回
String s = sqlStatement1.toString().replace("###.", "");
stopWatch.stop();
System.err.println("-- 最终sql-耗时[{" + stopWatch.getTotalTimeSeconds() + "}]:\n[{" + s + "}]");
return s;
}
private void extractedSelect(String dbName, SQLSelect select) {
SQLSelectQuery query = select.getQuery();
extractedQuery(dbName, query);
}
/**
* @param dbName 数据库名称
* @param query 语句对象
* @author bigHead
* @date 2021/10/13
* 功能描述: 判断整条sql是什么类型的语句后,走到最终的MySqlSelectQueryBlock下的 extracted进行字段解析
*/
private void extractedQuery(String dbName, SQLSelectQuery query) {
// 1.别名映射map,在这个位置正好解决在union语句下的别名重复问题
Map<String, String> aliasTableMap = new HashMap<>();
// 2.union类型的查询
if (query instanceof SQLUnionQuery) {
SQLUnionQuery unionQuery = (SQLUnionQuery) query;
// 2.1 解析两个子句
SQLSelectQuery left = unionQuery.getLeft();
extractedQuery(dbName, left);
SQLSelectQuery right = unionQuery.getRight();
extractedQuery(dbName, right);
} else if (query instanceof MySqlSelectQueryBlock) {
// 3. where 子查询类型 a3 : Q:判断 where中包含子句 A:where中的字段不会出现在结果集中,所以不需要解析
MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) query;
// 3.1 from 也重要,里面包含了 column;通过from后面语句判断是什么类型的语句(subQuery,union...)
SQLTableSource from = queryBlock.getFrom();
extractedFrom(dbName, from, aliasTableMap);
// 3.2 column 重要 (select <--> from 中的列名信息)
List<SQLSelectItem> selectList = queryBlock.getSelectList();
// 3.3 对column进行解析敏感字段
extracted(dbName, aliasTableMap, selectList);
} else {
throw new ParserException("暂不支持,请联系管理员!");
}
}
/**
* @param dbName 数据库名
* @param aliasTableMap 表名映射map
* @param selectList column集合
* @author bigHead
* @date 2021/10/13
* 功能描述: 解析 select <--> from 之间的信息
*/
private void extracted(String dbName, Map<String, String> aliasTableMap, List<SQLSelectItem> selectList) {
for (SQLSelectItem sqlSelectItem : selectList) {
// 1.获取列信息
SQLExpr expr1 = sqlSelectItem.getExpr();
extractedExprs(dbName, aliasTableMap, sqlSelectItem, expr1);
}
}
/**
* @param dbName 数据库名
* @param sqlTableSource from后面的信息
* @param aliasTableMap 表表名映射map
* @author bigHead
* @date 2021/10/13
* 功能描述: 解析 from 后的信息
*/
private void extractedFrom(String dbName, SQLTableSource sqlTableSource, Map<String, String> aliasTableMap) {
// 1.普通查询子句(所有复杂的语句经解析递归成单语句后都会进入到这个判断中) ★★★
if (sqlTableSource instanceof SQLExprTableSource) {
// 1.1 普通表名
SQLExprTableSource exprTableSource = (SQLExprTableSource) sqlTableSource;
// 1.2 获取后面的表名(不能确定是真实表名还是别名)
SQLExpr expr = exprTableSource.getExpr();
String s = expr.toString();
// 1.3 将表名放到map中进行结构组装 Q:针对子查询设置true标志 A:不用设置也行,直接判断如果存在别名映射,那么就说明是终点,因为只往终点中存放值
String alias = exprTableSource.getAlias();
aliasTableMap.put(alias, s);
} else if (sqlTableSource instanceof SQLJoinTableSource) {
// 2.join 子句
SQLJoinTableSource joinTableSource = (SQLJoinTableSource) sqlTableSource;
// 2.1 join左边的子句
SQLTableSource left = joinTableSource.getLeft();
extractedFrom(dbName, left, aliasTableMap);
// 2.2 join右边的子句
SQLTableSource right = joinTableSource.getRight();
extractedFrom(dbName, right, aliasTableMap);
// 2.3 如果是这个类型的子句,因为没有到 itemList那一层 所以不处理item
} else if (sqlTableSource instanceof SQLUnionQueryTableSource) {
// 3.from 后面的 union 子句
SQLUnionQueryTableSource unionQueryTableSource = (SQLUnionQueryTableSource) sqlTableSource;
SQLUnionQuery union = unionQueryTableSource.getUnion();
extractedQuery(dbName, union);
// 3.1 如果是这个类型的子句,因为没有到 itemList那一层 所以不处理item
} else if (sqlTableSource instanceof SQLSubqueryTableSource) {
// 4.from 后面的 子查询 子句
SQLSubqueryTableSource subqueryTableSource = (SQLSubqueryTableSource) sqlTableSource;
// 4.2 from后面的子句
SQLSelect select = subqueryTableSource.getSelect();
extractedSelect(dbName, select);
// 4.1 如果是这个类型的子句,因为没有到 itemList那一层 所以不处理item
} else {
throw new ParserException("暂不支持,请联系管理员!");
}
}
/**
* @param dbName 数据库名
* @param aliasTableMap 表名映射
* @param topSelectItem expr对应的column信息
* @param sqlExprs 举例: 'constant' as a, sum() , a+b , concat() , substr() 都属于expr
* @author bigHead
* @date 2021/10/13
* 功能描述: 解析 select <--> from 之间具体的字段信息
*/
private void extractedExprs(String dbName, Map<String, String> aliasTableMap, SQLSelectItem topSelectItem, SQLObject... sqlExprs) {
SQLObject itemParent0 = topSelectItem.getParent();
// 一:判断是不是最底层的查询语句
/*
1.SQLExprTableSource 肯定是底层的
2.SQLJoinTableSource 必须left 或 right 有一个是 SQLJoinTableSource 下的SQLExprTableSource类型才行
*/
if (itemParent0 instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock parent3 = (MySqlSelectQueryBlock) itemParent0;
SQLTableSource from = parent3.getFrom();
// 宇宙的尽头 SQLExprTableSource,SQLJoinTableSource
if (!(from instanceof SQLExprTableSource)
&& !(from instanceof SQLJoinTableSource)) {
return;
}
} else {
throw new ParserException("暂不支持其他数据库,请联系管理员!");
}
// 二:循环selectItem
for (SQLObject sqlExpr : sqlExprs) {
if (sqlExpr instanceof SQLQueryExpr) {
// 1.包含子句,如: select name , (select id from ccs_a where id ='1') from ccs_b; 里面的子句
SQLQueryExpr queryExpr = (SQLQueryExpr) sqlExpr;
List<SQLSelectItem> selectList1 = queryExpr.getSubQuery().getQueryBlock().getSelectList();
extracted(dbName, aliasTableMap, selectList1);
} else if (sqlExpr instanceof SQLIdentifierExpr) {
// 2.普通字段，如: id_no as idNo 不一定是敏感字段,只有最底层的才作为敏感字段进行解析
SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) sqlExpr;
String columnName = identifierExpr.getName();
// 2.1 如果他的parent是SQLPropertyExpr的话,说明 它是a.id_no中的a
if (isSensitive(identifierExpr.getLowerName())) {
identifierExpr.setName("'***'");
String alias = topSelectItem.getAlias();
// 2.2 Q:考虑别名是不是也会是敏感字段 A:不用,不管是别名还是原来的名属于敏感字段,都只对真实列做处理
topSelectItem.setAlias(alias == null ? columnName : alias);
}
} else if (sqlExpr instanceof SQLPropertyExpr) {
/*
3. log.idNo SQLPropertyExpr虚拟名称
如: WHEN a.id_no = '' THEN a.id_no -- 后面的a.id_no需要处理
select log.idNo operator1 from ccs_customer log where log.id_no in
( select id_no from ccs_customer)
*/
SQLPropertyExpr expr = (SQLPropertyExpr) sqlExpr;
String name = expr.getName();
// 3.1 处理select a.* from ccs_customer a;的情况,从aliasTableMap中获取表名
if ("*".equals(name) && aliasTableMap.get(expr.getOwnernName()) != null) {
String fullColumns = getFullColumnsByAlias(dbName, expr.getOwnernName(), aliasTableMap);
expr.setName(fullColumns);
SQLExpr owner = expr.getOwner();
// a.name 的情况
if (owner instanceof SQLIdentifierExpr) {
((SQLIdentifierExpr) owner).setName("###");
}
}
// 3.2 判断name是不是敏感字段
if (isSensitive(name)) {
SQLExpr owner = expr.getOwner();
// a.name 的情况
if (owner instanceof SQLIdentifierExpr) {
/*
3.3
Q:递归防止多层别名,SQLPropertyExpr的终点应该是 SQLIdentifierExpr
A:不用考虑 a.b.id_no 的情况 extractedExprs(topSelectItem, expr);
将 expr: a.id_no 改为 ###.** 再加他的parent一个别名,在最后输出时将###.** 替换
*/
((SQLIdentifierExpr) owner).setName("###");
expr.setName("'***'");
String alias = topSelectItem.getAlias();
if (alias == null) {
// 3.4 取原字段在后面拼接
topSelectItem.setAlias(name);
}
} else {
// 未考虑到的情况
expr.setName("'***'");
}
}
} else if (sqlExpr instanceof SQLMethodInvokeExpr) {
// 4.方法,如:substr , concat , case when then ...
SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) sqlExpr;
List<SQLExpr> arguments = methodInvokeExpr.getChildren();
SQLExpr[] sqlExprArr = new SQLExpr[arguments.size()];
arguments.toArray(sqlExprArr);
// 4.1 获取方法中的子元素进行递归
extractedExprs(dbName, aliasTableMap, topSelectItem, sqlExprArr);
} else if (sqlExpr instanceof SQLBinaryOpExpr) {
// 5. +-= 操作 = 判断符
SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) sqlExpr;
// 5.1 获取判断符两边的元素进行解析
List<SQLExpr> children = binaryOpExpr.getChildren();
SQLExpr[] sqlExprs1 = new SQLExpr[children.size()];
children.toArray(sqlExprs1);
extractedExprs(dbName, aliasTableMap, topSelectItem, sqlExprs1);
} else if (sqlExpr instanceof SQLAggregateExpr) {
// 7. 聚合操作 如:sum
SQLAggregateExpr aggregateExpr = (SQLAggregateExpr) sqlExpr;
List<SQLExpr> children = aggregateExpr.getChildren();
SQLExpr[] sqlExprs1 = new SQLExpr[children.size()];
children.toArray(sqlExprs1);
extractedExprs(dbName, aliasTableMap, topSelectItem, sqlExprs1);
} else if (sqlExpr instanceof SQLCaseExpr) {
// 8. case 操作
SQLCaseExpr caseExpr = (SQLCaseExpr) sqlExpr;
// 8.1 获取子元素进行解析
List<SQLObject> children = caseExpr.getChildren();
SQLObject[] sqlExprs1 = new SQLObject[children.size()];
children.toArray(sqlExprs1);
extractedExprs(dbName, aliasTableMap, topSelectItem, sqlExprs1);
} else if (sqlExpr instanceof SQLCaseExpr.Item) {
// 8.2 case when中的特殊判断
// 表达式 WHEN id_no = 'CRITICAL' THEN num
SQLCaseExpr.Item itemExpr = (SQLCaseExpr.Item) sqlExpr;
// when中的表达式不进行拦截,(when属于条件表达式),then 和 else 需要判断 都属于valueExpr
// SQLExpr conditionExpr = expr11.getConditionExpr();
SQLExpr valueExpr = itemExpr.getValueExpr();
extractedExprs(dbName, aliasTableMap, topSelectItem, valueExpr);
} else if (sqlExpr instanceof SQLAllColumnExpr) {
/*
10. * 查询所有数据
获取表名并对 * (如果a.* 的话不会进到这里来 )进行填充
select * from a 简单的情况
*/
SQLObject itemParent = topSelectItem.getParent();
// 10.1 因为没有别名 所以parent 是自己 '*' 一个selectItem
if (itemParent instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock parent3 = (MySqlSelectQueryBlock) itemParent0;
SQLTableSource from = parent3.getFrom();
if (from instanceof SQLJoinTableSource) {
// 10.2 宇宙的尽头 二
SQLJoinTableSource joinTableSource = (SQLJoinTableSource) from;
StringBuilder colBuilder = new StringBuilder();
// 10.3 处理joinTableSource
popCols4JoinTable(dbName, joinTableSource, colBuilder, aliasTableMap);
SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr(colBuilder.toString());
topSelectItem.setExpr(sqlIdentifierExpr);
} else {
// 宇宙的尽头 一
String realTableName = from.toString();
String columns = getFullColumnsByReal(dbName, realTableName);
// 因为没有修改 * 的方法,所以重新new一个Expr进行赋值
SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr(columns);
topSelectItem.setExpr(sqlIdentifierExpr);
}
}
} else if (sqlExpr instanceof SQLCharExpr
|| sqlExpr instanceof SQLIntegerExpr
|| sqlExpr instanceof SQLNullExpr
|| sqlExpr instanceof SQLInListExpr
|| sqlExpr instanceof SQLNumberExpr
|| sqlExpr instanceof SQLIntervalExpr) {
// 6. 标点符号 如:",".不做解析;方法中的数字 , null
// 6.1 条件类 in(""...) 表达式,不进行判断
// 6.2 SQLNumberExpr 数字类 1.23 不进行判断
} else {
throw new ParserException("暂不支持,请联系管理员!");
}
}
}
/**
* @param dbName 数据库类型
* @param joinTableSource 关联表数据
* @param colBuilder 关联表列名字符串
* @param aliasTableMap 表别名映射
* @author bigHead
* @date 2021/10/15
* 功能描述: 当join语句的查询列为 * 时进行解析
*/
private void popCols4JoinTable(String dbName, SQLJoinTableSource joinTableSource, StringBuilder colBuilder, Map<String, String> aliasTableMap) {
// 1.右侧是 SQLExprTableSource(解析真实的表名进行填充) 而如果是其他类型 (用a.* 代替,因为底层sql进行了脱敏)
SQLTableSource right = joinTableSource.getRight();
String rightAlias = right.getAlias();
if (rightAlias == null) {
throw new ParserException("子表缺少别名!");
}
if (right instanceof SQLExprTableSource) {
// 2.将组装好的放到最右侧
String rightFullColumns = getFullColumnsByAlias(dbName, rightAlias, aliasTableMap);
if (colBuilder.length() == 0) {
// 向后插
colBuilder.append(rightFullColumns);
} else {
// 向前插 保证表名的顺序,a,b,c
colBuilder.insert(0, rightFullColumns.concat(","));
}
} else {
// 2.1 比如subQuery,union类型.直接不填充.给它赋值一个 a.*
if (colBuilder.length() == 0) {
colBuilder.append(rightAlias.concat(".*"));
} else {
colBuilder.insert(0, rightAlias.concat(".*,"));
}
}
// 3.获取左边
SQLTableSource left = joinTableSource.getLeft();
if (left instanceof SQLJoinTableSource) {
SQLJoinTableSource leftJoinTable = (SQLJoinTableSource) left;
popCols4JoinTable(dbName, leftJoinTable, colBuilder, aliasTableMap);
} else {
String leftAlias = left.getAlias();
if (leftAlias == null) {
throw new ParserException("子表缺少别名!");
}
if (left instanceof SQLExprTableSource) {
// 4.将组装好的放到最左侧
String leftFullColumns = getFullColumnsByAlias(dbName, leftAlias, aliasTableMap);
// 5.肯定是向前插
colBuilder.insert(0, leftFullColumns.concat(","));
} else {
// 6.比如subQuery,union类型.直接不填充.给它赋值一个 a.*
colBuilder.insert(0, leftAlias.concat(".*,"));
}
}
}
private static String getFullColumnsByAlias(String dbName, String tableAlias, Map<String, String> aliasTableMap) {
String realTableName = aliasTableMap.get(tableAlias);
if (realTableName == null) {
// 该别名没有对应的真实表名
throw new ParserException("解析失败(该表不存在),请联系管理员! tableAlias:" + tableAlias);
}
// 1.通过数据库名和表名获取列数据
String columns = getFullColumnsByReal2(dbName, realTableName);
if (columns == null) {
throw new ParserException("解析失败(该表对应的表不存在),请联系管理员! realTableName:" + realTableName);
} else {
// 2.对列加别名
String[] split = columns.split(",");
// tableAlias + "." + 无用,前面的owner正好有
// 举例:[a1,a2,a3...] --> "a1,a.a2,a.a3..."
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < split.length; i++) {
String col = split[i];
if (isSensitive(col)) {
stringBuilder.append("case when ")
.append(col)
.append(" is null then null else '***' end as ")
.append(col);
} else {
stringBuilder.append(tableAlias).append(".").append(col);
}
if (i != split.length - 1) {
stringBuilder.append(",");
}
}
return stringBuilder.toString();
}
}
/**
* @param dbName 数据库
* @param realTableName 真实表名
* @return
* @author bigHead
* @date 2021/10/31
* 功能描述: 根据数据库名及真实表名获取对应的列数据,如:id,name,phone_no
*/
private static String getFullColumnsByReal(String dbName, String realTableName) {
// todo 需要判断null
String columns = DATABASE_TABLE_MAP.get(dbName).get(realTableName);
String[] split = columns.split(",");
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < split.length; i++) {
String col = split[i];
if (isSensitive(col)) {
stringBuilder.append("case when ")
.append(col)
.append(" is null then null else '***' end as ")
.append(col);
} else {
stringBuilder.append(col);
}
if (i != split.length - 1) {
stringBuilder.append(",");
}
}
return stringBuilder.toString();
}
private static String getFullColumnsByReal2(String dbName, String realTableName) {
// todo 需要判断null
return DATABASE_TABLE_MAP.get(dbName).get(realTableName);
}
/**
* @param column 待判断字段
* @return
* @author bigHead
* @date 2021/10/31
* 功能描述: 判断传入的参数是否为敏感列
*/
private static boolean isSensitive(String column) {
return SENSITIVE_COLUMNS.contains(column.toLowerCase());
}
}项目中使用的 druid 版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.7</version>
</dependency>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
