SQL Injection vulnerablities in ASP script

Question

Right now I have a sample ASP script below:

    <%Set objConn = CreateObject("ADODB.Connection")
    objConn.Open Application("WebUsersConnection")
    sSQL="SELECT * FROM Users where Username=? & Request("user") & _"?and Password=? & Request("pwd") & "?
    Set RS = objConn.Execute(sSQL)
    If RS.EOF then
       Response.Redirect("login.asp?msg=Invalid Login")
    Else
       Session.Authorized = True
    Set RS = nothing
    Set objConn = nothing Response.Redirect("mainpage.asp")
    End If%>

May I know what kind of SQL Injection will be caused by this script? What's the result of the execution, and any sample SQL that can inject into application with the above script? It's extracted from the paper. Thanks

I don't think it's correctly extracted, your quoting when setting sSQL looks a bit off. — Joachim Isaksson
– Joachim Isaksson, Commented Jun 26, 2013 at 9:12
@JoachimIsaksson, Can you please give me some hint on where's the problem? Thanks — Cheong
– Cheong, Commented Jun 26, 2013 at 9:22

Passerby · Accepted Answer · 2013-06-26 10:02:36Z

One of the problem of directly writing user input into a SQL query:

sSQL="SELECT * FROM Users where Username='" & Request("user") & "' and Password='" & Request("pwd") & "'"

is that if user submitted

username' OR 1=1 --

which makes your query eventually looks like this:

SELECT * FROM Users where Username='username' OR 1=1 --' and Password=''

depending on your database driver, this may return at least one row, making your script think this is a valid user (or even an admin, if defaultly sort by id ascending).

You can use ADODB.Command object to prepare SQL query and bind value to placeholder.

Something like this:

sSQL="SELECT * FROM Users where Username=? and Password=?"
set objCommand=CreateObject("ADODB.Command")
objCommand.Prepared = true
objCommand.ActiveConnection = objConn
objCommand.CommandText = sSQL
objCommand.Parameters.Append objCommand.CreateParameter("name",200,1,50,Request("user"))
objCommand.Parameters.Append objCommand.CreateParameter("password",200,1,64,Request("pwd"))
objCommand.Execute

MSDN doesn't seem to clear on whether ADODB.Command will actually treat query and value separately, but I guess for "modern" database driver, this is supported. If I remember correctly, this works on Oracle OLEDB database driver.

MSDN on ADODB.Command properties and methods

Svbaker · Accepted Answer · 2013-06-26 21:19:38Z

0

I have used the following two steps to protect against SQL injection with ASP for years on high traffic sites and never had an issue yet.

For each char datatype, make sure you replace any apostrophes with double apostrophes like this:

sql = "SELECT * FROM users WHERE "
sql = sql & "Username = '" & Replace(Request("user"), "'", "''") & "' "
sql = sql & "AND Password = '" & Replace(Request("pwd"), "'", "''") & "'"

For any number (non char) fields, verify that the input isNumeric first, otherwise ignore it, or return an error.

answered Jun 26, 2013 at 21:19

Svbaker

7063 silver badges4 bronze badges

Comments

S.Krishna · Accepted Answer · 2013-06-27 14:23:34Z

It is always good to use Regular Expressions to check for characters in the input (querystring / form variables / etc...) before you pass them onto your database for processing. The check should be done to see if all the characters in the input are within the allowed characters (whitelist check).

Function ReplaceRegEx(str, pattern)
set pw = new regexp
pw.global = true
pw.pattern = pattern
replaced = pw.replace(str, "") 'Find the pattern and store it in "replaced"
ReplaceRegEx = replace(str,replaced,"") 'Replace with blank.
End Function

'below is a sample. you can create others as needed
Function UserNameCheck(x)
UserNameCheck = ReplaceRegEx(x,"^[a-zA-Z_-]+$")
End Function

And this is how you call it in your ASP page:

fld_UserName=UserNameCheck(fld_UserName)
if fld_UserName="" then 
    'You can probably define the below steps as function and call it...
    response.write "One or more parameters contains invalid characters"
    response.write "processing stopped"
    response.end
end if

Collectives™ on Stack Overflow

SQL Injection vulnerablities in ASP script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related