使用ibatis防止sql注入

为了防止SQL注入，iBatis模糊查询时也要避免使用$$来进行传值。下面是三个不同数据库的ibatis的模糊查询传值。

    mysql: select * from stu where name like concat('%',#name #,'%')  
     
    oracle: select * from stu where name like '%'||#name #||'%' 
     
    SQL Server:select * from stu where name like '%'+#name #+'%   

如：

<!-- 用途：小二后台查询活动的数目 -->
	<!-- 频率：1000/天 -->
	<!-- 维护：刘飞 -->
	<select id="countActivitySearch" resultClass="java.lang.Long" parameterClass="actDO">
    	<![CDATA[
    		select count(id) from activity 
    		]]>
    	
    	<dynamic prepend="WHERE">
			<isNotNull prepend=" AND " property="name">
                name LIKE CONCAT('%', #name#, '%')
            </isNotNull>
            <isNotNull prepend=" AND "  property="itemId">
            	itemId = #itemId#
            </isNotNull>
            <isNotNull prepend=" AND " property="itemName">
                itemName LIKE CONCAT('%', #itemName#, '%')
            </isNotNull>         
            <isNotNull prepend=" AND " property="status">
                status = #status#
            </isNotNull> 
            <isNotNull prepend=" AND " property="actStatus">
                actStatus = #actStatus#
            </isNotNull>          
            <isNotNull prepend=" AND " property="domain">
                domain LIKE CONCAT('%', #domain#, '%')
            </isNotNull>
		</dynamic>
		
    </select>
    
    <!-- 用途：小二后台查询活动的列表 -->
	<!-- 频率：1000/天 -->
	<!-- 维护：刘飞 -->
    <select id="searchActivityForList" resultMap="actResult" parameterClass="actDO">
    	<![CDATA[
    		select * from activity 
    	]]>
    	
    	<dynamic prepend="WHERE">
			<isNotNull prepend=" AND " property="name">
                name LIKE CONCAT('%', #name#, '%')
            </isNotNull>
            <isNotNull prepend=" AND "  property="itemId">
            	itemId = #itemId#
            </isNotNull>
            <isNotNull prepend=" AND " property="itemName">
                itemName LIKE CONCAT('%', #itemName#, '%')
            </isNotNull>         
            <isNotNull prepend=" AND " property="status">
                status = #status#
            </isNotNull> 
            <isNotNull prepend=" AND " property="actStatus">
                actStatus = #actStatus#
            </isNotNull>          
            <isNotNull prepend=" AND " property="domain">
                domain LIKE CONCAT('%', #domain#, '%')
            </isNotNull>
		</dynamic>
        
    	<![CDATA[
    		order by starttime desc, createtime desc
    	    limit 
        		#startRow#, #perPageSize#	
        ]]>
    </select>

不要这样来写：

<select id="searchActivityForCount" resultClass="java.lang.Long" >
    	<![CDATA[
    		select count(*) from activity
    		]]>
    	
    	<dynamic prepend="WHERE">
			<isNotNull prepend=" AND " property="name">
                name LIKE '%$name$%'
            </isNotNull>
			<isNotNull prepend=" AND " property="itemId">
                itemId LIKE '%$itemId$%'
            </isNotNull>
            <isNotNull prepend=" AND " property="itemName">
                itemName LIKE '%$itemName$%'
            </isNotNull>         
            <isNotNull prepend=" AND " property="status">
                status = #status#
            </isNotNull> 
            <isNotNull prepend=" AND " property="actStatus">
                actStatus = #actStatus#
            </isNotNull>          
            <isNotNull prepend=" AND " property="domain">
                domain LIKE '%$domain$%'
            </isNotNull>
		</dynamic>
    </select>
    
    <select id="searchActivityForList" resultMap="actResult" parameterClass="actDO">
    	<![CDATA[
    		select * from activity 
    	]]>
    	
    	<dynamic prepend="WHERE">
			<isNotNull prepend=" AND " property="name">
                name LIKE '%$name$%'
            </isNotNull>
			<isNotNull prepend=" AND " property="itemId">
                itemId LIKE '%$itemId$%'
            </isNotNull>
            <isNotNull prepend=" AND " property="itemName">
                itemName LIKE '%$itemName$%'
            </isNotNull>         
            <isNotNull prepend=" AND " property="status">
                status = #status#
            </isNotNull> 
            <isNotNull prepend=" AND " property="actStatus">
                actStatus = #actStatus#
            </isNotNull>          
            <isNotNull prepend=" AND " property="domain">
                domain LIKE '%$domain$%'
            </isNotNull>
		</dynamic>
        
    	<![CDATA[
    		order by starttime desc, createtime desc
    	    limit 
        		#startRow#, #perPageSize#	
        ]]>
    </select>