MyBatis 中 #{} 并非万能：动态 SQL 实战

大家好，我是钢板兽。

在MyBatis 中，#{} 和 ${} 都用于参数替换，它们的底层机制和使用场景都不同。你一定知道${}会发生SQL注入的问题，而#{} 不会，所以#{} 更安全。

但是所有参数替换的情况都可以用#{} 吗？如果你回答是的话，就大错特错了。

这篇文章将以一个动态SQL生成的例子，来讲清楚#{} 和 ${}各自的使用时机，相信你一定会有收获。

1.#{} 和 ${}的区别

首先来看下#{} 和 ${}的区别：

• ${} 是字符串拼接：MyBatis 会在生成最终 SQL 前将其替换为对应的字符串，直接拼接到 SQL 语句中。因此它可以出现在 SQL 的任何位置，但是 容易发生 SQL 注入，例如：

  SELECT * FROM users WHERE username='用户名' AND password='密码' OR 1=1
  

  由于 OR 1=1 这个条件永远为真，所以不管 用户名 和 密码 是否匹配，查询结果都会返回所有记录，从而导致登录成功‌.

• #{} 是占位符绑定：MyBatis 会将其替换为 JDBC 的 ? 占位符，并进行参数绑定，能 有效防止 SQL 注入，是最推荐的传参方式，但也存在使用限制。

  JDBC（ Java Database Connectivity）是Java官方提供的一套访问关系型数据库的标准API，MyBatis是基于JDBC封装的ORM（对象关系映射）框架。

2.#{} 的使用限制

#{}的底层机制是：首先它会被 转换为 ? 占位符，然后 MyBatis 使用 JDBC 的 PreparedStatement 对象来绑定参数，类似这样：

PreparedStatement ps = connection.prepareStatement("SELECT * FROM user WHERE id = ?");
ps.setInt(1, 123);  // 1 为 占位符编号

而? 只能用于填充值类型的数据（如数字、字符串、日期等），不能用于动态替换 SQL 的结构性元素（如表名、列名、函数名或 SQL 子句），这些结构性内容必须在 SQL 字符串中提前写定，在SQL编译阶段就要确定，不能作为参数动态传入。

总之，只要是需要在编译期确定的字段，就不能使用 #{} 填充，主要有下面三个内容：

1. 不能作为表名或者字段名

   SELECT * FROM #{table} -- 错误
   

2. 不适合用于 ORDER BY、GROUP BY 中的字段

   ORDER BY #{sortField} -- 错误
   

3. 不适合用于SQL子句片段

   WHERE #{whereClause} -- 错误
   

这3个内容可以通过${}动态拼接，${} 是 MyBatis 在SQL编译前就直接字符串拼接进去的，但是为了防止SQL注入，使用${}之前要先对其进行字段白名单校验。

3.动态SQL生成

3.1 背景

下面来举一个动态SQL生成的例子来说明#{} 和 ${}各自的使用时机。

以动态报表为例，想要动态生成的SQL例子如下：

SELECT
    product_name,
    sale_date,
    SUM(sales_amount) AS sum_sales_amount,
    COUNT(order_count) AS count_order_count
FROM sales_order
WHERE
    region = '华东'
    AND sale_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY
    product_name,
    sale_date

• 从 sales_order 表中查询销售记录；
• 筛选出“华东”地区、2024年第一季度的数据**；**
• 按产品名（product_name）和销售时间（sale_date）进行分组；
• 计算每组的销售总额与订单数量。

3.2 前后端数据结构设计

为了动态生成SQL查询语句，我们需要从前端接收多个查询字段、分组字段、聚合字段和筛选条件，所以可以将前端传参JSON设计为如下：

• dimensions字段：用于 GROUP BY，决定分组方式；

• metrics字段：用于 SELECT 中做聚合，和dimensions字段共同组成查询字段；

• filters筛选条件：用于构造 WHERE 子句；

