MyBatis How to prevent SQL injection

Transferred from: http://www.myexception.cn/sql/1938757.html

SQL injection is a code injection technique used to attack data-driven applications where a malicious SQL statement is inserted into the Entity field being executed (for example, in order to dump the database contents to an attacker ).

SQL injection , everyone is not unfamiliar, is a common way of attack. An attacker could enter some strange SQL fragment (such as "or ' 1 ' = ' 1 ') on the form information or URL of the interface, potentially invading an application with insufficient parameters . Therefore, we need to do some work in our application to guard against such attack mode. In some security-demanding applications, such as banking software, it is often used to prevent SQL injection by replacing all SQL statements with stored procedures . This is certainly a safe way , but we may not need this kind of rigid way in our usual development.

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 compiled SQL, replace placeholder "?" You can do it. Because SQL injection can only work on the compilation process , this is a good way to avoid SQL injection problems .

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 other words, will we be able to prevent SQL Injection If we use MyBatis? Of Course not , take a look at the following code:

<select id= "getblogbyid" resulttype= "Blog" parametertype= "int" >

SELECT id,title,author,content

From blog

WHERE id=${id}

</select>

Careful observation, the format of the inline parameter changed from "#{xxx}" to "${xxx}". If we assign a value of "3" to the parameter "ID", the SQL is printed like this:

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

(The comparison example above is the one I added myself to make a sharp contrast to the previous example.) ）

<select id= "orderblog" resulttype= "Blog" parametertype= "map" >

SELECT id,title,author,content

From blog

ORDER by ${orderparam}

</select>

Careful observation, the format of the inline parameter changed from "#{xxx}" to "${xxx}". If we assign the parameter "orderparam" to "ID", the SQL is printed like this:

SELECT id,title,author,content from blog ORDER by ID

Obviously, this is not a way to prevent SQL injection . In MyBatis, a parameter of the format "${XXX}" is 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 "#{xxx}". If you have to use parameters such as "${XXX}", you should do the filtering work manually to prevent SQL injection attacks.

[Excerpt from] the difference between #{} and ${} in MyBatis and the order by injection problem

#{}: 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. If you determine whether the length of the input parameters is normal (the injection statement is generally very long), more accurate filtering can be queried whether the input parameters in the expected set of parameters.

MyBatis How to prevent SQL injection