jsqlparser:实现基于SQL语法分析的SQL注入攻击检查

之前写过一篇博客：《java:正则表达式检查SQL WHERE条件语句防止注入攻击和常量表达式》,当前时通过正则表达式来检查SQL语句中是否有危险关键字和常量表达式实现SQL语句的注入攻击检查。坦率的说，这个办法是有漏洞的，误判，漏判的概率很大，基于当前我的知识能力，也只能做到这样。
最近学习了jsqlparser,我知道我找到了更好的办法来解决SQL注入攻击检查问题。
jsqlparser是一个java的SQL语句解析器，在上一篇博客：《jsqlparser:基于抽象语法树(AST)遍历SQL语句的语法元素》介绍了如何通过jsqlparser来遍历SQL语句中所有的字段和表名引用。
其实它可以用来进行更复杂的工作，jsqlparser会将一条SQL语句的各种语法元素以抽象语法树(AST，abstract syntax tree)形式解析为很多不同类型对象，通过对AST的遍历就可以对SQL语句进行分析。采用这种方式做SQL注入攻击检查不会有误判，漏判的问题。

SqlInjectionAnalyzer

SqlInjectionAnalyzer SQL注入攻击分析器
jsqlparse提供了两种式遍历SQL语句解析对象，一种是基于TablesNamesFinder，TablesNamesFinder其实是实现jsqparser很多对象访问接口的一个基类
一种是基于CCJSqlParserDefaultVisitor接口。

/**
 * SQL注入攻击分析器
 * @author guyadong
 *
 */
public class SqlInjectionAnalyzer {
    private boolean injectCheckEnable = true;
    private final InjectionSyntaxObjectAnalyzer injectionChecker;
    private final InjectionAstNodeVisitor injectionVisitor;
    public SqlInjectionAnalyzer() {
        this.injectionChecker = new InjectionSyntaxObjectAnalyzer(); 
        this.injectionVisitor = new InjectionAstNodeVisitor();
    }
    /**
     * 启用/关闭注入攻击检查,默认启动
     * @param enable
     * @return
     */
    public SqlInjectionAnalyzer injectCheckEnable(boolean enable){
        injectCheckEnable = enable;
        return this;
    }
    /**
     * 对解析后的SQL对象执行注入攻击分析，有注入攻击的危险则抛出异常{@link InjectionAttackException}
     * @param sqlParserInfo
     * @throws InjectionAttackException 
     */
    public SqlParserInfo injectAnalyse(SqlParserInfo sqlParserInfo) throws InjectionAttackException{

        if(null != sqlParserInfo && injectCheckEnable){
            /** SQL注入攻击检查 */
            sqlParserInfo.statement.accept(injectionChecker);
            sqlParserInfo.simpleNode.jjtAccept(injectionVisitor, null);             
        }
        return sqlParserInfo;
    }
}

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
26
27
28
29
30
31
32
33
34
35
36
37
38

InjectionSyntaxObjectAnalyzer

InjectionSyntaxObjectAnalyzer 为基于SQL语法对象的SQL注入攻击分析实现
TablesNamesFinder是jsqlparser提供的一个语法元素遍历对象，继承这个对象可以实现对需要的语法元素的访问，当遇到有注入攻击危险的表达式，语句时抛出InjectionAttackException异常，就是这个类做的工作

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.schema.Column;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.util.TablesNamesFinder;

/**
 * 基于SQL语法对象的SQL注入攻击分析实现
 * @author guyadong
 *
 */
public class InjectionSyntaxObjectAnalyzer  extends TablesNamesFinder{
    /** 危险函数名 */
	private static final String DANGROUS_FUNCTIONS = "(sleep|benchmark|extractvalue|updatexml|ST_LatFromGeoHash|ST_LongFromGeoHash|GTID_SUBSET|GTID_SUBTRACT|floor|ST_Pointfromgeohash"
			+ "|geometrycollection|multipoint|polygon|multipolygon|linestring|multilinestring)";
	
