最佳实践-SQL语法校验

一、背景

在系统的版本发现过程，随需求的叠加，出现了多次版本的升级脚本填写不对或忘写的情况，虽然项目内严格要求研发必须针对SQL脚本手动执行且无报错前提下，才能验证通过。但效果仍然较差，基于此，组件急需一种可以自动化校验的能力。

参考如下链接中，使用了自已解析SQL进行语法的校验。
https://www.pudn.com/news/628f8474bf399b7f351eff74.html

二、依赖

系统提供一前置检查框架，所以在前置检查框架中叠加对应的校验项即可，通过技术选型，使用alibaba的SQLParserUtils进行检查。依赖的POM

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druidartifactId>
            <version>1.2.11version>
        dependency>
1
2
3
4
5

Druid 是阿里巴巴开源平台上一个数据库连接池实现，结合了 C3P0、DBCP 等 DB 池的优点，同时加入了日志监控。

Druid 可以很好的监控 DB 池连接和 SQL 的执行情况，天生就是针对监控而生的 DB 连接池。

Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。

Spring Boot 2.0 以上默认使用 Hikari 数据源，可以说 Hikari 与 Driud 都是当前 Java Web 上最优秀的数据源，我们来重点介绍 Spring Boot 如何集成 Druid 数据源，如何实现数据库监控。

Github地址：https://github.com/alibaba/druid/

    public static void format(String sql, DbType dbType) {
        String sqlFormat = SQLUtils.format(sql, dbType);
        if (sql.equals(sqlFormat)) {
            throw new RuntimeException("SQL格式错误");
        }
    }
1
2
3
4
5
6

三、测试

 public static void main(String[] args) {
        String sql = "FROM (SELECT p.datekey datekey, p.userid userid, c.clienttype  FROM detail.usersequence_client c JOIN fact.orderpayment p ON p.orderid = c.orderid  JOIN default.user du ON du.userid = p.userid WHERE p.datekey = 20131118 ) base  INSERT OVERWRITE TABLE `test`.`customer_kpi` SELECT base.datekey,   base.clienttype, count(distinct base.userid) buyer_count GROUP BY base.datekey, base.clienttype";

        SQLStatementParser hive = SQLParserUtils.createSQLStatementParser(sql, DbType.hive);
        SQLStatement statement = hive.parseStatement();
        System.out.println(statement);

        // as you can see , using this parseDriver will cause an error
//        ParseDriver pd = new ParseDriver();
//        ASTNode ast = pd.parse(sql);
//        System.out.println(ast.dump());

        sql = "create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name)";
        PgSqlChecker.format(sql, DbType.postgresql);
        SQLStatementParser pg = SQLParserUtils.createSQLStatementParser(sql, DbType.postgresql);
        statement = pg.parseStatement();
        System.out.println(statement);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在具体使用中，发现是存在SQL语句一定的误判，比如：

create index IDX_ltp_basic_info_inventory_name on ltp_basic_info(inventory_name varchar_pattern_ops)
ALTER TABLE eth_resource ADD COLUMN if not exists tpid_set_pg TEXT
1
2

系统中，所以对于特定不支持的语法SQL，代码中进行了人为的异常保护。

一些常见的低级问题还是可以检查出来，将功能嵌入到微服务的前置检查中，一定程度上可以避免人为的失误。

四、实践

1. 当sql语句中有ALTER和IF时会检测会不通过
2. token IDENTIFIER varchar_pattern_ops

    private boolean sqlCheck(String sql) {
        SQLStatementParser parser = null;
        if (isUnSupportSyntax(sql)) {
            return true;
        }
        try {
            parser = SQLParserUtils.createSQLStatementParser(sql, "postgresql");
            parser.parseStatementList();
        } catch (ParserException e) {
            String error = e.getMessage();
            // 当sql语句中有ALTER和IF时会检测会不通过，例如：ALTER TABLE eth_resource ADD COLUMN if not exists tpid_set_pg TEXT;
            // 会报异常：illegal name, pos 14, line 1, column 13, token IF
            if (error.startsWith("illegal name") && error.endsWith("token IF")) {
                return true;
            }
            //无法校验create index IDX_ltp_basic_info_lower_fd_ref on ltp_basic_info(lower_fd_ref varchar_pattern_ops);
            if (error.endsWith("token IDENTIFIER varchar_pattern_ops")) {
                return true;
            }
            LOG.error("SQL is {}, 转换中发生了错误：{} ", sql, e.getMessage());
            checkResult = false;
            return false;
        }
        return true;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25