Re: Simple queries with JDBC escaped scalar functions result in exceptions

In our development and test environments (but not production), our SQL
generation engine inserts comments at strategic places in the queries it
constructs, e.g., tagging a GUID with the real-world name of the object
it represents or providing details about the requests from higher layers
in the system that caused the query to be produced. When a query results
in an exception, incorrect results, or poor performance, those comments
help us better understand the query so we can fix or optimize it. Of
course, this is legal and has been supported by the driver for many years.

The dollar sign ($) is the only case we came across in our test
environments, but it looks like a single quote (') or double quote (")
in a comment also trips up the parser. As do the two-character
combinations {D, {E, {F, {O, and {T... and their lowercase equivalents.
A full set of test queries that fail when executed via
postgresql-9.4.1209.jar:

SELECT /* " */ {fn curdate()};
SELECT /* $ */ {fn curdate()};
SELECT /* ' */ {fn curdate()};
SELECT /* {D */ {fn curdate()};
SELECT /* {E */ {fn curdate()};
SELECT /* {F */ {fn curdate()};
SELECT /* {O */ {fn curdate()};
SELECT /* {T */ {fn curdate()};
SELECT /* {d */ {fn curdate()};
SELECT /* {e */ {fn curdate()};
SELECT /* {f */ {fn curdate()};
SELECT /* {o */ {fn curdate()};
SELECT /* {t */ {fn curdate()};

Thanks,
Adam

On 8/28/2016 6:43 AM, Andreas Joseph Krogh wrote:
> På søndag 28. august 2016 kl. 03:18:42, skrev Dave Cramer
> <pg(at)fastcrypt(dot)com <mailto:pg(at)fastcrypt(dot)com>>:
>
> Looks like the JDBC mini parser is having troubles with that. Why
> do you have comments in your sql ?
>
> I think the universial answer here is "Because you can". Many use
> comments in SQL as a way to both understand what's going on better
> (ie. when the SQL is generated by code) and to make logging easier.
> There's no reason why a driver should not cope with valid comments.
>