java:正则表达式检查SQL WHERE条件语句防止注入攻击和常量表达式_java 如何判断sql中where条件结束

防止外部输入的SQL语句包含注入式攻击代码，主要作法就是对字符串进行关键字检查，禁止不应该出现在SQL语句中的关键字如 union delete等等,同时还要允许这些字符串作为常量字符串中的内容出现在SQL 语句中。

对于 where 1=1或where 'hello'="hello"这种用法，虽然不能算是注入攻击，但在有的情况下属于危险用法 比如在DELETE语句中 delete * from table where 1=1会删除全表数据。也应该被警告或禁止。

针对这些情况可以通过正则表达式实现对SQL语句的安全检查，

在我的项目的中每次只允许执行一条SQL语句,用PreparedStatement编译SQL，所以SQL的安全检查只检查WHERE条件语句的安全性，
通过几个正则表达式就可以实现上面的判断。以下是checkWhere方法实现代码示例：
CheckWhere.java

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL WHERE 语句安全检查(防止注入攻击)实现
 * @author guyadong
 *
 */
public class CheckWhere {
	// WHERE 安全检查标志定义,每一位对应一个检查类型, /
	/** 禁用危险关键字(disable SQL dangerous key) */
	public static final int CWF_DISABLE_SQLKEY = 0x01;
	/** 禁用常量表达式(disable constant expression) */
	public static final int CWF_DISABLE_CONST_EXP = 0x02;
	/** 禁用常量等价表达式(disable constant equation expression) */
	public static final int CWF_DISABLE_EQUATION_EXP = 0x04;
	/** 禁用常量IN表达式(disable constant IN expression) */
	public static final int CWF_DISABLE_IN_EXP = 0x08;
	/** 安全检查标志, {@link #checkWhere(String)} 会根据此标志确定是否执行指定的检查 */
	private static int whereCheckFlag = CWF_DISABLE_SQLKEY;
	private static Matcher regexMatcher(String regex,int flags,String input){
		return Pattern.compile( regex,flags).matcher(input);
	}
	/**
	 * 检查输入的字符串是否指定指定的正则表达,如果找到匹配则抛出{@link IllegalArgumentException}异常
	 * @param checkFlags 是否执行正则表达匹配的检查标志,参见 CWF_DISABLE_xxx 系列定义
	 * @param regex 正则表达式
	 * @param flags 正则表达式匹配标志参见 {@link Pattern#compile(String, int)}
	 * @param input SQL 字符串
	 * @param errmsg 抛出异常时输出的字符串
	 */
	private static void checkMatchFind(int checkFlags,String regex,int flags,String input,String errmsg){
		if(isEnable(checkFlags)){
			Matcher matcher = regexMatcher(regex, flags, input);
			if(matcher.find()){
				throw new IllegalArgumentException(String.format(errmsg + " '%s'", matcher.group()));
			}
		}
	}
	private static boolean isEnable(int checkflag){
		return (whereCheckFlag & checkflag) == checkflag;
	}
	/**
	 * 对 where SQL 语句安全性(防注入攻击)检查
	 * @param where
	 * @return always where
	 * @throws IllegalArgumentException where 语句有安全问题
	 */
	static String checkWhere(String where){
		where = null == where ? "" : where.trim();
		if(!where.isEmpty()){    		
			if(!where.toUpperCase().startsWith("WHERE")){
				throw new IllegalArgumentException("WHERE expression must start with 'WHERE'(case insensitive)");
			}
			/** 禁止字符串常量比较 
			 *  如 'owner_id'='owner' "_id"
			 *  禁止左右完全相等的比较
			 *  如 hello=hello
			 *  禁止数字常量比较
			 *  如 7.0=7
			 *  如  .12='12'
			 *  如 ".1"=.1
			 * */
			checkMatchFind(CWF_DISABLE_EQUATION_EXP,"((\'[^\']*\'\\s*|\"[^\"]*\\\"\\s*)+\\s*=\\s*(\'[^\']*\'\\s*|\"[^\"]*\"\\s*)+|([+-]?(?:\\d*\\.)?\\d+)\\s*=\\s*[+-]?(?:\\d*\\.)?\\d+|([^\'\"\\s]+)\\s*=\\s*\\5\\b|([+-]?(?:\\d*\\.)?\\d+)\\s*=\\s*(\'|\")[+-]?(?:\\d*\\.)?\\d+\\s*\\7|(\'|\")([+-]?(?:\\d*\\.)?\\d+)\\s*\\8\\s*=\\s*[+-]?(?:\\d*\\.)?\\d+)",  0,  where, "INVALID WHERE equation  expression");
			if(isEnable(CWF_DISABLE_CONST_EXP)){    			
				/**
				 * 禁止恒为true的判断条件
				 * -- 禁止 非0数字常量为判断条件
				 * -- 禁止 not false,not true 
				 * 如: where "-055.55asdfsdfds0"  or  true  or not false
				 */
				Matcher m1 = regexMatcher("((?:where|or)\\s+)(not\\s+)?(false|true|(\'|\")([+-]?\\d+(\\.\\d+)?).*\\4)",  Pattern.CASE_INSENSITIVE,  where);
				while(m1.find()){
					boolean not = null != m1.group(2);
					String g3 = m1.group(3);
					Boolean isTrue;
					if(g3.equalsIgnoreCase("true")){
						isTrue = true;
					}else if(g3.equalsIgnoreCase("false")){
						isTrue = false;
					}else{    				
						String g5 = m1.group(5);
						isTrue = 0 != Double.valueOf(g5);
					}
					if(not){
						isTrue = ! isTrue;
					}
					if(isTrue){
						throw new IllegalArgumentException(String.format("INVALID WHERE const true  expression '%s'",m1.group()));
					}
				}
			}
			/**
			 * 禁止字符串常量或数字常量开头的 IN语句
			 * 如 7.0 IN ( 15, 7)
			 * 如 'hello' IN ( 'hello', 'world')
			 * */
			checkMatchFind(CWF_DISABLE_IN_EXP,"(((\'|\")[^\']*\\3\\s*)|[\\d\\.+-]+\\s*)\\s+IN\\s+\\(.*\\)",  Pattern.CASE_INSENSITIVE,  where, "INVALID IN expression");
			/** 删除where中所有字符串常量,再进行关键字检查,避免字符串中的包含的关键引起误判 */
			String nonestr=where.replaceAll("(\'[^\']*\'|\"[^\"]*\")", "");
			checkMatchFind(CWF_DISABLE_SQLKEY,"\\b(exec|insert|delete|update|join|union|master|truncate)\\b", Pattern.CASE_INSENSITIVE, nonestr,"ILLEGAL SQL key");
		}
		return where;
	}
	/**
	 * 设置安全检查标志，默认{@value #CWF_DISABLE_SQLKEY}
	 * @param whereCheckFlag
	 */
	public static void setWhereCheckFlag(int whereCheckFlag) {
		CheckWhere.whereCheckFlag = whereCheckFlag;
	}

