The difference between # and $in mybatis

Time:2021-6-13

The difference between # and $in mybatis:

1. # treat the incoming data as a string, and add a double quotation mark to the automatic incoming data.
For example: where user name = # {user name}, if the value passed in is 111, then the value parsed into SQL is where user name = # {user name}. If the value passed in is ID, then the value parsed into SQL is where user name = # {user name}
2. $directly display and generate the incoming data in SQL.
For example: where user name = ${user name}, if the value passed in is 111, then the value parsed into SQL is where user name = 111;

3. For the above SQL, if the value passed in is; drop table user;,

Then the first one is parsed with # {} SQL as: select id, username, password, role from user where username =; drop table user;”

Then the second one is parsed with ${} SQL as: select id, username, password, role from user where username =; drop table user;

At this time, SQL has been injected.

3. The # method can prevent SQL injection to a great extent, while the $method can’t prevent SQL injection.
4. The $, order by ${columnname} method is generally used to pass in database objects, such as table names and column names. In addition, $, order by ${columnname} should be used when the order by dynamic parameter is used for sorting
5. Generally, don’t use $, if you can use “${XXX}”. If you have to use a parameter like “${XXX}”, you should do a good job of filtering manually to prevent SQL injection attacks.
6. In mybatis, parameters with “${XXX}” format will directly participate in SQL compilation, so injection attack cannot be avoided. But when it comes to dynamic table names and column names, we can only use parameter formats like “${XXX}”. Therefore, such parameters need to be handled manually in the code to prevent injection.
[Conclusion] the format of “# {XXX}” should be used as much as possible when writing the mapping statement of mybatis. If you have to use parameters like “${XXX}”, you should do a good job of filtering manually to prevent SQL injection attacks.