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

This article focuses on "how to solve the problems caused by sql injection", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor learn how to solve the problems caused by sql injection.

Preface

Recently, I have been sorting out the issues related to security vulnerabilities, and I am ready to share them in the company. As it happens, the team discovered a sql injection vulnerability during this time: in a common paging function, the sort field is used as an input parameter, and the front-end page can be customized. In the mybatis mapper.xml of the paging sql, the $symbol is used after the order by field to dynamically receive the calculated sort parameters, so that the function of dynamic sorting can be realized.

However, if the input parameter is passed in:

Id; select 1--

The final executed sql will be:

Select * from user order by id; select 1-- limit 1 Magi 20

Comment out the following limit statement, invalidating the paging condition and returning all the data. An attacker can obtain all the data at once through this vulnerability.

Dynamic sorting is a good idea, but there is a risk of sql injection. Fortunately, this time we found the problem in time and solved it in time without causing any loss.

However, when I was at my old club a few years ago, I was not so lucky.

A sql injection directly failed our payment service.

1. Reduction accident scene

One day the little sister of the operator came up to me and told me that many users could not afford it. This payment service is an old system that has changed hands with 3 people and has been very stable without any problems.

Without saying a word, I began to locate the problem. I first looked at the server log and found a lot of anomalies that reported too many database connections. Because the payment function is too important, at that time, in order to ensure the rapid recovery of the payment function, we first asked the operation and maintenance to restart the two nodes of the payment service.

It returned to normal temporarily after 5 minutes.

I continue to locate the reason, according to my experience at that time, there are generally too many database connections, probably because the connection forgot to close. However, a careful examination of the code did not find a problem, and the database connection pool we used at that time, which automatically reclaimed idle connections, ruled out this possibility.

After a while, another node had the problem of too many database connections.

But at this time, the reason has not been found out, so we have no choice but to let the operation and maintenance restart the service again. But this time, the maximum number of database connections has been increased to 100. the default is 100. at that time, we set it to 1000. (in fact, most companies now set this parameter to 1000)

Use the command:

Set GLOBAL max_connections=500

It can take effect in time, and there is no need to restart mysql service.

This time it bought me more time and asked dba to help me find out the cause.

Use the show processlist; command to view the current thread execution:

You can also view the current connection status to help identify problematic query statements. (it should be noted that the above picture is just an example I gave, and the real online result is not like this.)

Id thread idUser executes sql account Host executes ip of sql database and pin db database name Command executes commands, including Daemon, Query, Sleep, etc. The time consumed by Time to execute sql State execution status info execution information, which may contain sql information.

Sure enough, an unusual query sql was found that had not been executed for almost an hour.

Dba copied the sql and sent it to me. Then kill-9 kills the SQL thread that takes a very long time to execute.

Later, the problem of too many database connections no longer appears.

I got the sql and analyzed it carefully and found that an order query statement was injected into a long sql by the attacker. It must have been written by a master, and there are some grammars I haven't seen before.

But it can be confirmed that it was injected by human sql.

Through the information in that sql, I quickly found the code that the input parameter used when querying the data, instead of the PrepareStatement precompilation mechanism.

If you know the reason, it is easy to deal with, and the problem can be finally solved by changing the place of querying data into preparestatement precompilation mechanism.

two。 Why does it cause too many database connections?

I believe many students will have a question when they see this: why does sql injection lead to too many database connections?

Let me use a picture to explain to you:

The attacker sql injected a parameter like this:-1; lock table statement -. Among them, the previous query statement is executed first. Because the following statements are commented, only the lock table statement is executed to lock the table. After the normal business request successfully obtains the connection from the database connection pool, when you need to manipulate the table, try to acquire the table lock, but do not get it until the timeout. Note that a large number of database connections may be occupied and not returned in time. The database connection pool is insufficient and there are no free connections. The new business request cannot get a connection from the database connection pool, reporting too many database connection exceptions.

Sql injection leads to the problem of too many database connections, and the most fundamental reason is to lock tables for a long time.

3. Why can precompilation prevent sql injection?

The preparestatement precompilation mechanism parses, compiles and optimizes the sql statement before it is executed, where the parameter position uses a placeholder? Instead.

When actually run, the passed parameter will be treated as plain text, will not be recompiled, and will not be treated as a sql instruction.

In this way, even if the input parameter passes in sql injection instructions such as:

Id; select 1--

The final executed sql will be:

Select * from user order by 'id; select 1--' limit 1 id; select 20

This way there will be no sql injection problems.

4. Pre-compilation must be safe?

I don't know if you have ever used like statements when querying data. For example, users with the word "Su" in their first names may use statements like this:

Select * from user where name like'% Su%'

Under normal circumstances, there is no problem.

However, in some scenarios, the input conditions are required. For example, name is required. If:% is injected, the final sql executed will look like this:

Select * from user where name like'%%'

In this case, the precompilation mechanism passes normally, but the result of the execution of sql does not return users who contain%, but all users.

The required name field becomes useless, and attackers can also get all the data in the user table.

Why did this problem arise?

% is a keyword in mysql, and if you use like'%%', the like condition will be invalidated.

How to solve the problem?

% needs to be escaped: /%.

The escaped sql becomes:

Select * from user where name like'% /%'

Only users containing% will be returned.

5. What about some special scenes?

In java, if mybatis is used as the persistence framework, and in the mapper.xml file, if the input parameter uses # to pass values, the precompilation mechanism will be used.

We usually use it like this:

Select * from user

