Prevent SQL injection attacks

Speaking of preventing SQL injection attacks, I feel very depressed. After so many years of discussion, I have been arguing, but it seems that I still have no final conclusion. When I don't know the injection principle, I think it's amazing. How can I get injected? It will be difficult to prevent. But after knowing the injection principle, isn't prevention easy?

The first time I heard about the SQL injection attack was in 2004 (it seems that it was late), but I was still writing asp. When I was writing code, some colleagues asked me, is your code protected against injection attacks? What attacks? What is this.
Then I came to the Internet to find out how the attack came in. Injection attacks come from the client. The principles of form submission, URL passing, and Cookie are the same. The server can be divided into three types: numbers, date and time, and string.

 

I. Numbers.

How to inject?

If we want to implement a page for displaying news, we may write the following code:

 

String id = Request. QueryString ["id"];
String SQL = "select * from news where ColID =" + id;
 

If the passed id is a number (such as 168), then naturally there will be no problem. However, if the passed id is "168 delete from table", the SQL value is changed to "select * from table where ColID = 168 delete from news ". For SQL Server, multiple SQL statements can be submitted at a time, which makes it easy for us and opens the door for SQL injection. Obviously, if this SQL statement is executed, the records in the news table will be lost.

 

So how to prevent it? It is very simple, because the type of the ColID field is int, we only need to verify whether the passed id is an integer. If it is an integer, no injection exists. If not, injection may exist. Even if there is no injection, splicing an id that is not an integer will cause execution errors.
Therefore, whether it is to prevent SQL injection, you should also verify whether the id is an integer.

You can use TryParse, regular expressions, or write a function for verification. However, try exception is not recommended because of this efficiency problem.

 

Another special case is that multiple numbers such as "1, 2, 3, 10" will be passed for batch deletion. This also needs to be verified in case someone exploits this vulnerability. As for the verification method, it is easy to write a function by yourself.

 

II. Date and time

This is the same as the number. Verify whether it is a date or not.

 

III. String

This is the most troublesome and controversial part.

Let's take a look at how to inject

For example, we need to query the news headlines first, and the code that may be written is as follows:

 

String key = txtTitle. Text;
String SQL = "select * from news where title like '%" + key + "% '";

 

How is this injected? I would like to ask you one question: if the key value will never contain single quotes, will it be injected in?

So how is single quotes injected? Assuming key = "'delete from news --", the SQL value is "select * from news where title like '%' delete from news --'".

Use a single quotation mark and a single quotation mark to form a pair of closed single quotation marks. This pair of single quotation marks ('%') is processed as strings, and the external quotation marks are processed as SQL statements, the second single quotation mark is commented out by "--", which ensures the correctness of the entire SQL statement.

This is a method of injection.

So how can we prevent it? Think about the problem just now. If there is no single quotation mark, isn't it so peaceful? For this situation (the preceding "number" is not counted), so far I have not found a method that can be injected without single quotes. Maybe it's just me. I don't know if you know how to inject it in this case without single quotes.

Now that the culprit is found, it's easy to handle it. It's okay to put off single quotes. Key = key. Replace ("'", "'' "); at this time, the SQL value is" select * from news where title like' % ''delete from news --'".
For SQL statements, the two single quotes inside a pair of single quotes represent a single string of single quotes. In this way, the culprit is transformed into a string. The "--" in a pair of single quotes is also a common string, not a comment.

 

The culprit is single quotes. I wonder why many people filter keywords such as "delete and update". They are anshanliangmin, and they are very jealous. Of course, if the program has already been written and internal code cannot be modified, let alone. As for "--", it is at most an accomplice. If you are not at ease, you can handle it as well.

 

Summary: numbers, date and time, verification type; string, processed single quotes.
For the sake of security, do not use sa to connect to the database. The xp_mongoshell type of dangerous extended storage process should also be processed (such as deletion ).