Re: the IN clause saga

Felipe Schnack wrote:
>   I also prefer number one.
>   Maybe we should do a poll? :-)
>

Oliver has to update his summary first.  There are some new info from
the backend side.

Note that option 2 now should read "when inside the parenthesis that
define an <in value list> of the IN <predicate>.  (I am using the SQL
standard clause names here).

I would go with number 2 because that is exactly what the backend does
in its PREPARE statement.

Cheers,
Fernando



> On Tue, 22 Jul 2003 16:11:19 +1200
> Oliver Jowett <oliver@opencloud.com> wrote:
>
>
>>Some of the threads on this are getting a bit bogged down, I thought I'd
>>summarize the viable options I've seen so far (well, from my point of view
>>anyway)
>>
>>setObject() currently allows the user to bypass parameter escaping via
>>setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
>>plugged as it's a potential security hole.
>>
>>However the same functionality lets you do the (nonstandard) trick of
>>providing an IN clause to a PreparedStatement like "SELECT * FROM table
>>WHERE pk IN ?". It'd be good to still allow this functionality somehow after
>>setObject is fixed. This is going to be a postgresql-specific extension
>>however we do it.
>>
>>Here are the permutations I can remember:
>>
>>Option 1: add a method to PGStatement that explicitly sets an IN clause,
>>  taking either a java.sql.Array, java.util.Collection + component type,
>>  array + component type, or a custom postgresql object
>>
>>  + there's no confusion as to what it means
>>  + using a custom object allows access via setObject(..., Types.OTHER)
>>    consistently, as well as via the extension method.
>>  - java.sql.Array and java.util.Collection have problems as PGStatement is
>>    compiled for all JDKs and JDBC versions and those types may not be present
>>    (we could do a PGJDBC2Statement or something, but that's getting messy)
>>  - have to downcast to a PGStatement to use it
>>
>>Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>>
>>  + no new methods or types needed
>>  - setArray() behaves differently depending on query context
>>  - user has to wrap the underlying array in a java.sql.Array
>>
>>Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>>
>>  + no new methods or types needed
>>  - must assume that the contents of the collection use the default type mapping
>>    if a type is not provided
>>  - if a type is provided and we apply it to the *components* of the
>>    collection, this breaks the general getObject() interface of "bind this
>>    object interpreting it as this particular type".
>>  - not obvious what to do with objects that are both Collections and some
>>    other SQL-relevant type; solutions make setObject's behaviour complex
>>    and/or query-context-dependent
>>
>>Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>>   java.util.Collection
>>
>>  + as 3, but the ambiguity of "object is both Collection and SQL type X"
>>    goes away.
>>
>>Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>>  in this way.
>>
>>  + no issues with server-side prepare
>>  - obviously, you can't set IN clauses in one go any more.
>>
>>1-4 all need to disable server-side prepare when used.
>>
>>Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
>>partial implementation of 2 written but it's easy to adapt that to whatever
>>external interface.
>>
>>setArray() needs fixing regardless of what happens here. I hope to have a
>>patch for that ready later today.
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>


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