The difference between "#" and "$" in MyBatis _mybatis

Dynamic SQL is one of the main features of MyBatis, and after the parameters defined in mapper are passed into the XML, MyBatis is dynamically parsed before the query. MyBatis provides us with two syntax to support dynamic SQL: #{} and ${}.

In the following statement, if the value of username is Zhangsan, there is no difference between the two ways:

　　

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

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

After parsing, the results are

SELECT * from user where name = ' Zhangsan ';

However, #{} and ${} are not handled in the precompilation. #{} When preprocessing, the parameter part is used as a placeholder? Instead, it becomes the following SQL statement:

SELECT * from user where name =?;

The ${} is simply a string replacement, which in the dynamic parsing phase is parsed into

SELECT * from user where name = ' Zhangsan ';

Above, the parameter substitution of #{} occurs in the DBMS, and ${} occurs in the dynamic parsing process.

So, which way should we use in the process?

The answer is, prioritize the use of #{}. Because ${} can cause problems with SQL injection. Look at the following example:

　　

SELECT * from ${tablename} where name = #{name}

In this example, if the table is named

User Delete user; --

After dynamic parsing, SQL is as follows:

select * from user; Delete user; --WHERE name =?;

-After the statement is commented out, and the original query user's statement into the query all user information + DELETE user table statements, will cause significant damage to the database, which may cause server downtime.

But the table name is passed in with the parameter, can only use ${}, the concrete reason may make a guess by oneself, to verify. This also reminds us of the problem of SQL injection being careful in this usage.