Re: Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

> -----Ursprüngliche Nachricht-----
> Von: David Johnston [mailto:polobo@yahoo.com]
> Gesendet: Montag, 24. September 2012 17:49
> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> Cc: pgsql-jdbc@postgresql.org
> Betreff: RE: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
>
> > At worse even if you cannot code it yourself you can propose pseudo-
> code/algorithm that people could review and then someone could just code
> > and deploy.
>
> Looking at
> http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
>
> 4.1.1. Identifiers and Key Words
> ...
> SQL identifiers and key words must begin with a letter (a-z, but also
letters
> with diacritical marks and non-Latin letters) or an underscore (_).
Subsequent
> characters in an identifier or key word can be letters, underscores,
digits (0-
> 9), or dollar signs ($). Note that dollar signs are not allowed in
identifiers
> according to the letter of the SQL standard, so their use might render
> applications less portable.
>
> it seems to me, that $ are not allowed as identifiers by the SQL standard.
But
> they are allowed in postgresql. But also in postgresql they are not
allowed at
> the beginning of an identifier.
> As far as I understand the parseSql method (line 864) in
> AbstractJdbc2Statement class, the looking for a dollar quoted string could
be
> done similar to the looking for escape codes (line 894).
> If we are IN_SQLCODE and we find a '$' as character, look through the
> following chars until the next '$' is found. Anything between the two '$'
is the
> tag. Then we would have a state IN_DOLLARQOUTE, until we find this '$tag$'
> again.
> The only problem I see with this idea might come from nested dollar
quotes.
>
> Marc

Which is why my suggestion is possibly superior and at least simpler (thus
less prone to bugs).

Once you find the non-conforming dollar-quote start tag in the query simply
abort the JDBC specific code and return the query unmodified.

As I mentioned in a separate post a dollar-sign cannot begin the identifier
while it must begin the dollar-quote so we should be able to unambiguously
determine which of the two options is valid upon encountering a dollar-sign
without having to perform any further scan.

You now remove the "information losing" quality of the current behavior.
I'm personally willing to risk the breakage of existing mixed code on the
basis that such mixing is inconsistent with the goal of the standard in the
first place and thus is likely to be minimal at best.  As a middle ground
the rest of the query should be scanned for JDBC escapes and, if found
(anywhere, even inside a dollar-quoted string), a RuntimeException sub-class
should be thrown pointing out the use of "mixed syntax".

That aside, nested quoting is not a concern; the string literal does not end
until the matching ending tag is found (i.e., at no point after encountering
a dollar-quote start tag does the parser state become IN_SQLCODE - until the
closing quote is matched).

David J.