Обсуждение: 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@ecircle-ag.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
>

Csaba Nagy <nagy@ecircle-ag.com> writes:
> 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...

But if the three statements are sent as a single query string, a failure
in any one will cause all three to be rolled back.  The complaint
suggests to me that JDBC *is* parsing the code, at least to the extent
of separating the statements.  Probably it should not.

Now, the real answer to the security aspect of this is "you should be
more careful about quoting strings from untrusted sources".  (Parameters
are just one way to do that.)  But I am curious to understand what
exactly JDBC is doing with a multi-statement query string.

            regards, tom lane

Tom Lane wrote:
But I am curious to understand what
> exactly JDBC is doing with a multi-statement query string.

If the jdbc driver accepts the multi-statement query string (it does in
some places but not in others), it will send the entire string to the
server as a single query.

--Barry