MyBatis 防止 % _ sql 注入攻击 解决方法

首先说思路，在mybatis中防止sql注入，目前只能在Controller层进行转义，后台sql进行查询，然后在controller层转义回来，返回到前台。

理论上应该可以在dao.xml中进行判断   但是目前还没写出来。Orz

上代码

@RequiresPermissions("member:member:view")@RequestMapping(value = {"list", ""})public String list(Member member, HttpServletRequest request, HttpServletResponse response, Model model) {/** -- 以下 防止sql%注入 故对其进行转化---- **/String companyName = member.getCompanyName();String loginName = member.getLoginName();if (companyName != null ) {if(companyName.contains("%")){member.setCompanyName(companyName.replaceAll("\\%", "\\\\%"));}if(companyName.contains("_")){member.setCompanyName(companyName.replaceAll("\\_", "\\\\_"));}}if (loginName != null) {if (loginName.contains("%")) {member.setLoginName(loginName.replaceAll("\\%", "\\\\%"));}if (loginName.contains("_")) {member.setLoginName(loginName.replaceAll("\\_", "\\\\_"));}}/** --  转化完毕 -- **/Page<Member> page = memberService.findPage(new Page<Member>(request,response), member);/**-- 将转化的字符恢复原来的值 --**/if (companyName != null) {member.setCompanyName(companyName);}/**-- 将转化的字符恢复原来的值 --**/if (loginName != null) {member.setLoginName(loginName);}model.addAttribute("page", page);return "dsp/member/memberList";}

sql

<select id="findList" resultType="Member">SELECT <include refid="memberColumns"/>FROM dsp_member a<include refid="memberJoins"/><where>a.del_flag = #{DEL_FLAG_NORMAL}<if test="companyName != null and companyName != ''">AND a.company_name LIKE <if test="dbName == 'oracle'">'%'||#{companyName}||'%' ESCAPE '\'</if><if test="dbName == 'mssql'">'%'+#{companyName}+'%'</if><if test="dbName == 'mysql'">concat('%',#{companyName},'%')</if></if><if test="loginName != null and loginName != ''">AND a.login_name LIKE <if test="dbName == 'oracle'">'%'||#{loginName}||'%' ESCAPE '\'</if><if test="dbName == 'mssql'">'%'+#{loginName}+'%'</if><if test="dbName == 'mysql'">concat('%',#{loginName},'%')</if></if><if test="validStatus != null and validStatus != ''">AND a.valid_status = #{validStatus}</if></where><if test="sqlMap.confSql != null and sqlMap.confSql != ''">${sqlMap.confSql}</if><choose><when test="page !=null and page.orderBy != null and page.orderBy != ''">ORDER BY ${page.orderBy}</when><otherwise>ORDER BY a.update_date DESC</otherwise></choose></select>

sql这里主要是用 ESCAPE 进行转义

<if test="dbName == 'oracle'">'%'||#{companyName}||'%' ESCAPE '\'</if>

<if test="dbName == 'oracle'">'%'||#{loginName}||'%' ESCAPE '\'</if>