Java Persistence Layer Framework MyBatis methods to prevent SQL injection _java

SQL injection is not unfamiliar to everyone, is a common attack, the attacker in the interface form information or URL on the input of some strange SQL fragments, such as "or ' 1 ' = ' 1 '" statement, it is possible to invade the application of insufficient parameter checksum. So in our application, we need to do some work to guard against this attack mode. In some highly secure applications, such as banking software, it is often safe to prevent SQL injection by replacing all SQL statements with stored procedures, which is certainly a very secure way, but we may not need this kind of rigid approach in our normal development.

MyBatis Framework as a semi-automatic persistence layer framework, its SQL statements are to be manually written by ourselves, this time, of course, need to prevent SQL injection. In fact, MyBatis's SQL is a "input + output" function, similar to a function of the structure, as follows:

<select id= "Getblogbyid" resulttype= "Blog" parametertype= "int" ><br>
    Select Id,title,author,content From blog where Id=#{id}
 
</select>

Here, the parametertype indicates the type of input parameter, resulttype the parameter type of the output. In response to the above, if we want to prevent SQL injection, we should certainly work on input parameters. The highlighted part of the code above is the part of the input parameter that is spliced in the SQL, and after passing the parameter, print out the executed SQL statement, and you will see that SQL is like this:

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

No matter what parameters are entered, this is the case for the printed SQL. This is because MyBatis enabled the precompiled feature, before SQL execution, the above SQL will be sent to the database for compilation, execution, directly using compiled SQL, replace the placeholder "? "It's okay. Because SQL injection can only work with 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, it is the PreparedStatement class in JDBC that works, PreparedStatement is a subclass of the statement we are familiar with, and its objects contain compiled SQL statements. This "ready" approach not only improves security, but also increases efficiency when executing one SQL multiple times, because SQL is compiled and no more compiles when executed again.

In other words, is it possible for us to use MyBatis to prevent SQL injection? Of course not, please look at the following code:

<select id= "Orderblog" resulttype= "blog" parametertype= "map" >
 
    select Id,title,author,content from Blog ORDER by ${orderparam}
 
</select>

Carefully observed, the format of the inline parameter changed from "#{xxx}" to ${xxx}. If we assign the parameter "Orderparam" to "id", print out the SQL:

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

Obviously, this is not a way to prevent SQL injection. In MyBatis, parameters in the format "${xxx}" are directly involved in SQL compilation, thus preventing injection attacks. However, when it comes to dynamic table names and column names, you can only use parameter formats such as "${xxx}", so such parameters require manual processing in the code to prevent injection.

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

The above is a small series for everyone to bring the Java Persistence Layer Framework mybatis Prevent SQL injection of all the content, I hope that we support cloud-Habitat Community ~