RE: SQL injection (no single quotes used)

[Michael Silk <michaelsilk () gmail com>]

Michael,

 But almost all of the "popular" languages implement it already (aside
from php (afaik)).
 
 ASP has it (SqlCommand?), .Net has it (SqlCommand), and as you
mention, Java has it.
 
 The only thing (That I know of) that a PreparedStatement, etc,
_can't_ handle is a dynamically-generated "IN" clause. These need to
be processed specially.
 
-- Michael (Silk - I'm not talking to myself :))



-- Original Message --


Juan et al,

The SQL injection thread seems to crop up every 6 months or so. From
the last one, I think that most people agreed that parameterizing the
sql was the best and most general choice (via something like Java's
PreparedStatement object)-- if you have a good structure like that,
then you don't have to worry about injection. If not (and if you don't
want to build one), you need to check data-types on all non-quoted
datatypes, and escape quotes on all the quoted ones. Then you have to
make sure that you account for all of the other encoding styles that
the underlying DBMS will handle, and deal with those. Finally, use the
principal of least privilege so that the process that usually checks
usernames doesn't have authorization to drop tables, etc.

I've been waiting for someone to announce cross-language (standard)
API that mimic's PreparedStatement. If anyone's looking for something
to do, it'd be a great help to the community...

Regards,

Michael Scovetta
Computer Associates
Senior Application Developer

-----Original Message-----
From: Juan Carlos [mailto:johnccr () yahoo com]
Sent: Thursday, December 16, 2004 10:49 AM
To: Michael Howard; Adam Tuliper; webappsec () securityfocus com
Subject: RE: SQL injection (no single quotes used)

hum...

not sure about this, from a web application
perspective,  all data is handled as plain text, I
mean, even if I encode the information in the URL (for
example) my java web application (for example), always
will get an ' character after calling getParamer. How
can en encoded character "touch" the Web Application
Software? Does the DB manager does decoding as well?.

Cheers
-JC

 --- Michael Howard <mikehow () microsoft com> escribió:
> From my experience, escaping is often never enough,
> because there a number of attacks that don't use
> quotes (etc)
>
> I'm not saying escaping quotes is bad, it's just not
> good enough on its own.
>
> [Writing Secure Code]
> http://www.microsoft.com/mspress/books/5957.asp
> [Protect Your PC] http://www.microsoft.com/protect
> [Blog] http://blogs.msdn.com/michael_howard
>
> [On-line Security Training]
> http://mste/training/offerings.asp?TrainingID=53074
>
>
> -----Original Message-----
> From: Adam Tuliper [mailto:amt () gecko-software com]
> Sent: Tuesday, December 14, 2004 11:30 AM
> To: Juan Carlos Calderon;
> webappsec () securityfocus com
> Subject: Re: SQL injection (no single quotes used)
>
> Michael Howard (and David LeBlanc) has a nice
> section in
> "writing secure code" about encoding characters. In
> some
> cases using char(0x27) as well as using entire words
> encoded via 0xXXXXXXXXXX can be used. Watching for
> "'" is
> not enough.
> I think Michael is on this list.. any words Michael?
>
>
>
> On Thu, 9 Dec 2004 09:53:03 -0600 (CST)
>  Juan Carlos Calderon <johnccr () yahoo com> wrote:
> > Hi all
> >
> > While in Oracle escaping apostrophe (') character
> > seems to be enough protection for Sql Injection (I
> > think is not), this is not true for Sql Server.
> Here a
> > little example I think many of you will find
> useful.
> > For an on-the-fly query like:
> > Query = "select field1, field2... from table where
> id
> > = '" + FixSQL (FieldValue) + "'"
> >
> > Where FixSQL will escape single quotes AKA
> apostrophe,
> > the following value for "FieldValue" will be
> > effective:
> >
> > FieldValue = "(NewLine)GO(NewLine)Desired Sql
> > Sentence(NewLine)GO"
> >
> > Final result is:
> > select field1, field2... from table where id = '
> > GO
> > Desired Sql Sentence
> > GO
> > '
> >
> > Here the MS Documentation for GO Keyword:
> > <snip>
> > SQL Server utilities interpret GO as a signal that
> > they should send the current batch of Transact-SQL
> > statements to SQL Server. The current batch of
> > statements is composed of all statements entered
> since
> > the last GO, or since the start of the ad hoc
> session
> > or script if this is the first GO
> > </snip>
> >
> > So one sentence become three, sentences one and
> three
> > will fail, but sentence two (the one of our
> interest)
> > will execute successfully.
> >
> > Hope you find this interesting
> >
> > Cheers,
> > -JC