Re: Prepared Statements - Mailing list pgsql-jdbc

Peter Kovacs wrote:> I think that the simplest thing would be to have an option
in the
> backend to disable processing of multiple statements in one query --
> i.e. disallow the use of ';' as a separator of statements. I am not sure
> why this feature (multiple statments in one query) is there anyway.
> "Reduce network roundtrips" is the usual reply, but, then, what is the
> purpose of stored procedures (functions in PostgreSQL)?
>

I don't think the backend maintainers will like that.  And btw we don't need
this at all.

1) There is no risk of SQL injection with "real" (or "server side") prepared
statements.

2) With the proper implementation of the client side prepared statements
emulation the injected statements will not go through, because:

    a) all non-string results are properly quoted and generated from primary types.

    b) String type contents are quoted and have its quotes escaped.

> In my perception, JDBC is meant to be a client side extension of the
> server - bridge for Java clients to use the server (which in our case is
> the right and honorable PostgreSQL). Prepared statements is a mechanism
> to indicate the server that more query of this kind is likely to come so
> the plan of the query should be kept ready to be used again. That is
> what meant by PreparedStatement in the JDBC driver. I find the concept
> of "client side prepared statements" pretty weird.
>

It was added before the server had prepared statements so the applications could
still use PreparedStatements and the getXXX and setXXX methods instead of
recreating strings with queries all the time.  Some of the applications run with
other databases as well.

>  From this perspective, the whole wrestling with "drop table..." and
> similar risks seem farily vain to me. At least, the driver is not the
> place to solve this kind of security problems which basically exist due
> to the wya the server behaves. Instead, the question should be asked: is
> the behaviour of the server optimal?. Do we need this feature
> (processing multiple, semi-colon separated statements)? Should not this
> feature be eventually optional?
>

No SQL injection is possible with the backend implementation of prepared
statements (with the V3 protocol, at least).



--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9