#{}是预编译处理,${}是字符串替换。
详情:
(1)mybatis在处理#{}时,会将sql中的#{}替换为?号,调用PreparedStatement的set方法来赋值。
(2)mybatis在处理时 , 就 是 把 {}时,就是把时,就是把{}替换成变量的值。
(3)使用#{}可以有效的防止SQL注入,提高系统安全性。原因在于:预编译机制。预编译完成之后,SQL的结构已经固定,即便用户输入非法参数,也不会对SQL的结构产生影响,从而避免了潜在的安全风险。
(4)预编译是提前对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=11
很明显,使用${}将参数拼接后在编译成SQL语句,不能防止SQL注入.如果password的传参是
"11111 or 1=1" 那生成的sql语句是:select * from user where account = 201301001 and password = 111111 or 1=1 ;其作用就相当于 select * from user ,会查出表中所有信息,这是很危险的