?
Solved

Cleansing SQL Injection attempts in querystring

Posted on 2007-07-19
14
Medium Priority
?
586 Views
Last Modified: 2008-05-28
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?
0
Comment
Question by:JuniorBee
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 19527958
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19528687
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19528692
p.s.

Those are HackerSafe certified, BTW, a eCommerce site I developed uses them:
http://www.apenloversparadise.com/deault.asp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Expert Comment

by:kevp75
ID: 19528695
sorry....that link is supposed to be http://www.apenloversparadise.com/default.asp
0
 
LVL 9

Expert Comment

by:deathtospam
ID: 19531020
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19531051
:)  Not a problem
0
 

Author Comment

by:JuniorBee
ID: 19542966
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19543080
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
 

Author Comment

by:JuniorBee
ID: 19544178
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19544457
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
 
LVL 25

Expert Comment

by:kevp75
ID: 19544463
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
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 19544475
>>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
 

Author Comment

by:JuniorBee
ID: 19544817
I did ryancys and thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question