An instance of mybatis preventing SQL injection

SQL injection is a common attack method, which is familiar to everyone. The attacker enters some strange SQL fragments on the form information or URL of the interface, such as "or '1' ='1 '" statements, 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, such as banking software, it is often used to replace all SQL statements with stored procedures to prevent SQL injection. Of course, this is a very safe way, but we may not need this rigid way in our usual development.

As a semi-automatic persistence layer framework, the SQL statements of mybatis framework need to be written manually by ourselves. At this time, we certainly need to prevent SQL injection. In fact, the SQL of mybatis has the function of "input + output", which is similar to the structure of function, as follows:

Here, parametertype indicates the input parameter type, and resulttype indicates the output parameter type. In response to the above, if we want to prevent SQL injection, we should naturally work on the input parameters. The highlighted part 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, and you will see that the SQL is as follows:

No matter what parameters are entered, the printed SQL is like this. This is because mybatis enables precompiling. Before SQL execution, the above SQL will be sent to the database for compilation. During execution, the compiled SQL will be directly used to replace the placeholder " That's it. Because SQL injection can only work on the compilation process, this method avoids the problem of SQL injection.

How does MySQL precompile SQL? In fact, at the bottom of the framework, the Preparedstatement class in JDBC works. Preparedstatement is a subclass of statement we are familiar with, and its object contains compiled SQL statements. This "ready" method can not only improve security, but also improve efficiency when executing an SQL multiple times. The reason is that the SQL has been compiled and does not need to be compiled when executing again.

In other words, can we use mybatis to prevent SQL injection? Of course not. Please see the following code:

Carefully observe that the format of inline parameters changes from "#{XXX}" to ${XXX}. If we assign the parameter "orderparam" to "Id" and print the SQL, it is as follows:

select id,content from blog order by id

Obviously, this cannot prevent SQL injection. In mybatis, parameters in the format of "${XXX}" will directly participate in SQL compilation, so injection attacks cannot be avoided. However, when it comes to dynamic table names and column names, only parameter formats such as "${XXX}" can be used. Therefore, such parameters need to be processed manually in the code to prevent injection.

Conclusion: when writing the mapping statement of mybatis, try to use the format of "#{XXX}". If you have to use parameters such as "${XXX}", you should filter manually to prevent SQL injection attacks.