1. The nature of SQL injection attacks: Let the client pass past strings into SQL statements, and can be executed.
2. Every programmer must shoulder the responsibility of preventing SQL injection attacks.
Talking about preventing SQL injection attacks, feeling very depressed, so many years have been discussed, but also has been arguing, but now seems to have no conclusion. When you do not know the principle of injection will feel very magical, how has been injected? Will find it difficult to prevent. But is it easy to prevent it when you know the principle of injection?
The first time I heard of SQL injection attack in 2004 (as if to learn later), that is still writing ASP. At the time of writing code, some colleagues asked me, your code to prevent injection attack? What attack? What is this?
Later on the internet to find a variety of, and finally figured out how the attack came in. Injection attacks are from the client, whether it is form submission, URL pass value or cookies, in fact, the principle is the same. To the server side can be divided into three kinds of cases: number, date time, String.
Number one.
How to inject?
Let's say we're going to implement a page that displays news, and we might be able to write the following code with handwriting:
Copy Code code as follows:
String id = request.querystring["id"];
String sql = "SELECT * FROM news where colid=" + ID;
If the ID passed over is the number we imagine (for example, 168), then naturally there is no problem. But if the ID passed is "a delete from table," then the SQL value becomes "select * from table where colid=168 delete". For SQL Server to support the submission of multiple SQL statements at a time, this provides us with convenience and open the door to SQL injection. Obviously, if this SQL statement is executed, then the record in the news is gone.
So how to prevent it? Simply because the type of the Colid field is int, we just need to verify that the passed ID is not an integer. is an integer and there is no injection; if not then there is the possibility of injection. Even if there is no injection, stitching an ID that is not an integer can result in an execution error.
So whether or not to prevent SQL injection, you should verify that the ID is not an integer.
Verification method, you can use TryParse, you can use regular, you can write function validation. However, it is not recommended to use a try exception because of the efficiency problem.
There is also a special case, is for the bulk of the deletion of this kind of will pass over several numbers, such as "1,2,3,10", this also need to verify, in case someone exploit this loophole. As for the verification method is also very simple, you write a function on the OK.
Ii. Date and time
This is the same as the number, and validation is not a date time.
Three, string
This is the most troublesome and controversial.
Let's take a look at how to inject
For example, we first need to follow the news headlines to query, may write code:
Copy Code code as follows:
string key = Txttitle.text;
String sql = "SELECT * from news where title like '% ' + key + '% '";
How is this injected? I want to ask you a question first: if the value of a key never contains a single quote, will it be injected?
So how do you inject them with single quotes? Assuming that key= "delete from News-", then the SQL value is "SELECT * from news where the title like ' deletes from news-'".
First, enclose a pair of enclosed single quotes with a single quotation mark and the preceding single quotation mark. This pair of single quotes inside ('% ') is treated as a string, while the outside is treated as an SQL statement, and the second single quote is "--" commented out, which guarantees the correctness of the entire SQL statement.
This is a method of injection.
So how to prevent it? Just think about the problem, if there is no single quotation mark is not the peace of mind? For this situation (the previous "number" does not count), so far I have not found the use of single quotes, but also can inject in the method. Perhaps it is my ignorance, I do not know whether the experts know for this situation, do not use single quotes can be injected in the method.
Now that you've found the culprit, it's OK to do it, just kill the single quote. Key = key. Replace ("'", "" "), at which point the SQL value is" SELECT * from news where the title like '% ' deletes from news-' ".
For SQL, the two single quotes inside a pair of single quotes represent single quotes in the form of a string. So we've transformed the culprit into a string. The "--" within a pair of single quotes is also an ordinary string without representing a comment.
The culprit is single quotes, do not understand why there are many people to filter the "Delete, update" This category of keywords, they are good people ah, they are very wronged. Of course, if the premise is that the program has been written, can not modify the internal code, it is another matter. As for "--" at most is an accomplice, if you do not trust, you can handle him.
Summary: number, date time, validation type; string, handle single quotes.
Also, for security purposes, do not use SA to connect to the database, and xp_cmdshell this class of risky extended stored procedures should be handled (for example, delete).
PS: When modifying data, you can use parameterized SQL statements, but the goal is not to prevent injection, but to reuse the execution plan.
There is the problem of JS script, this has not been carefully considered, you can use HTML encoding, you can also use the way to replace, there are ubb loopholes and so on.
