首页 计算机学科 blog sql

# MyBatis-plus 编写 SQL 中 #与 $ 的使用区别

# 今天看到了一个 sql 如下：

dao

/**
     * 根据 price 和多个 name 来查询信息
     * @param 
     * @return
     * @author 窦凯欣
     * @date 2024 年 07 月 09 日 0009 17:38:31
     */
    @Select({
            "SELECT",
            "id, name",
            "FROM tab_class",
            "WHERE",
            "price = #{price} and",
            "name in",
            "(${res})"
            })
    List<ClassEntity> getClassAll(String res, Integer price);

# 三层代码如下：

controller

@GetMapping("/classAll")
    public List<ClassEntity> getClassAll(@RequestBody List<String> className, Integer price)
    {
        String res = className.stream()
                .map(r ->
                {
                    StringBuilder sb = new StringBuilder();
                    sb.append("'" + r + "'");
                    return sb;
                }).collect(Collectors.joining(","));
        return classService.getClassAll(res, price);
    }

service

public List<ClassEntity> getClassAll(String res, Integer price)
    {
        return classDao.getClassAll(res, price);
    }

请求传参：

json

[
    "数学","语文"
]

param

price=10

查询结果：

[
    {
        "id": 1,
        "name": "数学"
    },
    {
        "id": 2,
        "name": "语文"
    }
]

# 问题：

为什么 price 条件接收参数使用的是 # 而 name 却是 $ 呢？我可以换成 # 吗？

PS：不合适！

解释如下：

原因分析：

  1. # 参数替换特点

    # 会将参数安全替换并转义为一个字符串，因此适用于单个值的传递

    但对于像 in 子句中的列表，需要直接将参数拼接到 SQL 语句中，而不是作为一个单独的字符串

  2. $ 参数拼接特点

    $ 直接拼接参数到 SQL 语句中，适用于动态生成的 SQL 片段，例如 in 子句中的列表

    这样可以确保列表中的每个值都是一个独立的元素，而不是被当作单个字符串处理

例举说明：

假设 res 的值是 '数学' ， '语文'

# 使用 #

@Select({
            "SELECT",
            "id, name",
            "FROM tab_class",
            "WHERE",
            "price = #{price} and",
            "name in",
            "(#{res})"
    })

如果 res 的值是 '数学' ， '语文' ，MyBatis 会将其转义为一个字符串，导致生成的 SQL 类似于

administrative_division_code in ('\'001\', \'002\', \'003\'')

这显然是不正确的，因为它将整个列表当作一个单独的字符串

# 使用 $

@Select({
            "SELECT",
            "id, name",
            "FROM tab_class",
            "WHERE",
            "price = #{price} and",
            "name in",
            "(${res})"
    })

如果 res 的值是 '数学' ， '语文' ，MyBatis 会直接将其拼接到 SQL 语句中，生成的 SQL 类似于

administrative_division_code in ('001', '002', '003')

这是正确的，因为它将列表中的每个值都作为单独的元素处理

# 总结

#{} 用于安全替换单个值，防止 SQL 注入，但不适合替换包含多个值的列表

${} 用于直接拼接 SQL 片段，适合动态生成的 SQL 语句片段，例如 in 子句中的列表

因此，在 in 子句中传递动态列表时，需要使用 ${} 来确保生成的 SQL 是正确的。使用 #{} 会导致生成的 SQL 不符合预期，甚至可能引发错误

# 替代方案

如果您担心 SQL 注入，可以考虑使用以下方式：

  1. 传递 List 参数

在 MyBatis 映射文件中使用 <foreach> 标签

@Select({
            "<script>",
            "SELECT",
            "id, name",
            "FROM tab_class",
            "WHERE",
            "price = #{price} and",
            "name in",
            "<foreach collection='res' item='item' open='(' close=')' separator=','>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    List<ClassEntity> getClassAll(@Param("res") List<String> res,@Param("price") Integer price);

在 java 代码中传递 List<String> 参数

@GetMapping("/classAll")
    public List<ClassEntity> getClassAll(@RequestBody List<String> className, Integer price)
    {
        return classService.getClassAll(className, price);
    }

打印结果：

[
    {
        "id": 1,
        "name": "数学"
    },
    {
        "id": 2,
        "name": "语文"
    }
]