利用 JSqlParser 防止 SQL 注入

高手文章《jsqlparser:实现基于SQL语法分析的SQL注入攻击检查》介绍了利用 JSqlParser 防止 SQL 注入，写得很好，只不过有两个问题，代码比较复杂，我于是作了简化，只有两个类；其次检测比较严格，连子查询都禁止，我把它开放了。

最简单的 SQL 注入检测

其实，利用 JSqlParser 解析一个语句，是否成功，就能说明这个 SQL 语句有没有被注入了。

try {
    CCJSqlParserUtil.parse(sql).accept(injectionChecker);
    return true;
} catch (Exception e) {
    e.printStackTrace();
    return false;
}

如果有异常说明被注入了。这是测试例子。

SqlInjectionAnalyzer.check("SELECT * FROM mytable WHERE id = ;DROP TABLE mytable;");

高阶的 JSqlParser 检测

就是文章所介绍的方法，主要是判断表达是否为常量来分析是否注入。主要两个类ConstAnalyzer和SqlInjectionAnalyzer。

package com.ajaxjs.data.util;

import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.arithmetic.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.conditional.XorExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.AllColumns;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.SubSelect;

/**
 * 判断表达是否为常量的分析器
 *
 * @author guyadong
 */
public class ConstAnalyzer implements ExpressionVisitor, ItemsListVisitor {
    private static final ThreadLocal<Boolean> constFlag = new ThreadLocal<Boolean>() {
        @Override
        protected Boolean initialValue() {
            return true;
        }
    };

    @Override
    public void visit(NullValue value) {
    }

    @Override
    public void visit(Function function) {
        constFlag.set(false);
    }

    @Override
    public void visit(SignedExpression expr) {
        expr.getExpression().accept(this);
    }

    @Override
    public void visit(JdbcParameter parameter) {
        constFlag.set(false);
    }

    @Override
    public void visit(JdbcNamedParameter parameter) {
        constFlag.set(false);
    }

    @Override
    public void visit(DoubleValue value) {

    }

    @Override
    public void visit(LongValue value) {
    }

    @Override
    public void visit(DateValue value) {
    }

    @Override
    public void visit(TimeValue value) {
    }

    @Override
    public void visit(TimestampValue value) {
    }

    @Override
    public void visit(Parenthesis parenthesis) {
        parenthesis.getExpression().accept(this);
    }

    @Override
    public void visit(StringValue value) {
    }