	/**
	 * 调用 {@link #checkWhere(String)}检查输入的SQL字符串是否合法,
	 * @param input WHERE SQL 字符串
	 * @param assertLegal 字符串合法性预定义值
	 * @throws IllegalArgumentException SQL字符串不合法
	 * @throws AssertionError 如果{@code assertLegal}为true,checkWhere 没有检查出异常则抛出此异常
	 *                                       如果{@code assertLegal}为false,checkWhere 检查出异常则抛出此异常
	 */
	private static void testCheckWhere(String input,boolean assertLegal){
		try {			
			checkWhere(input);
			if(!assertLegal){
				throw new AssertionError();
			}
		} catch (Exception e) {
			System.out.printf("%s\n", e.getMessage());
			if(assertLegal){
				throw new AssertionError();
			}
		}
	}
	/**
	 * WHERE语句全要素检测测试
	 */
	public static void main(String[] args) {
		setWhereCheckFlag(0xffffffff);
		testCheckWhere("WHERE ",true);
		testCheckWhere("WHERE name='1342342' or age=15",true);
		testCheckWhere("WHERE name like '1342342%' and age>15 and birthdate='1990-01-01'",true);
		testCheckWhere("WHERE name='1342342%' and age>15 and birthdate='1990-01-01'",true);
		testCheckWhere("WHERE 1=1",false);
		testCheckWhere("WHERE 1=1.0",false);
		testCheckWhere("WHERE 1=1.0",false);
		testCheckWhere("WHERE  .12=\'12\' or  \".1\"=.10 1=1 \"hello\"=\'world\'   hello=hello",false);
		testCheckWhere("WHERE true",false);
		testCheckWhere("WHERE false",true);
		testCheckWhere("WHERE not false",false);
		testCheckWhere("WHERE '12345'='1342342' or age=15",false);
		testCheckWhere("WHERE age=15 or 1=2",false);
		testCheckWhere("WHERE age in ()",true);
		testCheckWhere("WHERE age in (1,2,3,45)",true);
		testCheckWhere("WHERE 1 in ()",false);
		testCheckWhere("WHERE 1 in (1,2,3,45)",false);
		testCheckWhere("WHERE 'hello' in ('hello')",false);
		testCheckWhere("WHERE 'hello' in ('hello')",false);
		testCheckWhere("WHERE a=1 union select * from systemtable",false);
		testCheckWhere("WHERE a in ( select a from systemtable)",true);
		/** 允许字符串中有危险关键字 */
		testCheckWhere("WHERE name='union' or age=15",true);
	}
	
}

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

上面的代码是完整的可运行代码，调用示例运行输出

INVALID WHERE equation  expression '1=1'
INVALID WHERE equation  expression '1=1.0'
INVALID WHERE equation  expression '1=1.0'
INVALID WHERE equation  expression '.12='12''
INVALID WHERE const true  expression 'WHERE true'
INVALID WHERE const true  expression 'WHERE not false'
INVALID WHERE equation  expression ''12345'='1342342' '
INVALID WHERE equation  expression '1=2'
INVALID IN expression '1 in ()'
INVALID IN expression '1 in (1,2,3,45)'
INVALID IN expression ''hello' in ('hello')'
INVALID IN expression ''hello' in ('hello')'
ILLEGAL SQL key 'union'
1
2
3
4
5
6
7
8
9
10
11
12
13

该方法的实际项目应用参见 gu.sql2java.BaseTableManager