网站设计技术方案,河南省建设厅建筑信息网官网,如何使用域名访问网站,福建省建设执业注册资格管理中心网站高手文章《jsqlparser:实现基于SQL语法分析的SQL注入攻击检查》介绍了利用 JSqlParser 防止 SQL 注入#xff0c;写得很好#xff0c;只不过有两个问题#xff0c;代码比较复杂#xff0c;我于是作了简化#xff0c;只有两个类#xff1b;其次检测比较严格#xff0c;连…高手文章《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.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 ThreadLocalBoolean disableSubSelect new ThreadLocalBoolean() {
// Override
// protected Boolean initialValue() {
// return true;
// }
// };private final ConstAnalyzer constAnalyzer new ConstAnalyzer();public SqlInjectionAnalyzer() {super();init(true);}Overridepublic 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);}Overridepublic void visit(AndExpression andExpression) {super.visit(andExpression);checkConstExpress(andExpression.getLeftExpression());checkConstExpress(andExpression.getRightExpression());}Overridepublic void visit(OrExpression orExpression) {super.visit(orExpression);checkConstExpress(orExpression.getLeftExpression());checkConstExpress(orExpression.getRightExpression());}Overridepublic void visit(Function function) {if (function.getName().matches(DANGEROUS_FUNCTIONS))/* 禁用危险函数 */throw new SecurityException(DANGEROUS FUNCTION: function.getName());super.visit(function);}Overridepublic void visit(WithItem withItem) {
// try {
// /* 允许 WITH 语句中的子查询 */
// disableSubSelect.set(false);
// super.visit(withItem);
// } finally {
// disableSubSelect.set(true);
// }}Overridepublic void visit(SubSelect subSelect) {
// if (disableSubSelect.get()) // 禁用子查询
// throw new SecurityException(DISABLE subSelect subSelect);}Overridepublic void visit(Column tableColumn) {if (isBoolean(tableColumn))throw new SecurityException(DISABLE CONST BOOL tableColumn);super.visit(tableColumn);}Overridepublic 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.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 ThreadLocalBoolean disableSubSelect new ThreadLocalBoolean() {
// Override
// protected Boolean initialValue() {
// return true;
// }
// };private final ConstAnalyzer constAnalyzer new ConstAnalyzer();public SqlInjectionAnalyzer() {super();init(true);}Overridepublic 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);}Overridepublic void visit(AndExpression andExpression) {super.visit(andExpression);checkConstExpress(andExpression.getLeftExpression());checkConstExpress(andExpression.getRightExpression());}Overridepublic void visit(OrExpression orExpression) {super.visit(orExpression);checkConstExpress(orExpression.getLeftExpression());checkConstExpress(orExpression.getRightExpression());}Overridepublic void visit(Function function) {if (function.getName().matches(DANGEROUS_FUNCTIONS))/* 禁用危险函数 */throw new SecurityException(DANGEROUS FUNCTION: function.getName());super.visit(function);}Overridepublic void visit(WithItem withItem) {
// try {
// /* 允许 WITH 语句中的子查询 */
// disableSubSelect.set(false);
// super.visit(withItem);
// } finally {
// disableSubSelect.set(true);
// }}Overridepublic void visit(SubSelect subSelect) {
// if (disableSubSelect.get()) // 禁用子查询
// throw new SecurityException(DISABLE subSelect subSelect);}Overridepublic void visit(Column tableColumn) {if (isBoolean(tableColumn))throw new SecurityException(DISABLE CONST BOOL tableColumn);super.visit(tableColumn);}Overridepublic 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 {Testpublic void test() {assertFalse(SqlInjectionAnalyzer.check(select * from dc_device where id in (select id from other)));assertFalse(SqlInjectionAnalyzer.check(select * from dc_device where 22.0 or 2 ! 4));assertFalse(SqlInjectionAnalyzer.check(select * from dc_device where 1!2.0));assertFalse(SqlInjectionAnalyzer.check(select * from dc_device where idfloor(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 (32) ));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);}}
