Re: Prepared Statements

Actually, here is what javadoc comments say in the jdk's
PreparedStatement class:

/**
* An object that represents a precompiled SQL statement.
* <P>A SQL statement is precompiled and stored in a
* <code>PreparedStatement</code> object. This object can then be used to
* efficiently execute this statement multiple times.
*
* <P><B>Note:</B> The setter methods (<code>setShort</code>,
<code>setString</code>,
* and so on) for setting IN parameter values
* must specify types that are compatible with the defined SQL type of
* the input parameter. For instance, if the IN parameter has SQL type
* <code>INTEGER</code>, then the method <code>setInt</code> should be used.
*
* <p>If arbitrary parameter type conversions are required, the method
* <code>setObject</code> should be used with a target SQL type.
* <P>
*/

Two things that stricke me here:

- no mention of "security" stuff whatsoever. The sole purpose of
PreparedStatement according to this is to "efficiently execute this
statement multipe times",
not "to prevent slq injection attacks" or anything like that;

- it is *explicitly* stated that setObject () should be used for
"arbitrary type conversions";

Dima

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