Differences between # {} and $ {} In Mybatis,

Differences between # {} and $ {} In Mybatis,

I went to a company for a test a few days ago and asked the question "What is the difference between # {} and $ {} In Mybatis ?". To be honest, I was not very clear at the time, because I did not learn mybatis myself, although the most recent project used mybatis, however, it only involves the most basic addition, deletion, modification, and query. The association between multiple tables is almost useless. # {} Is used only in mapper ing files, and $ {} is used in JS and front-end JSP pages. Later, I checked on the Internet and found that the difference between the two was still very large. Now I have summarized the information I found as follows to facilitate discussion and study.

 

Generally, the differences between the two are summarized as follows:

(1) # When the incoming data is treated as a string, a double quotation mark is added to the automatically passed data. For example, order by # user_id #. If the input value is 111, the value parsed to SQL is order by "111". If the input value is id, the parsed SQL statement is order by "id ".

(2) $ directly displays incoming data and generates it in SQL. For example, order by $ user_id $. If the input value is 111, the value parsed to SQL is order by user_id. If the input value is id, the parsed SQL statement is order by id.

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

(4) $ cannot prevent SQL injection.

(5) The $ method is generally used to input database objects, such as the input table name.

(6) do not use $ if you can use.

Ps: You still encounter <! [CDATA []>: the statements in the symbol are not processed as strings, but directly as SQL statements, such as executing a stored procedure.

Example:

Dynamic SQL is one of the main features of mybatis. After the parameters defined in mapper are uploaded to xml, mybatis will dynamically Parse them before the query. Mybatis provides 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 parsed results are

select * from user where name = 'zhangsan';

However, the processing of # {} and $ {} in pre-compilation is different. # {} During preprocessing, a placeholder is used for the parameter part? Instead, it is changed to the following SQL statement:

select * from user where name = ?;

$ {} Is a simple string replacement. In the dynamic parsing stage, the SQL statement is parsed

select * from user where name = 'zhangsan';

Above, # {} parameters are replaced in DBMS, while $ {} occurs in dynamic parsing.

So what method should we use during usage?

The answer is: Priority #{}. $ {} Causes SQL Injection problems.

See the following example:

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

In this example, if the table name is

User; delete user ;--

After dynamic parsing, the SQL statement is as follows:

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

-- The subsequent statement is commented out, and the statement of the original query user becomes the statement for querying all user information + deleting the user table, which will cause major damage to the database, the server may be down.

However, when the table name is passed in with parameters, only $ {} can be used {}. This also reminds us to be careful with SQL Injection in this usage.