In the previous article, I introduced the difference between #{} and ${} parameters and the difference between # and $ in Mybatis. If you need it, you can refer to it.
$ and # simple description:
# is equivalent to adding double quotes to the data, and $ is equivalent to directly displaying the data.
1. Summary
When using sqlMap for SQL query in mybatis, it is often necessary to dynamically pass parameters. Dynamic SQL is one of the powerful features of mybatis and an important reason why it is superior to other ORM frameworks. Before precompiling the SQL statement, mybatis will dynamically parse the SQL and parse it into a BoundSql object, which is also processed here. In the dynamic SQL parsing stage, #{ } and ${ } will have different performances, and #{ } will be parsed into a parameter marker for a JDBC precompiled statement.
A #{ } is parsed into a parameter placeholder? . ${ } is just a pure string replacement, and variable replacement will be performed during the dynamic SQL parsing stage.
2. Bug description
Front-end parameters:
skip:0
Take:10
ruleName:A,B,C
Business layer processing:
package SQL;/*** Escape front-end multi-select parameters as SQL statement content*/public class SQLUtil {private final static String REPLACECHAR_COMMA = ",";private final static String REPLACECHAR_SEMICOLON = ";";public static void main(String[] args) {String s1 = "A,B,C";String s2 = "ABC";System.out.println("Comma-separated: " + formatInStr(s1));System.out.println("Space-separated: " + formatInStr(s2));}private static String formatInStr(String queryStr) {return queryInStr(sliptQueryStr(queryStr));}private static String[] slipQueryStr(String queryStr) {if (null == queryStr || "".equals(queryStr.trim())) return null;queryStr = queryStr.replaceAll(SQLUtil.REPLACECHAR_COMMA, " ").replaceAll(REPLACECHAR_SEMICOLON, " ");return queryStr.split("//s+");}private static String queryInStr(String[] queryStrs) {if (null == queryStrs || 0 == queryStrs.length) return null;StringBuffer buf = new StringBuffer();for (int i = 0; i < queryStrs.length; i++) {if (i != 0) buf.append(",");buf.append("'").append(queryStrs[i]).append("'");}return buf.toString();}} Mapper layer processing:
//Error handling <if test="ruleName != null and ruleName != ''">AND a.rule_name IN (#{ruleName})</if>//Correct handling <if test="ruleName != null and ruleName != ''">AND a.rule_name IN (${ruleName})</if> Log description:
[DEBUG] [2016-08-02 17:42:42.226] [qtp1457334982-157] java.sql.Connection - ==> Preparing: SELECT a.id, a.is_valid, a.rule_lable, a.rule_name, a.type, b.sp_id, b.sp_name, a.rule_content, c.user_name, a.gmt_modified, a.ordering FROM idc_logistics_assign_rules a LEFT JOIN app_user c on c.work_no=a.modifier and c.is_deleted='n', idc_sp_info b WHERE a.is_deleted = 'n' AND b.is_deleted = 'n' AND a.sp_id = b.sp_id AND a.rule_name IN (?) ORDER BY ordering asc limit ?, ? [DEBUG] [2016-08-02 17:42:42.226] [qtp1457334982-157] java.sql.PreparedStatement - ==> Parameters: 'A','B'(String), 0(Integer), 10(Integer)
Results analysis: The mapper layer has precompiled SQL, and there are placeholders for #? , but it will be replaced directly for $.
PS: 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:
The code copy is as follows:
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.