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)?
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.
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?
Cheers,
Peter
Fernando Nasser wrote:
> Dima Tkach wrote:
>
>> I was fairly happy with what it used to be - just call setObject ()
>> and be done with it
>
>
> Unfortunately that is not an option as it is a security risk.
>
> You cannot leave a driver out there which allows people to insert
> potentially harmful SQL statements just to make it easier for someone
> to specify a set.
>
> In any case, I wonder if all PreparedStatements won't be server side
> only one day as the client side interface was created to fill in for
> the lack of that in older backends. Once that happens and the V3
> protocol is used (7.4+ backends) I doubt that SQL injection, and the
> hack to set IN arguments, will work.
>
> Regards to all,
> Fernando
>