Secure ado.net encoding in asp.net (i)

Ado|asp.net| Security | Secure ado.net encoding in coded asp.net (i)
Ensuring application security includes writing secure code. The code must expose only the information and functionality required by the client code. A common attack associated with ado.net is the SQL insertion attack, which determines the private database information from the exception that the application returns.

Risk:
In a SQL insertion attack, an attacker inserts additional SQL statements from your command that perform processing at the data source. These commands can not only modify or destroy information in the data source location, but also retrieve your private information. The code that concatenates the command string with the external input is susceptible to SQL insertion attacks. For example, the following code is vulnerable to SQL insertion attacks.

[Visual Basic]

Dim CustID as String = Getcustomerid ()

Dim selectstring as String = "SELECT * from Customers WHERE CustomerID =" & CustID

Dim cmd as SqlCommand = New SqlCommand (selectstring, conn)
Conn. Open ()
Dim myreader as SqlDataReader = cmd. ExecuteReader ()
Myreader.close ()
Conn. Close ()
An attacker could enter a value of "1;drop TABLE Customers" for the CustomerID to be queried. This causes the following command to be executed for this query.

SELECT * from Customers WHERE CustomerID = 1;drop TABLE Customers
To prevent SQL insertion attacks, verify the input from the external source and pass the column values as parameters instead of concatenating the values to create the SQL statement.

Workaround One:
Using regular expressions
You can use regular expressions to verify that the input matches a particular format. The. NET Framework provides a Regex object to validate values based on regular expressions. For example, the following code is used to ensure an alphabetic string with a value of 5 characters.

[Visual Basic]

Public Static Function Validate (instring as String) as Boolean

Dim r as Regex = New regex ("^[a-za-z0-9]{5}$")

Return R.ismatch (instring)

End Function

Workaround Two:
Using Parameters
Parameters provide an efficient way to organize the values that are passed with the SQL statement, and the values passed to the stored procedure. In addition, you can prevent parameters from being attacked by SQL insertion by ensuring that values received from external sources are passed only as values, rather than as part of the SQL statement. Therefore, SQL commands inserted into values are not executed at the data source. Instead, the values passed are only treated as parameter values. The following code shows an example of using a parameter to pass a value.

[Visual Basic]

Dim CustID as String = Getcustomerid ()

Dim selectstring as String = "SELECT * from Customers WHERE CustomerID = @CustomerID"

Dim cmd as SqlCommand = New SqlCommand (selectstring, conn)

Cmd. Parameters.Add ("@CustomerID", SqlDbType.VarChar, 5). Value = CustID

Conn. Open ()

Dim myreader as SqlDataReader = cmd. ExecuteReader ()

Myreader.close ()

Conn. Close ()