{
  "dimensions": ["product_name", "sale_date"],
  "metrics": [
    { "name": "sales_amount", "agg": "SUM" },
    { "name": "order_count", "agg": "COUNT" }
  ],
  "filters": [
    { "field": "region", "op": "=", "value": "华东" },
    { "field": "sale_date", "op": "between", "value": ["2024-01-01", "2024-03-31"] }
  ]
}

相应的，Java后端数据结构可以设计为：

@Data
public class ReportRequest {
    private List<String> dimensions;
    private List<Metric> metrics;
    private List<Filter> filters;

    @Data
    public static class Metric {
        private String name; // 字段名，如 sales_amount
        private String agg;  // 聚合方式，如 SUM, AVG, COUNT
    }

    @Data
    public static class Filter {
        private String field;
        private String op;    // =, >, between...
        private Object value; // 可以是字符串，也可以是数组
    }
}

3.3 Mapper接口及XML映射

Mapper接口：

@Mapper
public interface ReportMapper {

    List<Map<String, Object>> queryDynamicReport(@Param("dimensions") List<String> dimensions,
                                                 @Param("metrics") List<ReportRequest.Metric> metrics,
                                                 @Param("filters") List<ReportRequest.Filter> filters);
}

XML映射：

<select id="queryDynamicReport" resultType="map">
    SELECT
        <foreach collection="dimensions" item="dim" separator=",">
            ${dim}
        </foreach>
        <if test="metrics != null and metrics.size() > 0">
            <foreach collection="metrics" item="m" separator=",">
                ${m.agg}( ${m.name} ) AS ${m.agg}_${m.name}
            </foreach>
        </if>
    FROM sales_order
    <where>
        <foreach collection="filters" item="f">
            <if test="f.op == 'between'">
                AND ${f.field} BETWEEN #{f.value[0]} AND #{f.value[1]}
            </if>
            <if test="f.op != 'between'">
                AND ${f.field} ${f.op} #{f.value}
            </if>
        </foreach>
    </where>
    <if test="dimensions != null and dimensions.size() > 0">
        GROUP BY
        <foreach collection="dimensions" item="dim" separator=",">
            ${dim}
        </foreach>
    </if>
</select>

3.3 Controller调用及白名单校验

Controller调用：

@PostMapping("/query")
public List<Map<String, Object>> query(@RequestBody ReportRequest request) {
    validateRequest(request);
    return reportMapper.queryDynamicReport(
        request.getDimensions(),
        request.getMetrics(),
        request.getFilters()
    );
}

在参数注入前，应确保每一个用于 SQL 拼接的字段值都合法、安全，进行白名单校验。

private static final Set<String> VALID_FIELDS = Set.of(
    "product_name", "sale_date", "region", "sales_amount", "order_count"
);

private static final Set<String> VALID_AGG_FUNCS = Set.of(
    "SUM", "AVG", "COUNT", "MAX", "MIN"
);

private static final Set<String> VALID_OPERATORS = Set.of(
    "=", ">", "<", ">=", "<=", "<>", "between"
);

private void validateRequest(ReportRequest request) {
    for (String dim : request.getDimensions()) {
        if (!VALID_FIELDS.contains(dim)) {
            throw new IllegalArgumentException("非法维度字段：" + dim);
        }
    }

    for (ReportRequest.Metric metric : request.getMetrics()) {
        if (!VALID_FIELDS.contains(metric.getName()) ||
            !VALID_AGG_FUNCS.contains(metric.getAgg().toUpperCase())) {
            throw new IllegalArgumentException("非法指标字段或聚合函数：" + metric);
        }
    }

    for (ReportRequest.Filter filter : request.getFilters()) {
        if (!VALID_FIELDS.contains(filter.getField()) ||
            !VALID_OPERATORS.contains(filter.getOp().toLowerCase())) {
            throw new IllegalArgumentException("非法筛选条件：" + filter);
        }
    }
}

总之，在实际项目中，应该使用 #{} 进行数据绑定传参，仅在结构字段（如列名、函数名）等必须动态拼接时谨慎使用 ${}，并对其进行字段白名单校验。

如果这篇文章对你有帮助，欢迎点赞、留言、转发！