Re: Invalid SQL still executes valid sub transactions

Well, that's not exactly correct.
The JDBC driver should not do any parsing of the SQL code in the first
place. That's the job of the server, so only the server can tell it's an
invalid statemnt.
And the server will interpret it as 3 different statements, treating
them independently, and 2 of them will be invalid, and one will execute
correctly...
You might argue that the statement should be treated as one, but this is
not mandated by any standard, and there are many legitimate uses of
multistatement queries. In fact the JDBC driver uses more than one
command itself to execute it's job.

HTH,
Csaba.

On Fri, 2004-01-16 at 15:44, Tom Hargrave wrote:
> Csaba, thanks for your comments and yes we are already re-engineering as
> suggested.
>
> Our main concern was the fact that an invalid piece of SQL was allowed
> to execute at all!
>
> Regards, Tom
>
> >>> Csaba Nagy <nagy(at)ecircle-ag(dot)com> 01/16/04 02:37pm >>>
> This is only true if you're in autocommit mode, otherwise it will be
> part of a transaction which will be rolled back.
> This should not happen if you use placeholders for parameters.
> In this particular example it might not be possible to use a
> placeholder
> for the order by column, but in that case you should be better
> encoding
> the available columns and translate the encoding to the actual column
> name in your code, and not include the column name directly from the
> web.
> There's no way to secure the SQL driver against malicious attacks if
> you
> insert web input directly in the SQL. The only security measures can
> be
> made for inputs passed in as parameters to a prepared statement with
> SQL
> using placeholders.
> If you want secure web input processing, you must learn how to do it
> properly... the most important measure is: never insert web input
> directly in SQL commands.
>
> Cheers,
> Csaba.
>
>
> On Fri, 2004-01-16 at 15:04, Tom Hargrave wrote:
> > Details:
> >
> > If a piece of SQL is executed in a JDBC prepared statement that
> > includes a
> > semicolon and a valid piece of SQL, then the embedded valid piece of
> > SQL
> > still executes even though the overall statement is invalid.
> >
> > Example:
> >
> > select c1 from t1 order by;drop t2; c1
> >
> > This causes security issues if the SQL is constructed from a web
> page
> > that
> > inputs strings that are used to construct a statement, since a
> hacker
> > can
> > embed SQL within a single field that executes regardless of the
> overall
> >
> > statement being invalid.
> >
> > See article:
> >
> >
> http://www.computerweekly.com/articles/article.asp?liArticleID=127470&liFlavourID=1
>
> >
> >
> >
> **************************************************************************************************
> > CONFIDENTIAL AND PRIVILEGED INFORMATION
> >
> > IMPORTANT: This message is intended for the addressee only and is
> privileged and
> > confidential. If you are not the addressee, then please DO NOT read,
> copy or
> > distribute it, but reply to the sender that you received it in error
> and delete it. Thank
> > you.
> >
> > Fisher Scientific U.K., Limited.
> >
> > Registered Office:
> > Bishop Meadow Road,
> > Loughborough LE11 5RG
> > England
> >
> > Registered in England No: 2883961
> >
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
>