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方法实现代码示例：

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


public class CheckWhere {
	// WHERe 安全检查标志定义,每一位对应一个检查类型, /
	
	public static final int CWF_DISABLE_SQLKEY = 0x01;
	
	public static final int CWF_DISABLE_CONST_EXP = 0x02;
	
	public static final int CWF_DISABLE_EQUATION_EXP = 0x04;
	
	public static final int CWF_DISABLE_IN_EXP = 0x08;
	
	private static int whereCheckFlag = CWF_DISABLE_SQLKEY;
	private static Matcher regexMatcher(String regex,int flags,String input){
		return Pattern.compile( regex,flags).matcher(input);
	}
	
	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;
	}
	
	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)");
			}
			
			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)){    			
				
				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()));
					}
				}
			}
			
			checkMatchFind(CWF_DISABLE_IN_EXP,"((('|")[^']*\3\s*)|[\d\.+-]+\s*)\s+IN\s+\(.*\)",  Pattern.CASE_INSENSITIVE,  where, "INVALID IN expression");
			
			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;
	}
	
	public static void setWhereCheckFlag(int whereCheckFlag) {
		CheckWhere.whereCheckFlag = whereCheckFlag;
	}

	
	private static void testCheckWhere(String input,boolean assertLegal){
		try {			
			checkWhere(input);
			if(!assertLegal){
				throw new AssertionError();
			}
		} catch (Exception e) {
			System.out.printf("%sn", e.getMessage());
			if(assertLegal){
				throw new AssertionError();
			}
		}
	}
	
	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);
	}
	
}

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

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'

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