深入解析 MySQL 中 `${}` 和 `#{}` 的动态 SQL 实战技巧与避坑指南

1. 从零理解动态SQL中的占位符刚接触MyBatis时我也曾被${}和#{}搞得晕头转向。直到有次线上系统被SQL注入攻击才真正明白它们的区别有多重要。简单来说这两个符号就像做菜时的不同处理方式——#{}像用保鲜膜密封食材安全但有限制${}像直接暴露食材灵活但危险。在MySQL中执行SQL语句时数据库引擎会经历解析→编译→执行三个阶段。#{}在编译阶段会被替换为问号占位符?实际值在执行阶段才传入这种机制叫做预编译。而${}则是简单的字符串替换就像用文本编辑器做查找替换一样直接。举个例子当执行这个查询时SELECT * FROM users WHERE name #{userName}数据库实际收到的是一条带占位符的模板SELECT * FROM users WHERE name ?然后才会把具体的张三、李四这些值传进去。这种机制从根本上杜绝了SQL注入的可能——因为传进去的值永远只会被当作数据不会被解析为SQL指令。2. 核心机制深度对比2.1 预编译原理剖析#{}的工作流程就像寄快递时的标准化包装先把空箱子SQL模板交给快递公司数据库快递公司检查箱子是否合规语法校验最后才把物品参数值放进箱子这种机制带来三个关键优势性能提升相同的SQL只需编译一次比如分页查询绝对安全参数值不可能改变SQL结构类型安全自动处理日期、字符串的引号等问题而${}的字符串拼接相当于直接把物品裸露着运输。我曾遇到过这样的惨案String orderBy name; DROP TABLE users -- ; String sql SELECT * FROM users ORDER BY orderBy;执行后用户表就被删除了——这就是典型的SQL注入攻击。2.2 必须使用${}的三大场景有些场景就像必须用裸装运输的特殊物品动态表名/列名分表场景中像logs_2023这样的表名必须在编译前确定select idgetLogs SELECT * FROM logs_${yearMonth} /select排序字段ORDER BY create_time DESC这样的子句需要完整拼接Select(SELECT * FROM products ORDER BY ${sortField}) ListProduct getProducts(Param(sortField) String sortField);动态SQL片段当需要条件组装时if testincludeDetails ${detailColumns} /if3. 安全防护实战方案3.1 白名单校验机制对于必须使用${}的场景我总结了一套防护方案// 表名校验正则 private static final Pattern TABLE_NAME_PATTERN Pattern.compile(^[a-z][a-z0-9_]{0,63}$); public void validateTableName(String name) { if (!TABLE_NAME_PATTERN.matcher(name).matches()) { throw new IllegalArgumentException(非法表名); } }在MyBatis拦截器中可以这样实现Intercepts(Signature(type StatementHandler.class,...)) public class SafeTableInterceptor implements Interceptor { Override public Object intercept(Invocation invocation) { String sql boundSql.getSql(); if (sql.contains(${table})) { validateTableName(parameterObject.getTable()); } return invocation.proceed(); } }3.2 权限最小化原则即使做了校验也要遵循动态SQL使用只读账号禁止跨库查询去掉账号的*.*权限重要表操作增加审批流程就像我们金融系统会这样配置CREATE USER report_user% IDENTIFIED BY complexPwd123; GRANT SELECT ON analytics.report_* TO report_user%;4. 性能优化技巧4.1 预编译重用策略观察这个慢查询日志# Time: 2023-08-20T15:23:45.123456Z # Query_time: 1.234s SET name张三; SELECT * FROM users WHERE namename;问题出在每次执行都重新声明变量。优化方案是// 使用#{}确保预编译 Select(SELECT * FROM users WHERE name#{name}) ListUser findByName(Param(name) String name);4.2 分表查询优化对于按月分表的场景不要这样写for (Month month : months) { String sql SELECT * FROM logs_ month WHERE...; // 每次都是新的SQL }应该使用MyBatis的DatabaseIdProviderselect idqueryLogs databaseIdmysql SELECT * FROM logs_${month} WHERE ... /select这样至少能复用相同月份查询的编译结果。5. 面试深度应答指南面试官问为什么表名不能用#{}可以这样展示深度这涉及到数据库协议层设计。MySQL的预编译协议Protocol::COM_STMT_PREPARE规定占位符只能替换值类型不能替换标识符。就像您不能用变量代替函数名一样// 这是合法的 printf(%s, hello); // 但这是非法的 printf(%s, hello);JDBC驱动在底层会把#{}转换为PreparedStatement的参数标记而表名属于SQL语法结构的一部分必须在预处理前确定。这也是为什么ORM框架都保留${}这种看似危险的功能——没有它就无法实现动态DDL操作。6. 复杂场景解决方案6.1 动态列查询优化当需要动态选择返回字段时推荐方案public interface UserMapper { SelectProvider(type UserSqlBuilder.class, method buildGetUser) User getUser(Param(columns) ListString columns); class UserSqlBuilder { public String buildGetUser(MapString, Object params) { ListString columns (ListString) params.get(columns); return SELECT String.join(,, columns) FROM users; } } }比直接拼接SQL更安全因为列名来自代码而非用户输入可以使用Column注解做校验方便统一添加审计字段6.2 多租户架构实践在SAAS系统中我这样处理租户隔离sql idtenantFilter WHERE tenant_id #{tenantId} if testdynamicTable AND ${tableName}.tenant_id #{tenantId} /if /sql配合拦截器自动注入tenantIdpublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) { if (parameter instanceof TenantAware) { ((TenantAware) parameter).setTenantId(getCurrentTenant()); } }7. 源码级原理分析查看MyBatis的ParameterMappingTokenHandler类会发现// 处理#{}的逻辑 public String handleToken(String content) { parameterMappings.add(buildParameterMapping(content)); return ?; } // 处理${}的逻辑 public String handleToken(String content) { Object value ... // 直接求值 return value ! null ? value.toString() : ; }这就是为什么${}容易导致SQL注入——它直接把表达式结果转为字符串拼接进SQL。在PreparedStatementLogger中可以看到// 预编译SQL日志输出 debug( Parameters: parameterValue ( parameterType ));而普通Statement的日志是完整SQLdebug( Preparing: sql);8. 监控与应急方案我们在生产环境会通过ELK收集所有含${}的SQL对非常规表名操作触发告警保留SQL模板与参数分离的日志格式关键日志格式示例[动态SQL监控] templateSELECT * FROM ${table}, params{tableusers_2023}, executoradmin192.168.1.100遇到注入攻击时的应急流程立即下线相关功能从日志定位攻击模式增加参数校验规则进行数据备份检查9. 框架整合最佳实践与Spring结合时推荐配置mybatis: configuration: default-statement-timeout: 30 safe-row-bounds-enabled: true safe-result-handler-enabled: true在Spring Boot中增加安全拦截器Bean public MybatisInterceptor safeSqlInterceptor() { return new SafeSqlInterceptor(); }拦截器核心逻辑public Object intercept(Invocation invocation) { String sql getSql(invocation); if (sql.contains(${) !isSafeDynamicSql(sql)) { throw new DangerousSqlException(发现危险的动态SQL); } return invocation.proceed(); }10. 前沿技术演进新一代ORM框架如JOOQ采用类型安全的DSL// 编译期就能发现表名错误 dsl.select().from(LOGS_2023).where(...)MyBatis Plus的Wrapper机制也在尝试替代${}queryWrapper.select(id, name).orderByDesc(create_time);但动态表名等场景仍需要底层支持这也是为什么${}短期内不会被完全淘汰。关键在于建立完善的安全防护体系就像我们既要使用电力又要安装漏电保护器一样。