What is the difference between #{} and ${} in Mybatis?

I went to a company for a written test a few days ago, and one of the questions was "What is the difference between #{} and ${} in Mybatis?". To be honest, I was really not very clear at the time, because I had not learned much about mybatis myself. Although mybatis was used in the recent projects, it only involved the most basic addition, deletion, modification, and query, between multiple tables. The correlation is almost never used. While #{} is only used in mapper mapping files, ${} is used in JS and front-end JSP pages. Later, I checked online and found that there is still a big difference between the two. Now I summarize the information I found as follows to facilitate everyone's discussion and study.

Generally speaking, the difference between the two can be summarized as the following 6 points:

(1) # The incoming data is treated as a string, and a double quotation mark is added to the automatically incoming data. For example: order by #user_id#, if the value passed in is 111, then the value when parsed into sql is order by "111", if the value passed in is id, the parsed sql is order by " id".

(2)$ Directly display and generate the incoming data in sql. For example: order by $user_id$, if the value passed in is 111, then the value when parsed into sql is order by user_id. If the value passed in is id, the parsed sql is order by id.

(3)#method can prevent sql injection to a large extent.

(4)$ method cannot prevent sql injection.

(5) The $ method is generally used to pass in database objects, such as table names.

(6) Generally, don’t use $ if you can use #.

ps: When using mybatis, I also encountered the usage of . The statements within this symbol will not be treated as strings, but directly as sql. Statement, such as executing a stored procedure.

Explanation with examples:

Dynamic sql is one of the main features of mybatis. After the parameters defined in the mapper are passed to xml, mybatis will dynamically parse them before querying. mybatis provides us with two syntaxes that support dynamic sql: #{} and ${}.

In the following statement, if the value of name is zhangsan, there is no difference between the two methods:

select * from user where name = #{name};

select * from user where name = ${name};

The results after parsing are all

select * from user where name = 'zhangsan';

But #{} and ${} are processed differently in precompilation. #{} During preprocessing, the parameter part will be replaced with a placeholder? to replace and become the following sql statement:

select * from user where name = ?;

And ${} is just a simple string replacement . In the dynamic parsing stage, the sql statement will be parsed into

select * from user where name = 'zhangsan';

above, the parameter replacement of #{} occurs in the DBMS, while ${} occurs during dynamic parsing.

So, which method should we use during use?

The answer is: Use #{} first. Because ${} will cause sql injection problems.

Look at the following example:

select * from ${tableName} where name = #{name}

In this example, if the table name is

user; delete user; --

The sql after dynamic parsing is as follows:

select * from user; delete user; -- where name = ?;

--The following statements are commented out, and the original query for users becomes query for all user information + delete users The statements in the table will cause significant damage to the database and may very well cause server downtime.

　But when the table name is passed in as a parameter, you can only use ${}. This also reminds us to be careful about sql injection in this usage.

The above is the detailed content of What is the difference between #{} and ${} in Mybatis?. For more information, please follow other related articles on the PHP Chinese website!