Cleansing SQL Injection attempts in querystring

How can I cleanse any incoming querystrings like this one:

/default.asp?-='+AND+'b'> 'a&cmd=resetall

Is there a way to detect invalid characters and then remove them and redirect to the same page minus the invalid characters in the querystring?
JuniorBeeAsked:
Who is Participating?
 
Ryan ChongCommented:
try something like:

<%
      Dim q, tmp, isfound
      tmp = ""
      isfound = false
      findStr = "and"
      findStr = lcase(findStr)
      for each q in Request.QueryString
            if instr(lcase(Request.QueryString(q)), findStr) > 0 then
                  isfound = true
                  qvalue = Replace(Request.QueryString(q),findStr, "")
            else
                  qvalue = Request.QueryString(q)
            end if
            if tmp = "" then
                  tmp = "?" & q & "=" & qvalue
            else
                  tmp = tmp & "&" & q & "=" & qvalue
            end if            
            response.write q & ":" & Request.QueryString(q) & "<br>"            
      next
      if isfound then
            Response.Redirect(Request.ServerVariables("SCRIPT_NAME") & tmp)
      end if
%>


But to really prevent SQL Injection, you should check the compare key(s) once again if it's not EOF.

like:


...
SQLStr = "Select * from yourTable where userid = '" & replace(userid,"'","''") & "' and pwd = '" & replace(pwd,"'","''")& "' "
rs.open SQLStr, conn, 1, 1

if rs.eof = false then
   if rs("userid") <> userid or rs("pwd") <> pwd then  'SQL Injection check
         response.redirect "login.asp?err=1"
   end if
else
   response.redirect "login.asp?err=1"
end if

....

hope this helps
0
 
kevp75Commented:
i have a few...

<%
'Remove HTML Tags
function SafeHTML(ByVal pStrHTML)
      Dim lObjRegExp
      if VarType(pStrHTML) = vbNull Then Exit function
      if pStrHTML = "" Then Exit function
      Set lObjRegExp = New RegExp
      lObjRegExp.Global = True
      lObjRegExp.IgnoreCase = True
      lObjRegExp.Pattern = "<(/)?SCRIPT|META([^>]*)>"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "<$1SCRIPT$3>")
      lObjRegExp.Pattern = "<(/)?(LINK|IFRAME|FRAMESET|FRAME|APPLET|OBJECT)([^>]*)>"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "<$1LINK$3>")
      lObjRegExp.Pattern = "(<A[^>]+href\s?=\s?""?javascript:)[^""]*(""[^>]+>)"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "$1//protected$2")
      lObjRegExp.Pattern = "(<IMG[^>]+src\s?=\s?""?javascript:)[^""]*(""[^>]+>)"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "$1//protected$2")
      lObjRegExp.Pattern = "<([^>]*) on[^=\s]+\s?=\s?([^>]*)>"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "<$1$3>")
      lObjRegExp.Pattern = "<(/)?(SCRIPT|META|STYLE|INPUT|LINK|IFRAME|FRAMESET|FRAME|APPLET|OBJECT)([^>]*)>"
      pStrHTML = lObjRegExp.Replace(pStrHTML, "<$1_$2$3>")
      lObjRegExp.Pattern = "(Java|J|VB)(Script)"
      'pStrHTML = lObjRegExp.Replace(pStrHTML, "_$1$2")
      Set lObjRegExp = Nothing
      SafeHTML = pStrHTML
End function
'Valid Email email@domain.com
Function validEmail(strValue)
      validEmail = False'default
      Set objRegExp = New RegExp
            objRegExp.Pattern = "^((([a-z]|[0-9]|!|#|$|%|&|'|\*|\+|\-|/|=|\?|\^|_|`|\{|\||\}|~)+(\.([a-z]|[0-9]|!|#|$|%|&|'|\*|\+|\-|/|=|\?|\^|_|`|\{|\||\}|~)+)*)@((((([a-z]|[0-9])([a-z]|[0-9]|\-){0,61}([a-z]|[0-9])\.))*([a-z]|[0-9])([a-z]|[0-9]|\-){0,61}([a-z]|[0-9])\.(af|ax|al|dz|as|ad|ao|ai|aq|ag|ar|am|aw|au|at|az|bs|bh|bd|bb|by|be|bz|bj|bm|bt|bo|ba|bw|bv|br|io|bn|bg|bf|bi|kh|cm|ca|cv|ky|cf|td|cl|cn|cx|cc|co|km|cg|cd|ck|cr|ci|hr|cu|cy|cz|dk|dj|dm|do|ec|eg|sv|gq|er|ee|et|fk|fo|fj|fi|fr|gf|pf|tf|ga|gm|ge|de|gh|gi|gr|gl|gd|gp|gu|gt| gg|gn|gw|gy|ht|hm|va|hn|hk|hu|is|in|id|ir|iq|ie|im|il|it|jm|jp|je|jo|kz|ke|ki|kp|kr|kw|kg|la|lv|lb|ls|lr|ly|li|lt|lu|mo|mk|mg|mw|my|mv|ml|mt|mh|mq|mr|mu|yt|mx|fm|md|mc|mn|ms|ma|mz|mm|na|nr|np|nl|an|nc|nz|ni|ne|ng|nu|nf|mp|no|om|pk|pw|ps|pa|pg|py|pe|ph|pn|pl|pt|pr|qa|re|ro|ru|rw|sh|kn|lc|pm|vc|ws|sm|st|sa|sn|cs|sc|sl|sg|sk|si|sb|so|za|gs|es|lk|sd|sr|sj|sz|se|ch|sy|tw|tj|tz|th|tl|tg|tk|to|tt|tn|tr|tm|tc|tv|ug|ua|ae|gb|us|um|uy|uz|vu|ve|vn|vg|vi|wf|eh|ye|zm|zw|com|edu|gov|int|mil|net|org|biz|info|name|pro|aero|coop|museum|arpa))|(((([0-9]){1,3}\.){3}([0-9]){1,3}))|(\[((([0-9]){1,3}\.){3}([0-9]){1,3})\])))$"
                  validEmail = objRegExp.Test(strValue)
      Set objRegExp = Nothing
End Function
'Valid Characters A-Z/a-z/0-9
Function validChars(strValue)
      validChars = False'Default
      Set objRegExp = New RegExp
            objRegExp.Pattern = "^([a-zA-Z0-9])"
                  validChars = objRegExp.Test(strValue)
      set objRegExp = Nothing
End Function
'Valid Number
Function validNumber(strValue)
      validNumber = False'Default
      Set objRegExp = New RegExp
            objRegExp.Pattern = "^(?:-?(?:[0-9]+\.?|[0-9]*(?:\.[0-9]+){1}))$"
                  validNumber = objRegExp.Test(strValue)
      set objRegExp = Nothing
End Function
'Valid Phone
Function validPhone(strValue)
      validPhone = False'default
      Set objRegExp = New RegExp
            objRegExp.Pattern = "^((\+\d{1,3}(-| )?\(?\d\)?(-| )?\d{1,3})|(\(?\d{2,3}\)?))(-| )?(\d{3,4})(-| )?(\d{4})(( x| ext)\d{1,5}){0,1}$"
                  validPhone = objRegExp.Test(strValue)
      Set objRegExp = Nothing
End Function
'Valid GUID
Function validGUID(strValue)
      validGUID = False'default
      Set objRegExp = New RegExp
            objRegExp.Pattern = "^(?:{[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}})$"
                  validGUID = objRegExp.Test(strValue)
      Set objRegExp = Nothing
End Function
Function reqField(strFormField)
      reqField = True 'by default
      if strFormField = "" then reqField = False
      if isnull(strFormField) then reqField = False
      if len(strFormField) <= 0 then reqField = False
      if isempty(strFormField) then reqField = False
End Function
'SQL Injection Protection
function killC(strWords)
      if not ISNULL(strWords) then
            if instr(1,strWords,"'") > 0 then strWords = replace(strWords, "'", "&#39;")
            if instr(1,strWords,"@") > 0 then strWords = replace(strWords, "@", "&#64;")
            'if instr(1,strWords,";") > 0 then strWords = replace(strWords, ";", "&#59;")
            if instr(1,strWords,"|") > 0 then strWords = replace(strWords, "|", "&#124;")
            if instr(1,strWords,"*") > 0 then strWords = replace(strWords, "*", "&#42;")
            if instr(1,strWords,"--") > 0 then strWords = replace(strWords, "--", "&#45;")
            if instr(1,strWords,"=") > 0 then strWords = replace(strWords, "=", "&#61;")
            if instr(1,strWords,"(") > 0 then strWords = replace(strWords, "(", "&#40;")
            if instr(1,strWords,")") > 0 then strWords = replace(strWords, ")", "&#41;")
      end if
      killC = SafeHTML(strWords)
end function
%>

simply wrap your requests with killC to filter the invalid characters.  And use the valid(Blah) to return a boolean value on the match
0
 
kevp75Commented:
p.s.

Those are HackerSafe certified, BTW, a eCommerce site I developed uses them:
http://www.apenloversparadise.com/deault.asp
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
kevp75Commented:
sorry....that link is supposed to be http://www.apenloversparadise.com/default.asp
0
 
Mass Dot NetCommented:
Kevp75 --

I hope you don't mind if I plunder those functions -- that, and try to figure out how each of those regular expressions work.  Those look great...


-= DeathToSpam =-
0
 
kevp75Commented:
:)  Not a problem
0
 
JuniorBeeAuthor Commented:
kev, thanks for that...but how do I filter the actual querystring if someone goees directly to my site and types in a funky querystring.  I want to check it and cleanse it and redirect to the same page minus the funky characters.

:)
0
 
kevp75Commented:
precisely how I showed you.

I am not going to hold your hand, you've been on the scene long enough to know how a function works...
0
 
JuniorBeeAuthor Commented:
kev, that IS what the question asked:
/////////////////////////////////////////////////////////
Is there a way to detect invalid characters and then remove them and redirect to the same page minus the invalid characters in the querystring?
/////////////////////////////////////////////////////////

So forgive me for wanting an answer LOL.
0
 
kevp75Commented:
I have given you the answer, with the functions above.  I also told you how to use them.  It's real simple now....  killC(request.querystring("WHATEVERYOURQUERYSTRINGIS"))....as I already stated here "simply wrap your requests with killC to filter the invalid characters."

Than function will take out the bad characters and replace the with the ASCII equivelant.  The other functions I posted do validations, check for valid GUID, phone, number, etc...

*Comment edited by Netminder 22 July 2007*
0
 
kevp75Commented:
with the killC function there is no need to "do the redirect" as it already filters out the bad stauff...., hence eliminating the need to redirect back to the same page....
0
 
Ryan ChongCommented:
>>Is there a way to detect invalid characters and then remove them and redirect to the same page minus the invalid characters in the querystring?

Tried what I posted above?
0
 
JuniorBeeAuthor Commented:
I did ryancys and thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.