Re: SQL Injection Code Help

The most important characters to get rid of are the single quote and
the semi-colon. Simply using stored procedures will not prevent SQL
injection. I would start by getting rid of any single quotes and
semi-colons. If your application connects to SQL server with a SQL
user that is a member of the sysadmin role, SQL injection could be very
dangerous to your app and network. If that is the case, I would also
replace the following string values as well: "xp_" & "sp_".

As far as a quick solution, I would create a single, standard ASP
include file that contains a single function...for example, CleanValue.
It should, at a minimum, accept two parameters (argValue and argType).
Any time you get a value from the user (i.e., Request.Form or
Request.QueryString), I would wrap this function around it.

For example...
myNumericValue = CleanValue(Request.QueryString("numvalue"),1)
myStringValue = CleanValue(Request.QueryString("strvalue"),2)

Function CleanValue(argValue, argType)

' Validate the type
If StrComp("1",argType) <> 0 And StrComp("2",argType) <> 0 Then
' Perform error routine or redirect to error page.
End If

' Trim the value
CleanValue = Trim(argValue)

' Replace globally disallowed characters:
CleanValue = Replace(CleanValue,"'","") ' single quote
CleanValue = Replace(CleanValue,";","") ' semi-colon
CleanValue = Replace(CleanValue,"xp_","") ' extended stored procedures
CleanValue = Replace(CleanValue,"sp_","") ' system stored procedure

' Check for attack attempts (optional)
If Len(CleanValue) <> Len(argValue) Then
' Perform error routine or redirect to error page
End If

' Now make sure the value is the expected type

' Whole Numbers only
If StrComp("1",argType) = 0 Then
' Loop through each character, checking if it is numeric.
' If it is not numeric, replace it with empty string.
End If

' Normal Character Text
If StrComp("2",argType) = 0 Then
' Loop through each character, checking if it is what you expect it
to be.
' Replace non-expected characters with empty string.
End If

' Datetime

' Date

' Decimal Number 0123456789.

' Negative/Positive Decimal Number 0123456789.+-

' After replacing bad characters, you have the choice of comparing the
length to the original input parameter argValue. If they are
different, you can choose to allow it or throw an error. Depending on
your system, you may choose either. If a lot of user input text fields
exist in your system, you may not want to throw an error. You might
want to just allow the cleaned text to be used in the SQL statement.

End Function

Hope this helps,