MyBatis中使用parameterType向SQL语句传参,parameterType支持的类型可以是基本类型int,String,HashMap和java自定义类型。
在SQL中引用这些参数的时候,可以使用两种方式:
#{parameterName}
${parameterName}
首先,我们说一下这两种引用参数时的区别,使用#{parameterName}引用参数的时候,Mybatis会把这个参数认为是一个字符串,并自动加上'',例如传入参数是“Smith”,那么在下面SQL中:
Select * from emp where name = #{employeeName}
使用的时候就会转换为:
Select * from emp where name = 'Smith';
同时使用${parameterName}的时候在下面SQL中
Select * from emp where name = ${employeeName}
就会直接转换为:
Select * from emp where name = Smith
简单说#{}是经过预编译的,是安全的。
而${}是未经过预编译的,仅仅是取变量的值,是非安全的,存在SQL注入。
#{} 这种取值是编译好SQL语句再取值 ${} 这种是取值以后再去编译SQL语句
下面我们用一个实际的例子看看分别使用和是否可以防止SQL注入。
首先是使用#{}:
<!-- 使用#{} --> <select id="selectUser" parameterType="String" resultType="com.mybatis.po.MyUser"> select * from user where account = #{account} and password = #{password} </select>
分别测试正常传参和拼接传参:
// 使用#{} 正常传参 Map<String, Object> parameter = new HashMap<>(); parameter.put("account", ); parameter.put("password", password); MyUser mu = ss.selectOne("com.mybatis.mapper.UserMapper.selectUser", parameter); System.out.println("返回结果:" + mu); // 使用#{} 拼接传参 Map<String, Object> parameter_1 = new HashMap<>(); parameter_1.put("account", "201301001"); parameter_1.put("password", "111111" + "or account = 'admin' "); MyUser mu_1 = ss.selectOne("com.mybatis.mapper.UserMapper.selectUser", parameter_1); System.out.println("返回结果:" + mu_1);
结果如下:
DEBUG [http-nio-8080-exec-5] - ==> Preparing: select * from user where account = ? and password = ? DEBUG [http-nio-8080-exec-5] - ==> Parameters: 201301001(String), 111111(String) DEBUG [http-nio-8080-exec-5] - <== Total: 1 返回结果:MyUser [id=17, account=201301001, password=111111, name=蒙奇D路飞] DEBUG [http-nio-8080-exec-5] - ==> Preparing: select * from user where account = ? and password = ? DEBUG [http-nio-8080-exec-5] - ==> Parameters: 201301001(String), 111111 or account = 'admin' (String) DEBUG [http-nio-8080-exec-5] - <== Total: 0 返回结果:null
很明显,使用#{}的时候,即使传入了恶意参数,#{}只会将其作为一个占位符的参数,如上面这个例子:
DEBUG [http-nio-8080-exec-5] - ==> Preparing: select * from user where account = ? and password = ? DEBUG [http-nio-8080-exec-5] - ==> Parameters: 201301001(String), 111111 or account = 'admin' (String) DEBUG [http-nio-8080-exec-5] - <== Total: 0 转换为实际的SQL语句:select * from user where account = '201301001' and password = '111111 or account = 'admin''
现在是使用${}:
<!-- 使用${} --> <select id="selectUser2" parameterType="String" resultType="com.mybatis.po.MyUser"> select * from user where account = ${account} and password = ${password} </select>
分别测试正常传参和拼接传参:
// 使用${} 正常传参 Map<String, Object> parameter = new HashMap<>(); parameter.put("account", "201301001"); parameter.put("password", "111111"); MyUser mu = ss.selectOne("com.mybatis.mapper.UserMapper.selectUser2",parameter); System.out.println("返回结果:" + mu); // 使用${} 拼接传参 Map<String, Object> parameter2 = new HashMap<>(); parameter2.put("account", "201301001"); parameter2.put("password", "111111" + " or account = 'admin' "); MyUser mu2 = ss.selectOne("com.mybatis.mapper.UserMapper.selectUser2", parameter2); System.out.println("返回结果:" + mu2);
结果如下:
DEBUG [http-nio-8080-exec-18] - ==> Preparing: select * from user where account = 201301001 and password = 111111 DEBUG [http-nio-8080-exec-18] - ==> Parameters: DEBUG [http-nio-8080-exec-18] - <== Total: 1 返回结果:MyUser [id=17, account=201301001, password=111111, name=蒙奇D路飞] DEBUG [http-nio-8080-exec-18] - ==> Preparing: select * from user where account = 201301001 and password = 111111 or account = 'admin' DEBUG [http-nio-8080-exec-18] - ==> Parameters: DEBUG [http-nio-8080-exec-18] - <== Total: 2 返回结果:[MyUser [id=1, account=admin, password=111111, name=管理员], MyUser [id=17, account=201301001, password=111111, name=蒙奇D路飞]]
很明显,使用${}将参数拼接后在编译成SQL语句,不能防止SQL注入,查询出了有关account=admin的额外信息,这是很危险的。