In the previous article, we have already talked about the essence of SQL injection attack vulnerabilities being caused by improper coding by programmers. Now we will continue to talk about how to code correctly so as not to be attacked by SQL injection. Before talking about this issue Let's take a look at a piece of code first:
Copy the code code 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 prompts, please do not try to inject again!'); 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 prompts, please do not try to inject again!'); history.back(-1)</Script>
Response.end
end if
next
next
end if
This is a piece of ASP anti-injection code that is widely popular on the Internet. The idea is to check the data submitted by the Post method and Get method, and to prevent SQL injection by filtering sensitive characters such as Insert, Update, And, etc. In theory, if we filter enough characters, we can definitely guarantee that we will not be attacked by SQL injection, but please be careful. Read this code and pay attention to how it is judged. It is judged through the instr function. That is to say, if I want to filter the and character, it is not only the word And that is filtered, but also all the words containing and. All words with the following character combinations have been filtered out, such as island, mainland, hand... If these characters were filtered out, would anyone still be willing to use them? Therefore, this method of filtering sensitive characters is meaningless at all. What surprises me is that such a piece of garbage is actually posted on the Internet as a classic. I am really speechless.
Some people say that SQL injection attacks are caused by splicing SQL query strings, so using stored procedures without splicing SQL query strings can protect you from SQL injection attacks. Is this true? Not necessarily, let us look at an example of a stored procedure injection attack.
The code of the stored procedure dt_GetNews is as follows:
CREATE PROCEDURE dt_GetNews
@newstype int
AS
select * from news where newstype=@newstype
GO
Calling code:
<%
dim adoconnection
set adoconnection=server.createobject(adodb.connection)
'.........The relevant code for establishing a database connection is omitted here
adoconnection.execute exec dt_GetNews +request(newstype)
adoconnection.close
%>
If the value of request(newstype) is equal to 1, the result of the operation is to return all records with the newstype field in the news table being 1. However, if the value of request(newstype) is 1; drop table news, the returned result is that the news table is deleted. .
It can be seen from this example that even using stored procedures will still be attacked. Besides, select * from news where newstype=@newstype is not splicing, so there is no inevitable connection between splicing SQL query strings and SQL injection attacks. Contact, stored procedures may not be able to protect against injection attacks.
So how do you write it without being attacked by SQL injection? Below I will introduce an ultimate method. To put it bluntly, it is very simple and primitive, which is data type verification and single quote replacement. Whether it is Oracle, Sql Server, mySql, Access or other relational databases, field types can be roughly divided into two categories: numerical types (such as int, float, etc.) and character types (such as char, varchar, etc.). The corresponding SQL statements are slightly different depending on the field type, such as:
The newstype field in Select * from news where newstype=1 must be a numeric field.
select * from news where newstype='social news' the newstype field must be a character field.
For numeric fields, what we must do is to check the data type of the parameter. For example, when we construct a query statement using select * from news where newstype=+v_newstype, we must check the data type of the v_newstype variable. v_newstype is at least It must be a number, which can be an integer or a floating point number. If such a check is made, select * from news where newstype=+v_newstype will never construct something similar to select * from news where newstype=1;drop Statements like table news. The reason why ASP is more vulnerable to attacks than ASP.Net, JSP, etc. is because variables in ASP do not need to be declared and the variable type is unclear.
For character fields, what we must do is to process single quotes ('). The method of processing is to replace one single quote with two single quotes (''). For example, we use select * from news where When newstype='+v_newstype+' is used to construct the query statement, the single quotation mark in v_newstype must be replaced with two single quotation marks, because in SQL the part enclosed by two single quotation marks represents a string, and two consecutive A single quote represents a single quote character. After such processing, we will 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--
After replacing one single quote to two single quotes, the value of v_newstype becomes:
Social news'';drop table news--
The constructed SQL statement becomes:
select * from news where newstype='social news'';drop table news—'
The result of the query is to return records whose value of the newstype field in the news table is social news';drop table news--, without causing the news table to be deleted as before.
In addition, it is not just the Select statement that needs to be processed, including Insert, Update, Delete, Exec, etc., you can take a look at the following injection methods:
In the structure insert into news(title) values('+v_title+'),
When v_title=123';drop table news--';
When update news set /> when v_title=123'-- or v_id=1;drop table news--, so it is not only a problem with the Select statement, but other statements may have problems, don't just focus on Select
In short, after verifying the data type and processing the single quote characters, even if it has all the abilities, it will not be able to fly out of the palm of my Tathagata.