${param}传递的参数会被当成sql语句中的一部分,比如传递表名,字段名
例子:(传入值为id)
order by ${param}
则解析成的sql为:
order by id
#{parm}传入的数据都当成一个字符串,会对自动传入的数据加一个双引号
例子:(传入值为id)
select * from table where name = #{param}
则解析成的sql为:
select * from table where name = "id"
为了安全,能用#的地方就用#方式传参,这样可以有效的防止sql注入攻击
sql注入简介
直接上了百度的例子,感觉一看就清晰明了
strSQL =
"SELECT * FROM users WHERE (name = '"
+ userName +
"') and (pw = '"
+
passWord
+
"');"
恶意填入
userName =
"1' OR '1'='1"
;
与passWord
=
"1' OR '1'='1"
;
时,将导致原本的SQL字符串被填为strSQL =
"SELECT * FROM users WHERE (name = '1' OR '1'='1') and (pw = '1' OR '1'='1');"
也就是实际上运行的SQL命令会变成下面这样的strSQL =
"SELECT * FROM users;"
这样在后台帐号验证的时候巧妙地绕过了检验,达到无账号密码,亦可登录网站。所以SQL注入攻击被俗称为黑客的填空游戏。
通用拦截器:
package com.lvtu.service.api.rop.intercept;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts2.ServletActionContext;
import com.lambdaworks.redis.cluster.RedisAdvancedClusterConnection;
import com.lvtu.utils.LvtuClientMemCacheConstants;
import com.opensymphony.xwork2.ActionInvocation;
import com.opensymphony.xwork2.interceptor.AbstractInterceptor;
import net.sf.json.JSONObject;
/**
* 类描述:防止sql注入
* 类名称:PreventSQLInjectInterceptor
* 修改备注:
* @version
*
*/
public class PreventSQLInjectInterceptor extends AbstractInterceptor {
private static final long serialVersionUID = -4421675513682811750L;
private static final Log logger = LogFactory.getLog(PreventSQLInjectInterceptor.class);
@Resource(name = "redisAdvancedClusterConnection")
private RedisAdvancedClusterConnection<String, Object> redisAdvancedClusterConnection;
private static final String PATTERN_REGEX = "\\'|exec|execute|insert|select|delete|update|count|drop|\\*|\\%|chr|mid|master|truncate|" +
"char|declare|sitename|net user|xp_cmdshell|;|or|-|\\+|like\\'|exec|execute|insert|create|drop|" +
"table|from|grant|use|group_concat|column_name|" +
"information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|\\*|" +
"chr|mid|master|truncate|char|declare|or|;|-|--|\\+|like|\\//|\\/|\\%|\\#|\\$";//过滤掉的sql关键字,可以手动添加
/**
* 是否需要防sql注入校验默认不需要
*/
private static boolean checkFlag=false;
@Override
public String intercept(ActionInvocation invocation) throws Exception {
//获取md5的counter
long switchStatus = -1l;
try {
Object obj = redisAdvancedClusterConnection.get(LvtuClientMemCacheConstants.CUSTOMIZE_COUNTER_CONSTANTS.PREVENT_SQLINJECT_INTERCEPTOR_COUNTER.getCacheKey());
switchStatus = (obj == null ? -1L : (Long) obj);
//membercache 配置小于 -1 的时候走 检验
if(switchStatus < -1l){
checkFlag=true;
}
} catch (Exception e) {
logger.info("memcached访问出错");
}
if(checkFlag){
try {
String uri = this.getRequest().getRequestURI();
if(StringUtils.isNotBlank(uri) && uri.contains("router")){
@SuppressWarnings("unchecked")
Map<String, Object> paramMap = this.getRequest().getParameterMap();
if(null == paramMap || paramMap.isEmpty()){
return invocation.invoke();
}
for(String key : paramMap.keySet()){
if(StringUtils.isNotBlank(key) && "keyword".equals(key.toLowerCase())){
String str = ((String[])paramMap.get(key))[0];
if(this.sqlValidate(str)){
logger.error("sql注入攻击 param = " + str + ".........................");
Map<String,Object> resultMap = resultMapCreator();
resultMap.put("errorMessage", "关键字中包含敏感信息");
resultMap.put("message", "抱歉,您输入的信息有敏感信息,请您修改后重新输入!");
this.sendAjaxResultByJson(resultMap);
return null;
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return invocation.invoke();
}
/**
* 构造返回数据(返回-1)
* @return
*/
public Map<String,Object> resultMapCreator(){
Map<String,Object> map = new HashMap<String,Object>();
map.put("code","-1");
return map;
}
/**
* 发送Ajax请求结果json
*
* @throws ServletException
* @throws IOException
*/
public void sendAjaxResultByJson(Map<String,Object> resultMap) {
this.getResponse().setContentType("application/json;charset=UTF-8");
this.getResponse().setCharacterEncoding("UTF-8");
try {
PrintWriter out = this.getResponse().getWriter();
JSONObject jsonObj = JSONObject.fromObject(resultMap);
out.write(jsonObj.toString());
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 是否输入sql注入验证
* @param name
* @param @return 设定文件
* @return String DOM对象
* @Exception 异常对象
* @since CodingExample Ver(编码范例查看) 1.1
*/
private boolean sqlValidate(String str){
str = str.replaceAll(" ", "").toLowerCase();//统一转为小写
Pattern p = Pattern.compile(PATTERN_REGEX);
Matcher m = p.matcher(str); // 获取 matcher 对象
return m.find();
}
/**
* 获取HttpRequest
*
* @return
*/
private HttpServletRequest getRequest() {
return ServletActionContext.getRequest();
}
private HttpServletResponse getResponse() {
return ServletActionContext.getResponse();
}
/**
* @param args
*/
public static void main(String[] args) {
String regex = "\\'|exec|execute|insert|select|delete|update|count|drop|\\*|\\%|chr|mid|master|truncate|" +
"char|declare|sitename|net user|xp_cmdshell|;|or|-|\\+|like\\'|exec|execute|insert|create|drop|" +
"table|from|grant|use|group_concat|column_name|" +
"information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|\\*|" +
"chr|mid|master|truncate|char|declare|or|;|-|--|\\+|like|\\//|\\/|\\%|\\#|\\$";//过滤掉的sql关键字,可以手动添加
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher("where1=1"); // 获取 matcher 对象
System.out.println(m.find());
}
}