Preventing SQL injection

Published: 2008-06-23. Last Updated: 2008-06-25 12:56:40 UTC
by donald smith (Version: 2)

Here is a function that a reader wrote that does sanitizing of input for all inputted data.
I am not an asp function programmer so I can not claim that it is complete or correct
but it does appears to work.

This was written by Brian Erman.
Brian spent many hours testing and modifying to make it work. It has stopped
the insertion of bad data into their database. They have been using it now for
over 1 month and have not had a single SQL injection since they added this function.

It eliminates any string that contains the word "declare" and shoots them
off to Google. It also creates a new string from the old string character by
character into the new string. Not by moving the original character into the string.

It also replaces known keywords (i.e. insert, delete, etc...) that may cause
problems within SQL.

,,,,,,Begin Function,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Function cleanchars(str)
'this gets put in the program that you want to cleans the data with.
'fname = cleanchars(trim(Request("xxxxx"))) <<<Function Call<<<<<<
'here is the call for the function
'Author:
'President Brian Erman
'Nopork Motorsports, Inc.
'2585 Hamner Ave,
'Norco CA 92860
'
'This is licensed under the creative commons attribution-noncommercial 3.0 framework
'http://creativecommons.org/licenses/by-nc/3.0/us/
'
'This function assumes you are using CDO as your object for sending mail, if
'you have CDONTS on your server, simply change the CDO to CDONTS and it
'should process exactly the same.
'
'
newstr = ""

if InStr(str, "'") > 0 then
    str = ""
    end if

if instr(str, "DECLARE") > 0 then
    newstr = ""
    Set Mailer = Server.CreateObject("CDO.Message")
    Mailer.From = "Email_From"
    Mailer.To = "Email_To"
    Mailer.Subject = "Your_Domain Hacking Attempt"
    msg = Date & VbCrLf & VbCrLf
    msg = msg & "Hacking Blocked, but check the data" & VbCrLf & VbCrLf
    msg = msg & "STR: " & str & " char " & char & VbCrLf & VbCrLf
    msg = msg & "Here is the IP " & Request.ServerVariables("REMOTE_ADDR") & VbCrLf & VbCrLf
    msg = msg & "Web Page " & Request.ServerVariables("URL") & VbCrLf & VbCrLf
    msg = msg & "Host " & Request.ServerVariables("HOST") & VbCrLf & VbCrLf
    msg = msg & "Length of String " & len(str) & vbcrlf & vbcrlf
    Mailer.TextBody = msg
    Mailer.Send
    Set Mailer = nothing
    Response.Redirect("http://www.google.com/")
end if

For ii = 1 to Len(str)
        char = Mid(str,ii,1)
Select Case char
        case " ", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j",
"k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y",
"z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",
"O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2",
"3", "4", "5", "6", "7", "8", "9", "@", ".", "-", "_", "/", "&"
        newstr = newstr & char
Case Else

    Set Mailer = Server.CreateObject("CDO.Message")
    Mailer.From = "Email_From"
    Mailer.To = "Email_To"
    Mailer.Subject = "Your_Domain Hacking Attempt"
    msg = Date & VbCrLf & VbCrLf
    msg = msg & "Hacking Blocked, but check the data" & VbCrLf & VbCrLf
    msg = msg & "STR: " & str & " char " & char & VbCrLf & VbCrLf
    msg = msg & "Here is the IP " & Request.ServerVariables("REMOTE_ADDR") & VbCrLf & VbCrLf
    msg = msg & "Web Page " & Request.ServerVariables("URL") & VbCrLf & VbCrLf
    msg = msg & "Host " & Request.ServerVariables("HOST") & VbCrLf & VbCrLf
    msg = msg & "Length of String " & len(str) & vbcrlf & vbcrlf
    Mailer.TextBody = msg
    Mailer.Send
    Set Mailer = nothing

End Select
Next

if len(str) > 350 then
    newstr = ""
    Response.Redirect("http://www.Your_Domain/")
    end if

if instr(str, "DECLARE") > 0 then
    newstr = ""
    Response.Redirect("http://www.Your_Domain/")
    end if


if instr(str, "declare") > 0 then
    Response.Redirect("http://www.Your_Domain/")
    end if

if instr(str, "www") > 0 then
    Response.Redirect("http://www.Your_Domain/")
    end if

    newstr = Replace(lcase(newstr), " or ", "")
    newstr = Replace(lcase(newstr), " and ", "")
    newstr = Replace(lcase(newstr), " from ", "")
    newstr = Replace(lcase(newstr), " into ", "")
    newstr = Replace(lcase(newstr), "insert", "")
    newstr = Replace(lcase(newstr), "update", "")
    newstr = Replace(lcase(newstr), "set", "")
    newstr = Replace(lcase(newstr), "where", "")
    newstr = Replace(lcase(newstr), "drop", "")
    newstr = Replace(lcase(newstr), "values", "")
    newstr = Replace(lcase(newstr), "null", "")
    newstr = Replace(lcase(newstr), "http", "")
    newstr = Replace(lcase(newstr), "js", "")
    newstr = Replace(lcase(newstr), "declare", "")
    newstr = Replace(lcase(newstr), "script", "")
    newstr = Replace(lcase(newstr), "xp_", "")
    newstr = Replace(lcase(newstr), "CRLF", "")
    newstr = Replace(lcase(newstr), "%3A", "")'; HEX
    newstr = Replace(lcase(newstr), "%3B", "")':
    newstr = Replace(lcase(newstr), "%3C", "")'<
    newstr = Replace(lcase(newstr), "%3D", "")'=
    newstr = Replace(lcase(newstr), "%3E", "")'>
    newstr = Replace(lcase(newstr), "%3F", "")'?
    newstr = Replace(lcase(newstr), """, "")'"
    newstr = replace(lcase(newstr), "&", "")'&
    newstr = replace(lcase(newstr), "<", "")'<
    newstr = replace(lcase(newstr), ">", "")'&
    newstr = replace(lcase(newstr), "exec", "")'&
    newstr = replace(lcase(newstr), "onvarchar", "")'&
        newstr = replace(lcase(newstr), "set", "")'&
    newstr = replace(lcase(newstr), " cast ", "")'&
    newstr = replace(lcase(newstr), "00100111", "")'
    newstr = replace(lcase(newstr), "00100010", "")';
    newstr = replace(lcase(newstr), "00111100", "")'<
    newstr = replace(lcase(newstr), "select", "")'<
    newstr = replace(lcase(newstr), "0x", "")'<
    newstr = replace(lcase(newstr), "exe", "")'<
    newstr = replace(lcase(newstr), "delete", "")'<
    newstr = replace(lcase(newstr), "go ", "")'<
    newstr = replace(lcase(newstr), "create", "")'<
    newstr = replace(lcase(newstr), "convert", "")'<

    cleanchars = newstr

    End Function
,,,,,,End Function,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Additionally several sites have published documents describing how to prevent SQL injection.
Open Web Application Security Project:
http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java#Defence_Strategy

Canadian Cyber Incident Response Centre:
http://www.publicsafety.gc.ca/prg/em/ccirc/_fl/tr08-001-Alleviating-the-threat-of-mass-sql-injection-attacks-eng.pdf