为了防止SQL注入,同时避免用户输入特殊字符时查询结果不准确的问题(特别是 % _ ' 这三个字符)
public static String escapeSQL(String str) {
if (str == null || str.length() == 0) {
return str;
}
String[] chars = new String[4], escape = new String[4];
chars[0] = "\\";
escape[0] = "\\\\\\\\";
chars[1] = "\n";
escape[1] = "\\\\n";
chars[2] = "'";
escape[2] = "''";
chars[3] = "\r";
escape[3] = "\\\\r";
for (int i = 0; i < chars.length; ++i) {
str = str.replace(chars[i], escape[i]);
}
str = str.replace("%", "\\%").replace("_", "\\_");
return str.trim();
}
if (str == null || str.length() == 0) {
return str;
}
String[] chars = new String[4], escape = new String[4];
chars[0] = "\\";
escape[0] = "\\\\\\\\";
chars[1] = "\n";
escape[1] = "\\\\n";
chars[2] = "'";
escape[2] = "''";
chars[3] = "\r";
escape[3] = "\\\\r";
for (int i = 0; i < chars.length; ++i) {
str = str.replace(chars[i], escape[i]);
}
str = str.replace("%", "\\%").replace("_", "\\_");
return str.trim();
}
这样处理的弊端:当用户输入带%或_的查询条件时, 会查不到数据。
解决办法 :动态加上ESCAPE '\'语句
sql语句:
<select id="SELECT.A_USR_S02.GET_LIST_USER"
parameterClass="my.com.honda.servicebooking.a_usr.dto.A_USR_S02_Input"
resultClass="my.com.honda.servicebooking.a_usr.dto.A_USR_S02_Output">
<include refid="sql_head" />
<dynamic prepend="and">
<isNotEmpty property="userType" prepend="">
U.USER_TYPE=#userType#
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="userStatus" prepend="">
U.USER_STATUS=#userStatus#
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="userName" prepend="">
<isNotEmpty property="userNameSingleQuotes" prepend="">
UPPER(U.USER_NAME) LIKE UPPER(' %$userName$% ') //此处检测当输入的查询条件中含有单引号',就不用 '%'||#userName#||'%',
parameterClass="my.com.honda.servicebooking.a_usr.dto.A_USR_S02_Input"
resultClass="my.com.honda.servicebooking.a_usr.dto.A_USR_S02_Output">
<include refid="sql_head" />
<dynamic prepend="and">
<isNotEmpty property="userType" prepend="">
U.USER_TYPE=#userType#
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="userStatus" prepend="">
U.USER_STATUS=#userStatus#
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="userName" prepend="">
<isNotEmpty property="userNameSingleQuotes" prepend="">
UPPER(U.USER_NAME) LIKE UPPER(' %$userName$% ') //此处检测当输入的查询条件中含有单引号',就不用 '%'||#userName#||'%',
</isNotEmpty> 而用%$userName$%
UPPER(U.USER_NAME) LIKE UPPER( '%'||#userName#||'%' )
</isEmpty>
<isNotEmpty property="userNamePercentOrUnderline" prepend="">
ESCAPE '\' //此处检测当输入的查询条件含有%或者_时,就加上 ESCAPE '\'
</isNotEmpty>
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="idUser" prepend="">
<isNotEmpty property="idUserSingleQuotes" prepend="">
UPPER(U.USER_ID) LIKE UPPER('%$idUser$%')
</isNotEmpty>
<isEmpty property="idUserSingleQuotes" prepend="">
UPPER(U.USER_ID) LIKE UPPER('%'||#idUser#||'%')
</isEmpty>
<!-- idUser have % or _ -->
<isNotEmpty property="idUserPercentOrUnderline" prepend="">
ESCAPE '\'
</isNotEmpty>
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty property="svcCtrCode" prepend="">
S.SVC_CTR_CODE=#svcCtrCode#
</isNotEmpty>
</dynamic>
<dynamic>
ORDER BY UPPER(U.USER_ID) ASC,
UPPER(U.USER_NAME) ASC
</dynamic>
</select>
由于前面加了%和_的处理,那么单引号用这种方法就查不到数据了
解决办法:将'%'||#userName#||'%'替换成%$userName$%
*注: $param$ 是ibatis内部自带的,而#param#是oracle自带的,两者想过等价