    @Override
    public void visit(Addition expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Division expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(IntegerDivision expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Multiplication expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Subtraction expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(AndExpression expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(OrExpression expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(XorExpression expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Between expr) {
        expr.getLeftExpression().accept(this);
        expr.getBetweenExpressionStart().accept(this);
        expr.getBetweenExpressionEnd().accept(this);
    }

    @Override
    public void visit(EqualsTo expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(GreaterThan expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(GreaterThanEquals expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(InExpression expr) {
        if (expr.getLeftExpression() != null)
            expr.getLeftExpression().accept(this);
    }

    @Override
    public void visit(IsNullExpression expr) {
        expr.getLeftExpression().accept(this);
    }

    @Override
    public void visit(FullTextSearch expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(IsBooleanExpression expr) {
        expr.getLeftExpression().accept(this);
    }

    @Override
    public void visit(LikeExpression expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(MinorThan expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(MinorThanEquals expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(NotEqualsTo expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Column column) {
        if (!SqlInjectionAnalyzer.isBoolean(column))
            constFlag.set(false);
    }

    @Override
    public void visit(SubSelect subSelect) {
        constFlag.set(false);
    }

    @Override
    public void visit(CaseExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(WhenClause expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(ExistsExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(AnyComparisonExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(Concat expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(Matches expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(BitwiseAnd expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(BitwiseOr expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(BitwiseXor expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(CastExpression expr) {
        expr.getLeftExpression().accept(this);
    }

    @Override
    public void visit(TryCastExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(Modulo expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(AnalyticExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(ExtractExpression expr) {
        expr.getExpression().accept(this);
    }

    @Override
    public void visit(IntervalExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(OracleHierarchicalExpression expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(RegExpMatchOperator expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(ExpressionList expressionList) {
        for (Expression expr : expressionList.getExpressions())
            expr.accept(this);
    }

    @Override
    public void visit(NamedExpressionList namedExpressionList) {
        for (Expression expr : namedExpressionList.getExpressions())
            expr.accept(this);
    }

    @Override
    public void visit(MultiExpressionList multiExprList) {
        for (ExpressionList list : multiExprList.getExpressionLists())
            visit(list);
    }

    @Override
    public void visit(NotExpression notExpr) {
        notExpr.getExpression().accept(this);
    }

    @Override
    public void visit(BitwiseRightShift expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(BitwiseLeftShift expr) {
        visitBinaryExpression(expr);
    }

    protected void visitBinaryExpression(BinaryExpression expr) {
        expr.getLeftExpression().accept(this);
        expr.getRightExpression().accept(this);
    }

    @Override
    public void visit(JsonExpression jsonExpr) {
        jsonExpr.getExpression().accept(this);
    }

    @Override
    public void visit(JsonOperator expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(RegExpMySQLOperator expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(UserVariable var) {
        constFlag.set(false);
    }

    @Override
    public void visit(NumericBind bind) {
        constFlag.set(false);
    }

    @Override
    public void visit(KeepExpression expr) {
        for (OrderByElement element : expr.getOrderByElements())
            element.getExpression().accept(this);
    }

    @Override
    public void visit(MySQLGroupConcat groupConcat) {
        constFlag.set(false);
    }

    @Override
    public void visit(ValueListExpression valueListExpression) {
        for (Expression expr : valueListExpression.getExpressionList().getExpressions())
            expr.accept(this);
    }

    @Override
    public void visit(AllColumns allColumns) {

    }

    @Override
    public void visit(AllTableColumns allTableColumns) {

    }

    @Override
    public void visit(AllValue allValue) {

    }

    @Override
    public void visit(IsDistinctExpression isDistinctExpression) {
        visitBinaryExpression(isDistinctExpression);
    }

    @Override
    public void visit(RowGetExpression rowGetExpression) {
        rowGetExpression.getExpression().accept(this);
    }

    @Override
    public void visit(HexValue hexValue) {

    }

    @Override
    public void visit(OracleHint hint) {

    }

    @Override
    public void visit(TimeKeyExpression timeKeyExpression) {

    }

    @Override
    public void visit(DateTimeLiteralExpression literal) {
    }

    @Override
    public void visit(NextValExpression nextVal) {
        constFlag.set(false);
    }

    @Override
    public void visit(CollateExpression col) {
        constFlag.set(false);
    }

    @Override
    public void visit(SimilarToExpression expr) {
        visitBinaryExpression(expr);
    }

    @Override
    public void visit(ArrayExpression array) {
        array.getObjExpression().accept(this);
        if (array.getIndexExpression() != null)
            array.getIndexExpression().accept(this);

        if (array.getStartIndexExpression() != null)
            array.getStartIndexExpression().accept(this);

        if (array.getStopIndexExpression() != null)
            array.getStopIndexExpression().accept(this);
    }

    @Override
    public void visit(ArrayConstructor aThis) {
        for (Expression expression : aThis.getExpressions())
            expression.accept(this);
    }

    @Override
    public void visit(VariableAssignment var) {
        constFlag.set(false);
    }

    @Override
    public void visit(XMLSerializeExpr expr) {
        constFlag.set(false);
    }

    @Override
    public void visit(TimezoneExpression expr) {
        expr.getLeftExpression().accept(this);
    }

    @Override
    public void visit(JsonAggregateFunction expression) {
        Expression expr = expression.getExpression();

        if (expr != null)
            expr.accept(this);

        expr = expression.getFilterExpression();

        if (expr != null)
            expr.accept(this);
    }

    @Override
    public void visit(JsonFunction expression) {
        for (JsonFunctionExpression expr : expression.getExpressions())
            expr.getExpression().accept(this);
    }

    @Override
    public void visit(ConnectByRootOperator connectByRootOperator) {
        constFlag.set(false);
    }

    @Override
    public void visit(OracleNamedFunctionParameter oracleNamedFunctionParameter) {
        constFlag.set(false);
    }

    @Override
    public void visit(GeometryDistance geometryDistance) {
        visitBinaryExpression(geometryDistance);
    }

    @Override
    public void visit(RowConstructor rowConstructor) {
        constFlag.set(false);
    }

    public boolean isConstExpression(Expression expression) {
        if (null != expression) {
            constFlag.set(true);
            expression.accept(this);

            return constFlag.get();
        }

        return false;
    }
}

package com.ajaxjs.data.util;

import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.util.regex.Pattern;

/**
 * 基于 SQL 语法对象的 SQL 注入攻击分析实现
 *
 * @author guyadong
 */
public class SqlInjectionAnalyzer extends TablesNamesFinder {
    /**
     * 危险函数名
     */
    private static final String DANGEROUS_FUNCTIONS = "(sleep|benchmark|extractvalue|updatexml|ST_LatFromGeoHash|ST_LongFromGeoHash|GTID_SUBSET|GTID_SUBTRACT|floor|ST_Pointfromgeohash"
            + "|geometrycollection|multipoint|polygon|multipolygon|linestring|multilinestring)";

//    private static final ThreadLocal<Boolean> disableSubSelect = new ThreadLocal<Boolean>() {
//        @Override
//        protected Boolean initialValue() {
//            return true;
//        }
//    };

    private final ConstAnalyzer constAnalyzer = new ConstAnalyzer();

    public SqlInjectionAnalyzer() {
        super();
        init(true);
    }

    @Override
    public void visitBinaryExpression(BinaryExpression binaryExpression) {
        if (binaryExpression instanceof ComparisonOperator) {
            if (isConst(binaryExpression.getLeftExpression()) && isConst(binaryExpression.getRightExpression()))
                /* 禁用恒等式 */
                throw new SecurityException("DISABLE IDENTICAL EQUATION " + binaryExpression);
        }

        super.visitBinaryExpression(binaryExpression);
    }

    @Override
    public void visit(AndExpression andExpression) {
        super.visit(andExpression);
        checkConstExpress(andExpression.getLeftExpression());
        checkConstExpress(andExpression.getRightExpression());
    }

    @Override
    public void visit(OrExpression orExpression) {
        super.visit(orExpression);
        checkConstExpress(orExpression.getLeftExpression());
        checkConstExpress(orExpression.getRightExpression());
    }

    @Override
    public void visit(Function function) {
        if (function.getName().matches(DANGEROUS_FUNCTIONS))
            /* 禁用危险函数 */
            throw new SecurityException("DANGEROUS FUNCTION: " + function.getName());

        super.visit(function);
    }

    @Override
    public void visit(WithItem withItem) {
//        try {
//            /* 允许 WITH 语句中的子查询 */
//            disableSubSelect.set(false);
//            super.visit(withItem);
//        } finally {
//            disableSubSelect.set(true);
//        }
    }

    @Override
    public void visit(SubSelect subSelect) {
//        if (disableSubSelect.get()) // 禁用子查询
//            throw new SecurityException("DISABLE subSelect " + subSelect);
    }

    @Override
    public void visit(Column tableColumn) {
        if (isBoolean(tableColumn))
            throw new SecurityException("DISABLE CONST BOOL " + tableColumn);

        super.visit(tableColumn);
    }

    @Override
    public void visit(PlainSelect plainSelect) {
        if (plainSelect.getSelectItems() != null) {
            for (SelectItem item : plainSelect.getSelectItems())
                item.accept(this);
        }

        if (plainSelect.getFromItem() != null)
            plainSelect.getFromItem().accept(this);

        if (plainSelect.getJoins() != null) {
            for (Join join : plainSelect.getJoins()) {
                join.getRightItem().accept(this);

                for (Expression e : join.getOnExpressions())
                    e.accept(this);
            }
        }

        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(this);
            checkConstExpress(plainSelect.getWhere());
        }

        if (plainSelect.getHaving() != null)
            plainSelect.getHaving().accept(this);

        if (plainSelect.getOracleHierarchical() != null)
            plainSelect.getOracleHierarchical().accept(this);

        if (plainSelect.getOrderByElements() != null) {
            for (OrderByElement orderByElement : plainSelect.getOrderByElements())
                orderByElement.getExpression().accept(this);
        }

        if (plainSelect.getGroupBy() != null) {
            for (Expression expression : plainSelect.getGroupBy().getGroupByExpressionList().getExpressions())
                expression.accept(this);
        }
    }

    private boolean isConst(Expression expression) {
        return constAnalyzer.isConstExpression(expression);
    }

    private void checkConstExpress(Expression expression) {
        if (constAnalyzer.isConstExpression(expression))
            /* 禁用常量表达式 */
            throw new SecurityException("DISABLE CONST EXPRESSION " + expression);
    }

    private static final Pattern BOL = Pattern.compile("(true|false)", Pattern.CASE_INSENSITIVE);

    /**
     * 如果{@link Column}没有定义table,且字段名为true/false(不区分大小写)则视为布尔常量
     */
    public static boolean isBoolean(Column column) {
        return null != column && null == column.getTable() && BOL.matcher(column.getColumnName()).matches();
    }

    private static final SqlInjectionAnalyzer injectionChecker = new SqlInjectionAnalyzer();

    /**
     * SQL 注入攻击分析器
     * 对解析后的SQL对象执行注入攻击分析，有注入攻击的危险则抛出异常，
     * 并通过{@code visitor}参数提供基于AST(抽象语法树)的遍历所有节点的能力。
     *
     * @param sql SQL语句
     * @throws SecurityException 输入的SQL语句有语法错误
     */
    public static boolean check(String sql) {
        boolean allowComplexParsing = CCJSqlParserUtil.getNestingDepth(sql) <= CCJSqlParserUtil.ALLOWED_NESTING_DEPTH;

        try {
            CCJSqlParserUtil.newParser(sql).withAllowComplexParsing(allowComplexParsing).Statement().accept(injectionChecker);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}

测试：

package com.ajaxjs.data;

import com.ajaxjs.data.util.SqlInjectionAnalyzer;
import org.junit.Test;

import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

public class TestSqlInject {
    @Test
    public void test() {
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where id in (select id from other)"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 2=2.0 or 2 != 4"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 1!=2.0"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where id=floor(2.0)"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where not true"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 1 or id > 0"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 'tom' or id > 0"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where '-2.3' "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 2 "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where (3+2) "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where  -1 IS TRUE"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where 'hello' is null "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where '2022-10-31' and id > 0"));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where id > 0 or 1!=2.0 "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device where id > 0 or 1 in (1,3,4) "));
        assertFalse(SqlInjectionAnalyzer.check("select * from dc_device UNION select name from other"));
        assertTrue(SqlInjectionAnalyzer.check("WITH SUB1 AS (SELECT user FROM t1) SELECT * FROM T2 WHERE id > 123 "));

        boolean check = SqlInjectionAnalyzer.check("SELECT * FROM mytable WHERE id = ;DROP TABLE mytable;");
        System.out.println(check);
    }

}