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

This article introduces the relevant knowledge of "what is the method of injecting Mybatis into SQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mybatis, talk about your insights into SQL injection. What is 1.sql injection?

Sql injection means that some illegal users make the server execute malicious sql commands unknowingly by inserting some special characters or sql statements into the form to be submitted, thus causing a series of security risks.

The popular point is that users use SQL syntax to add some sql statements to some fields, and when submitting a form, the server executes sql commands that do not achieve the desired results but cause exceptions and data leaks.

This is a typical system loophole, so the harm of sql injection to the system is very great, and the system must be improved to prevent sql injection.

2.sql injection instance

I wrote a simple login program, the framework is Spring+SpringMVC+Mybatis. The table structure is as follows:

(1) Special symbols, such as' and. Etc.

You can see that when I enter a special symbol and the background is not filtered, a sql exception is thrown:

And if there is no global exception handling, users can get the relevant information about the database and query statements through the developer mode of the browser.

And this is a dangerous loophole for the system.

(2) injection of sql statement

When you enter the correct account password, you can see that the execution was successful.

But if you add such a sql statement to the form, we can see that the result can still be executed successfully.

Illegal users can use this to test the table name of the database, other field names of the table, the magnitude of the data, and so on.

For example: try to get other field names for the table

All you need to do is replace the "1percent 1" in the previous form with a test sql statement, which means that this field exists in the table if the execution is successful.

Take a look at the last executed sql statement:

It's clear at a glance. This is caused by injection.

Similarly, 'or''=' not only executes successfully, but also queries all the data.

(3) inject through the in and order by of the sql statement

The above three points are mainly due to the use of ${} in mybatis, and the sql statement does not perform precompilation, which cannot prevent sql injection.

How to solve the problem of sql injection under 3.mybatis Framework

The mybatis framework itself has the feature of preventing sql injection, which requires that we must follow the writing specifications of the framework when writing sql statements.

(1) replace all ${param} with # {param}

Because ${} is a query that splices sql characters without precompilation, because it is impossible to defend against sql injection, while # {} needs to be precompiled, which largely prevents sql injection.

In some places where errors will be reported when using # {}, such as like query, in query, order by sorting, etc.

A) like:

Select * from Users where username like'% ${username}%'

To be replaced by:

Select * from Users where username like concat ('%', # {username},'%')

B) in

Select * from Users where id in (${id})

Replace with the foreach loop that comes with the mybatis framework:

C) order by

Do not use: splicing sort directly

Such as:

Select * from Users order by ${id}

Instead, it is mapped at the java level, and then used to make judgments.

For other similar cases, please handle them in the same way.

(2) sql injection verification of the data entered by the user

SqlServer's own anti-sql injection mechanism can avoid sql injection by using stored procedures. Users should be prohibited from entering some key special symbols, such as semicolons, delimiters, single quotes, etc., and sql keywords should be shielded for some key locations, such as or, and and so on. The type, length, format and scope of the content entered by the user must be verified.

(3) it is necessary to distinguish the permissions of users.

There must be a strict distinction between the permissions of ordinary users and those of administrators, and administrators must be verified with a higher level of security, so as to prevent sql injection attacks when they obtain higher privileges artificially.

(4) higher level of verification

Turn on the verification of sql injection in the back-end code and database, and use professional injection tools to find loopholes in the system for repair. Account deception can also be carried out to set thousands of passwords for vulnerable users such as "admin", so that the attacker's software is overloaded because of the large amount of parsing, thus running out of resources and downtime.

A common SQL injection of Mybatis I. scene reproduction

There is now a table called student with the following structure:

Among them, id is the self-increasing primary key, and the remaining fields are English, math, art, student's student number, student's name and student's phone number.

There are currently six pieces of data in the table:

Second, code display

Use the mybatis framework to find the corresponding records based on the art scores. In the mapper.xml file, the code will probably write like this:

Select id, english_grade, math_grade, art_grade, number, name, telephone from student and art_grade = # {artGrade}

Of course, you can also write:

Select id, english_grade, math_grade, art_grade, number, name, telephone from student and art_grade = ${artGrade}

The difference between the two ways of writing is that the incoming parameters are received differently, the former being # {property} and the latter ${property}.

Third, simulate the request & response result

To query a record with an art score of 70, use postman to make the request. The parameter json is:

{

"artGrade": "70"

}

The test results are both:

It seems to be the same result, and both methods are correct.

But I changed the parameters to look like this:

{

"artGrade": "'70' and id='4'"

}

The result is completely different!

The result returned using # {property} is empty:

Through the log, it is found that the SQL actually executed is:

Select id, english_grade, math_grade, art_grade, number, name, telephone from classroom.student WHERE art_grade = "'70' and id='4'"

The result returned using ${property} is one:

Through the log, it is found that the SQL actually executed is:

Select id, english_grade, math_grade, art_grade, number, name, telephone from classroom.student WHERE art_grade = '70' and id='4' "what is the method of Mybatis injecting SQL", thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.