mybatis sql注入安全

標籤：style   blog   http   color   java   使用   io   資料   

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注入攻擊。