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

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(at)yahoo(dot)com]
Gesendet: Freitag, 21. September 2012 18:43
An: GEISINGER Marc - Contractor; 'Dave Cramer'
Cc: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org [mailto:pgsql-jdbc-
> owner(at)postgresql(dot)org] On Behalf Of David Johnston
> Sent: Friday, September 21, 2012 12:22 PM
> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
> Cc: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org) To make
changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc