Ultimate prevention of SQL injection attacks

Let's take a look at this question first. Code : Copy code The Code is as follows: dim SQL _injdata, SQL _inj, SQL _get, SQL _data, SQL _post
SQL _injdata = "'| and | exec | insert | select | Delete | update | count | * | % | CHR | mid | master | truncate | char | declare"
SQL _inj = Split (SQL _injdata, "| ")
If request. querystring <> "then
For each SQL _get in request. querystring
For SQL _data = 0 to ubound (SQL _inj)
If instr (request. querystring (SQL _get), SQL _inj (SQL _data)> 0 then
Response. Write "<script language = JavaScript> alert ('SQL anti-injection system prompt, please do not try to inject! '); History. Back (-1) </SCRIPT>"
Response. End
End if
Next
Next
End if
If request. Form <> "" then
For each SQL _post in request. Form
For SQL _data = 0 to ubound (SQL _inj)
If instr (request. Form (SQL _post), SQL _inj (SQL _data)> 0 then
Response. Write "<script language = JavaScript> alert ('SQL anti-injection system prompt, please do not try to inject! '); History. Back (-1) </SCRIPT>"
Response. End
End if
Next
Next
End if

This is a widely-used ASP injection prevention code on the Internet. Its idea is to check the data submitted by the post and get methods, attackers can filter insert, update, and other sensitive characters to prevent SQL injection attacks, theoretically, if we filter out enough characters, it will definitely not be subject to SQL injection attacks. But please read this code carefully and pay attention to its judgment method, it is determined by the instr function. That is to say, if I want to filter and characters, it is not only the word "and, at the same time, all words that contain the and character combination are filtered out, such as island, mainland, hand ............, Will anyone use these characters if they are all filtered out? Therefore, this method of filtering sensitive characters is meaningless at all. What surprised me was that such a junk object was actually pasted as a classic post on the Internet. It was speechless.
Some people say that SQL injection attacks are caused by splicing SQL query strings. Therefore, using stored procedures without concatenating SQL query strings can be prevented from SQL injection attacks. Is that true? Not necessarily. Let's look at an example of a stored procedure injection attack.
The stored procedure dt_getnews code is as follows:
Create procedure dt_getnews
@ Newstype int
As
Select * from news where newstype = @ newstype
Go
Call code:
<%
Dim adoconnection
Set adoconnection = server. Createobject ("ADODB. Connection ")
'............ The code for establishing a database connection is omitted here.
Adoconnection.exe cute "Exec dt_getnews" + Request ("newstype ")
Adoconnection. Close
%>
If the value of request ("newstype") is equal to 1, the running result returns the record with all newstype fields in the News table being 1, but if request ("newstype ") the value is "1; drop table News". The returned result is that the news table is deleted.
From this example, we can see that even using stored procedures will be attacked. Besides, select * from news where newstype = @ newstype is not a concatenation, therefore, splicing SQL query strings does not necessarily associate with SQL injection attacks, and stored procedures may not be able to defend against injection attacks.
So how can I write it without being attacked by SQL Injection? Next I will introduce an ultimate method. To put it bluntly, it is very simple. It is also very primitive that data type verification should be replaced with single quotes. Whether it is Oracle, SQL Server, MySQL, access, or other relational databases, the field types can be roughly divided into two categories: numeric type (such as: int, float, etc.) and numeric type (such: for example:
The newstype field in "select * from news where newstype = 1" must be a numeric field,
The newstype field in "select * from news where newstype = 'social news '" must be a complex field.
For numeric fields, we must check the data type of the parameter, for example, when we use "select * from news where newstype =" + v_newstype to construct a query statement, we must check the data type of the v_newstype variable. v_newstype must be at least one number, it can be an integer or a floating point number. If this check is performed, "select * from news where newstype =" + v_newstype this method will never create statements like "select * from news where newstype = 1; drop table News. ASP is more vulnerable to attacks than ASP. NET, JSP, and so on, because the variables in ASP do not need to be affirmed or the variable type is not clear.
What we need to do for fields of the simplified type is to process the single quotation marks ('). The processing method is to replace one single quotation mark with two single quotation marks (''), for example, when we construct a query statement using the "select * from news where newstype = '" + v_newstype + "'" method, we must replace the single quotes in v_newstype with two single quotes, in SQL, two single quotes enclose a string, while two consecutive single quotes represent a single quotes, after doing this, let's look at the construction method of "select * from news where newstype = '" + v_newstype + "'". When the value of v_newstype is:
"Social News"; drop table news --"
The value of v_newstype becomes:
"Social News"; drop table news --"
The constructed SQL statement is:
"Select * from news where newstype = 'social news''; drop table news -'"
The result of the query is that the value of the newstype field in the news table is "social news"; the drop table news -- "record is returned, and the news table is not deleted as before.
In addition, the Select statements, including insert, update, delete, exec, and so on, need to be processed. You can look at the following injection methods:
In the construction of "insert into news (title) values ('" + v_title +,
When v_title = "123 '; drop table news;
In the structure of "Update News set Title = '" + v_title + "'where id =" + v_id,
When v_title = "123 '--" or v_id = "1; drop table news --", other statements may have problems, do not just stare at select
In short, after verifying the data type and processing the single quotation marks, even if Sun and monkey have the power, they will not be able to fly.