Обсуждение: Simple queries with JDBC escaped scalar functions result in exceptions

I upgraded to postgresql-9.4.1209.jar and fixed all the places in our code that used JDBC escaped scalar functions (e.g., {fn now()}) inside function definitions. However, we continued to see the telltale exception (org.postgresql.util.PSQLException: ERROR: syntax error at or near "{") when executing straightforward SELECT queries that included scalar functions. I tracked this down to comments; a simple repro is to execute the following query via JDBC:

SELECT /* $ */ {fn curdate()}

The original comments included many characters, but the dollar sign seems to be the culprit. I think this indicates a problem with the parser that's attempting to detect string literals.

Thanks,
Adam

Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 

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@fastcrypt.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.

 

Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 

Thanks, Vladimir! Local build of 9.4.1210-SNAPSHOT works great; all my legal test queries execute successfully now.

Just FYI, the new driver threw java.lang.ArrayIndexOutOfBoundsException with an illegal query: SELECT /* /* */ 1

java.lang.ArrayIndexOutOfBoundsException
    at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:597)
    at java.lang.StringBuilder.append(StringBuilder.java:190)
    at org.postgresql.core.Parser.parseSql(Parser.java:1037)
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:974)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19)
    at org.postgresql.util.LruCache.borrow(LruCache.java:118)
    at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:261)
    at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:146)
    at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:91)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1259)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1625)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:418)

I'm not suggesting this needs to be addressed... unbalanced comments will of course be rejected by the server anyway.

Thanks,
Adam
On 8/28/2016 8:00 AM, Vladimir Sitnikov wrote:

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

Thanks for the analysis.

I think https://github.com/pgjdbc/pgjdbc/commit/2d9b313160dc5b73df2ae9001795592b37e8e8e0 should fix the issue.

The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Dave Cramer wrote:
> Looks like the JDBC mini parser is having troubles with that.

> Why do you have comments in your sql ?
>
> Dave Cramer
>

MyJSQLView SQL schema dump. All databases that my application supports
comments, and is documented.

--
-- MyJSQLView SQL Dump
-- Version: 7.07
-- WebSite: http://myjsqlview.com
--
-- Host: 127.0.0.1
-- Generated On: 2016.05.08 AD at 02:11:28 MDT
-- SQL version: PostgreSQL 9.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."procedure_files"
--

DROP TABLE IF EXISTS "public"."procedure_files";
CREATE TABLE "public"."procedure_files" (
     "id" serial NOT NULL,
     "procedure_id" integer DEFAULT NULL,
     "file_name" varchar(null) DEFAULT NULL,
     "file_oid" oid DEFAULT NULL
);

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@fastcrypt.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.

 

Thanks, Vladimir! Local build of 9.4.1210-SNAPSHOT works great; all my legal test queries execute successfully now.

Just FYI, the new driver threw java.lang.ArrayIndexOutOfBoundsException with an illegal query: SELECT /* /* */ 1

java.lang.ArrayIndexOutOfBoundsException
    at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:597)
    at java.lang.StringBuilder.append(StringBuilder.java:190)
    at org.postgresql.core.Parser.parseSql(Parser.java:1037)
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:974)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19)
    at org.postgresql.util.LruCache.borrow(LruCache.java:118)
    at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:261)
    at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:146)
    at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:91)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1259)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1625)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:418)

I'm not suggesting this needs to be addressed... unbalanced comments will of course be rejected by the server anyway.

Thanks,
Adam
On 8/28/2016 8:00 AM, Vladimir Sitnikov wrote:

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

Thanks for the analysis.

I think https://github.com/pgjdbc/pgjdbc/commit/2d9b313160dc5b73df2ae9001795592b37e8e8e0 should fix the issue.

The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Dave Cramer wrote:
> Looks like the JDBC mini parser is having troubles with that.

> Why do you have comments in your sql ?
>
> Dave Cramer
>

MyJSQLView SQL schema dump. All databases that my application supports
comments, and is documented.

--
-- MyJSQLView SQL Dump
-- Version: 7.07
-- WebSite: http://myjsqlview.com
--
-- Host: 127.0.0.1
-- Generated On: 2016.05.08 AD at 02:11:28 MDT
-- SQL version: PostgreSQL 9.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."procedure_files"
--

DROP TABLE IF EXISTS "public"."procedure_files";
CREATE TABLE "public"."procedure_files" (
     "id" serial NOT NULL,
     "procedure_id" integer DEFAULT NULL,
     "file_name" varchar(null) DEFAULT NULL,
     "file_oid" oid DEFAULT NULL
);