RE: SQL Injection Basics

["Logan F.D. Greenlee" <lgreenlee () ciretose net>]

Robert is quite right.
        The best way to prevent SQL Injection attacks in ASP is to use a
stored procedure and a Command object to call it. Specifically the
syntax would look something like the following (it isn't tested):

        set conn=Server.CreateObject("ADODB.Connection")
        conn.ConnectionString = "Provider"
        set comm=Server.CreateObject("ADODB.Command")
        comm.CommandText="spTest"
        comm.CommandType=adCmdStoredProc
        set parameters = comm.Parameters
        parameters.Append comm.CreateParameter("@InputTestOne", adChar,
adParamInput, 50)
        comm("@InputTestOne") = "TestOne"
        ' Some Result Set Operation here
        conn.close

The Command object prevents in valid characters from entering the
database. Clearly you must rely on the quality of the drivers that
connect to the database. If the scrubbing and escaping is flawed in the
Command object then the web application is at risk.
        Not only does this technique prevent invalid characters causing
query failure and/or injection attacks, it helps performance of the web
app and offloads complex processing tasks to the database server. Other
benefits include enforcing character limits at the database, a second
level of protection for people wishing to Break Things.
        This method is available in most mature web
application/scripting environments.

-Logan


-----Original Message-----
From: Keith Smith [mailto:ksmith () firesnacks com]
Sent: Monday, February 10, 2003 8:12 PM
To: rnilsen () catalystone com; 'Taco Fleur'; webappsec () securityfocus com
Cc: 'Nick Jacobsen'
Subject: RE: SQL Injection Basics


Robert:

In ASP, what is equivalent to a prepared statement... i.e. must one use
for
example a Stored Procedure in MS SQL Server, or is there some ASP code
level
means of doing so... e.g. Recordsets etc.

Any good sources for information here?

Keith

-----Original Message-----
From: Robert Nilsen [mailto:rnilsen () catalystone com]
Sent: Monday February 10, 2003 6:08 PM
To: Taco Fleur; webappsec () securityfocus com
Cc: Nick Jacobsen
Subject: RE: SQL Injection Basics


I might be missing the point here (and surely it must have been
posted/explained before), but in my world, the safest way to do SQL is
through prepared statements, a.k.a. bind variables/paramenters whenever
someone out in the client segment has "touched" the input. Rule number
one
must always be to never thrust the client! And being sloppy just once
with
validation could mean the end of your data = not putting focus on
security!
By using prepared statements, the code is safer AND, in most cases, the
next
run will execute quicker.

-Robert


-----Original Message-----
From: Taco Fleur [mailto:tacofleur () nella net au]
Sent: 10. februar 2003 23:31
To: webappsec () securityfocus com
Cc: Nick Jacobsen
Subject: Re: SQL Injection Basics


SQL Injection works only when

1. SELECT * FROM foo WHERE foobar = $var
2. SELECT * FROM foo WHERE foobar = '$var'

In number 1, if the variable is not checked for the type of integer
people
can submit for example
/urlstring/index.cfm?var=1; AND NASTY CODE HERE

In number 2, if the variable is not checked for tick marks, and does not
escape any that are found
/urlstring/index.cfm?var=blah' AND NASTY CODE HERE --

In ColdFusion ALL ticks submitted are escaped, I'm sure more languages
out
there do that.
Anyway, what I am after is the backdoor, the Unicode equivalent...
Anyone?

I got this %25%32%37  from Davy, which makes sense but still does not
work
with ColdFusion
The following is the result
SELECT     category
FROM        mytable
WHERE    (category = '%27')

I am trying to find all possible ways for SQL Insertion so I can protect
myself against it.
Like I said I already convert any charters like ' () < > to its HTML
equivalent, but I beleive there is a way to get around this with
Unicode,
but not sure...


----- Original Message -----
From: "Nick Jacobsen" <nick () ethicsdesign com>
To: <dhurst () spidynamics com>; <webappsec () securityfocus com>
Sent: Tuesday, February 11, 2003 7:37 AM
Subject: Re: SQL Injection Basics


> Right, I wasn't thinking too well...  makes sense.  Though, according
to
> quite a few SQL injection faqs I have read, it said that you could
only
> inject code if the developer used tick marks.  However, I just
recently
used
> SQL injection on some code where the developer used NO tick marks, but
> injection still worked if the injection string contained TWO tick
marks.
> Was this just a fluke, or is it something that the faqs had wrong?
>
> Nick J.
> nick () ethicsdesign com
>
> ----- Original Message -----
> From: "Dennis Hurst" <dhurst () spidynamics com>
> To: "'Nick Jacobsen'" <nick () ethicsdesign com>
> Cc: <webappsec () securityfocus com>
> Sent: Monday, February 10, 2003 8:59 AM
> Subject: RE: SQL Injection Basics
>
>
> > Nick,
> >
> > Good question, but SQL Injection is absolutely an issue in VBScript
(ASP
> > pages).  Here's the deal, the ' only acts as a comment if it's in
the
> > source code, not when it's in a variable. So...
> >
> >
> > If you have code that looks like this:
> >
> > Dim sSql, rs, oConnection
> >
> > '..... Setup the connection......
> >
> > sSql = "Select * from myCustomers where FirstName = '" &
> > Request("txtFirstName") & "'"
> >
> > Set rs = oConnection.Execute(sSql)
> >
> > '.....do something with the returned data.....
> >
> >
> > And a user puts a ' in the text box called txtFirstName you end up
with
> > something that acts like this:
> >
> > Dim sSql, rs, oConnection
> >
> > '..... Setup the connection......
> >
> > sSql = "Select * from myCustomers where FirstName = '''"
> >
> > Set rs = oConnection.Execute(sSql)
> >
> > '.....do something with the returned data.....
> >
> >
> >
> >
> >
> > Have a great day,
> >
> > Dennis Hurst
> > dhurst () spidynamics com
> > SPI Labs
> >
> >
> >
> > -----Original Message-----
> > From: Nick Jacobsen [mailto:nick () ethicsdesign com]
> > Sent: Monday, February 10, 2003 6:07 AM
> > To: Loki; raul.johhut () hushmail com
> > Cc: webappsec () securityfocus com
> > Subject: Re: SQL Injection Basics
> >
> >
> > Hmm...  just a gues here, but if a developer is using VBScript as
the
> > scripting language, would SQL injection be impossible, since in
VBScript
> > the
> > " ' " mark is a comment mark, and therefore never used in SQL
> > statements?
> >
> > Nick J
> > nick () ethicsdesign com
> >
> > ----- Original Message -----
> > From: "Loki" <loki () fatelabs com>
> > To: <raul.johhut () hushmail com>
> > Cc: <webappsec () securityfocus com>
> > Sent: Saturday, February 08, 2003 9:16 PM
> > Subject: Re: SQL Injection Basics
> >
> >
> > > Raul:
> > >
> > > SQL injection is not replacing the userid field in the url with
> > "sdfsd",
> > > its escaping an SQL query with a single tick (') that the
developer
> > > doesn't escape (or in the case of PHP, GLOBALS is turned on in the
> > > php.ini).
> > >
> > > SQL injection is simply altering the SQL query sent to the SQL
server
> > > and executing an a malicious query instead of what was expected by
the
> > > developer. Depending on the remote server (Oracle, Microsoft SQL,
> > MySQL,
> > > PostgreSQL), these statements will only differ based on their
stored
> > > procedures. Microsoft SQL containing the more fun procedure of
> > > (xp_cmdshell) :)
> > >
> > > e.g.
> > >
> > > 'SELECT * FROM USERS
> > > Username: ' or 1=1--
> > >
> > >
> > > There are several papers available on SQL injection attacks, one
in
> > > particular written by Chris Anley at
> > > http://www.nextgenss.com/research/papers.html
> > >
> > > Typically, you can quickly check web apps for vulnerability to
> > injection
> > > by just entering a single tick (') in the form submission field,
> > hitting
> > > submit, and looking for any errors such as ODBC, etc.
> > >
> > >
> > >
> > > Loki
> > > Fate Research Labs
> > > www.fatelabs.com
> > >
> > >
> > >
> > > On Sat, 2003-02-08 at 20:21, raul.johhut () hushmail com wrote:
> > > > I am pen testing a webapp and am having some problems with SQL
> > injection.
> > > > The app creates an ODBC error. Is this a garuntee of SQL
Injection ?
> > > > If I use www.victim/test.asp?userid=sfdsd
> > > >
> > > > the error is "inncorrect syntax near line 28 of test.asp" (or
thats
> > the
> > English translation equiv in my case).
> > > > I know the database is called master, and has a table test. What
is
> > the
> > syntax I should use ?
> > > > What are the best freeware and open source tools for testing SQL
> > injection ? I tried WPosion which was OK.
> > > > I also tried WebSleuth (which seems to have gone from GPL to
closed
> > source commercial btw). Am I right is saying that the SQL plugin has
to
> > connect directly to the database to work ? I can only see port 80 so
> > don't
> > think this will work ?
> > > > Thanks, Raul.
> > > >
> > > >
> > > >
> > > > Concerned about your privacy? Follow this link to get
> > > > FREE encrypted email: https://www.hushmail.com/?l=2
> > > >
> > > > Big $$$ to be made with the HushMail Affiliate Program:
> > > > https://www.hushmail.com/about.php?subloc=affiliate&l=427
> > > --
> > > Loki <loki () fatelabs com>