• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

SQL Injection protection in ASP

I have a function that I want to call to protect my querystring parameters and Request.Form.

The function ::

<%
Function CleanURL(QueryParameter)
dim badChars
dim newChars

badChars = array("select", "drop", ";", "--", "insert",
"delete", "update","'","truncate","between","like","having","union")
newChars = lcase(QueryParameter)

for i = 0 to uBound(badChars)
newChars = replace(newChars, badChars(i), "")
next

CleanURL = newChars
end function
%>

The sql select to protect is

sqlStr2 = "SELECT p.id, p.reference, p.town, p.quote, p.description, p.sleeps_text, p.bedrooms_text, p.sleeps_baby, r.id AS region_id, r.label AS region, s.label AS subregion, r.image_path as RegionMap FROM property p, region r, subregion s WHERE p.id = '"& request.querystring("ObjectID") &"' AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"

where i need to call the function at '"& request.querystring("ObjectID") &"'


How do I insert a call to function at this point and how do I add this to my form submissions to protect the Request.Form

as below

strName = Request.Form("requiredname")
strInitials = Request.Form("initials")
strTitle_Position = Request.Form("requiredtitle")
0
Andrew Wilkinson
Asked:
Andrew Wilkinson
  • 4
  • 3
  • 3
  • +1
1 Solution
 
snavebelacCommented:
CleanURL(sqlstr2)

