SQL Injection defense method-Programmer

Source: Neeaos Blog

Author: NB Alliance-Xiaozhu
Ps: old things, no other meaning. I miss 54nb, which has promoted network security!
SQL injection is increasingly exploited to intrude into websites. Some WEB programmers are also paying attention to this knowledge. However, due to the lack of knowledge about the intrusion method, some characters are missing during filtering, this vulnerability may cause security vulnerabilities. You may also want to reject some legitimate user requests. If you want to input an Im a boy, you will be scolded, will he be willing to visit your website again?

Next, I will introduce the SQL Injection defense methods from the perspective of the program. First, let's take a look at the three simplest SQL statements.
1. SQL = "select * from Users where UserID =" & Request ("ID ")
2. SQL = "select * from Users where UserID =" & Request ("ID ")&""
3. SQL = "select * from Users where UserName like %" & Request ("Name") & "%"



First, the parameter is numeric, which is obvious. In the second sentence, if the field UserID is of the int type, some people may not be clear. In fact, to distinguish between numeric and numeric parameters, you only need to check whether there are single quotation marks on both sides of the SQL statement parameters. Obviously, the first sentence does not have single quotation marks, and the second and third sentences have single quotation marks, which is Numeric.

For numeric variables, input parameters are directly appended to SQL statements for execution. Because the parameters are numeric, it is safe to use isNumeric, I tried to disconnect a parameter like this, but the results all failed.

For struct variables, all input parameters are used as constants. For example, if you input 1 and 1 = 1, the SQL statement is UserID = 1 and 1 = 1, the value in the single quotation mark defining range is always a constant. To break this range, the only character is the defined character: single quotation mark. Therefore, it is completely safe to filter the number of a variable of the primary type. As for how to filter, it is best to replace a single quotation mark with two single quotation marks, because the SQL statement stipulates that constants are represented in constants like this, to enclose single quotes in a constant, use two single quotes instead. In this way, you can maintain the original appearance of user input and ensure program security.

The following are two functions. You can Copy them and call them directly.
---------------------------------------------------------------
Anti-injection function ReqNum/ReqStr of nb Consortium
---------------------------------------------------------------
Function ReqNum (StrName)
ReqNum = Request (StrName)
If Not isNumeric (ReqNum) then
Response. Write "parameter must be numeric! "
Response. End
End if
End Function

Function ReqStr (StrName)
ReqStr = Replace (Request (StrName ),"","")
End Function



The preceding three SQL statements describe the calling method:
1. SQL = "select * from Users where UserID =" & ReqNum ("ID ")
2. SQL = "select * from Users where UserID =" & ReqStr ("ID ")&""
3. SQL = "select * from Users where UserName like %" & ReqStr ("Name") & "%"


Repeat one point: the above method is absolutely applicable and secure for SQL Server databases, Access databases, and other databases, but note that SQL server stored procedures are an exception, in this case, you must replace single quotes with four single quotes to ensure security.

In addition, NB consortium-pants once wrote an article on SQL Server Security Settings. When the program has vulnerabilities, this article can allow intruders or have as few permissions and data as possible, this article has been published at www.54NB.com. If you are interested, go and have a look.