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

Is there any SQL syntax that allows for a dollar-sign outside of a string
literal?  If not then if we are not in a string and we encounter a
dollar-sign we could assume that we are dealing with a PostgreSQL string and
immediately return the original input unchanged.

Baring that option I was pondering pre-processing the entire string with
something like:

Regex Matcher: '(\$[a-zA-z]*\$).+?(\1)' --syntax might be off but basically
check for at least one matching pair of dollar-quotes and if found disable
escape processing.

You could also implement a look-ahead mechanic within the parser so that
when you encounter "$" outside of a string you at least verify that you
encounter another dollar-sign before you encounter a space (or other invalid
identifier symbol).

I'll repeat but it is worth having someone agree explicitly that it is "an
error" conceptually to mix JDBC escapes and PostgreSQL dollar-quoting.
Either we should throw an exception if this occurs (making the parser more
robust but increasing the coding difficulty somewhat) or simply define the
behavior that in the presence of dollar-quoting any JDBC escapes are
silently ignored - basically making the server responsible for throwing a
generic syntax error instead of JDBC throwing a more precise
"MixedSyntaxEnvironment" exception.

David J.


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Dave Cramer
> Sent: Monday, September 24, 2012 9:21 AM
> To: David Johnston
> Cc: GEISINGER Marc - Contractor; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> 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/sqlesca
> >> p
> >> > 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
> >> >>
> >
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc