java persistence layer framework mybatis method to prevent sql injection

sql injection is a familiar and common form of attack, in which an attacker enters a strange sql fragment, such as "or '1' ='1'", on the form information of the interface or on url. So we need to do something in our application to guard against this kind of attack. In some high-security applications, such as banking software, it is often used to replace all sql statements with stored procedures to prevent the injection of sql. This is of course a very safe way, but in our normal development, we may not need such a rigid way.

As a semi-automated persistence layer framework, mybatis requires us to manually write sql statements, which of course requires us to prevent sql injection. In fact, sql of Mybatis is a structure with "input + output" function, which is similar to a function, as follows:

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

Here, parameterType identifies the input parameter types, and resultType identifies the output parameter types. In response to the above, if we want to prevent sql injection, we have to work on the input parameters. The highlighted part of the above code is the part where the input parameters are spliced in sql. After passing in the parameters, the sql statement is printed out for execution. You will see that sql looks like this:

select id,title,author,content from blog where id = ?

The printed sql looks like this no matter what parameter is entered. This is because mybatis enables precompilation. Before sql is executed, sql above will be sent to the database for compilation. When executing, sql will be directly used to replace the placeholder "? ". That's it. Because sql injection only works with the compilation process, this approach nicely avoids the problem of sql injection.

How does mybatis do sql precompile? In fact, at the bottom of the framework, it is the PreparedStatement class in jdbc that is at work. PreparedStatement is a familiar subclass of Statement whose objects contain compiled sql statements. This "ready" approach not only improves security, but also efficiency when one sql is executed multiple times, because sql is compiled and does not need to be compiled when it is executed again.

Then again, can we use mybatis to prevent injection of sql? Of course not, see the following code:

<select id= " orderBlog "  resultType= " Blog "  parameterType= " map " >
 
    select id,title,author,content from blog order by ${orderParam}
 
</select>

On closer inspection, the format of the inline parameter changes from "#{xxx}" to ${xxx}. If we assign the parameter "orderParam" to "id", print out sql and it looks like this:

select id,title,author,content from blog order by id

Obviously, this will not prevent the injection of sql. In mybatis, parameters in the form of "${xxx}" are directly involved in sql compilation and cannot avoid injection attacks. But when it comes to dynamic table names and column names, you can only use parameter formats like "${xxx}," so you need to manually handle such parameters in your code to prevent injection.

Conclusion: when writing mybatis mapping statements, use the format "#{xxx}" whenever possible. If you have to use a parameter like "${xxx}", do the filtering manually to prevent an sql injection attack.