面试为什么会为 #{}和${}的区别?

面试为什么会为 #{}和${}的区别

背景

由于一次需求编写, 在使用动态语句拼接时, 使用#{} 和 ${} 得到两种不同的结果集.
通过对问题的回顾, 来体会到面试的时候问这种问题的原因

复现

编写一个接口, 其主要的功能是根据传入的字段进行条件查询. 而且这个字段可以传多个值
经过思考: 决定使用list去接收这个字段, 并且将该list通过Mybaties动态拼接来实现多条件查询

1. Dao 层接口
   主要注意最后一个字段

List<Map<String, Object>> queryDetailedInfo(@Param("startTime") String startTime, @Param("endTime") String endTime,
                                            @Param("staffName") String staffName, @Param("list") List<String> businessList);

2. 对应Mapper文件中的SQL

 <select id="queryDetailedInfo" resultType="java.util.Map">
      select
 		*    <!--这里略去详细字段介绍-->
      from t_number_takers
      where 1=1
        <if test="param1 != null and param1 != ''">
            <if test="param2 != null and param2 != ''">
                and takeTime between  #{param1,jdbcType=VARCHAR} and  #{param2,jdbcType=VARCHAR}
            </if>
        </if>
        <if test="param3 != null and param3 != ''">
            and staff_name=#{param3,jdbcType=VARCHAR}
        </if>
        <!--主要注意这里, 修改前-->
        <if test="list != null and list.size() != 0">
            and (
            <foreach collection="list" item="item" separator="or">
                businame = #{item}
            </foreach>
            )
        </if>
      order by id asc
    </select>

3. 通过SQL语句执行输出可以看到执行的语句和填入的参数如下
   可以看到结果条数为0, 也就是没有查到数据.
   但是通过粗略的观察发现符合这几个条件的确实是有的, 而且还很多. 到底是为什么呢? 带着疑问我们进入下一个环节

-==>  Preparing: select id, IFNULL(window_no,'-') AS '窗口号', IFNULL(staff_no,'-') AS '员工工号', IFNULL(staff_name,'-') AS '员工姓名', businame AS '业务名称', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS '取号时间', IFNULL(acceptTime,'-') AS '受理时间', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS '办理时长' from t_number_takers where 1=1 and takeTime between ? and ? and staff_name=? and ( businame = ? or businame = ? ) order by id asc 
-==> Parameters: 2021-01-31 00:00:00(String), 2021-12-31 00:00:00(String), wyf(String), "开户"(String), "发票账单"(String)
-<==      Total: 0

4. 我们将语句和参数代入mysql, 查看执行结果
   可以看到在MySQL上面是执行成功的, 那为什么在Mybaties的解释下却执行为空呢?
   聪明的你也会和我一样, 立马反应到可能是 #{} 有问题. 替换成 ${} 就可以了
   这里就是面试的时候为什么会问这题的原因. 通过让我们背题来形成强相关记忆,
   在遇到相关问题后能迅速反映出问题的解决方式

sql完整语句见下图连接

5. 问题代码以及修改
   可以看到, 仅仅将 # 替换成 $ , 问题就解决了. 那么到底是什么原因导致这个差异的呢?

    <!--主要注意这里, 修改前-->
   <if test="list != null and list.size() != 0">
       and (
       <foreach collection="list" item="item" separator="or">
           businame = #{item}
       </foreach>
       )
   </if>
	
	 <!--主要注意这里, 修改后-->
   <if test="list != null and list.size() != 0">
       and (
       <foreach collection="list" item="item" separator="or">
           businame = ${item}
       </foreach>
       )
   </if>

分析

Mybatis中进行参数传递，可以使用两种方式#{}或者${}, 下面介绍下二者区别:

1. #{ } 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符；一个 #{ } 被解析为一个参数占位符 ?
   并且在使用#{}时形成的sql语句，已经带有引号，例，select * from table where id=#{id} 在调用这个语句时我们可以通过后台看到打印出的sql为：select * from table where id='2' 加入传的值为2.也就是说在组成sql语句的时候把参数默认为字符串。
2. ${ } 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换
3. ${ } 的变量的替换阶段是在动态 SQL 解析阶段，而 #{ }的变量的替换是在 DBMS 中。
4. #{} 方式能够很大程度防止sql注入, 而 ${} 方式无法防止Sql注入。

对比下两种方式控制台的输出

1. 使用#{} 时, 被占位符替换前list中的字段已经有 ""

-==>  Preparing: select id, IFNULL(window_no,'-') AS '窗口号', IFNULL(staff_no,'-') AS '员工工号', IFNULL(staff_name,'-') AS '员工姓名', businame AS '业务名称', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS '取号时间', IFNULL(acceptTime,'-') AS '受理时间', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS '办理时长' from t_number_takers where 1=1 and takeTime between ? and ? and staff_name=? and ( businame = ? or businame = ? ) order by id asc 
-==> Parameters: 2021-01-31 00:00:00(String), 2021-12-31 00:00:00(String), wyf(String), "开户"(String), "发票账单"(String)
-<==      Total: 0

2. 使用 ${} 时, list中带有 “” 的字段在预编译前已经被替换

-==>  Preparing: select id, IFNULL(window_no,'-') AS '窗口号', IFNULL(staff_no,'-') AS '员工工号', IFNULL(staff_name,'-') AS '员工姓名', businame AS '业务名称', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS '取号时间', IFNULL(acceptTime,'-') AS '受理时间', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS '办理时长' from t_number_takers where 1=1 and takeTime between ? and ? and staff_name=? and ( businame = "开户" or businame = "发票账单" ) order by id asc 
-==> Parameters: 2021-01-01 00:00:00(String), 2021-12-31 23:00:00(String), wyf(String)
-<==      Total: 9

替换成${}后, 通过SQL语句执行输出可以看到执行的语句和填入的参数如上
可以清楚的看到, 在预编译时. ${} 就已经被替换到SQL语句中, 而不是通过占位符进行填入!!!

下面对比下集中拼接的区别

-- 自己手动拼接的条件
where 1=1 
and takeTime between  '2021-01-01 00:00:00' and  '2021-12-31 00:00:00'
and staff_name= 'wyf'
and ( businame = '开户' or businame = '发票账单' ) 
order by id asc 

-- 使用${}
-- mysql中 '' 和 "" 无区别
where 1=1 
and takeTime between  '2021-01-01 00:00:00' and  '2021-12-31 00:00:00'
and staff_name= 'wyf'
and ( businame = "开户" or businame = "发票账单" ) 
order by id asc 

-- 使用#{}
-- 这里需要注意, 因为使用的list 接收的, 但传入的list数据自带了双引号!!!因此拼接语句就变成下面的样式
where 1=1 
and takeTime between  '2021-01-01 00:00:00' and  '2021-12-31 00:00:00'
and staff_name= 'wyf'
and ( businame = '"开户"' or businame = '"发票账单"' ) 
order by id asc 

通过上面对比, 结合postman入参可知
原来是在请求时, 传入list的时候的时候, 在每个字符串上都加上了双引号( “”), 如下图, 才导致了这次问题.
将请求中list参数去掉双引号后, 再去修改mybaties中对list的引用为 #{} , 然后再去请求就会惊讶的发现也没有问题了.

完整请求完整语句见下图连接

反思

通过上面的分析我们可以看到 #{}和${}两者主要区别:

#{} 会被解析成占位符并且会为解析的字段加上一个引号 '',
而${} 则是毫无修饰直接替换. 因为${} 是直接替换, 所以容易被SQL注入, 与 #{} 则相反
因此建议尽量使用 #{}

---