	private static ThreadLocal<Boolean> disableSubselect = new ThreadLocal<Boolean>(){
        @Override
        protected Boolean initialValue() {
            return true;
        }};
	private ConstAnalyzer constAnalyzer = new ConstAnalyzer();
    public InjectionSyntaxObjectAnalyzer() {
		super();
		init(true);
		
	}
	@Override
    public void visitBinaryExpression(BinaryExpression binaryExpression) {
	    if(binaryExpression instanceof ComparisonOperator){
	        if(isConst(binaryExpression.getLeftExpression()) && isConst(binaryExpression.getRightExpression())){
	            /** 禁用恒等式 */
	            throw new InjectionAttackException("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(DANGROUS_FUNCTIONS)){
    		/** 禁用危险函数 */
    		throw new InjectionAttackException("DANGROUS 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 InjectionAttackException("DISABLE subselect " + subSelect);
        }
    }
    @Override
    public void visit(Column tableColumn) {
        if(ParserSupport.isBoolean(tableColumn)){
            throw new InjectionAttackException("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 InjectionAttackException("DISABLE CONST EXPRESSION " + expression);
        }
    }
}

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

InjectionAstNodeVisitor

InjectionAstNodeVisitor 为基于抽象语法树(AST)的注入攻击分析实现
这部分代码很少，实现禁用用UNION语句

import net.sf.jsqlparser.parser.CCJSqlParserDefaultVisitor;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.statement.select.UnionOp;

/**
 * 基于抽象语法树(AST)的注入攻击分析实现
 * @author guyadong
 *
 */
public class InjectionAstNodeVisitor extends CCJSqlParserDefaultVisitor{
    public InjectionAstNodeVisitor() {
    }
    @Override
    public Object visit(SimpleNode node, Object data) {
        Object value = node.jjtGetValue();
        if(value instanceof UnionOp){
            throw new InjectionAttackException("DISABLE UNION");
        }
        return super.visit(node, data);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

单元测试

InjectAttackCheckerTest SQL注入攻击检查测试

import static org.junit.Assert.*;

import org.junit.BeforeClass;
import org.junit.Test;

import gu.sql2java.parser.InjectionAttackException;
import gu.sql2java.parser.ParserSupport;
import gu.sql2java.parser.SqlInjectionAnalyzer;
import gu.sql2java.parser.ParserSupport.SqlParserInfo;
import net.sf.jsqlparser.JSQLParserException;

import static gu.sql2java.SimpleLog.log;

/**
 * SQL注入攻击检查测试
 * @author guyadong
 *
 */
public class InjectAttackCheckerTest {

    private static SqlInjectionAnalyzer analyser;
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        analyser= new SqlInjectionAnalyzer();
    }
    private boolean injectAnalyse(String sql) throws JSQLParserException{
        SqlParserInfo sqlParserInfo = ParserSupport.parse0(sql, null);
        try {
            analyser.injectAnalyse(sqlParserInfo);
            return true;
        } catch (InjectionAttackException e) {
            log("{}",e.getMessage());
            //log(e);
            return false;
        }
    }
    @Test
    public void test() throws JSQLParserException {
        assertFalse(injectAnalyse("select * from dc_device where id in (select id from other)"));
        assertFalse(injectAnalyse("select * from dc_device where 2=2.0 or 2 != 4"));
        assertFalse(injectAnalyse("select * from dc_device where 1!=2.0"));
        assertFalse(injectAnalyse("select * from dc_device where id=floor(2.0)"));
        assertFalse(injectAnalyse("select * from dc_device where not true"));
        assertFalse(injectAnalyse("select * from dc_device where 1 or id > 0"));
        assertFalse(injectAnalyse("select * from dc_device where 'tom' or id > 0"));
        assertFalse(injectAnalyse("select * from dc_device where '-2.3' "));
        assertFalse(injectAnalyse("select * from dc_device where 2 "));
        assertFalse(injectAnalyse("select * from dc_device where (3+2) "));
        assertFalse(injectAnalyse("select * from dc_device where  -1 IS TRUE"));
        assertFalse(injectAnalyse("select * from dc_device where 'hello' is null "));
        assertFalse(injectAnalyse("select * from dc_device where '2022-10-31' and id > 0"));
        assertFalse(injectAnalyse("select * from dc_device where id > 0 or 1!=2.0 "));
        assertFalse(injectAnalyse("select * from dc_device where id > 0 or 1 in (1,3,4) "));
        assertFalse(injectAnalyse("select * from dc_device  UNION select name from other"));
        assertTrue(injectAnalyse("WITH SUB1 AS (SELECT user FROM t1) SELECT * FROM T2 WHERE id > 123 "));
    }

}

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

完整代码参见码云仓库：
https://gitee.com/l0km/sql2java/tree/dev/sql2java-manager/src/main/java/gu/sql2java/parser

jsqlparser系列文章

《jsqlparser(一):基于抽象语法树(AST)遍历SQL语句的语法元素》
《jsqlparser(二):实现基于SQL语法分析的SQL注入攻击检查》
《jsqlparser(三):基于语法分析实现SQL中的CAST函数替换》
《jsqlparser(四):实现MySQL 函数DATE_FORMAT到Phoenix函数TO_CHAR的替换》
《jsqlparser(五):修改语法定义(JSqlParserCC.jjt)实现UPSERT支持Phoenix语法ON DUPLICATE KEY IGNORE》