Re: the IN clause saga

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

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)


--

 /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
 \ / Ribbon Campaign  Analista de Sistemas
  X  Against HTML     Cel.: 51-91287530
 / \ Email!           Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341