解析SQL并添加条件的两种实现方式
在最近的工作中，需要用到解析SQL并添加相应的条件以实现某个功能。调研时发现了两种方案，在此之前并没有接触过相应的SQL解析方案。故记录一下，以供参考
SQL解析用途
在之前的开发中，了解最多的关于SQL解析就是mysql架构的SQL解析器，但是并没有深入了解过该原理。
那么在业务层进行SQL的解析有何用途？我认为最关键的是权限，在当前环境中，数据安全是方方面面的。在大型组织中，人员繁杂，对于数据权限的管控非常严格，如何控制不同成员的数据权限非常重要。
数据库中数据的透出大都是通过SQL进行查询的，那么业务层就可以在组织中人员查询数据的时候，根据不同的人员赋予不同的权限。即使对数据的保护，也是减少人员干涉的，减轻DBA的审核工作。
如阿里的ADB可以对不同的员工的操作数据库的权限进行控制。
Coding
目前解析SQL方式一般有JSQL Parser、 ANTLR4、JavaCC几种方式。
像Druid中使用的SQL解析就是JSQL Parser的方式。而ANTLR4是开源的语法解析框架，如果是自己动手实现，比较困难，同时也需要较长的时间进行验证。所以选择了Druid中已经实现的SQL解析器实现当前这个功能---解析SQL，并根据用户添加权限。因为Druid并不支持大数据相关的数据源SQL解析，所以我也根据部分数据源中已经实现的解析框架实现了该功能。
Druid 解析SQL语法的代码实现
阿里的Druid框架，支持解析SQL并添加相应的条件。实现起来比较简单。以下代码仅为示例，具体实现还要以需求进行进一步开发
/*** @author leo_zl 2022/2/28*/publicclassMysqlParserVisitorextendsMySqlASTVisitorAdapter { /*** 限制访问的表名* [parser_table,demo_table]*/privatefinalSettableNameSet; /*** 每个表的约束条件** {* "parser_table":"id=1" ,* "demo_table":"id=2"* }*/privatefinalMaptableConditionMap; publicMysqlParserVisitor(SettableNameSet, MaptableConditionMap) { this.tableNameSet=tableNameSet; this.tableConditionMap=tableConditionMap; } publicbooleanvisit(SQLExprTableSourcereference) { StringtableName=reference.getName().toString(); // 符合目标表源名if (isTargetTable(tableName)) { SQLObjectparent=reference.getParent(); Stringalias=reference.getAlias(); // 回溯到选择语句while (!(parentinstanceofMySqlSelectQueryBlock) &&parent!=null) { parent=parent.getParent(); } // 插入行控制条件Stringconditions=formCondition(tableName, alias); if (StringUtils.isNotEmpty(conditions) &&parent!=null) { ((MySqlSelectQueryBlock) parent).addCondition(conditions); } } returnfalse; } privateStringformCondition(StringtableName, Stringalias) { Stringcondition=tableConditionMap.get(tableName); if (StringUtils.isEmpty(condition)) { returnnull; } returnalias==null?condition : alias+"."+condition; } privatebooleanisTargetTable(StringtableName) { returntableNameSet.contains(tableName); } }
单测以及使用方式
/*** @author leo_zl 2022/2/28*/(PowerMockRunner.class) (MysqlParserVisitor.class) publicclassMysqlParserVisitorTest { publicvoidvisit() { SettableNameSet=newHashSet<>(); tableNameSet.add("parser_table"); MaptableConditionMap=newHashMap<>(); tableConditionMap.put("parser_table", "id=1"); StringinputSql="SELECT * FROM parser_table"; SQLStatementsqlStatement=SQLUtils.parseSingleStatement(inputSql, JdbcConstants.MYSQL); MysqlParserVisitorvisitor=newMysqlParserVisitor(tableNameSet, tableConditionMap); sqlStatement.accept(visitor); StringexpectSql="SELECT *\n"+"FROM parser_table\n"+"WHERE id = 1"; Assert.assertEquals(expectSql, sqlStatement.toString()); } }
### ANTLR4 方式的解析SQL
此处说的ANTLR4的解析SQL的方式，指的是复用Phoenix的解析器进行实现的。并非从ANTLR4框架构架的。
/*** @author leo_zl 2022/2/9*/publicclassPhoenixSqlParser { privatestaticfinalLoggerLOG=LoggerFactory.getLogger(PhoenixSqlParser.class); publicStringprivilegeControl(Stringsql, MapprivilegeInfo) { LOG.info("original sql :\n{}", sql); SQLParsersqlParser=newSQLParser(sql); try { SelectStatementselectStatement=sqlParser.parseQuery(); selectStatement=parserSelect(selectStatement, privilegeInfo); if (selectStatement.isUnion()) { Listselects=selectStatement.getSelects(); returnStringUtils.join(selects, " \n UNION ALL \n"); } returnselectStatement.toString(); } catch (SQLExceptione) { LOG.info("解析SQL 异常:{}", e.getMessage()); } returnsql; } privateSelectStatementparserSelect(SelectStatementselectStatement, MapprivilegeInfo) throwsSQLException { if (selectStatement.isUnion()) { returnparseUnion(selectStatement, privilegeInfo); } // 获取from nodereturnparseTable(selectStatement, privilegeInfo); } privateSelectStatementparseUnion(SelectStatementselectStatement, MapprivilegeInfo) throwsSQLException { Listselects=selectStatement.getSelects(); ListnewStatements=newArrayList<>(); for (SelectStatementselect : selects) { SelectStatementnewStatement=parserSelect(select, privilegeInfo); newStatements.add(newStatement); } StringdestSql=StringUtils.join(newStatements, " \n UNION ALL "); SQLParsersqlParser=newSQLParser(destSql); returnsqlParser.parseQuery(); } privateSelectStatementparseTable(SelectStatementselectStatement, MapprivilegeInfo) throwsSQLException { TableNodetableNode=selectStatement.getFrom(); if (tableNodeinstanceofJoinTableNode) { // join 查询JoinTableNodejoinTableNode= (JoinTableNode) tableNode; TableNodelhs=joinTableNode.getLHS(); SelectStatementleftSelect=parseJoinTable(selectStatement, lhs, privilegeInfo); TableNoderhs=joinTableNode.getRHS(); returnparseJoinTable(leftSelect, rhs, privilegeInfo); } else { returnparseJoinTable(selectStatement, tableNode, privilegeInfo); } } privateSelectStatementparseJoinTable(SelectStatementselectStatement, TableNodetableNode, MapprivilegeInfo) throwsSQLException { if (tableNodeinstanceofDerivedTableNode) { // 子查询DerivedTableNodesubQuery= (DerivedTableNode) tableNode; SelectStatementsubQuerySelect=subQuery.getSelect(); returnparserSelect(subQuerySelect, privilegeInfo); } elseif (tableNodeinstanceofNamedTableNode) { // 普通表NamedTableNodenamedTableNode= (NamedTableNode) tableNode; TableNamename=namedTableNode.getName(); Stringalias=namedTableNode.getAlias(); ParseNodeparseNode=constructParseNode(name, alias, privilegeInfo); returnselectStatement.combine(parseNode); } returnselectStatement; } privateParseNodeconstructParseNode(TableNametableName, StringaliasName, MapprivilegeInfo) throwsSQLException { Stringconditions=privilegeInfo.get(tableName.getTableName()); if (Objects.isNull(conditions)) { returnnull; } conditions=aliasName==null?conditions : aliasName+"."+conditions; // 因为不熟悉Phoenix parser的API，所以暂时使用这种笨办法，构建对应的nodeStringBuildersqlBuilder=newStringBuilder("SELECT * FROM "+tableName+" "); if (Objects.nonNull(aliasName)) { sqlBuilder.append(aliasName).append(" "); } Stringsql=sqlBuilder.append("WHERE ").append(conditions).toString(); SQLParsersqlParser=newSQLParser(sql); returnsqlParser.parseQuery().getWhere(); } }
单测以及使用
/*** @author leo_zl 2022/2/28*/(PowerMockRunner.class) ({"javax.*"}) publicclassPhoenixSqlParserTest { privatePhoenixSqlParserphoenixSqlParser; publicvoidprivilegeControl() { StringinputSql="SELECT full_name FROM SALES_PERSON WHERE ranking >= 5.0\n"+" UNION ALL SELECT reviewer_name FROM CUSTOMER_REVIEW WHERE score >= 8.0\n"+"\n"; Mapconditions=newHashMap<>(); conditions.put("SALES_PERSON","id = 1"); StringresultSql=phoenixSqlParser.privilegeControl(inputSql, conditions); StringexpectSql="SELECT FULL_NAME FROM SALES_PERSON WHERE (RANKING >= 5.0 AND ID = 1) \n"+" UNION ALL \n"+"SELECT REVIEWER_NAME FROM CUSTOMER_REVIEW WHERE SCORE >= 8.0"; Assert.assertEquals(expectSql,resultSql); } }
后记
Druid虽然支持了大部分的数据源，但是比较灵活的实现方式还是ANTLR4。但是因为时间的缘故，并没有选择使用ANTLR4进行解析SQL。
以后有机会还是可以去实现一下，增长见识。