CleanURL(Request.Form("requiredname")

etc...

HTH

C
0
 
Andrew WilkinsonAuthor Commented:
Nearly worked but strips out all the text required for the sqlstr2.

I need it to only work on the '"& request.querystring("ObjectID") &"' part of the sql..

cheers
0
 
snavebelacCommented:
my apologies

you can just do

CleanURL(request.querystring("ObjectID"))

You can use in the creation of strsql2

HTH

C
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Andrew WilkinsonAuthor Commented:
so I would incorporate it like this

sqlStr2 = "SELECT p.id, p.reference, p.town, p.quote, p.description, p.sleeps_text, p.bedrooms_text, p.sleeps_baby, r.id AS region_id, r.label AS region, s.label AS subregion, r.image_path as RegionMap FROM property p, region r, subregion s WHERE p.id = '"& CleanURL(request.querystring("ObjectID")) &"' AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"

or

sqlStr2 = "SELECT p.id, p.reference, p.town, p.quote, p.description, p.sleeps_text, p.bedrooms_text, p.sleeps_baby, r.id AS region_id, r.label AS region, s.label AS subregion, r.image_path as RegionMap FROM property p, region r, subregion s WHERE p.id =CleanURL('"& request.querystring("ObjectID") &"') AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"

??
0
 
Andrew WilkinsonAuthor Commented:
or do I drop the '"& completely, sorry
0
 
snavebelacCommented:
the first one - no need to apologise
0
 
rdivilbissCommented:
There is a much better way to do ths.

Any filtering routine can defeated.  It is simply impossible to account for every possible combination of various character encoding schemes.  When you build an SQL statement on the fly you are sending user input to the database engine to be executed.  The best way to minimize the risks of SQL injection is to never send input to the database engine to be executed.

By using a stored procedure or parameterized SQL, your input is only a parameter and the database engine know it is not to be executed.  Therefore you do not have to worry about reserved words or special characters.  For example, your ObjectID is only going to be used to match the objectIDs stored in your database.  Even if your parameter was unfiltered input and said '; drop all' it will never execte the drop command, it will only look for a value that matches ; drop all, which of course it would not find.

Here is an example of using a parameterized SQL statement to execute your query.  It all but eliminates SQL injection risks.

'---------------- the example ----------
<!--#include virtual="include/paramSQL.asp"-->
Dim objID
objID = getQueryStringFiltered("ObjectID")
call selectPropertyInfo(objID)
'* db_rs is global and now has your results


'--------------- the subroutine to get your results ------------

sub selectPropertyInfo(pObjectID)
      Dim cmdText
      
      cmdText = "SELECT p.id, p.reference, p.town, p.quote, p.description, p.sleeps_text, p.bedrooms_text, p.sleeps_baby, r.id AS region_id, r.label AS region, s.label AS subregion, r.image_path as RegionMap FROM property p, region r, subregion s WHERE p.id = @ObjectID AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"
      
      '* Add the new row
      openCommand Application("examplemembers"),"addNewUser"

      '* assuming your ObjectID is text 50 or varChar 50...make changes as necessary.
      addParam "@ObjectID",adVarChar,adParamInput,CLng(50),pObjectID,"selectPropertyInfo 1"


      getRS db_rs, cmdText, "selectPropertyInfo"

      '* clean up
      closeCommand
end sub


'-------------- a helper function and probably not needed, but paranoid is good
function getQueryStringFiltered(fldName)
      '**********************************************
      '* PURPOSE: Retrieves a posted form field and
      '*   ensures it contains only safe characters
      '*   as defined by a regular expression.
      '* INPUT: The field name
      '* OUTPUT: The field value if it contains only
      '*   allowed characters, otherwise an empty
      '*   string.
      '**********************************************
      Dim tmp, strIn, regEx, pattern, matches, match
      tmp = ""
      strIn = request.querystring(fldName)
      Set regEx = New RegExp
      '**********************************************
      '* The following regular expression matches any
      '* combination of non whitespace characters
      '* (a-z, A-Z, 0-9).
      '**********************************************
      regEx.pattern = "[a..z,A..Z,0..9]{1,255}"
      regEx.IgnoreCase = false
      regEx.Global = True
      Set Matches = regEx.Execute(strIn)
      for each match in matches
            tmp = tmp & match
      next
      if tmp = strIn then
            getQueryStringFiltered = tmp
      end if
end function


'---------- a library to simplify using parameterized SQL ------------
<!--#include virtual="include/adovbs.inc"-->
<%
Dim db_cmd, db_conn, db_rs, db_cmdText, numAffected

Sub openCommand(pConnStr,pSource)
      if pConnStr="" then
            Response.Write "There was an openCommand error: "& pSource &" 1.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
      on error resume next
      
      Set db_cmd = Server.CreateObject("ADODB.Command")
      if err then
            Response.Write "There was an openCommand error: "& pSource &" 2.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
      
      Set db_conn = Server.CreateObject("ADODB.Connection")
      if err then
            Response.Write "There was an openCommand error: "& pSource &" 3.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
      db_conn.open pConnStr

      if err then
            Response.Write "There was an openCommand error: "& pSource &" 4.<br>" & vbLF
            '* Developer Only *
            Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if

      Set db_cmd.ActiveConnection = db_conn
      if err then
            Response.Write "There was an openCommand error: "& pSource &" 5.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
End Sub


Sub closeCommand()
      on error resume next
      if IsObject(db_cmd) then
            Set db_cmd = nothing
      end if
      if IsObject(db_conn) then
            if db_conn.open then
                  db_conn.close
            end if      
            Set db_conn = nothing
      end if      
End Sub


Sub addParam(pName,pType,pDir,pSize,pValue,pSource)
      Dim param
      on error resume next
      Set param = db_cmd.CreateParameter(pname,ptype,pdir,psize,pvalue)
      if err then
            Response.Write "There was an error creating a database parameter: "& pSource &" 1.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
      
      db_cmd.Parameters.Append param
      if err then
            Response.Write "There was an error appending the database parameter: "& pSource &" 2.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      end if
      
      Set param = nothing
End Sub


Sub getRS(db_rs,db_cmdText,pSource)
      on error resume next
      Set db_rs = Server.CreateObject("ADODB.Recordset")
      if err then
            Response.Write "There was an error: "&pSource&" 1.<br>" & vbLF
            '* Developer Only *
            Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End      
      else
            db_cmd.CommandText = db_cmdText
            if err then
                  Response.Write "There was an error: "&pSource&" 2.<br>" & vbLF
                  '* Developer Only *
                  Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
                  Response.End
            else
                  ' db_rs.Open db_cmd, adOpenStatic, adLockOptimistic
                  db_rs.Open db_cmd
                  if err then
                        Response.Write "There was an error "&pSource&" 3.<br>" & vbLF
                        '* Developer Only *
                        Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
                        Response.End
                  end if
            end if
      end if                        
End Sub

Sub execCmd(db_cmdText)
      on error resume next
      db_cmd.CommandText = db_cmdText
      if err then
            Response.Write "There was an error setting command text. execCmd 1.<br>" & vbLF
            '* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
            Response.End
      else
            db_cmd.Execute numAffected,, adExecuteNoRecords
            if err then
                  Response.Write "There was an error executing command. execCmd 2.<br>" & vbLF
                  '* Developer Only *
                  Response.Write err.number & " " & err.description & " " & err.source & " Command: " &db_cmdText& "<br>" & vbLF
                  Response.End
            end if
      end if                        
End Sub

Sub closeRS
      on error resume next
      if IsObject(db_rs) then
            db_rs.close
      end if
End Sub
%>
0
 
Andrew WilkinsonAuthor Commented:
Like the answer but means rewriting lots of code and database is MySql so no stored procedures yet!

I generally do not add to database, but just select a number of property records to display on each page or just add user requests to form fields and then fire these off by email. SQL does get complicated because it selects several tables to check availability, regions and descriptions etc..

If there is any way of incorporating the above would love to know as some of your code is a bit above my station..

Thanks
0
 
rdivilbissCommented:
That is paramerterized SQL which will work for mySQL, MS Acess and MS SQL.  I think Oracle would require a slight mod.

Since the library is just an include, the only mod would be replacing your on the fly SQL with a small subroutine as indicated.  Not really necessary to understand the library code.

What you have to remember about on the fly SQL is everything is executed, so if someone can craft an entry to terminate your statement, and add a second command, it will execute as well.

For example:

sqlStr2 = "SELECT p.id, p.reference, p.town, p.quote, p.description, p.sleeps_text, p.bedrooms_text, p.sleeps_baby, r.id AS region_id, r.label AS region, s.label AS subregion, r.image_path as RegionMap FROM property p, region r, subregion s WHERE p.id = '"& request.querystring("ObjectID") &"' AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"

Since ObjectID is passed on the URL as part of the querystring, if I change the URL;

objectid=abcd';delete%20*%20from%20property;--

Your SQL will become;

WHERE p.id = 'abcd'; delete * from property';--' AND p.subregion_id = s.id AND s.region_id = r.id AND p.status = 1"

Note your statement is completed, the delete statement will execute and the remainder is commented out with --

Even select statement are dangerous.

Would a hacker know the table name?  Maybe not, but a few quesses may do it, and there are more dangerous things that can be done without that knowledge.

So, you say, I'll filter out all the bad characters.  Same problem as with the key words... those characters can be encoded so many different ways, it is almost impossible.

0
 
TovarishCommented:
Here's what I used successfully for quite some time on ASP coupled with MySQL.
Examples and code below, and yes, for now with MySQL get in the habit of always escaping where you print user entered data or form a dynamic sql statement.


!!! NEVER EVER INSERT A STRING INTO A HTML, JAVASCRIPT OR EVEN SQL CODE WITHOUT ESCAPING IT PROPERLY!!!

' HTMLescape : if the variable is to be showed on the page
'      <td>< %=HTMLescape( variable )% ></td>

' TAGescape : if its used to set the value of a field
'      <input type="text" name="Foo" value="< %=TAGescape( variable )% >">

' JSescape : if it's used as a parameter to a JavaScript function
'      <a href="JavaScript:foo( '< %=JSescape(variable)% >' );">

' URLescape : if it's to become part of a query string
'      <a href="Foo.asp?name=< %=URLescape(variable)% >"

' SQLescape : if it's to become part of an SQL query
'      sql = "select * from Table where name = '" & SQLescape( name) & "'"

' SQLquote : if it's to become part of an SQL query, adds the quotes aroung the value, works correct with NULLs
'      sql = "select * from Table where name = " & SQLquote( name)

Function HTMLescape (s)
      if isEmpty(s) or isNull(s) then
            HTMLescape = ""
      else
            HTMLescape = Server.HTMLEncode(s)
      end if
End Function

Function TAGescape (s)
      if isEmpty(s) or isNull(s) then
            TAGescape = ""
      else
            TAGescape = Replace(Replace(Server.HTMLEncode(s),"""", "&dblquote;" ), "'", "&#39;")
      end if
End Function

Function URLescape (s)
      if isEmpty(s) or isNull(s) then
            URLescape = ""
      else
            URLescape = Replace(Replace(Server.URLEncode(s),"'","%27"), """", "%22" )
      end if
End Function

Function JSescape (s)
      if isEmpty(s) or isNull(s) then
            JSescape = ""
      else
            JSescape = TAGescape( Replace( Replace( Replace( s, "\", "\\"), """", "\""" ) , "'", "\'" ))
      end if
End Function

Function SQLescape (s)
      if isEmpty(s) or isNull(s) then
            SQLescape = ""
      else
            SQLescape = Replace( s, "'", "''")
      end if
End Function

Function SQLquote (s)
      if isEmpty(s) or isNull(s) then
            SQLquote = "NULL"
      else
            SQLquote = "'" & Replace( s, "'", "''") & "'"
      end if
End Function
0
 
rdivilbissCommented:
Tovarish,

You don't have to escape with parameterized SQL.  The input will never be executed.  Plus you no longer have problems handling strings like "O'Brain".  

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now