2023年7月3日发(作者：)

wireshark抓包分析mybatis的sql参数化查询　　我们使⽤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://:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai　　如下　　 　　select * 　　from `user` 　　where user_id = #{name}　　　　测试⽤例如下：public class UserMapperTest { @Test public void findByPk() throws IOException { String resource = ""; InputStream inputStream = ourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = ssion()) { UserMapper mapper = per(); User user = Name("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); n(user); } }} 　　执⾏测试⽤例，通过wireshak监听请求，如下：　　从上图wireshark抓到的数据来看，执⾏查询并没有使⽤preparestatement，也不是参数化的sql，都是把拼装好参数的sql发送到mysql执⾏引擎去执⾏，为什么呢？经过查资料发现，db配置的url配置，漏了⼀个属性配置分别是useServerPrepStmts，修改后的db的url配置如下：jdbc:mysql://: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 = ""; InputStream inputStream = ourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = ssion()) { UserMapper mapper = per(); User user = Name("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); if (user != null || user == null) { user = Name("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); } n(user); } }　　执⾏测试⽤例，⽤wireshark抓包，如下：　　通过上图发现，怎么第⼆个findByName，压根就没发请求到mysql服务器，原来是因为本地的jdbc发现是相同的查询，直接返回了上⼀个查询的结果，所以不需要重新到mysql服务器去请求数据。那我在第⼆个findByName改⼀个和第⼀个不⼀样的参数，测试⽤例如下： @Test public void findByPk() throws IOException { String resource = ""; InputStream inputStream = ourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = ssion()) { UserMapper mapper = per(); User user = Name("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG8"); if (user != null || user == null) { user = Name("SYS_APP_d5bwx8AfZXkKewMkOkaZhBv7MWqjiDX3qIkfPkG9"); } n(user); } }　　执⾏上⾯这个测试⽤例，wireshark抓包结果如下：　　上图发现，竟然两次请求都重复发送了模板sql到mysql服务器预编译，为何呢？原来db的url配置⾥⾯还漏了⼀个属性配置（cachePrepStmts），增加cachePrepStmts配置之后，db的url配置如下：jdbc:mysql://: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。

附录：　verPrepStmts=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=02019-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=02019-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