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>