I have been using mybatis recently. I have used ibatis before. Overall, it is similar, but I still encountered many problems. I will record it again.
Let me first introduce the difference between #{} and ${} in MyBatis, as follows:
1. # Treat all the incoming data as a string, and add double quotes to the automatically incoming data. For example: order by #user_id#, if the value passed in is 111, then the value when parsing into sql is order by "111". If the value passed in is id, the parsed into sql is order by "id".
2. $Displays the passed data directly and generates 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 into sql is order by id.
3. The # method can greatly prevent SQL injection.
4. The $ method cannot prevent Sql injection.
5. The $ method is generally used to pass in database objects, such as passing in table names.
6. Generally, if you can use #, don’t use $.
When using order by dynamic parameters when sorting MyBatis, you need to pay attention to using $ instead of #
String replacement
By default, using the #{} format syntax causes MyBatis to create a preprocessed statement property and set a safe value with it as the background (such as?). This is safe and quick, and sometimes you just want to insert a string that doesn't change 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 strings.
Important: It is not safe to accept content output from the user and provide it to an unchanged string in the statement. This can lead to potential SQL injection attacks, so you should not allow users to enter these fields, or usually escape and check them yourself.
Description of mybatis itself:
String SubstitutionBy default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (eg ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject a string unmodified into the SQL Statement. For example, for ORDER BY, you might use something like this:ORDER BY ${columnName}Here MyBatis won't modify or escape the string.NOTE It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks. From the above, we can see:
1. Use the #{} format syntax to use Preparement statement in mybatis to safely set values, and execute SQL similar to the following:
PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1,id);
The benefit of this is: safer, faster, and is usually the preferred practice.
2. But 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} At this time, MyBatis will not modify or escape the string.
This method is similar to:
Statement st = conn.createStatement();ResultSet rs = st.executeQuery(sql);
The disadvantages of this method are:
It is unsafe to accept content output from the user and provide unchanged strings in the statement in this way, resulting in potential SQL injection attacks, so either the user is not allowed to enter these fields or escape and verify by themselves.