Shulou(Shulou.com)06/02 Report--

This article mainly explains the "Mybatis prevent sql injection principle is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "Mybatis prevent sql injection principle is what" it!

Principle of preventing sql injection by Mybatis

SQL injection is a code injection technique used to attack data-driven applications where malicious SQL statements are inserted into executed entity fields (for example, to dump database contents to an attacker). [excerpt from] SQL injection-Wikipedia SQL injection, which is not unfamiliar to everyone, is a common attack. It is possible for an attacker to enter some strange SQL fragments (such as "or" or "1 parameter checking 1") on the form information or URL of the interface to invade applications with inadequate parameter verification. Therefore, we need to do something in our application to guard against this kind of attack. In some applications with high security requirements (such as banking software), it is often used to replace all SQL statements with stored procedures to prevent SQL injection. Of course, English here is a very safe way, but we may not need this rigid way in our usual development.

MyBatis framework as a semi-automated ORM framework, its SQL statements have to be manually written by ourselves, at this time, of course, we need to prevent SQL injection. In fact, MyBatis's SQL has an "input + output" function, similar to the structure of a function, as follows:

SELECT id,title,author,contentFROM blogWHERE id = # {id}

Here, the parameter type represents the parameter type of the input, and resultType represents the parameter type of the output, in response to the above, if we want to prevent SQL injection, of course we have to work on the input parameters. The yellow highlight in the above code is the part where the input parameters are stitched together in SQL. After passing the parameters, print out the executed SQL statement, and you will see that the SQL looks 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 the precompilation function of MyBatis is enabled. Before the 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 "?" Just do it. Because SQL injection only works for the compilation process, this approach avoids the problem of SQL injection.

Underlying implementation principle

The reason is that if JDBC's PreparedStatement is adopted, the sql statement will be: "select id,no from user where id =?" Pre-compiled, that is, the SQL engine will pre-parse, generate a syntax tree, generate an execution plan, that is to say, the parameters you enter later, no matter what you enter, will not affect the syntax structure of the SQL statement, because the parsing has been completed, and parsing is mainly to analyze sql commands, such as select,from,where,and,or,order by and so on.

So even if you enter these sql commands later, they will not be executed as sql commands, because the execution of these SQL commands must first go through parsing to generate an execution plan. Since the parsing has been completed and has been pre-compiled, then the parameters entered later can never be executed as SQL commands, but will only be treated as string literal parameters.

So the precompilation of sql statements can protect against SQL injection. And efficiency can be improved when the same SQL is executed multiple times. The reason is that SQL has been compiled and does not need to be compiled when it is executed again.

Then again, is it certain that the MyBatis we use will prevent SQL injection? of course not, see the following code?

SELECT id,title,author,content FROM blogWHERE id = ${id}

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

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

(I added the above comparison example myself to contrast with the previous example. )

SELECT id,title,author,content FROM blogORDER BY ${orderParam}

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

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

Obviously, this won't stop SQL injection. In MyBatis, parameters in the format "${xxx}" are directly involved in SQL compilation, so injection attacks cannot be avoided. However, when it comes to dynamic table and column names, only parameter formats such as ${xxx} "can be used, so such parameters need to be handled manually in the code to prevent injection.

[conclusion] when writing the mapping statement of MyBatis, the format of "# {xxx}" should be adopted as far as possible. If you have to use a parameter like "${xxx}", filter it manually to prevent SQL injection attacks.

# {}: equivalent to PreparedStatement in JDBC

${}: is the value of the output variable

To put it simply, # {} is precompiled and secure; ${} is not precompiled, just takes the value of the variable, is unsafe, and there is SQL injection.

If we use the order ${} after passing the statement, there is a danger of SQL injection without doing any processing. If you say how to prevent it, I can only tell you tragically that you have to manually filter the input. If you judge whether the length of the input parameters is normal (the injection statement is usually very long), you can query whether the parameters on the input side are in the expected set of parameters.

Mybatis solves sql injection problem

Sql injection is not unfamiliar to everyone, and it is a common method of attack. An attacker enters some strange sql fragments on the form information or url of the interface, such as "or'1s missing parameters 1'", and it is possible to invade applications with insufficient parameter verification.

So in our application, we need to do some work to prevent this kind of attack. In some highly secure applications, such as banking software, it is often used to replace all sql statements with stored procedures to prevent sql injection, which is of course a very safe way, but we may not need this rigid approach in our usual development.

Mybatis framework as a semi-automatic persistence layer framework, its sql statements have to be manually written by ourselves, at this time, of course, we need to prevent sql injection. In fact, Mybatis's sql has an "input + output" function, which is similar to the structure of a function, as follows:

Select id,title,author,content from blog where id=# {id}

Here, parameterType marks the input parameter type, and resultType marks the output parameter type. In response to the above, if we want to prevent sql injection, of course 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 the parameters, print out the executed sql statement, and you will see that the sql looks like this:

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

No matter what parameters you enter, the printed sql looks like this. This is because mybatis enables the precompilation function. Before sql execution, the above sql will be sent to the database for compilation. When executing, the compiled sql will be directly used to replace the placeholder "?" Just do it. Because sql injection only works for the compilation process, this approach avoids the problem of sql injection.

How does mybatis precompile sql? In fact, at the bottom of the framework, it is the PreparedStatement class in jdbc that works. PreparedStatement is a familiar subclass of Statement, and its object contains compiled sql statements. This "ready" approach not only improves security, but also improves efficiency when executing a sql multiple times, because the sql has been compiled and does not need to be compiled when it is executed again.

Then again, is it certain that we can prevent sql injection by using mybatis? Of course not, take a look at the following code:

Select id,title,author,content from blog order by ${orderParam}

Observe carefully that the format of the inline parameter has changed from "# {xxx}" to ${xxx}. If we assign the parameter "orderParam" to "id" and print out the sql, it looks like this:

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

Obviously, this won't stop sql injection. In mybatis, parameters in the format "xxx" are directly involved in sql compilation, so injection attacks cannot be avoided. However, when it comes to dynamic table and column names, only parameter formats such as "{xxx}" can be used, so such parameters need to be handled manually in the code to prevent injection.

Thank you for reading, the above is the content of "what is the principle of Mybatis preventing sql injection". After the study of this article, I believe you have a deeper understanding of what the principle of Mybatis preventing sql injection is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.