sql语句中一些特殊字符的处理

为了防止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();
    }

这样处理的弊端：当用户输入带%或_的查询条件时， 会查不到数据。

解决办法 ：动态加上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#||'%'，

                </isNotEmpty>                                                                             而用%$userName$%

                <isEmpty property="userNameSingleQuotes" prepend="">     
                    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自带的，两者想过等价