wireshark抓包分析mybatis的sql參數化查詢
- 2019 年 10 月 3 日
- 筆記
我們使用jdbc操作數據庫的時候，都習慣性地使用參數化的sql與數據庫交互。因為參數化的sql有兩大有點，其一，防止sql注入；其二，提高sql的執行性能（同一個connection共用一個的sql編譯結果）。下面我們就通過mybatis來分析一下參數化sql的過程，以及和非參數化sql的不同。
注意：
①本次使用wireshark來監聽網卡的請求，測試過程中，如果使用的是本地的mysql的話，java和mysql的交互是不需要經過wireshark的，所以如果是想用wireshark監聽網卡的請求，推薦是鏈接遠程的數據庫。
②本文的項目源代碼在文章末尾有鏈接（項目源代碼中也有設計的表的sql）。
③可以結合wiereshark的抓包和mysql的general_log一起來查看sql的參數化過程，文章末尾會貼上從mysql的general_log角度檢測到useServerPrepStmts=true/false兩種執行方式的區別。
一開始，項目中我的db配置如下，我們就先用這個配置來測試一下。
jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
mapper.xml如下
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="mapper.UserMapper">
<select id="findByName" resultType="domain.User">
select *
from `user`
where user_id = #{name}
</select>
</mapper>
測試用例如下：
public class UserMapperTest { @Test public void findByPk() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { UserMapper mapper = session.getMapper(UserMapper.class); User user = mapper.findByName("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); System.out.println(user); } } }
執行測試用例，通過wireshak監聽請求，如下：
從上圖wireshark抓到的數據來看，執行查詢並沒有使用preparestatement，也不是參數化的sql，都是把拼裝好參數的sql發送到mysql執行引擎去執行，為什麼呢？經過查資料發現，db配置的url配置，漏了一個屬性配置分別是useServerPrepStmts，修改後的db的url配置如下：
jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useServerPrepStmts=true
增加了useServerPrepStmts屬性配置之後，再來執行測試用例，看wireshark抓到的數據如下：
(ps.如果useServerPrepStmts=true,是通過wireshark抓包結果可以看到，先是發送Request Prepare Statement–sql模板（同一connection第一次執行改sql模板才會發送，後面就不會在發送該Request），再發送Request Execute Statement–sql參數。而useServerPrepStmts=false的話，都是清一色的Request Query，其實就是沒用到mysql server的預編譯功能，所以是推薦配置useServerPrepStmts=true，提高參數化sql的執行性能)
上圖就是先發送待執行的sql模板（不帶參數）到mysql服務端進行預編譯，並且會在該請求的response中返回該sql編譯之後的id，名曰：Statement ID，wireshark抓到的response數據如下：
發送完sql模板之後，從response中拿到statement id之後，緊跟着就發送參數和statement id到mysql執行引擎，wireshark抓到的數據如下：
如此，便可實現sql的參數化查詢。按照理解，如果此時再用此sql模板查詢另外一個user_id的數據，理論上是不需要再發送sql模板到mysql服務器了的，只需要發送參數和statement ID就可以了的，下面我就試一下，測試用例如下：
@Test public void findByPk() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { UserMapper mapper = session.getMapper(UserMapper.class); User user = mapper.findByName("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); if (user != null || user == null) { user = mapper.findByName("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); } System.out.println(user); } }
執行測試用例，用wireshark抓包，如下：
通過上圖發現，怎麼第二個findByName，壓根就沒發請求到mysql服務器，原來是因為本地的jdbc發現是相同的查詢，直接返回了上一個查詢的結果，所以不需要重新到mysql服務器去請求數據。那我在第二個findByName改一個和第一個不一樣的參數，測試用例如下：
@Test public void findByPk() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { UserMapper mapper = session.getMapper(UserMapper.class); User user = mapper.findByName("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); if (user != null || user == null) { user = mapper.findByName("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG9"); } System.out.println(user); } }
執行上面這個測試用例，wireshark抓包結果如下：
上圖發現，竟然兩次請求都重複發送了模板sql到mysql服務器預編譯，為何呢？原來db的url配置裏面還漏了一個屬性配置（cachePrepStmts），增加cachePrepStmts配置之後，db的url配置如下：
jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useServerPrepStmts=true&cachePrepStmts=true
更新db的url配置之後，再執行測試用例，wireshark抓包結果如下：
通過上圖發現，第二次findByName不再發送模板sql了，直接就是發送Execute Statement了，其實Execute Statement就是執行sql的參數和statement ID（該connection第一次預編譯模板sq的時候l返回的）。但是中間還是會發一個Reset Statement的mysql數據包，為什麼要發這個Reset Statement數據包，有知道的同學，可以評論回復一下，我也還沒去深究~謝謝~
這裡附帶再說一下mybatis的參數化sql可以防止sql注入的理解，其實防止sql注入，有兩點，其一，mybatis本身會有一個sql參數化的過程，這裡涉及到mybatis的#和$的區別，參數化sql是用#引用變量，mybatis會對參數進行特殊字符以及敏感字符的轉義以防止sql注入；其二，db的url配置中加了useServerPrepStmts=true之後，mysql服務端會對Execute Statement發送的參數中涉及的敏感字符進行轉義，以防止sql注入，所以，如果不加useServerPrepStmts=true的話，會發現，mybatis在本地就已經對參數中涉及的敏感字符進行了轉義之後，再發往mysql server，可以使用wireshark抓包看到；但是如果是加了useServerPrepStmts=true之後，會發現client發往mysql server的參數（Execute Statement），mybatis不會對其中的參數進行轉義了，參數敏感字符轉義這一塊交給了mysql server去做，也可以通過wireshark抓包看到。so，這裡會有兩塊地方防止sql注入，一塊在client，一塊在mysql server（使用存儲過程防止sql注入也是使用了mysql server的該功能），就看你是否使用useServerPrepStmts。
附錄：
1.useServerPrepStmts=false/true，wireshark抓包結果
useServerPrepStmts=false，wireshark抓包結果如下：
useServerPrepStmts=true，wireshark抓包結果如下：
2. mysql server的general_log角度檢測到useServerPrepStmts=false/true的執行sql
useServerPrepStmts=false的general_log
2019-08-18T15:19:12.330744Z 38 Query SET autocommit=0 2019-08-18T15:19:12.345704Z 38 Query select * from `user` where user_id = 'SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8' or 1 = 1 #' 2019-08-18T15:19:12.358669Z 38 Query select * from `user` where user_id = 'SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG9' 2019-08-18T15:19:12.359666Z 38 Query SET autocommit=1
useServerPrepStmts=true的general_log
2019-08-18T09:39:42.533289Z 30 Query SET autocommit=0 2019-08-18T09:39:42.546254Z 30 Prepare select * from `user` where user_id = ? 2019-08-18T09:39:42.550244Z 30 Execute select * from `user` where user_id = 'SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8' or 1 = 1 #' 2019-08-18T09:39:42.560217Z 30 Reset stmt 2019-08-18T09:39:42.561214Z 30 Execute select * from `user` where user_id = 'SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG9' 2019-08-18T09:39:42.563210Z 30 Query SET autocommit=1
最後，附上上面的測試用例：https://download.csdn.net/download/ismallboy/11577637
