Effective prevention of SQL injection vulnerabilities detailed description

1. If you have parameters in a dynamically constructed SQL statement, you must do the following for the parameters
A. Replace ' (single quotes) with ' (two single quotes)
B. Replace--(annotation character)
C. When you add a parameter to a statement, be sure to enclose it in quotes, such as: ' SELECT * from table where id= ' + @id + '
2. If a dynamically constructed SQL statement contains table parameters, do not necessarily add [] (brackets) to the table, such as: ' SELECT * from [' + @tab + '] '

3. Avoid dynamic SQL statements: Especially from the IE client to get query, modify, delete the conditions of the field is most easily injected, such as the above from the client to obtain PersonID, in order to develop conveniently, directly from the client to obtain the Persongid as a condition of SQL statements, The PersonID is not checked as necessary, so it is best to use the PreparedStatement class when executing SQL statements at development time.

4. Verify the data: In the client IE use the Web page effect to verify the legality of user input data is not very large, must be in the acquisition of client data, the data for rigorous verification, developers do not pretend that users will only enter the legitimate data. Be sure to check for semicolons, quotes, parentheses, SQL keywords, and so on in your application. You can use regular expressions to perform complex pattern matching, which can be used to achieve good results.

XXX website Address Book View program needs to pass a Personid,personid can pass through the URL parameters, due to the location of the view program directly to obtain PersonID, did not do any validation of data legality, and PersonID is a string variable, The code to get PersonID is as follows:

if (GetParameter (req, "PersonID")!=null) {

Personid=getparameter (req, "PersonID"). Trim ();

}else{

Personid= "";

}

The dynamic SQL statements that are grouped into this program are as follows:

personsql= "select * from table name where userid=" +long.tostring (userid) + "and addrcontactid=" +personid;

Since the program does not check whether PersonID is an integer, the attacker assigns a value to PersonID to continue running the subsequent program logic if the attacker enters the following URL:

http://www.----------------------? personid=6414 or 2=2

The SQL statements are grouped as follows:

select * FROM table name where userid=1433620 and addrcontactid=6414 or 2=2

Prevention methods

SQL injection vulnerabilities can be described as "sink, Shan", which is very common on the web, usually because programmers don't understand the injection, or if the program is not filtered strictly, or if a parameter forgets to be checked. Here, I give you a function, instead of the request function in the ASP tutorial, you can inject say no into all of your SQL, functions as follows:

function Saferequest (paraname,paratype)
'---incoming parameters---
' Paraname: Parameter name-character type
' Paratype: Parameter Type-numeric (1 indicates that the above parameter is a number, and 0 indicates that the above parameter is a character)

Dim paravalue
Paravalue=request (Paraname)
If Paratype=1 Then
If not IsNumeric (Paravalue) Then
Response.Write "Parameter" & Paraname & must be a numeric type! "
Response.End
End If
Else
Paravalue=replace (Paravalue, "'", "" ")
End If
Saferequest=paravalue
End Function

Above function application

For arguments of type int, such as the ID of the article, you can first determine if it is an integer.

ID =trim (Request ("id"))
If id<> "" Then
If not IsNumeric (ID) Then
Response.Write "Please provide numeric parameters"
Response.End
End If
id = clng (ID)
Else
Response.Write "Please enter parameter ID"
Response.End
End If