利用 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;
}
1
2
3
4
5
6
7

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

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

高阶的 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;
    }
}
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503

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 disableSubSelect = new ThreadLocal() {
//        @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;
        }
    }
}
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

测试：

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);
    }

}
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