Preventing SQL injection

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

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), "&quot;", "")'"
    newstr = replace(lcase(newstr), "&amp;", "")'&
    newstr = replace(lcase(newstr), "&lt;", "")'<
    newstr = replace(lcase(newstr), "&gt;", "")'&
    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

UPDATE: Jason Lam wrote two additional diaries that have additional information on preventing SQL injection.

http://isc.sans.org/diary.html?storyid=4621

http://isc.sans.org/diary.html?storyid=4610

1 comment(s)

SSH scans, source port 80?

Published: 2008-06-23
Last Updated: 2008-06-23 21:13:28 UTC
by Joel Esler (Version: 1)
0 comment(s)

Got an email today from a reader named Justin (thank you Justin) who asks us if we have seen alot of SSH scans with a source port of 80 before.  Of course, the answer is yes, but only in test cases!

I've never actually seen this take place on the internet, (well, yes, I have, but very very rarely), and of course I can cause it with certain nmap settings.  But this kind of scanning isn't commonplace, afaik, to an automated tool or script kiddie run. 

Any information that anyone could provide so that we can help out Justin, and of course the rest of the readers of the Internet Storm Center would be much appreciated.  Please write in via that Contact link at the top of our home page.  Thank you.

--

Joel Esler

http://www.joelesler.net

Keywords:
0 comment(s)

Comments


Diary Archives