Java Persistence Layer Framework MyBatis How to prevent SQL injection

SQL injection is not unfamiliar, is a common attack, the attacker in the interface of the form information or URL input some strange SQL fragments, such as "or ' 1 ' = ' 1 '", such as a statement, it is possible to invade the parameters of the application of insufficient checksum. So in our application, we need to do some work to guard against such attack mode. In some high-security applications, such as banking software, it is a safe way to prevent SQL injection by often using a way to replace all SQL statements with stored procedures, but we may not need this kind of rigid approach in our usual development.

The MyBatis framework is a semi-automated persistence layer framework, and its SQL statements are written manually by ourselves, which of course need to prevent SQL injection. In fact, MyBatis SQL is an "input + output" function, similar to the structure of the function, as follows:

<select id=“getBlogById“ resultType=“Blog“ parameterType=” int ”><br>         select id,title,author,content from blog where id=#{id} </select>

Here, ParameterType indicates the type of input parameter, and Resulttype indicates the type of the output parameter. In response to the above, if we want to prevent SQL injection, we naturally have to work on the input parameters. The highlighted part of the above code is the part of the input parameter in SQL, 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 is like this. This is because MyBatis enabled the pre-compilation feature, before SQL execution, the above SQL will be sent to the database to compile, execute, directly using compiled SQL, replace placeholder "?" "It's OK." Because SQL injection can only work on the compilation process, this is a good way to avoid the problem of SQL injection.

How does 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 statements. This "ready" approach not only improves security, but also improves efficiency when executing a SQL multiple times, because SQL is compiled and is no longer required to be compiled 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=“orderBlog“ resultType=“Blog“ parameterType=”map”>         select id,title,author,content from blog order by ${orderParam} </select>

After careful observation, the format of the inline parameter changed from "#{xxx}" to ${xxx}. If we assign the parameter "Orderparam" to "id", print out SQL, this is the case:

Select Id,title,author,content from blog order by ID

Obviously, this is not a way to prevent SQL injection. In MyBatis, the parameters of the format "${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 this parameter requires us to handle the code manually to prevent injection.

Conclusion: When writing MyBatis mapping statements, use the format "#{xxx}" as much as possible. If you have to use parameters such as "${xxx}", do the filtering work manually to prevent SQL injection attacks.

Java Persistence Layer Framework MyBatis How to prevent SQL injection