Re: Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting? - Mailing list pgsql-jdbc

David,

Thanks for the summary, I understand the problem now. I had a look at
the code and the current parser basically looks at 1 character to
determine state. Having to look at N characters to look at state will
be a significant amount of work.I'd certainly welcome a patch.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 8:55 AM, David Johnston <polobo@yahoo.com> wrote:
> Marc,
>
> Since you have gotten this far, and seem to understand the issue fairly
> well, are you in a position to contribute back to the project and submit a
> patch for the community to review and hopefully apply that will implement
> this feature?
>
> Even if the community agrees on the merits of the feature someone will still
> have to take time to actually implement it.  It is not a bug, contrary to
> your claims, for the reasons I've stated before and the impact of the
> behavior on the community seems negligible since no one has bothered to
> implement this and it has been around pretty much since day one.  Most
> people likely just turn off JDBC escape processing as being not worth the
> hassle.
>
> I'm actually considered doing it myself (the patch) but the better solution
> for me is to do just that (turn off escaping) and given my own priorities
> that is what I am going to do for now.
>
> @Dave Cramer
> The main reason for turning off processing, and not just treating
> dollar-quoting as a string literal, is to try and help any developer who do
> wants to code generically using JDBC escapes but inadvertently uses
> dollar-quoting.  In that situation their attempt is going to fail on all
> other databases and thus it should arguably fail on PostgreSQL as well.  The
> solution for them is to use normal quoting if they want to use JDBC escaping
> or forgo escaping if they want to use dollar-quoting.  Enabling both has
> some merit but IMO it makes the driver to forgiving in the face of
> ambiguity.  That said there are transition concerns to be discussed.  Also,
> a flag to raise some form of warning if an escape sequence is found inside
> dollar-quoting is probably worthwhile as well.  That should help in
> identifying problem SQL in existing "static" code.
>
> David J.
>
>> -----Original Message-----
>> From: GEISINGER Marc - Contractor
>> [mailto:Marc.GEISINGER@external.thalesgroup.com]
>> Sent: Monday, September 24, 2012 7:54 AM
>> To: Dave Cramer
>> Cc: David Johnston; pgsql-jdbc@postgresql.org
>> Subject: AW: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>>
>> Hi Dave,
>> The comment i am refering to is in
>> org.postgresql.jdbc2.AbstractJdbc2Statement and it says:
>>
>>     /*
>>      * Filter the SQL string of Java SQL Escape clauses.
>>      *
>>      * Currently implemented Escape clauses are those mentioned in 11.3
>>      * in the specification. Basically we look through the sql string for
>>      * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
>>      * code. When we find them, we just strip the escape part leaving only
>>      * the xxx part.
>>      * So, something like "select * from x where d={d '2001-10-09'}" would
>>      * return "select * from x where d= '2001-10-09'".
>>      */
>>     protected String replaceProcessing(String p_sql) throws SQLException
>>
>> And I don't want escape processing to be turned off just for me, I just
> want
>> dollar quoted strings to be treated the same as strings in single quotes
> would
>> be. In the replaceProcessing method there is another comment:
>>
>> // Since escape codes can only appear in SQL CODE, we keep track // of if
> we
>> enter a string or not.
>>
>> Everything between dollar quotes is not sql code.
>>
>> Marc
>>
>> -----Ursprüngliche Nachricht-----
>> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
>> von Dave Cramer
>> Gesendet: Montag, 24. September 2012 13:42
>> An: GEISINGER Marc - Contractor
>> Cc: David Johnston; pgsql-jdbc@postgresql.org
>> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>>
>> Marc,
>>
>> Where is this comment you are referring to ? What I suspect you want is
>> once a dollar quote is found you want escape processing to be turned off?
>> What do I do with the next guy who wants it back on ?
>>
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor
>> <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> > Hi Dave,
>> > I don't want this feature removed. I just would like to see this fixed
> so that
>> dollar quoted strings are correctly seen as strings, and therefor without
> sql
>> escape clauses replacing. Like the comment for replaceProcessing says
> "look
>> through non-string sql code". Dollar quoted strings are strings for
> postgresql.
>> >
>> > Marc
>> >
>> > -----Ursprüngliche Nachricht-----
>> > Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
>> von
>> > Dave Cramer
>> > Gesendet: Montag, 24. September 2012 11:28
>> > An: GEISINGER Marc - Contractor
>> > Cc: David Johnston; pgsql-jdbc@postgresql.org
>> > Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>> >
>> > Marc,
>> >
>> > I haven't had time to look at this in detail but I suspect that what
>> > is happening is that a JDBC *feature* which is here
>> >
>> http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescap
>> > e.html explains why {d} is replaced by DATE. I'm not sure how you
>> > would like to see this fixed ? This is the JBDC spec, replace {d} with
> DATE. As
>> someone suggested, turn escape processing off and it will work fine.
>> >
>> >
>> > dave.cramer(at)credativ(dot)ca
>> > http://www.credativ.ca
>> >
>> >
>> > On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor
>> <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> >> Hi,
>> >> when using dollar quoting in my statements I knew it would be
> postgresql
>> specific. That was ok since our project isn't likely to be ported to
> another db.
>> It wasn't meant to be multiy db platform compatible. If anybody want's to
>> write dbms independent code, I wouldn't think to find dollar quoting
> there.
>> >>
>> >> I don't know how the jdbc driver development is done, and how platform
>> specific features are handled there. But as it is a documented postgresql
>> feature and it is understood by PgAdmin itself (and other postgresql
> drivers),
>> I think the postgresql jdbc driver should be able to do it too.
>> >> I also don't think it is needed to make it work with other jdbc
> drivers, but
>> that might be out of my scope.
>> >>
>> >> Marc
>> >>
>> >> -----Ursprüngliche Nachricht-----
>> >> Von: David Johnston [mailto:polobo@yahoo.com]
>> >> Gesendet: Freitag, 21. September 2012 18:43
>> >> An: GEISINGER Marc - Contractor; 'Dave Cramer'
>> >> Cc: pgsql-jdbc@postgresql.org
>> >> Betreff: RE: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing
>> when using dollar quoting?
>> >>
>> >> All the following said would it be within the realm of acceptability to
> test
>> the query for dollar-quoting and, if found, to automatically disable JDBC
>> escape mode?
>> >>
>> >> The presence of dollar-quoting in a PostgreSQL driver is something that
> is
>> likely to occur and when it does the user should hopefully understand that
>> any JDBC escaping isn't going to guarantee the portability that is implied
> by its
>> presence.  It would seem to be a newbie friendly way of making things work
>> given the escaping is on by default and for someone not as familiar with
> JDBC
>> features as they are PostgreSQL features the risk posed by the
> combination,
>> while minimal, would be mostly invisible (since it would not generate
> syntax
>> errors directly but would rather simply store invalid data).
>> >>
>> >> David J.
>> >>
>> >>
>> >>> -----Original Message-----
>> >>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>> >>> owner@postgresql.org] On Behalf Of David Johnston
>> >>> Sent: Friday, September 21, 2012 12:22 PM
>> >>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>> >>> Cc: pgsql-jdbc@postgresql.org
>> >>> Subject: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing
>> >>> when using dollar quoting?
>> >>>
>> >>> > -----Original Message-----
>> >>> >
>> >>> > Hi Dave,
>> >>> > in the potsgresql documentation it says:
>> >>> >
>> >>> >
>> >>> > And that is excactly what i am trying to do. An easy and safe way
>> >>> > to
>> >>> escape
>> >>> > strings that are coming from "outside". And since I tried it and
>> >>> > it worked
>> >>> with
>> >>> > PgAdmin, i expect it to be a way that can be used.
>> >>> >
>> >>> >
>> >>> > To Victor:
>> >>> > And I cannot just setEnableProcessing to false. The code I've
>> >>> > posted here
>> >>> is
>> >>> > just an example code. The code where I came to this problem is not
>> >>> > that simple. And I would have to either disable it completely or
>> >>> > not at all (we
>> >>> are
>> >>> > using an ORM framework for db access). Since reading the above I
>> >>> > think
>> >>> this
>> >>> > is a thing that should be fixed in the driver I don't want to
>> >>> > completely
>> >>> block
>> >>> > SQL escape clauses in my code (even though i never used them
>> >>> > myself
>> >>> > :)
>> >>> > )
>> >>> >
>> >>> >
>> >>> > Can you show me where in the spec it says you can use dollar sign
>> >>> > quoting like that ?
>> >>> >
>> >>>
>> >>> So:
>> >>>
>> >>> Dollar-quoting is PostgreSQL specific and if used with any other
>> >>> database
>> >> the
>> >>> query will likely fail.
>> >>>
>> >>> The JDBC escape mechanism is defined to allow for cross-vendor query
>> >>> writing.
>> >>>
>> >>> In order for the JDBC escape mechanism to serve its purpose it would
>> >>> have to recognize dollar-quoting generally and convert it into
>> >>> whatever string delimiting mechanism its server is familiar with
>> >>> (i.e., back to
>> >> single-quote and
>> >>> quote escaping) in order to serve its function.
>> >>>
>> >>> If it simply accepts dollar-quoting but does not convert it when
>> >>> necessary then queries using the escapes will fail anyway when put
>> >>> to a database not supporting dollar-quoting.  While the PostgreSQL
>> >>> driver could indeed do
>> >> this
>> >>> properly it does not mean that the, for example, Oracle and
>> >>> SQLServer drivers out there are going to perform the conversion
>> >>> since it is not
>> >> required
>> >>> of them in the JDBC specification.
>> >>>
>> >>> The decision of whether to allow escaping by default is project
>> >>> specific
>> >> but
>> >>> regardless of the default decision the driver and whatever interface
>> >>> your ORM provides should allow you to make the decision on a per-
>> query basis.
>> >>>
>> >>> The main risk I can see with using dollar-quoting and having
>> >>> escaping
>> >> enabled
>> >>> is if the strings in question use the obscure escape syntax for some
>> >> reason
>> >>> (unlikely) then the string valued stored is going to be messed up
>> >>> but otherwise PostgreSQL will still treat it as a string.  I have in
>> >>> fact been
>> >> living
>> >>> with this for quite a while (when I store and execute CREATE
>> >>> FUNCTION) and haven't had any clobbering.  The question to ask
>> >>> yourself is whether that risk window is large enough to warrant
>> >>> modifying your execution environment.
>> >>>
>> >>> You need to decide whether all of your code is supposedly
>> >>> vendor-neutral and so you can leave escaping on and ignore
>> >>> dollar-quoting OR you can code in a strictly literal syntax and
>> >>> disable escaping.  If you want to live in
>> >> both
>> >>> worlds then you need to be able to tell your execution environment
>> >>> which world you are living in for each query you write.
>> >>>
>> >>> David J.
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
>> >> changes
>> >>> to your subscription:
>> >>> http://www.postgresql.org/mailpref/pgsql-jdbc
>> >>
>