Re: Prepared Statements

Peter Kovacs wrote:
> That's, then, even simpler that I originally thought. The only thing to
> be done is to make using "real" prepared statements the default
> behaviour of the PreparedStatement instances, is not it?
>

Yes and that is the plan. But it is a lot of work.

--Barry

> Fernando Nasser wrote:
>
>> 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).
>>
>>
>>
>
>