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).
>>
>>
>>
> 
>