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

时间静止不是简史

发布于 2022-04-02 08:14:50

7480

发布于 2022-04-02 08:14:50

文章被收录于专栏：Java探索之路Java探索之路

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

背景

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

复现

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

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

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

对应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>

通过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

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

sql完整语句见下图连接

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

    <!--主要注意这里, 修改前-->
   <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中进行参数传递，可以使用两种方式#{}或者${}, 下面介绍下二者区别:

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

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

使用#{} 时, 被占位符替换前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

使用 ${} 时, 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的引用为 #{} , 然后再去请求就会惊讶的发现也没有问题了.