如何用Java从SQL中提取出涉及到的表名、列名?


=Start=

缘由:

记录整理一下如何用Java从SQL中提取出涉及到的表名和列名。

正文:

参考解答:

最早我是想用Python实现这个功能的,因为在字符串处理方面Python相比于Java来说要方便太多,但是在网上找了一圈资料后发现,如果想单纯用Python自己手写实现,难度有点大,而且很难保证提取的效果。而Java在资源生态这方面的优势就比较大了,比如:

ANTLR4、Apache Calcite、Alibaba Druid

上面的这几个工具各有优缺点,在此不细说,后续会整理一些这方面的资料方便需要的人进行参考。这里主要把使用Alibaba Druid提取SQL表名、列名的方法实践整理并记录一下,方便自己查阅和参考。

Alibaba Druid其实是阿里开源的一个(有统计、监控功能的)数据库连接池实现,作为数据库连接池,它自身也有一个SQL解析模块,这里就是借助它提供的SQL解析模块实现我们需要的一些功能。

代码样例:

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Created by ixyzero on 2019/5/10.
 */
public class ParseSQL {
    public static void main(String[] args) {
        String sql = "SELECT\n" +
                "  a.id,\n" +
                "  username,\n" +
                "  b.workflow_name,\n" +
                "  reviewok_time\n" +
                "FROM archer.sql_users a\n" +
                "  JOIN sql_workflow b ON a.username = b.engineer;\n";

        // 新建 MySQL Parser
        MySqlStatementParser parser = new MySqlStatementParser(sql);

        // 使用Parser解析生成AST,这里SQLStatement就是AST
        SQLStatement statement = parser.parseStatement();

        // 使用visitor来访问AST
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        statement.accept(visitor);

        // System.out.println(sql);

        System.out.println(visitor.getAliasMap());
        System.out.println();

        // 从visitor中拿出你所关注的信息
        // System.out.println(visitor.getTables());
        // System.out.println();
        // System.out.println(visitor.getColumns());
        // System.out.println();


        //使用mysql解析
        MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sql) ;

        //解析select查询
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) mySqlStatementParser.parseSelect();
        SQLSelect sqlSelect = sqlSelectStatement.getSelect() ;

        //获取sql查询块
        SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock)sqlSelect.getQuery() ;
        System.out.println(sqlSelectQueryBlock.getSelectList());

        //格式化输出
        String result = SQLUtils.format(sql, JdbcConstants.MYSQL);
        System.out.println("\n");
        System.out.println(result); // 缺省大写格式
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);

        //解析出的独立语句的个数
        System.out.println("\nsize is:" + stmtList.size() + "\n");
        for (int i = 0; i < stmtList.size(); i++) {

            SQLStatement stmt = stmtList.get(i);

            MySqlSchemaStatVisitor visitorX = new MySqlSchemaStatVisitor();
            stmt.accept(visitorX);

            // Map<String, String> aliasmap = visitorX.getAliasMap();
            // for (Iterator iterator = aliasmap.keySet().iterator(); iterator.hasNext();) {
            //     String key = iterator.next().toString();
            //     System.out.println("[ALIAS]" + key + " - " + aliasmap.get(key));
            // }
            // //
            // Set<TableStat.Column> groupby_col = visitorX.getGroupByColumns();
            // for (Iterator iterator = groupby_col.iterator(); iterator.hasNext();) {
            //     TableStat.Column column = (TableStat.Column) iterator.next();
            //     System.out.println("[GROUP]" + column.toString());
            // }

            //获取表名称
            System.out.println("table names:");
            Map<TableStat.Name, TableStat> tabmap = visitorX.getTables();
            for (Iterator iterator = tabmap.keySet().iterator(); iterator.hasNext();) {
                TableStat.Name name = (TableStat.Name) iterator.next();
                System.out.println(name.toString() + " - " + tabmap.get(name).toString());
            }
            //System.out.println("Tables : " + visitorX.getCurrentTable());
            //获取操作方法名称,依赖于表名称
            System.out.println("Manipulation : " + visitorX.getTables());
            //获取字段名称
            System.out.println("fields : " + visitorX.getColumns());
            System.out.println("");
        }
    }
}

参考链接:

关于SQL解析,为何编程语言解析器ANTLR更胜一筹?
https://mp.weixin.qq.com/s/Di11ZzqiiY0tJYDRhfdCPw

Parsing mysql using ANTLR4 simple example
https://stackoverflow.com/questions/49769147/parsing-mysql-using-antlr4-simple-example

Dynamic Data Masking 动态数据脱敏
https://hhyo.github.io/2018/04/16/data-masking/

几种基于Java的SQL解析工具的比较与调用
https://www.cnblogs.com/blueglass/p/6286814.html
http://www.zhongruitech.com/168055984.html

=END=


《“如何用Java从SQL中提取出涉及到的表名、列名?”》 有 11 条评论

  1. Alibaba Druid SQL Parser
    https://github.com/alibaba/druid/wiki/SQL-Parser
    `
    和Antlr生成的SQL有很大不同的是,Druid SQL Parser性能非常好,可以用于生产环境直接对SQL进行分析处理。
    Druid的SQL Parser是手工编写,性能非常好,目标就是在生产环境运行时使用的SQL Parser,性能比antlr、javacc之类工具生成的Parser快10倍甚至100倍以上。

    Druid的sql parser是目前支持各种数据语法最完备的SQL Parser。
    `
    Alibaba Druid SQL_Parser_Demo_visitor
    https://github.com/alibaba/druid/wiki/SQL_Parser_Demo_visitor

  2. 用 Go 构建一个 SQL 解析器
    https://mp.weixin.qq.com/s/F9z5oPk-mSauliOHk3FJEA
    http://github.com/marianogappa/sqlparser
    `
    本文旨在简单介绍如何在 Go 中构造 LL(1) 解析器,在本例中用于解析SQL查询。

    为了简单起见,我们将处理子选择、函数、复杂嵌套表达式和所有 SQL 风格都支持的其他特性。这些特性与我们将要使用的策略紧密相关。

    一个解析器包含两个部分:
    词法分析:也就是“Tokeniser”
    语法分析:AST 的创建

    这个实验非常适合:
    · 学习 LL(1) 解析器算法
    · 自定义解析无依赖关系的简单语法

    然而,这种方法可能会变得单调乏味,而且有一定的局限性。考虑一下如何解析任意复杂的复合表达式(例如 sqrt(a) =(1 *(2 + 3)))。
    要获得更强大的解析模型,请查看解析器组合符。goyacc 是一个流行的Go实现。
    `

  3. 只是简单的获取了所有的列名,能 不能获取到 最后的metadata 里面的列名呢?
    实现类似 如 resultSet.getMetaData().getColumns()

    • 根本实现不了我说的这个功能,而且 解析出来的 ATS 乱七八糟,毫无章法,根本无法实际的使用起来。
      druid的SQL 解析只适合简单 的判断 SQL类型,格式化,等等基本功能。要想实现 自定义的语法,还是 得在druid 基础上 手写 lexer的逻辑。

  4. 基于语法树对SQL语句敏感字段进行脱敏的一种解决方案
    https://blog.csdn.net/lovepeacee/article/details/120956630
    `
    一:需求
    二:思路演进
    三:实现功能
    四:待优化
    五:小马过河
    六:代码部分

    二:思路演进

    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信息,对上层关系进行忽略.因为只有最底层数据包含了敏感信息,只要把最底层的敏感信息去除就可以解决问题.
    `

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注