Sql注入

为了防止SQL注入，iBatis模糊查询时也要避免使用$$来进行传值。下面是三个不同数据库的ibatis的模糊查询传值。
Sql代码
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 #+'%


如：
Sql代码
<!-- 用途：小二后台查询活动的数目 -->
<!-- 频率：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>

不要这样来写：
Sql代码
<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>