the IN clause saga - Mailing list pgsql-jdbc

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