li00lee 2011-12-19
为了防止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>
如:对于sql语句order by #user_id#,如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id,则解析成的sql为order by "id"。