我们使用 jdbc 操作数据库的时候, 都习惯性地使用参数化的 sql 与数据库交互. 因为参数化的 sql 有两大有点, 其一, 防止 sql 注入; 其二, 提高 sql 的执行性能(同一个 connection 共用一个的 sql 编译结果). 下面我们就通过 mybatis 来分析一下参数化 sql 的过程, 以及和非参数化 sql 的不同.
注意:
1本次使用 wireshark 来监听网卡的请求, 测试过程中, 如果使用的是本地的 MySQL 的话, java 和 MySQL 的交互是不需要经过 wireshark 的, 所以如果是想用 wireshark 监听网卡的请求, 推荐是链接远程的数据库.
2本文的项目源代码在文章末尾有链接(项目源代码中也有设计的表的 sql).
3可以结合 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://www.cnblogs.com/ismallboy/p/11374510.html