文章目录每日一句正能量前言1. 背景与问题2. 环境与数据3. 复现过程3.1 复现 SQL 注入风险3.2 复现高频 SQL 文本膨胀4. 方案实施4.1 JDBC标准 PreparedStatement 写法4.2 Spring JdbcTemplate 已经默认参数化4.3 MyBatis#{} 和 ${} 必须区分4.4 JPA / Hibernate 参数绑定4.5 计划复用不要过度承诺4.6 高频写入PreparedStatement Batch4.7 批量异常与事务边界4.8 参数化并不能处理所有动态 SQL4.9 IN 查询的参数化5. 结果对比5.1 安全5.2 SQL 模板稳定性5.3 高频性能5.4 批量写入6. 风险与复盘6.1 不要把 PreparedStatement 神化6.2 参数嗅探与计划不稳定6.3 日志不要还原成完整 SQL6.4 ORM 也可能制造动态 SQL 爆炸6.5 事务中的预编译语句要正确关闭结语每日一句正能量所行化坦途所愿成星盏事业如竹拔节步步攀登财源似水长流涓涓不息健康若山屹立岁岁长青喜乐像光弥漫时时相伴。朝暮与年岁并往与你一起共至光年。前言在数据库开发里“不要拼接 SQL要使用参数化查询”几乎已经成为常识。但真正到了高频在线接口很多团队对 PreparedStatement 的理解仍停留在“防 SQL 注入”这一层。实际上预编译语句同时涉及四件事安全参数与 SQL 结构分离 性能减少重复解析与优化成本 稳定SQL 模板固定更容易聚合和观察 工程驱动、ORM、事务和批量执行行为更可控更重要的是“使用 PreparedStatement”并不自动等于“数据库一定复用了执行计划”。不同数据库、JDBC 驱动、连接池、ORM 框架对预编译的处理不同。MySQL 还存在客户端模拟预编译和服务端预编译的差异JPA/Hibernate 也可能在 SQL 生成阶段做额外处理。本文通过一个高频用户查询接口和一个批量写入接口拆解预编译语句在性能、安全、驱动适配和事务边界上的真实价值。1. 背景与问题先看一段最常见的错误代码publicUserfindByName(Stringname){StringsqlSELECT id, user_name, status FROM users WHERE user_name name;returnjdbcTemplate.queryForObject(sql,(rs,rowNum)-newUser(rs.getLong(id),rs.getString(user_name),rs.getString(status)));}问题不仅是 SQL 注入。当输入值不断变化时生成的 SQL 文本也不断变化SELECT...WHEREuser_namealice;SELECT...WHEREuser_namebob;SELECT...WHEREuser_namecarol;从数据库视角看这是三条不同文本。如果换成参数化SELECT...WHEREuser_name?SQL 模板固定参数值作为独立数据传输。这带来两个直接收益用户输入不再被当成 SQL 结构解析数据库和驱动更有机会复用解析结果与执行计划。对于一个每天执行几十次的后台查询这点差异可能不明显但对于每秒数千次的高频接口SQL 解析、优化和计划缓存碎片会逐渐变成数据库 CPU 的组成部分。2. 环境与数据本文示例环境JDK 21 Spring Boot 3.3 MySQL 8.0 MySQL Connector/J HikariCP Spring JDBC MyBatis 3.x Hibernate 6 / JPA建立测试表CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_nameVARCHAR(64)NOTNULL,emailVARCHAR(128)NOTNULL,statusVARCHAR(16)NOTNULL,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,UNIQUEKEYuk_user_name(user_name),KEYidx_status_created(status,created_at));插入测试数据INSERTINTOusers(user_name,email,status)VALUES(alice,aliceexample.com,ACTIVE),(bob,bobexample.com,ACTIVE),(carol,carolexample.com,DISABLED);高频接口GET /users/by-name?namealice批量接口POST /users/batch用于测试批量插入。3. 复现过程3.1 复现 SQL 注入风险字符串拼接版本GetMapping(/unsafe)publicListMapString,Objectunsafe(RequestParamStringname){StringsqlSELECT id, user_name, status FROM users WHERE user_name name;returnjdbcTemplate.queryForList(sql);}如果输入alice OR 11最终 SQL 可能变成SELECTid,user_name,statusFROMusersWHEREuser_namealiceOR11此时用户输入改变了 SQL 的逻辑结构。参数化版本GetMapping(/safe)publicListMapString,Objectsafe(RequestParamStringname){returnjdbcTemplate.queryForList( SELECT id, user_name, status FROM users WHERE user_name ? ,name);}这里的输入只会作为值参与比较而不会重新组成 SQL 语法。3.2 复现高频 SQL 文本膨胀假设接口每秒执行 5000 次。字符串拼接生成5000 个不同 user_name - 大量不同 SQL 文本而参数化版本只有一个 SQL 模板SELECTid,user_name,statusFROMusersWHEREuser_name?数据库是否真正复用执行计划要看数据库和驱动实现但从应用侧至少已经满足了“稳定 SQL 模板”的必要条件。4. 方案实施4.1 JDBC标准 PreparedStatement 写法最基础也是最可靠的实现publicUserfindByName(Stringname)throwsSQLException{Stringsql SELECT id, user_name, email, status FROM users WHERE user_name ? ;try(ConnectionconnectiondataSource.getConnection();PreparedStatementpsconnection.prepareStatement(sql)){ps.setString(1,name);try(ResultSetrsps.executeQuery()){if(!rs.next()){returnnull;}returnnewUser(rs.getLong(id),rs.getString(user_name),rs.getString(email),rs.getString(status));}}}这里有三个关键点SQL 模板固定 参数单独绑定 类型由 JDBC API 明确传递不要写ps.setObject(1,name);然后所有参数都依赖驱动猜类型。更稳妥的是setString()setLong()setInt()setBigDecimal()setTimestamp()类型越明确驱动适配越可控。4.2 Spring JdbcTemplate 已经默认参数化正确示例publicUserfindByName(Stringname){returnjdbcTemplate.queryForObject( SELECT id, user_name, email, status FROM users WHERE user_name ? ,(rs,rowNum)-newUser(rs.getLong(id),rs.getString(user_name),rs.getString(email),rs.getString(status)),name);}JdbcTemplate最常见的错误不是不会绑定参数而是开发人员为了“方便调试”先拼字符串Stringsql SELECT * FROM users WHERE user_name %s .formatted(name);然后再交给 JdbcTemplate。这相当于主动放弃参数化。4.3 MyBatis#{}和${}必须区分MyBatis 中selectidfindByNameresultTypeUserSELECT id, user_name, email, status FROM users WHERE user_name #{name}/select#{name}会走参数绑定。而WHERE user_name ${name}是文本替换。这两个写法看起来只差一个符号安全语义完全不同。${}并非永远禁止它适用于一些无法参数化的 SQL 结构例如动态列名 动态表名 ORDER BY 字段但必须使用白名单。例如privatestaticfinalSetStringALLOWED_SORTSet.of(created_at,user_name,status);publicStringsafeSort(Stringsort){if(!ALLOWED_SORT.contains(sort)){thrownewIllegalArgumentException(illegal sort column);}returnsort;}然后才允许进入${sort}。不能直接把 HTTP 参数原样放进去。4.4 JPA / Hibernate 参数绑定JPQLTypedQueryUserEntityqueryentityManager.createQuery( select u from UserEntity u where u.userName :name ,UserEntity.class);query.setParameter(name,name);Native SQLQueryqueryentityManager.createNativeQuery( SELECT id, user_name, email, status FROM users WHERE user_name :name );query.setParameter(name,name);JPA 参数化查询不仅有安全价值也能让 ORM 生成更稳定的 SQL 模板。4.5 计划复用不要过度承诺从理论上说固定 SQL 模板更利于数据库复用解析和执行计划。但必须注意一个事实PreparedStatement ≠ 一定服务端预编译 ≠ 一定复用同一个执行计划以 MySQL Connector/J 为例驱动可以有客户端预处理行为也可以开启服务端预编译模式。典型 JDBC URLjdbc:mysql://127.0.0.1:3306/demo ?useServerPrepStmtstrue cachePrepStmtstrue prepStmtCacheSize250 prepStmtCacheSqlLimit2048这些参数的意义通常包括useServerPrepStmts 尝试使用服务端预编译 cachePrepStmts 启用 PreparedStatement 缓存 prepStmtCacheSize 每个连接缓存的语句数量 prepStmtCacheSqlLimit 允许缓存的 SQL 文本长度上限连接池又会进一步影响效果因为 PreparedStatement 缓存通常和物理连接生命周期有关。所以真实优化应该观察数据库 CPU Com_stmt_prepare Com_stmt_execute 语句缓存命中 SQL 模板数量 吞吐量 P95/P99而不是只看代码里有没有?。4.6 高频写入PreparedStatement Batch如果每次插一行for(Useruser:users){jdbcTemplate.update(INSERT INTO users(user_name,email,status) VALUES (?,?,?),user.name(),user.email(),user.status());}虽然每次都是参数化但会产生大量独立执行。更适合高频批量写入的是TransactionalpublicvoidbatchInsert(ListUserusers)throwsSQLException{Stringsql INSERT INTO users(user_name, email, status) VALUES (?, ?, ?) ;try(ConnectioncdataSource.getConnection();PreparedStatementpsc.prepareStatement(sql)){for(Useruser:users){ps.setString(1,user.name());ps.setString(2,user.email());ps.setString(3,user.status());ps.addBatch();}int[]countsps.executeBatch();if(counts.length!users.size()){thrownewIllegalStateException(batch result count mismatch);}}}这时候收益来自两部分同一个 SQL 模板重复绑定参数 批量减少驱动与数据库之间的交互次数MySQL 还常见rewriteBatchedStatementstrue但是否开启、如何改写应结合驱动版本和实际压测判断。4.7 批量异常与事务边界批量执行时最容易被忽略的是BatchUpdateException。例如try{ps.executeBatch();}catch(BatchUpdateExceptione){int[]updateCountse.getUpdateCounts();log.error(batch failed, successCount{}, sqlState{},updateCounts.length,e.getSQLState(),e);throwe;}不能只写批量执行失败因为某些驱动或数据库可能已经执行了部分语句。如果业务要求“100 条要么全成功要么全失败”必须使用事务TransactionalpublicvoidimportUsers(ListUserusers){repository.batchInsert(users);}异常必须继续抛出让事务回滚。错误做法TransactionalpublicvoidimportUsers(ListUserusers){try{repository.batchInsert(users);}catch(Exceptione){log.warn(ignore batch error,e);}}这会让事务语义变得危险。4.8 参数化并不能处理所有动态 SQL以下结构不能简单使用?ORDERBY?多数数据库不会把参数值当成列名解释。同样SELECT*FROM?不能通过普通参数绑定把表名传进去。因此安全动态 SQL 要做值 - PreparedStatement 参数 结构 - 白名单例如publicStringbuildOrderBy(Stringsort){returnswitch(sort){casetime-created_at;casename-user_name;casestatus-status;default-thrownewIllegalArgumentException(unsupported sort field);};}然后 SQL 结构由后端确定。4.9 IN 查询的参数化错误做法StringidsrequestIds.stream().map(String::valueOf).collect(Collectors.joining(,));StringsqlSELECT * FROM users WHERE id IN (ids);更好的 JDBC 做法StringplaceholdersString.join(,,Collections.nCopies(ids.size(),?));StringsqlSELECT id,user_name,status FROM users WHERE id IN (placeholders);try(PreparedStatementpsc.prepareStatement(sql)){for(inti0;iids.size();i){ps.setLong(i1,ids.get(i));}}虽然 SQL 模板会随IN参数个数变化但仍然保持“值参数化”。如果列表非常大还要考虑临时表 批量 join 分段查询 数据库数组参数不要无限扩展IN (...)。5. 结果对比可以从四个维度比较。5.1 安全字符串拼接用户输入可能改变 SQL 结构参数化用户输入按值绑定这是最确定的收益。5.2 SQL 模板稳定性字符串拼接WHERE user_namealice WHERE user_namebob WHERE user_namecarol参数化WHERE user_name?稳定模板更适合慢 SQL 聚合 SQL 指纹 调用次数统计 APM 分析 计划缓存5.3 高频性能在高频接口里应重点比较吞吐量 P95/P99 数据库 CPU SQL 解析次数 PreparedStatement 缓存命中 网络往返一个常见趋势是字符串拼接 - SQL 文本变化更多 - 解析/优化机会更多 - 计划缓存碎片更明显 参数化 - SQL 模板稳定 - 驱动/数据库更容易复用 - 高频调用 CPU 更稳定但最终数字必须以实际压测为准。5.4 批量写入逐条执行prepare / execute prepare / execute prepare / execute ...批量参数化prepare once bind × N executeBatch在网络往返成为主要成本时差异尤其明显。6. 风险与复盘6.1 不要把 PreparedStatement 神化PreparedStatement 的确定价值是参数分离 类型绑定 接口标准化计划复用属于“更容易实现”但是否真正复用要看数据库和驱动实现。6.2 参数嗅探与计划不稳定部分数据库会根据参数值选择执行计划。如果数据分布严重倾斜statusACTIVE 占 99% statusDELETED 占 1%同一个参数化 SQL 可能对不同参数并不适合使用完全相同的计划。这就是为什么“计划复用”并不总是越多越好。出现明显参数倾斜时应结合执行计划 统计信息 直方图 索引 数据库参数化策略分析而不是简单关闭参数化。6.3 日志不要还原成完整 SQL有些团队为了排障把WHEREuser_name?重新拼成WHEREuser_namealice然后写日志。这样会重新引入敏感信息泄露风险。更好的日志{sqlTemplate:SELECT ... WHERE user_name ?,parameterTypes:[VARCHAR],parameterCount:1,maskedParameters:[a***e]}6.4 ORM 也可能制造动态 SQL 爆炸即使使用 Hibernate如果动态查询每次拼出不同字段组合有 name 有 status 有 createTime 不同排序 不同 join仍可能产生大量 SQL 模板。因此 ORM 项目同样应该关注SQL 指纹数量 高频模板数量 动态条件组合6.5 事务中的预编译语句要正确关闭PreparedStatement 和 ResultSet 都必须正确关闭。推荐try(ConnectioncdataSource.getConnection();PreparedStatementpsc.prepareStatement(sql);ResultSetrsps.executeQuery()){...}连接归还连接池之前未关闭的资源可能给连接复用带来隐患。结语预编译语句的真正价值不只是“写法更安全”。在高频在线接口里它同时解决SQL 注入风险 SQL 模板不稳定 重复解析与优化成本 驱动与数据库的计划复用机会 批量写入效率 可观测性聚合可以用一句工程化原则总结数据值全部参数化 SQL 结构只允许后端白名单生成。如果再往前一步还应把PreparedStatement 缓存 服务端预编译 批量执行 事务边界 异常回滚 SQL 指纹一起纳入治理。真正优秀的参数化查询方案不是简单地把字符串里的值换成?而是让安全边界、性能边界、驱动行为和事务语义保持一致。转载自https://blog.csdn.net/u014727709/article/details/165241270欢迎 点赞✍评论⭐收藏欢迎指正
`)
