MyBatis How to prevent SQL injection

SQL injection is a code injection technique used to attack data-driven applications. Malicious SQL statements are inserted into the executed entity fields (for example, in order to dump the contents of the database to the attacker). [From] SQL injection-Wikipedia

SQL injection, everyone is familiar with, is a common attack method. The attacker enters some strange SQL fragments (such as "or ‘1’=’1’") in the form information or URL on the interface, which may invade applications with insufficient parameter verification. Therefore, we need to do some work in our application to prevent such attacks. In some applications with high security requirements (such as banking software), the method of replacing all SQL statements with stored procedures is often used to prevent SQL injection. This is of course a very safe way, but we may not need this rigid way in our usual development.

As a semi-automated persistence layer framework, MyBatis framework requires us to manually write its SQL statements. Of course, we need to prevent SQL injection at this time. In fact, MyBatis 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, and resultType represents the output parameter type. In response to the above, if we want to prevent SQL injection, of course we have to work hard on input parameters. The yellow highlight in the above code is the part where the input parameters are spliced in SQL. After passing in the parameters, print out the executed SQL statement, you will see the SQL like this:

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

No matter what parameters are entered, the printed SQL is like this. This is because MyBatis enables the pre-compilation function. Before SQL is executed, the above SQL will be sent to the database for compilation; when executing, you can directly use the compiled SQL and replace the placeholder "?". Because SQL injection can only affect the compilation process, this approach can avoid the problem of SQL injection.

[Underlying Implementation Principle] How does MyBatis achieve SQL precompilation? In fact, at the bottom of the framework, the PreparedStatement class in JDBC is at work. PreparedStatement is a subclass of Statement that we are familiar with, and its objects contain compiled SQL statements. This "ready" approach not only improves security, but also improves efficiency when executing the same SQL multiple times. The reason is that the SQL has been compiled, and there is no need to recompile it when executed again.

Having said that, can we use MyBatis to prevent SQL injection? Of course not, please see the following code:

<select id="getBlogById" resultType="Blog" parameterType=”int”>

         SELECT id,title,author,content

         FROM blog

WHERE id=${id}

</select>

Observe carefully, the format of inline parameters has changed from "#{xxx}" to "${xxx}". If we assign the value "3" to the parameter "id", the SQL will be printed like this:

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

(The comparison example above was added by myself to provide a sharp contrast with the previous example.)

<select id="orderBlog" resultType="Blog" parameterType=”map”>

         SELECT id,title,author,content

         FROM blog

ORDER BY ${orderParam}

</select>

Observe carefully, the format of inline parameters has changed from "#{xxx}" to "${xxx}". If we assign "id" to the parameter "orderParam", the SQL will be printed like this:

SELECT id,title,author,content FROM blog ORDER BY id

Obviously, this cannot prevent SQL injection. In MyBatis, parameters in the format "${xxx}" will directly participate in SQL compilation, so injection attacks cannot be avoided. But when it comes to dynamic table names and column names, only parameter formats like "${xxx}" can be used. Therefore, such parameters need to be processed 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 must manually filter to prevent SQL injection attacks.

 

[Excerpt] The difference between #{} and ${} of mybatis and the problem of order by injection

#{}: Equivalent to PreparedStatement in JDBC

${}: is the value of the output variable

Simply put, #{} is pre-compiled and is safe; ${} is not pre-compiled, it just takes the value of the variable, it is not safe, and there is SQL injection.

If we use ${} after the order by statement, there is a danger of SQL injection without any processing. If you say how to prevent it, then I can only tell you sadly that you have to manually process and filter the input. For example, to determine whether the length of the input parameter is normal (the injection statement is generally very long), more precise filtering can be used to query whether the input parameter is in the expected parameter set.

How does MyBatis prevent SQL injection