SQL解析工具jsqlParser

1. Maven依赖

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.5</version>
</dependency>
2. SQL校验

@Override
    public boolean validAdvancedSql(String advancedSql, List<String> allowedColumns) {
        if (StringUtils.isBlank(advancedSql)) {
            return true;
        }

        // 允许的查询操作符不能为空
        Map<String, String> queryOperatorMap = aptSurveyLogService.getSysDictDataByType("advanced_query_operator");
        if (CollectionUtils.isEmpty(queryOperatorMap)) {
            return false;
        }

        Set<String> allowedOperators = queryOperatorMap.keySet();
        // 构建完整SQL语句进行解析
        String sql = String.format("SELECT id FROM table WHERE %s", advancedSql);
        try {
            Select statement = (Select) CCJSqlParserUtil.parse(sql);
            PlainSelect plain = (PlainSelect) statement.getSelectBody();

            return validateExpression(plain.getWhere(), allowedColumns, allowedOperators);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 校验SQL中where语句是否合法
     *
     * @param expression sql表达式
     * @param allowedColumns 允许的列
     * @param allowedOperators 允许的操作符
     * @return 是否合法
     */
    private boolean validateExpression(Expression expression, List<String> allowedColumns, Set<String> allowedOperators) {
        if (expression instanceof AndExpression || expression instanceof OrExpression) {
            // 处理AND/OR逻辑表达式
            BinaryExpression binaryExpr = (BinaryExpression) expression;
            return validateExpression(binaryExpr.getLeftExpression(), allowedColumns, allowedOperators) && validateExpression(binaryExpr.getRightExpression(), allowedColumns, allowedOperators);
        } else if (expression instanceof Parenthesis) {
            // 处理括号表达式
            return validateExpression(((Parenthesis) expression).getExpression(), allowedColumns, allowedOperators);
        } else if (expression instanceof NotExpression) {
            // 处理Not条件
            return validateExpression(((NotExpression) expression).getExpression(), allowedColumns, allowedOperators);
        } else if (expression instanceof ComparisonOperator) {
            // 处理比较操作符
            ComparisonOperator comparison = (ComparisonOperator) expression;
            // 验证左操作数是否为合法列名
            if (!(comparison.getLeftExpression() instanceof Column)) {
                return false;
            }

            String columnName = ((Column) comparison.getLeftExpression()).getColumnName();
            if (!CollectionUtils.isEmpty(allowedColumns) && !allowedColumns.contains(columnName)) {
                return false;
            }

            // 验证操作符
            return allowedOperators.contains(comparison.getStringExpression());
        } else if (expression instanceof InExpression) {
            // 处理IN表达式
            InExpression inExpr = (InExpression) expression;
            if (!(inExpr.getLeftExpression() instanceof Column)) {
                return false;
            }

            String columnName = ((Column) inExpr.getLeftExpression()).getColumnName();
            if (!CollectionUtils.isEmpty(allowedColumns) && !allowedColumns.contains(columnName)) {
                return false;
            }

            return allowedOperators.contains(inExpr.isNot() ? "NOT IN" : "IN");
        } else if (expression instanceof LikeExpression) {
            // 处理LIKE表达式
            LikeExpression likeExpr = (LikeExpression) expression;
            if (!(likeExpr.getLeftExpression() instanceof Column)) {
                return false;
            }

            String columnName = ((Column) likeExpr.getLeftExpression()).getColumnName();
            if (!CollectionUtils.isEmpty(allowedColumns) && !allowedColumns.contains(columnName)) {
                return false;
            }

            return allowedOperators.contains(likeExpr.isNot() ? "NOT LIKE" : "LIKE");
        } else if (expression instanceof IsNullExpression) {
            // 处理IS NULL/IS NOT NULL
            IsNullExpression isNullExpr = (IsNullExpression) expression;
            if (!(isNullExpr.getLeftExpression() instanceof Column)) {
                return false;
            }

            String columnName = ((Column) isNullExpr.getLeftExpression()).getColumnName();
            if (!CollectionUtils.isEmpty(allowedColumns) && !allowedColumns.contains(columnName)) {
                return false;
            }

            return allowedOperators.contains(isNullExpr.isNot() ? "IS NOT NULL" : "IS NULL");
        } else if (expression instanceof Between) {
            // 处理BETWEEN
            Between betweenExpr = (Between) expression;
            if (!(betweenExpr.getLeftExpression() instanceof Column)) {
                return false;
            }

            String columnName = ((Column) betweenExpr.getLeftExpression()).getColumnName();
            if (!CollectionUtils.isEmpty(allowedColumns) && !allowedColumns.contains(columnName)) {
                return false;
            }

            return allowedOperators.contains("BETWEEN");
        } else {
            return false;
        }
    }