Re: SQL Injection Code Help
- From: "jbiros" <jbiros@xxxxxxxxxx>
- Date: 19 Jun 2006 07:02:12 -0700
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.
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.
' 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
' 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.
' Normal Character Text
If StrComp("2",argType) = 0 Then
' Loop through each character, checking if it is what you expect it
' Replace non-expected characters with empty string.
' 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.
Hope this helps,