mybatis sql注入安全

1.mybatis语句

SELECT * FROM console_operator    WHERE login_name=#{loginName} AND login_pwd=#{loginPwd}

2.日志打印信息
正确情况：username:admin, password:admin

2014-07-30 10:39:10,646 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==>  Preparing: SELECT * FROM console_operator WHERE login_name=? AND login_pwd=? 
2014-07-30 10:39:10,646 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==> Parameters: admin(String), admin(String)
2014-07-30 10:39:10,661 DEBUG [http-bio-8080-exec-9] org.logicalcobwebs.proxool.null.AbstractProxyStatement#trace [AbstractProxyStatement.java:185] SELECT * FROM console_operator    WHERE login_name='admin' AND login_pwd='admin';  (15 milliseconds)
2014-07-30 10:39:10,661 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] <==      Total: 1

非法注入：admin'#

2014-07-30 10:39:10,646 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==>  Preparing: SELECT * FROM console_operator WHERE login_name=? AND login_pwd=? 
2014-07-30 10:39:10,646 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==> Parameters: admin(String), admin(String)
2014-07-30 10:39:10,661 DEBUG [http-bio-8080-exec-9] org.logicalcobwebs.proxool.null.AbstractProxyStatement#trace [AbstractProxyStatement.java:185] SELECT * FROM console_operator    WHERE login_name='admin'# AND login_pwd='4545';  (15 milliseconds)
2014-07-30 10:39:10,661 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] <==      Total: 0

==>返回Total:0  注入失败~！

 

-----------------------------------------分割线-----------------------------------------

 

3.mybatis语句修改为：

SELECT * FROM console_operator    WHERE login_name=${loginName} AND login_pwd=${loginPwd}

4.日志打印信息：
非法注入情况一：'admin'#

2014-07-30 11:23:45,845 DEBUG [http-bio-8080-exec-1] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==>  Preparing: SELECT * FROM console_operator WHERE login_name='admin'# AND login_pwd=7878 
2014-07-30 11:23:45,846 DEBUG [http-bio-8080-exec-1] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==> Parameters: 
2014-07-30 11:23:45,884 DEBUG [http-bio-8080-exec-1] org.logicalcobwebs.proxool.null.AbstractProxyStatement#trace [AbstractProxyStatement.java:185] SELECT * FROM console_operator    WHERE login_name='admin'# AND login_pwd=7878;  (38 milliseconds)
2014-07-30 11:23:45,884 DEBUG [http-bio-8080-exec-1] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] <==      Total: 1

==>返回Total:1  注入成功~！

非法注入情况二：'admin' or 1=1

2014-07-30 11:26:56,943 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==>  Preparing: SELECT * FROM console_operator WHERE login_name='admin' or 1=1 AND login_pwd=7878 
2014-07-30 11:26:56,944 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] ==> Parameters: 
2014-07-30 11:26:57,002 DEBUG [http-bio-8080-exec-9] org.logicalcobwebs.proxool.null.AbstractProxyStatement#trace [AbstractProxyStatement.java:185] SELECT * FROM console_operator    WHERE login_name='admin' or 1=1 AND login_pwd=7878;  (58 milliseconds)
2014-07-30 11:26:57,015 DEBUG [http-bio-8080-exec-9] com.autoyolConsole.mapper.OperatorMapper.login.BaseJdbcLogger#debug [BaseJdbcLogger.java:139] <==      Total: 1

==>返回Total:1  注入成功~！

 

综上所述：

SELECT * FROM console_operator WHERE login_name=? AND login_pwd=? 

不管输入什么参数，打印出的sql都是这样的。这是因为mybatis启用了预编译功能，在sql执行前，会先将上面的sql发送给数据库进行编译，执行时，直接使用编译好的sql，替换占位符“？”就可以了。因为sql注入只能对编译过程起作用，所以这样的方式就很好地避免了sql注入的问题。

mybatis是如何做到sql预编译的呢？其实在框架底层，是jdbc中的PreparedStatement类在起作用，PreparedStatement是我们很熟悉的Statement的子类，它的对象包含了编译好的sql语句。这种“准备好”的方式不仅能提高安全性，而且在多次执行一个sql时，能够提高效率，原因是sql已编译好，再次执行时无需再编译。

在mybatis中，”${xxx}”这样格式的参数会直接参与sql编译，从而不能避免注入攻击。但涉及到动态表名和列名时，只能使用“${xxx}”这样的参数格式，所以，这样的参数需要我们在代码中手工进行处理来防止注入。

结论：在编写mybatis的映射语句时，尽量采用“#{xxx}”这样的格式。若不得不使用“${xxx}”这样的参数，要手工地做好过滤工作，来防止sql注入攻击。