Re: 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.