Name = # {name}

In most cases, people are encouraged to use # to transmit parameters, which is safer and more efficient.

But sometimes there are special circumstances, such as:

Order by ${sortString}

The content of the sortString field is dynamically calculated in a method, in which case # cannot be used instead of $, so the program will report an error.

The use of $carries the risk of sql injection.

So what should we do in this situation?

Write a util tool to filter out all the injection keywords and call it when calculating dynamically. If the data source uses Ali's druid, you can turn on the wall (firewall) in filter, which includes the function to prevent sql injection. However, there is a problem, that is, by default, it does not allow multiple statements to operate at the same time, and batch update operations will be intercepted, so we need to customize filter. 6. How is the table information leaked?

Some careful students may ask a question: in the example of locking the table above, how did the attacker get the table information?

Method 1: blind guess

It is the name of the table that the attacker guesses according to common sense.

Suppose we have the following query conditions:

Select * from t_order where id = ${id}

Input parameter:-1 position select * from user

Finally, the execution of sql becomes:

Select * from t_order where id =-1; select * from user

If the sql has data returned, it means that the user table exists and is guessed.

It is recommended that the table name should not be too simple and can be prefixed with an appropriate prefix, such as t_user. This can increase the difficulty of blind guessing.

Method 2: through the system table

In fact, mysql has some system tables, you can find our custom database and table information.

Suppose we still take this sql as an example:

Select code,name from t_order where id = ${id}

The first step is to get the database and account name.

Pass parameters as:-1 union select database (), user () #

Finally, the execution of sql becomes:

Select code,name from t_order where id =-1 union select database (), user () #

The current database name: sue and account name: root@localhost are returned.

Step two, get the table name.

The parameter passed is changed to:-1 union select table_name,table_schema from information_schema.tables where table_schema='sue'# finally executes sql to:

Select code,name from t_order where id =-1 union select table_name,table_schema from information_schema.tables where table_schema='sue'#

All table names under the database sue are returned.

It is recommended that the database account accessed by the program in the generation environment should be separated from the administrator account, and the permissions must be controlled and the system tables cannot be accessed. What are the hazards of 7.sql injection? 1. Core data leakage

The purpose of most attackers is to make money, to put it bluntly, to get valuable information and sell it for money, such as user account number, password, mobile phone number, ID card information, bank card number, address and other sensitive information.

They can inject statements like this:

-1; select * from user;-

All the information in the user table can be easily obtained.

Therefore, it is recommended that you encrypt and store these sensitive information and use AES symmetric encryption.

two。 Delete the library and run away

There is no lack of some attackers who do not play cards according to common sense and delete the tables or databases of the system directly after sql injection.

They can inject statements like this:

-1; delete from user;-

The above statement deletes all data in the user table.

-1; drop database test;-

The above statement deletes everything from the entire test database.

Under normal circumstances, we need to control the permissions of online accounts and only allow DML (data manipulation language) data to manipulate language statements, including select, update, insert, delete and so on.

DDL (data definition language) database definition language statements are not allowed, including: create, alter, drop, etc.

Also does not allow DCL (Data Control Language) database control language statements, including: grant,deny,revoke and so on.

DDL and DCL statements can only be operated by the administrator account of dba.

By the way: if the table is deleted or the library is deleted, there is a remedy, that is, to restore from the backup file, only a small amount of real-time data may be lost, so there must be a backup mechanism.

3. Hang up the system.

Some attackers can even fail our service directly, as was the case in our old employer.

They can inject statements like this:

-1; lock table statement;--

Locking the table for a long time may cause the database connection to run out.

At this point, we need to monitor the database thread and send an email warning if the execution time of a sql is too long. In addition, setting a reasonable timeout for database connections can also slightly alleviate this kind of problem.

From the above three aspects, we can see that the harm of sql injection problem is really great, we must avoid the occurrence of this kind of problem, do not have the psychology of luck. If you encounter some attackers who do not vote according to common sense, once you are attacked, you may lose a lot of money.

8. How to prevent sql injection? 1. Use the precompilation mechanism

Try to use precompilation mechanism, less string concatenation to pass parameters, which is the root of the problem of sql injection.

two。 To escape special characters

Some special characters, such as:%, are escaped when they are used as parameters in a like statement.

3. To catch an exception

All exceptions need to be caught. Remember that the API returns exception information directly, because some exception information contains sql information, including library name, table name, field name and so on. With this information, an attacker can attack your database at will through sql injection. At present, the more mainstream approach is to have a special gateway service, which uniformly exposes the external interface. When a user requests an interface, it passes through it, and then it forwards the request to the business service. The advantage of this is that it can uniformly encapsulate the return body of the returned data, and if an exception occurs, it can return unified abnormal information and hide sensitive information. In addition, it can also do current restriction and authority control.

4. Use the code detection tool

Using code detection tools such as sqlMap, it can detect sql injection vulnerabilities.

5. There should be monitoring.

Need to monitor the implementation of the database sql, if there is any abnormal situation, timely email or SMS reminder.

6. Database account needs to control permissions

Set up a separate account for the database of the production environment, assign only DML-related permissions, and cannot access system tables. Do not use administrator account directly in the program.

7. Code review

The establishment of code review mechanism can find out some hidden problems and improve the quality of code.

8. Use other means to deal with

When precompiled parameters cannot be used, either turn on druid's filter firewall, or write your own code logic to filter out all possible injection keywords.

At this point, I believe you have a deeper understanding of "how to solve the problems caused by sql injection". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.