Summary of the difference between #{} and ${} in passing parameters in Mybatis and the difference between # and $

I have been using mybatis recently, and I have used ibatis before. Generally speaking, it is similar, but I still encountered a lot of problems. I will record them again.

For example, use #{} and ${} to pass parameters. The difference,

use # to pass in parameters, the sql statement parsing will add "", for example, select * from table where name = #{name}, the name passed in is Xiao Li, then it will be printed out at the end What is

select * from table where name = 'Xiao Li' is that it will be parsed as a string. The advantage compared to $ is more obvious, right? #{} passing parameters can prevent sql Injection, if the parameters you pass in are single quotes', then if you use ${}, this method will report an error.

Another scenario is if you want to do dynamic sorting, such as order by column, be sure to use ${} at this time, because if you use #{}, what will be printed will be

select * from table order by 'name', which is useless.

Currently, if you can use #, don’t use $.

The difference between # and $ in mybatis

1. #Treat the incoming data as one character string, a double quotation mark will be added to the automatically passed in 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. $ Display the incoming data directly and generate it 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 great extent.

4.$ method cannot prevent Sql injection.

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

6. If you can generally use #, don’t use $.

MyBatis needs to pay attention when using order by dynamic parameters when sorting. Use $ instead of

#string replacement

By default, using the #{} format syntax will cause MyBatis to create preprocessing statement attribute and set a safe value (such as ?) against it. This is safe, fast and preferred, sometimes you just want to insert an unchanged string directly into the SQL statement. For example, like ORDER BY, you can use it like this:

ORDER BY ${columnName}

Here MyBatis will not modify or escape the string.

Important: It is not safe to accept output from the user and provide it with an immutable string in the statement. This leads to potential SQL injection attacks, so you should not allow users to enter these fields, or generally escape and check them yourself.

A brief summary of the difference between $ and # in Mybatis

Not long ago, someone came to our company for an interview, and our manager asked this question. I also had only a partial understanding of it, so I went to Baidu to do some research. .

In fact, the difference is very simple. You will understand it with an example. Write a SQL sentence - for example: select * from user_role where user_code = "100";

For this sentence, you need to write select * from ${tableName} where user_code = #{userCode}

So, the $ symbol is directly spelled into sql, and the # symbol will be spliced ​​with sql in the form of a string.

For more related articles on the difference between #{} and ${} passing parameters in Mybatis and the summary of the difference between # and $, please pay attention to the PHP Chinese website!