Differences between #{} and ${} in MyBatis (How to prevent SQL injection)

By default, using the #{} syntax, MyBatis produces preparedstatement statements, and safe settings PreparedStatement parameters, which mybatis perform the necessary security checks and escapes.

#相当于对数据 with double quotes, $ equivalent to displaying data directly

Example 1:
Execute Sql:select * from emp WHERE name = #{employeename}
Parameter: Employeename=>smith
Parsed after executing sql:select * from emp where name =?

Example 2:

Execute Sql:select * from emp WHERE name = ${employeename}
Parameter: EmployeeName The incoming value is: Smith
Sql:select executed after parsing * from EMP where name =smith

In summary, the ${} approach raises the issue of SQL injection, and it also affects the precompilation of SQL statements, so do not use ${if you can use #{} from a security and performance standpoint.

Q: But under what circumstances is ${} used?

A: Sometimes you may need to insert a string that does not make any changes to the SQL statement. The ${} syntax should be used at this time.

For example, a field name in dynamic SQL, such as: ORDER by ${columnname}

Important: It is not safe to accept the output from the user and provide the invariant 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 themselves.

The MyBatis framework, as a semi-automated persistence layer framework, has its SQL statements written manually by us, which of course needs to prevent SQL injection . In fact, MyBatis's SQL is a function with " input + output ", similar to the structure of the function, as follows:

<select id= "Getblogbyid" resulttype= "Blog" parametertype= "int" >         Select Id,title,author, Content from         blog         WHERE id=#{id}</select>

Here,ParameterType represents the input parameter type, andResulttype represents the output parameter type. In response to the above, if we want to prevent SQL injection, we naturally have to work on the input parameters . In the above code, the yellow highlight is the input parameter in the SQL splicing part, after passing in the parameters, print out the executed SQL statement, you will see that SQL is:

SELECT id,title,author,content from blog WHERE id =?

regardless of the input parameters, the printed SQL It's all the same. this is because MyBatis enabled the pre-compilation feature , before SQL execution, the above SQL will be sent to the database to compile, when executed, directly using the compiled SQL , replace the placeholder "?" You can do it. Because SQL injection can only work on the compilation process , this is a good way to avoid the problem of SQL injection .

How does the "underlying implementation principle" MyBatis do SQL precompilation ? In fact, at the bottom of the framework, the PreparedStatement class in JDBC is working, PreparedStatement is a subclass of statement that we are familiar with, and its objects contain compiled SQL statement . This "ready" approach not only improves security , but also improves efficiency when executing the same SQL multiple times. The reason is that SQL has been compiled and is not compiled again when executed again.

　　

In MyBatis, the parameters in the format of${XXX} are directly involved in SQL compilation , thus preventing injection attacks . But when it comes to dynamic table names and column names, you can only use parameter formats such as ${XXX}. So, such parameters need to be handled manually in the code to prevent injection.

Conclusion

When writing MyBatis mapping statements, try to use the format of #{XXX}. If you have to use parameters such as ${XXX}, you should manually do the filtering work to prevent SQL injection attacks.

#{}: Equivalent to PreparedStatement in JDBC

${}: Is the value of the output variable

Simply put,#{} is pre-compiled , is secure ,${} is not precompiled, just takes the value of the variable, is non-secure, there is SQL injection.

If we use ${} after the order BY statement, there is a risk of SQL injection when we do not do any processing. You say how to prevent, then I can only sad to tell you, you have to manually deal with filtering the input content. Determine if the length of the input parameter is normal (the injected statement is generally very long)

Reference:

http://blog.csdn.net/szwangdf/article/details/26714603

Http://www.myexception.cn/sql/1938757.html

Differences between #{} and ${} in MyBatis (How to prevent SQL injection)