Why is the interview the difference between #{} and ${}

background

Due to one-time requirement writing, when using dynamic statement splicing, #{} and ${} are used to get two different result sets
Through the review of the questions, we can understand the reasons for asking such questions during the interview

Reappearance

Write an interface whose main function is to query conditions according to the incoming fields And this field can pass multiple values
After thinking: I decided to use list to receive this field, and dynamically splice the list through mybats to realize multi condition query

1. Dao layer interface
   Focus on the last field

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

1. Corresponding to SQL in Mapper file

 <select id="queryDetailedInfo" resultType="java.util.Map">
      select
 		*    <!--The detailed field introduction is omitted here-->
      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>
        <!--Mainly pay attention here, Before modification-->
        <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>

1. Through the SQL statement execution output, you can see the executed statement and the filled parameters as follows
   You can see that the number of results is 0, that is, no data is found
   However, through rough observation, it is found that there are indeed some and many that meet these conditions Why on earth? With questions, we move on to the next step

-==>  Preparing: select id, IFNULL(window_no,'-') AS 'Window number', IFNULL(staff_no,'-') AS 'Employee job number', IFNULL(staff_name,'-') AS 'Employee name', businame AS 'Business name', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS 'Number taking time', IFNULL(acceptTime,'-') AS 'Acceptance time', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS 'Processing time' 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), "Open an account"(String), "Invoice bill"(String)
-<==      Total: 0

1. We substitute the statements and parameters into mysql to view the execution results
   It can be seen that MySQL is executed successfully. Why is the execution empty under the explanation of mybats?
   Smart you, like me, will immediately react that there may be #{} a problem Just replace it with ${}
   This is why I asked this question during the interview By asking us to recite questions to form relevant memories,
   Be able to quickly think about solutions to problems after encountering relevant problems

The complete sql statement is shown in the following figure

1. Problem codes and modifications
   As you can see, simply replace # with $, and the problem is solved So what is the reason for this difference?

    <!--Mainly pay attention here, Before modification-->
   <if test="list != null and list.size() != 0">
       and (
       <foreach collection="list" item="item" separator="or">
           businame = #{item}
       </foreach>
       )
   </if>
	
	 <!--Mainly pay attention here, After modification-->
   <if test="list != null and list.size() != 0">
       and (
       <foreach collection="list" item="item" separator="or">
           businame = ${item}
       </foreach>
       )
   </if>

analysis

There are two methods #{} or ${} for parameter transfer in Mybatis. The following describes the differences between the two:

1. #{} resolves to a parameter marker of a JDBC prepared statement; A #{} is resolved to a parameter placeholder?
   The sql statement formed when #{} is used has been quoted. For example, select * from table where id=#{id} when calling this statement, we can see in the background that the printed sql is: select * from table where id='2 'and the value added is 2 That is, when composing sql statements, the parameters are defaulted to strings.
2. ${} is only replaced by a pure broken string. Variable replacement will be carried out in the dynamic SQL parsing phase
3. The variable replacement phase of ${} is in the dynamic SQL parsing phase, while the variable replacement of #{} is in the DBMS.
4. #The {} method can prevent SQL injection to a great extent, while the ${} method cannot prevent SQL injection.

Compare the console output of the next two methods

1. When using #{}, the fields in the list before being replaced by placeholders already have ""

-==>  Preparing: select id, IFNULL(window_no,'-') AS 'Window number', IFNULL(staff_no,'-') AS 'Employee job number', IFNULL(staff_name,'-') AS 'Employee name', businame AS 'Business name', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS 'Number taking time', IFNULL(acceptTime,'-') AS 'Acceptance time', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS 'Processing time' 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), "Open an account"(String), "Invoice bill"(String)
-<==      Total: 0

1. When using ${}, the field with '' in the list has been replaced before precompiling

-==>  Preparing: select id, IFNULL(window_no,'-') AS 'Window number', IFNULL(staff_no,'-') AS 'Employee job number', IFNULL(staff_name,'-') AS 'Employee name', businame AS 'Business name', DATE_FORMAT(takeTime,'%Y-%m-%d %H:%i:%s') AS 'Number taking time', IFNULL(acceptTime,'-') AS 'Acceptance time', (UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(acceptTime)) AS 'Processing time' from t_number_takers where 1=1 and takeTime between ? and ? and staff_name=? and ( businame = "Open an account" or businame = "Invoice bill" ) order by id asc 
-==> Parameters: 2021-01-01 00:00:00(String), 2021-12-31 23:00:00(String), wyf(String)
-<==      Total: 9

After replacing with ${}, you can see the executed statement and filled parameters through the SQL statement execution output, as shown above
It can be clearly seen that during precompiling$ {} has been replaced into the SQL statement instead of filling in through placeholders!!!

The following is a comparison of the differences between centralized splicing

-- Conditions for manual splicing
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 = 'Open an account' or businame = 'Invoice bill' ) 
order by id asc 

-- use ${}
-- mysql in '' and "" No difference
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 = "Open an account" or businame = "Invoice bill" ) 
order by id asc 

-- use#{}
-- Here we need to pay attention, Because of the list Received, But incoming list The data comes with double quotes!!!Therefore, the splicing statement becomes the following style
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 = '"Open an account"' or businame = '"Invoice bill"' ) 
order by id asc 

Through the above comparison, combined with the postman input parameter
It turned out that double quotation marks ("") were added to each string during the request and when the list was passed in, as shown in the figure below
Remove the double quotation marks from the list parameter in the request, and then modify the reference to the list in mybaties to #{}, and then you will be surprised to find that there is no problem

The complete request statement is shown in the following figure

reflect

Through the above analysis, we can see the main differences between #{} and ${}:

#{} will be resolved to a placeholder and a quote '' will be added to the resolved field,
${} is a direct replacement without modification. Because ${} is a direct replacement, it is easy to be injected by SQL, which is the opposite of #{}
Therefore, it is recommended to use #{} as much as possible