Re: Problems with question marks in operators (JDBC, ECPG, ...)

On Sun, May 17, 2015 at 5:15 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com>
wrote:

>
> > In that case my vote is new operators. This has been a sore point for the
> > JDBC driver
>
> Um, no, new operators is a bad idea. Question marks are used by hstore,
> json, geometry, and who knows what else. I think the onus is solely on
> JDBC to solve this problem. DBD::Pg solved it in 2008 with
> the pg_placeholder_dollaronly solution, and earlier this year by allowing
> backslashes before the question mark (because other parts of the stack were
> not able to smoothly implement pg_placeholder_dollaronly.) I recommend
> all drivers implement \? as a semi-standard workaround.
>
> See also:
> http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
>
>

I'm not sure the onus is solely on JDBC. Using question marks in operators
clearly has required a number of connectors to implement their own
workarounds, in different ways. This also seems to affect some libraries
and frameworks that depend on those connectors (and for which the
workarounds may even be more convoluted).

My main point was that this is not specific to JDBC. Considering that even
PostgreSQL's own ECPG is affected, the issue goes probably deeper than it
seems. I'm just not convinced that passing the problem onto connectors,
libraries and ultimately application developers is the right thing to do
here.

In the discussion on the OpenJDK JDBC list two years ago (
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000050.html
), Lance Andersen said "There is nothing in the SQL standard that would
support the use of an '?' as anything but a parameter marker.". It might be
worth finding out whether this is indeed the case according to the SQL
specifications (I'm afraid I'm not familiar with these specifications to do
it myself).
If this was indeed the case, it would certainly make sense to deprecate
these operators, so as not to be incompatible with the specifications.
Independently of the specifications, it seems that a number of libraries
and framework developers (again, not just JDBC) have this expectation
anyway.

Considering the number of problems question marks in operators cause
downstream, I think their use should be discouraged. Of course, the problem
remains for existing operators. I would suggest providing new, alternative
operators that don't have such issues, and eventually deprecate the
operators that cause those problems.

Recommending that all drivers implement \? as a semi-standard workaround is
actually a much more difficult problem than it seems: it requires following
the development of each project, making the case to each community
(assuming they're all open source), and reasonable in-depth knowledge of
their respective implementation, also assuming that \? won't cause further
problems there (of course, all that is easier if you're already working on
that particular project).
Even according to what you're saying this issue has required a first
workaround back in 2008, and another one earlier this year, probably due to
concerns that weren't spotted when implementing the first workaround (this
also presumably requires users to run a fairly recent version of this
connector now). (It looks like PHP/PDO is another one to add to the list:
https://bugs.php.net/bug.php?id=62493, it's been open for almost 3 years.)

Best wishes,

Bruno.