为了防止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>