问题
SELECT
*
FROM a
LEFT JOIN ( SELECT * FROM b WHERE #{condition} ) b
在自定义分页SQL中进行letf join语句查询报错,假如有3个#{}参数,一个在left join中,最终会报java.sql.SQLException: Parameter index out of range 实际参数有3个,在SQL中只找到2个#{}
解决方式
防注入
/**
* SQL注入过滤
*
* @param str 待验证的字符串
* @return 处理过的字符串
*/
public static String sqlInject(String str) throws Exception {
if (StringUtils.isBlank(str)) {
return null;
}
//去掉'|"|;|\字符
str = StringUtils.replace(str, "'", "");
str = StringUtils.replace(str, "\"", "");
str = StringUtils.replace(str, ";", "");
str = StringUtils.replace(str, "\\", "");
//转换成小写
str = str.toLowerCase();
//非法字符
String[] keywords = {"master", "truncate", "insert", "select", "delete", "update", "declare", "alter", "drop"};
//判断是否包含非法字符
for (String keyword : keywords) {
if (str.indexOf(keyword) != -1) {
throw new Exception("包含非法字符");
}
}
return str;
}