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,



Relevant Pages

  • Re: Trouble reading fields containing special characters
    ... I double any possible single quote characters in the string. ... Any double single quotes in a string (all SQL strings are enclosed by single ...
  • Re: Using part of a field
    ... See the article "Finding and replacing characters using wildcards" at: ... AS400 SQL may have a different way of doing ... called i.dsn, containing the followiing text: ... sort of thing in a query. ...
  • RE: Collation settings for ASCII code page
    ... The string sort and comparison behavior for non-Unicode data ... characters you can store without using Unicode. ... There is no such thing as an "ASCII code page" in SQL Server. ...
  • Re: sendStringParameterAsUnicode: How to insert unicode data corre
    ... We are using SAP XI and connecting it to SQL Server 2005 using JDBC. ... The destination columns are nvarchar and store unicode data correctly. ... is unable to display the characters you have. ... Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1 ...
  • Re: simple (?) question about having ";" in the password
    ... whatever characters they damn well please. ... Attempting to escape SQL strings is a non-starter. ... themselves "do I need to use a prepared statement here ?". ... but it could happen) since its going to be added to the SQL string. ...