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

Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

From

GEISINGER Marc - Contractor

Date:

20 September 2012, 18:53:09

Hi,

I think there is a bug in AbstracJdbc2Statement.replaceProcessing. When I am trying to execute the following sql command with a java.sql.Statement the data written to the database is not what it should be:

INSERT INTO dev.stringtest (id, val) VALUES (1, $a$ {a}{f} {b} {f}$a$)

The replaceProcessing method removes {f} and cuts the string. If i execute this string using pgAdmin it works perfectly ok. Also inserting this string using a prepared statement is ok. But i also have an occasion where strings like this will be inserted as statement. And then the result is not what is expected (see below).

I am using PostgreSQL 9.1.5 build 1500 32-bit Windows as local test server, the jdbc driver version is postgresql-9.1-902.jdbc4.jar.

For the example code i use one table:

CREATE TABLE dev.stringtest

(

val character varying(50),

id integer NOT NULL,

CONSTRAINT stringsest_pkey PRIMARY KEY (id )

)

And a litte test to see the bug:

public class PostgresTest {

/** the string to insert. */

public static final String formatString = "{a} {f} {b} {f}";

/**

* @param args

public static void main(final String[] args) {

final PostgresTest pgTest = new PostgresTest();

try {

pgTest.doInsert();

pgTest.doSelect();

} catch (final SQLException e) {

e.printStackTrace();

}

System.out.println("Finished");

}

/**

* Constructor.

private PostgresTest() {

try {

initDB();

} catch (final SQLException e) {

e.printStackTrace();

}

/**

* Clear db and insert test data.

* @throws SQLException

* in case of a db error

public void doInsert() throws SQLException {

final String param = "$a$" + formatString + "$a$";

final String deleteSql = "TRUNCATE dev.stringtest";

final String insertSql = "INSERT INTO dev.stringtest (id, val) VALUES (1, "

+ param + ")";

final Statement stmt = connection.createStatement();

stmt.execute(deleteSql);

stmt.execute(insertSql);

}

/**

* Load inserted test data and check it.

* @throws SQLException

* in case of a db error

public void doSelect() throws SQLException {

final String selectSql = "SELECT val FROM dev.stringtest WHERE id = 1";

final Statement stmt = connection.createStatement();

final ResultSet resultSet = stmt.executeQuery(selectSql);

if (resultSet.next()) {

final String val = resultSet.getString(1);

System.out.println("Expected: " + formatString);

System.out.println("Loaded: " + val);

}

/**

* Init the DB connection.

* @throws SQLException

* in case of a db error

private void initDB() throws SQLException {

connection = DriverManager.getConnection(

"jdbc:postgresql://localhost:5432/dev", "user", "password");

}

/** the connection. */

private Connection connection;

}

When running this code i get the result:

Expected: {a} {f} {b} {f}

Loaded: {a} {b

Finished

I could trace the problem to the method org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing(String). Seems dollar quoted strings are not supported here. And additionally the function parseSql should only replace {fn xxx} but it is replacing {f} and even more characters until the final '}'.

If i use {d} in the string it gets replaced by DATE.

Expected: {a} {d} {b} {f}

Loaded: {a} DATE {b}

Finished

which i think can be seen as correct, but still this is not what i expected here. I did not try any more letters because f is the maximum I insert.

regards

Marc

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

From

Dave Cramer

Date:

20 September 2012, 21:36:58

Marc,

Can you show me where in the spec it says you can use dollar sign
quoting like that ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Thu, Sep 20, 2012 at 7:25 AM, GEISINGER Marc - Contractor
<Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi,
>
> I think there is a bug in AbstracJdbc2Statement.replaceProcessing.  When I
> am trying to execute the following sql command with a java.sql.Statement the
> data written to the database is not what it should be:
>
>
>
> INSERT INTO dev.stringtest (id, val) VALUES (1, $a$ {a}{f} {b} {f}$a$)
>
>
>
> The replaceProcessing method removes {f} and cuts the string. If i execute
> this string using pgAdmin it works perfectly ok. Also inserting this string
> using a prepared statement is ok. But i also have an occasion where strings
> like this will be inserted as statement. And then the result is not what is
> expected (see below).
>
>
>
> I am using PostgreSQL 9.1.5 build 1500 32-bit Windows  as local test server,
> the jdbc driver version is postgresql-9.1-902.jdbc4.jar.
>
>
>
> For the example code i use one table:
>
>
>
> CREATE TABLE dev.stringtest
>
> (
>
>   val character varying(50),
>
>   id integer NOT NULL,
>
>   CONSTRAINT stringsest_pkey PRIMARY KEY (id )
>
> )
>
>
>
> And a litte test to see the bug:
>
>
>
> public class PostgresTest {
>
>
>
>     /** the string to insert. */
>
>     public static final String formatString = "{a} {f} {b} {f}";
>
>
>
>     /**
>
>      *
>
>      * @param args
>
>      */
>
>     public static void main(final String[] args) {
>
>          final PostgresTest pgTest = new PostgresTest();
>
>          try {
>
>              pgTest.doInsert();
>
>              pgTest.doSelect();
>
>          } catch (final SQLException e) {
>
>              e.printStackTrace();
>
>          }
>
>          System.out.println("Finished");
>
>     }
>
>
>
>     /**
>
>      *
>
>      * Constructor.
>
>      *
>
>      */
>
>     private PostgresTest() {
>
>          try {
>
>              initDB();
>
>          } catch (final SQLException e) {
>
>              e.printStackTrace();
>
>          }
>
>     }
>
>
>
>     /**
>
>      * Clear db and insert test data.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     public void doInsert() throws SQLException {
>
>          final String param = "$a$" + formatString + "$a$";
>
>          final String deleteSql = "TRUNCATE dev.stringtest";
>
>          final String insertSql = "INSERT INTO dev.stringtest (id, val)
> VALUES (1, "
>
>                    + param + ")";
>
>          final Statement stmt = connection.createStatement();
>
>          stmt.execute(deleteSql);
>
>          stmt.execute(insertSql);
>
>     }
>
>
>
>     /**
>
>      * Load inserted test data and check it.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     public void doSelect() throws SQLException {
>
>          final String selectSql = "SELECT val FROM dev.stringtest WHERE id =
> 1";
>
>          final Statement stmt = connection.createStatement();
>
>          final ResultSet resultSet = stmt.executeQuery(selectSql);
>
>          if (resultSet.next()) {
>
>              final String val = resultSet.getString(1);
>
>              System.out.println("Expected: " + formatString);
>
>              System.out.println("Loaded: " + val);
>
>          }
>
>     }
>
>
>
>     /**
>
>      * Init the DB connection.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     private void initDB() throws SQLException {
>
>          connection = DriverManager.getConnection(
>
>                    "jdbc:postgresql://localhost:5432/dev", "user",
> "password");
>
>     }
>
>
>
>     /** the connection. */
>
>     private Connection connection;
>
>
>
> }
>
>
>
>
>
> When running this code i get the result:
>
>
>
> Expected: {a} {f} {b} {f}
>
> Loaded: {a}  {b
>
> Finished
>
>
>
>
>
> I could trace the problem to the method
> org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing(String). Seems
> dollar quoted strings are not supported here. And additionally the function
> parseSql should only replace {fn xxx} but it is replacing {f} and even more
> characters until the final '}'.
>
>
>
> If i use {d} in the string it gets replaced by DATE.
>
>
>
> Expected: {a} {d} {b} {f}
>
> Loaded: {a} DATE  {b}
>
> Finished
>
>
>
> which i think can be seen as correct, but still this is not what i expected
> here. I did not try any more letters because f is the maximum I insert.
>
>
>
> regards
>
> Marc
>
>

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

From

"victor.nevsky"

Date:

21 September 2012, 01:39:21

Use
        ... stmt = connection.createStatement();
        *stmt.setEscapeProcessing(false);*
        stmt.execute(...



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Bug-in-AbstracJdbc2Statement-replaceProcessing-when-using-dollar-quoting-tp5724744p5724779.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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

From

GEISINGER Marc - Contractor

Date:

21 September 2012, 18:06:19

Hi Dave,
in the potsgresql documentation it says:


> 4.1.2. Constants

> There are three kinds of implicitly-typed constants in PostgreSQL: strings, bit strings, and numbers.
...
> 4.1.2.1. String Constants
> A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a
string'.To include a  
> single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'.
...
> 4.1.2.4. Dollar-quoted String Constants
> While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand
whenthe desired string 
> contains many single quotes or backslashes, since each of those must be doubled. To allow more readable queries in
suchsituations, 
> PostgreSQL provides another way, called "dollar quoting", to write string constants. A dollar-quoted string constant
consistsof a dollar 
> sign ($), an optional "tag" of zero or more characters, another dollar sign, an arbitrary sequence of characters that
makesup the string 
> content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. For example, here are two
differentways to specify the 
> string "Dianne's horse" using dollar quoting:
> $$Dianne's horse$$
> $SomeTag$Dianne's horse$SomeTag$

I also found an answer to an escaping question that says

> Instead of escaping how about looking at double $ quoting.
> http://postgresql.1045698.n5.nabble.com/how-to-escape-in-select-td2257404.html

And that is excactly what i am trying to do. An easy and safe way to escape strings that are coming from "outside". And
sinceI tried it and it worked with PgAdmin, i expect it to be a way that can be used. 

In this group I also found and old question about dollar quoting:

> This is a known bug in the JDBC driver.  It does not support dollar quotes.
> https://groups.google.com/d/topic/pgsql.interfaces.jdbc/JMnMAgrTWP0/discussion

But this answer was from 2006.


To Victor:
And I cannot just setEnableProcessing to false. The code I've posted here is just an example code. The code where I
cameto this problem is not that simple. And I would have to either disable it completely or not at all (we are using an
ORMframework for db access). Since reading the above I think this is a thing that should be fixed in the driver I don't
wantto completely block SQL escape clauses in my code (even though i never used them myself :) ) 

Marc



-----Ursprüngliche Nachricht-----
Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag von Dave Cramer
Gesendet: Donnerstag, 20. September 2012 20:37
An: GEISINGER Marc - Contractor
Cc: pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

Marc,

Can you show me where in the spec it says you can use dollar sign quoting like that ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Thu, Sep 20, 2012 at 7:25 AM, GEISINGER Marc - Contractor <Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi,
>
> I think there is a bug in AbstracJdbc2Statement.replaceProcessing.
> When I am trying to execute the following sql command with a
> java.sql.Statement the data written to the database is not what it should be:
>
>
>
> INSERT INTO dev.stringtest (id, val) VALUES (1, $a$ {a}{f} {b} {f}$a$)
>
>
>
> The replaceProcessing method removes {f} and cuts the string. If i
> execute this string using pgAdmin it works perfectly ok. Also
> inserting this string using a prepared statement is ok. But i also
> have an occasion where strings like this will be inserted as
> statement. And then the result is not what is expected (see below).
>
>
>
> I am using PostgreSQL 9.1.5 build 1500 32-bit Windows  as local test
> server, the jdbc driver version is postgresql-9.1-902.jdbc4.jar.
>
>
>
> For the example code i use one table:
>
>
>
> CREATE TABLE dev.stringtest
>
> (
>
>   val character varying(50),
>
>   id integer NOT NULL,
>
>   CONSTRAINT stringsest_pkey PRIMARY KEY (id )
>
> )
>
>
>
> And a litte test to see the bug:
>
>
>
> public class PostgresTest {
>
>
>
>     /** the string to insert. */
>
>     public static final String formatString = "{a} {f} {b} {f}";
>
>
>
>     /**
>
>      *
>
>      * @param args
>
>      */
>
>     public static void main(final String[] args) {
>
>          final PostgresTest pgTest = new PostgresTest();
>
>          try {
>
>              pgTest.doInsert();
>
>              pgTest.doSelect();
>
>          } catch (final SQLException e) {
>
>              e.printStackTrace();
>
>          }
>
>          System.out.println("Finished");
>
>     }
>
>
>
>     /**
>
>      *
>
>      * Constructor.
>
>      *
>
>      */
>
>     private PostgresTest() {
>
>          try {
>
>              initDB();
>
>          } catch (final SQLException e) {
>
>              e.printStackTrace();
>
>          }
>
>     }
>
>
>
>     /**
>
>      * Clear db and insert test data.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     public void doInsert() throws SQLException {
>
>          final String param = "$a$" + formatString + "$a$";
>
>          final String deleteSql = "TRUNCATE dev.stringtest";
>
>          final String insertSql = "INSERT INTO dev.stringtest (id,
> val) VALUES (1, "
>
>                    + param + ")";
>
>          final Statement stmt = connection.createStatement();
>
>          stmt.execute(deleteSql);
>
>          stmt.execute(insertSql);
>
>     }
>
>
>
>     /**
>
>      * Load inserted test data and check it.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     public void doSelect() throws SQLException {
>
>          final String selectSql = "SELECT val FROM dev.stringtest
> WHERE id = 1";
>
>          final Statement stmt = connection.createStatement();
>
>          final ResultSet resultSet = stmt.executeQuery(selectSql);
>
>          if (resultSet.next()) {
>
>              final String val = resultSet.getString(1);
>
>              System.out.println("Expected: " + formatString);
>
>              System.out.println("Loaded: " + val);
>
>          }
>
>     }
>
>
>
>     /**
>
>      * Init the DB connection.
>
>      *
>
>      * @throws SQLException
>
>      *             in case of a db error
>
>      */
>
>     private void initDB() throws SQLException {
>
>          connection = DriverManager.getConnection(
>
>                    "jdbc:postgresql://localhost:5432/dev", "user",
> "password");
>
>     }
>
>
>
>     /** the connection. */
>
>     private Connection connection;
>
>
>
> }
>
>
>
>
>
> When running this code i get the result:
>
>
>
> Expected: {a} {f} {b} {f}
>
> Loaded: {a}  {b
>
> Finished
>
>
>
>
>
> I could trace the problem to the method
> org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing(String).
> Seems dollar quoted strings are not supported here. And additionally
> the function parseSql should only replace {fn xxx} but it is replacing
> {f} and even more characters until the final '}'.
>
>
>
> If i use {d} in the string it gets replaced by DATE.
>
>
>
> Expected: {a} {d} {b} {f}
>
> Loaded: {a} DATE  {b}
>
> Finished
>
>
>
> which i think can be seen as correct, but still this is not what i
> expected here. I did not try any more letters because f is the maximum I insert.
>
>
>
> regards
>
> Marc
>
>

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

From

"David Johnston"

Date:

21 September 2012, 19:23:09

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

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

From

"David Johnston"

Date:

21 September 2012, 19:43:35

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

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

From

Dave Cramer

Date:

24 September 2012, 12:28:33

Marc,

I haven't had time to look at this in detail but I suspect that what
is happening is that a JDBC *feature* which is here
http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescape.html
explains why {d} is replaced by DATE. I'm not sure how you would like
to see this fixed ? This is the JBDC spec, replace {d} with DATE. As
someone suggested, turn escape processing off and it will work fine.


dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor
<Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi,
> when using dollar quoting in my statements I knew it would be postgresql specific. That was ok since our project
isn'tlikely to be ported to another db.  It wasn't meant to be multiy db platform compatible. If anybody want's to
writedbms 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
isa documented postgresql feature and it is understood by PgAdmin itself (and other postgresql drivers), I think the
postgresqljdbc 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@yahoo.com]
> Gesendet: Freitag, 21. September 2012 18:43
> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> Cc: pgsql-jdbc@postgresql.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
shouldhopefully 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
someonenot 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
invaliddata). 
>
> David J.
>
>
>> -----Original Message-----
>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>> owner@postgresql.org] On Behalf Of David Johnston
>> Sent: Friday, September 21, 2012 12:22 PM
>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
> changes
>> to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>

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

From

Dave Cramer

Date:

24 September 2012, 14:42:47

Marc,

Where is this comment you are referring to ? What I suspect you want
is once a dollar quote is found you want escape processing to be
turned off? What do I do with the next guy who wants it back on ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor
<Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi Dave,
> I don't want this feature removed. I just would like to see this fixed so that dollar quoted strings are correctly
seenas strings, and therefor without sql escape clauses replacing. Like the comment for replaceProcessing says "look
throughnon-string sql code". Dollar quoted strings are strings for postgresql. 
>
> Marc
>
> -----Ursprüngliche Nachricht-----
> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag von Dave Cramer
> Gesendet: Montag, 24. September 2012 11:28
> An: GEISINGER Marc - Contractor
> Cc: David Johnston; pgsql-jdbc@postgresql.org
> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?
>
> Marc,
>
> I haven't had time to look at this in detail but I suspect that what is happening is that a JDBC *feature* which is
herehttp://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescape.html 
> explains why {d} is replaced by DATE. I'm not sure how you would like to see this fixed ? This is the JBDC spec,
replace{d} with DATE. As someone suggested, turn escape processing off and it will work fine. 
>
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> Hi,
>> when using dollar quoting in my statements I knew it would be postgresql specific. That was ok since our project
isn'tlikely to be ported to another db.  It wasn't meant to be multiy db platform compatible. If anybody want's to
writedbms 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
itis a documented postgresql feature and it is understood by PgAdmin itself (and other postgresql drivers), I think the
postgresqljdbc 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@yahoo.com]
>> Gesendet: Freitag, 21. September 2012 18:43
>> An: GEISINGER Marc - Contractor; 'Dave Cramer'
>> Cc: pgsql-jdbc@postgresql.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
shouldhopefully 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
someonenot 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
invaliddata). 
>>
>> David J.
>>
>>
>>> -----Original Message-----
>>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>>> owner@postgresql.org] On Behalf Of David Johnston
>>> Sent: Friday, September 21, 2012 12:22 PM
>>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
>> changes
>>> to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>

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

From

Dave Cramer

Date:

24 September 2012, 16:21:34

David,

Thanks for the summary, I understand the problem now. I had a look at
the code and the current parser basically looks at 1 character to
determine state. Having to look at N characters to look at state will
be a significant amount of work.I'd certainly welcome a patch.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 8:55 AM, David Johnston <polobo@yahoo.com> wrote:
> Marc,
>
> Since you have gotten this far, and seem to understand the issue fairly
> well, are you in a position to contribute back to the project and submit a
> patch for the community to review and hopefully apply that will implement
> this feature?
>
> Even if the community agrees on the merits of the feature someone will still
> have to take time to actually implement it.  It is not a bug, contrary to
> your claims, for the reasons I've stated before and the impact of the
> behavior on the community seems negligible since no one has bothered to
> implement this and it has been around pretty much since day one.  Most
> people likely just turn off JDBC escape processing as being not worth the
> hassle.
>
> I'm actually considered doing it myself (the patch) but the better solution
> for me is to do just that (turn off escaping) and given my own priorities
> that is what I am going to do for now.
>
> @Dave Cramer
> The main reason for turning off processing, and not just treating
> dollar-quoting as a string literal, is to try and help any developer who do
> wants to code generically using JDBC escapes but inadvertently uses
> dollar-quoting.  In that situation their attempt is going to fail on all
> other databases and thus it should arguably fail on PostgreSQL as well.  The
> solution for them is to use normal quoting if they want to use JDBC escaping
> or forgo escaping if they want to use dollar-quoting.  Enabling both has
> some merit but IMO it makes the driver to forgiving in the face of
> ambiguity.  That said there are transition concerns to be discussed.  Also,
> a flag to raise some form of warning if an escape sequence is found inside
> dollar-quoting is probably worthwhile as well.  That should help in
> identifying problem SQL in existing "static" code.
>
> David J.
>
>> -----Original Message-----
>> From: GEISINGER Marc - Contractor
>> [mailto:Marc.GEISINGER@external.thalesgroup.com]
>> Sent: Monday, September 24, 2012 7:54 AM
>> To: Dave Cramer
>> Cc: David Johnston; pgsql-jdbc@postgresql.org
>> Subject: AW: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>>
>> Hi Dave,
>> The comment i am refering to is in
>> org.postgresql.jdbc2.AbstractJdbc2Statement and it says:
>>
>>     /*
>>      * Filter the SQL string of Java SQL Escape clauses.
>>      *
>>      * Currently implemented Escape clauses are those mentioned in 11.3
>>      * in the specification. Basically we look through the sql string for
>>      * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
>>      * code. When we find them, we just strip the escape part leaving only
>>      * the xxx part.
>>      * So, something like "select * from x where d={d '2001-10-09'}" would
>>      * return "select * from x where d= '2001-10-09'".
>>      */
>>     protected String replaceProcessing(String p_sql) throws SQLException
>>
>> And I don't want escape processing to be turned off just for me, I just
> want
>> dollar quoted strings to be treated the same as strings in single quotes
> would
>> be. In the replaceProcessing method there is another comment:
>>
>> // Since escape codes can only appear in SQL CODE, we keep track // of if
> we
>> enter a string or not.
>>
>> Everything between dollar quotes is not sql code.
>>
>> Marc
>>
>> -----Ursprüngliche Nachricht-----
>> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
>> von Dave Cramer
>> Gesendet: Montag, 24. September 2012 13:42
>> An: GEISINGER Marc - Contractor
>> Cc: David Johnston; pgsql-jdbc@postgresql.org
>> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>>
>> Marc,
>>
>> Where is this comment you are referring to ? What I suspect you want is
>> once a dollar quote is found you want escape processing to be turned off?
>> What do I do with the next guy who wants it back on ?
>>
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor
>> <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> > Hi Dave,
>> > I don't want this feature removed. I just would like to see this fixed
> so that
>> dollar quoted strings are correctly seen as strings, and therefor without
> sql
>> escape clauses replacing. Like the comment for replaceProcessing says
> "look
>> through non-string sql code". Dollar quoted strings are strings for
> postgresql.
>> >
>> > Marc
>> >
>> > -----Ursprüngliche Nachricht-----
>> > Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
>> von
>> > Dave Cramer
>> > Gesendet: Montag, 24. September 2012 11:28
>> > An: GEISINGER Marc - Contractor
>> > Cc: David Johnston; pgsql-jdbc@postgresql.org
>> > Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
>> using dollar quoting?
>> >
>> > Marc,
>> >
>> > I haven't had time to look at this in detail but I suspect that what
>> > is happening is that a JDBC *feature* which is here
>> >
>> http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescap
>> > e.html explains why {d} is replaced by DATE. I'm not sure how you
>> > would like to see this fixed ? This is the JBDC spec, replace {d} with
> DATE. As
>> someone suggested, turn escape processing off and it will work fine.
>> >
>> >
>> > dave.cramer(at)credativ(dot)ca
>> > http://www.credativ.ca
>> >
>> >
>> > On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor
>> <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> >> 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@yahoo.com]
>> >> Gesendet: Freitag, 21. September 2012 18:43
>> >> An: GEISINGER Marc - Contractor; 'Dave Cramer'
>> >> Cc: pgsql-jdbc@postgresql.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@postgresql.org [mailto:pgsql-jdbc-
>> >>> owner@postgresql.org] On Behalf Of David Johnston
>> >>> Sent: Friday, September 21, 2012 12:22 PM
>> >>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>> >>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
>> >> changes
>> >>> to your subscription:
>> >>> http://www.postgresql.org/mailpref/pgsql-jdbc
>> >>
>

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

From

"David Johnston"

Date:

24 September 2012, 16:23:16

Marc,

Since you have gotten this far, and seem to understand the issue fairly
well, are you in a position to contribute back to the project and submit a
patch for the community to review and hopefully apply that will implement
this feature?

Even if the community agrees on the merits of the feature someone will still
have to take time to actually implement it.  It is not a bug, contrary to
your claims, for the reasons I've stated before and the impact of the
behavior on the community seems negligible since no one has bothered to
implement this and it has been around pretty much since day one.  Most
people likely just turn off JDBC escape processing as being not worth the
hassle.

I'm actually considered doing it myself (the patch) but the better solution
for me is to do just that (turn off escaping) and given my own priorities
that is what I am going to do for now.

@Dave Cramer
The main reason for turning off processing, and not just treating
dollar-quoting as a string literal, is to try and help any developer who do
wants to code generically using JDBC escapes but inadvertently uses
dollar-quoting.  In that situation their attempt is going to fail on all
other databases and thus it should arguably fail on PostgreSQL as well.  The
solution for them is to use normal quoting if they want to use JDBC escaping
or forgo escaping if they want to use dollar-quoting.  Enabling both has
some merit but IMO it makes the driver to forgiving in the face of
ambiguity.  That said there are transition concerns to be discussed.  Also,
a flag to raise some form of warning if an escape sequence is found inside
dollar-quoting is probably worthwhile as well.  That should help in
identifying problem SQL in existing "static" code.

David J.

> -----Original Message-----
> From: GEISINGER Marc - Contractor
> [mailto:Marc.GEISINGER@external.thalesgroup.com]
> Sent: Monday, September 24, 2012 7:54 AM
> To: Dave Cramer
> Cc: David Johnston; pgsql-jdbc@postgresql.org
> Subject: AW: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> Hi Dave,
> The comment i am refering to is in
> org.postgresql.jdbc2.AbstractJdbc2Statement and it says:
>
>     /*
>      * Filter the SQL string of Java SQL Escape clauses.
>      *
>      * Currently implemented Escape clauses are those mentioned in 11.3
>      * in the specification. Basically we look through the sql string for
>      * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
>      * code. When we find them, we just strip the escape part leaving only
>      * the xxx part.
>      * So, something like "select * from x where d={d '2001-10-09'}" would
>      * return "select * from x where d= '2001-10-09'".
>      */
>     protected String replaceProcessing(String p_sql) throws SQLException
>
> And I don't want escape processing to be turned off just for me, I just
want
> dollar quoted strings to be treated the same as strings in single quotes
would
> be. In the replaceProcessing method there is another comment:
>
> // Since escape codes can only appear in SQL CODE, we keep track // of if
we
> enter a string or not.
>
> Everything between dollar quotes is not sql code.
>
> Marc
>
> -----Ursprüngliche Nachricht-----
> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
> von Dave Cramer
> Gesendet: Montag, 24. September 2012 13:42
> An: GEISINGER Marc - Contractor
> Cc: David Johnston; pgsql-jdbc@postgresql.org
> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> Marc,
>
> Where is this comment you are referring to ? What I suspect you want is
> once a dollar quote is found you want escape processing to be turned off?
> What do I do with the next guy who wants it back on ?
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor
> <Marc.GEISINGER@external.thalesgroup.com> wrote:
> > Hi Dave,
> > I don't want this feature removed. I just would like to see this fixed
so that
> dollar quoted strings are correctly seen as strings, and therefor without
sql
> escape clauses replacing. Like the comment for replaceProcessing says
"look
> through non-string sql code". Dollar quoted strings are strings for
postgresql.
> >
> > Marc
> >
> > -----Ursprüngliche Nachricht-----
> > Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
> von
> > Dave Cramer
> > Gesendet: Montag, 24. September 2012 11:28
> > An: GEISINGER Marc - Contractor
> > Cc: David Johnston; pgsql-jdbc@postgresql.org
> > Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
> >
> > Marc,
> >
> > I haven't had time to look at this in detail but I suspect that what
> > is happening is that a JDBC *feature* which is here
> >
> http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescap
> > e.html explains why {d} is replaced by DATE. I'm not sure how you
> > would like to see this fixed ? This is the JBDC spec, replace {d} with
DATE. As
> someone suggested, turn escape processing off and it will work fine.
> >
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor
> <Marc.GEISINGER@external.thalesgroup.com> wrote:
> >> 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@yahoo.com]
> >> Gesendet: Freitag, 21. September 2012 18:43
> >> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> >> Cc: pgsql-jdbc@postgresql.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@postgresql.org [mailto:pgsql-jdbc-
> >>> owner@postgresql.org] On Behalf Of David Johnston
> >>> Sent: Friday, September 21, 2012 12:22 PM
> >>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
> >>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
> >> changes
> >>> to your subscription:
> >>> http://www.postgresql.org/mailpref/pgsql-jdbc
> >>

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

From

GEISINGER Marc - Contractor

Date:

24 September 2012, 16:34:16

Hi,
when using dollar quoting in my statements I knew it would be postgresql specific. That was ok since our project isn't
likelyto be ported to another db.  It wasn't meant to be multiy db platform compatible. If anybody want's to write dbms
independentcode, 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
isa documented postgresql feature and it is understood by PgAdmin itself (and other postgresql drivers), I think the
postgresqljdbc 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@yahoo.com]
Gesendet: Freitag, 21. September 2012 18:43
An: GEISINGER Marc - Contractor; 'Dave Cramer'
Cc: pgsql-jdbc@postgresql.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
shouldhopefully 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
someonenot 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
invaliddata). 

David J.


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of David Johnston
> Sent: Friday, September 21, 2012 12:22 PM
> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

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

From

GEISINGER Marc - Contractor

Date:

24 September 2012, 16:35:10

Hi Dave,
The comment i am refering to is in org.postgresql.jdbc2.AbstractJdbc2Statement and it says:

    /*
     * Filter the SQL string of Java SQL Escape clauses.
     *
     * Currently implemented Escape clauses are those mentioned in 11.3
     * in the specification. Basically we look through the sql string for
     * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
     * code. When we find them, we just strip the escape part leaving only
     * the xxx part.
     * So, something like "select * from x where d={d '2001-10-09'}" would
     * return "select * from x where d= '2001-10-09'".
     */
    protected String replaceProcessing(String p_sql) throws SQLException

And I don't want escape processing to be turned off just for me, I just want dollar quoted strings to be treated the
sameas strings in single quotes would be. In the replaceProcessing method there is another comment: 

// Since escape codes can only appear in SQL CODE, we keep track
// of if we enter a string or not.

Everything between dollar quotes is not sql code.

Marc

-----Ursprüngliche Nachricht-----
Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag von Dave Cramer
Gesendet: Montag, 24. September 2012 13:42
An: GEISINGER Marc - Contractor
Cc: David Johnston; pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

Marc,

Where is this comment you are referring to ? What I suspect you want is once a dollar quote is found you want escape
processingto be turned off? What do I do with the next guy who wants it back on ? 


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor <Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi Dave,
> I don't want this feature removed. I just would like to see this fixed so that dollar quoted strings are correctly
seenas strings, and therefor without sql escape clauses replacing. Like the comment for replaceProcessing says "look
throughnon-string sql code". Dollar quoted strings are strings for postgresql. 
>
> Marc
>
> -----Ursprüngliche Nachricht-----
> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag von
> Dave Cramer
> Gesendet: Montag, 24. September 2012 11:28
> An: GEISINGER Marc - Contractor
> Cc: David Johnston; pgsql-jdbc@postgresql.org
> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?
>
> Marc,
>
> I haven't had time to look at this in detail but I suspect that what
> is happening is that a JDBC *feature* which is here
> http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescap
> e.html explains why {d} is replaced by DATE. I'm not sure how you
> would like to see this fixed ? This is the JBDC spec, replace {d} with DATE. As someone suggested, turn escape
processingoff and it will work fine. 
>
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor <Marc.GEISINGER@external.thalesgroup.com> wrote:
>> Hi,
>> when using dollar quoting in my statements I knew it would be postgresql specific. That was ok since our project
isn'tlikely to be ported to another db.  It wasn't meant to be multiy db platform compatible. If anybody want's to
writedbms 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
itis a documented postgresql feature and it is understood by PgAdmin itself (and other postgresql drivers), I think the
postgresqljdbc 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@yahoo.com]
>> Gesendet: Freitag, 21. September 2012 18:43
>> An: GEISINGER Marc - Contractor; 'Dave Cramer'
>> Cc: pgsql-jdbc@postgresql.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
shouldhopefully 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
someonenot 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
invaliddata). 
>>
>> David J.
>>
>>
>>> -----Original Message-----
>>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>>> owner@postgresql.org] On Behalf Of David Johnston
>>> Sent: Friday, September 21, 2012 12:22 PM
>>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
>> changes
>>> to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>

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

From

"David Johnston"

Date:

24 September 2012, 17:02:41

Is there any SQL syntax that allows for a dollar-sign outside of a string
literal?  If not then if we are not in a string and we encounter a
dollar-sign we could assume that we are dealing with a PostgreSQL string and
immediately return the original input unchanged.

Baring that option I was pondering pre-processing the entire string with
something like:

Regex Matcher: '(\$[a-zA-z]*\$).+?(\1)' --syntax might be off but basically
check for at least one matching pair of dollar-quotes and if found disable
escape processing.

You could also implement a look-ahead mechanic within the parser so that
when you encounter "$" outside of a string you at least verify that you
encounter another dollar-sign before you encounter a space (or other invalid
identifier symbol).

I'll repeat but it is worth having someone agree explicitly that it is "an
error" conceptually to mix JDBC escapes and PostgreSQL dollar-quoting.
Either we should throw an exception if this occurs (making the parser more
robust but increasing the coding difficulty somewhat) or simply define the
behavior that in the presence of dollar-quoting any JDBC escapes are
silently ignored - basically making the server responsible for throwing a
generic syntax error instead of JDBC throwing a more precise
"MixedSyntaxEnvironment" exception.

David J.


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Dave Cramer
> Sent: Monday, September 24, 2012 9:21 AM
> To: David Johnston
> Cc: GEISINGER Marc - Contractor; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> David,
>
> Thanks for the summary, I understand the problem now. I had a look at the
> code and the current parser basically looks at 1 character to determine
state.
> Having to look at N characters to look at state will be a significant
amount of
> work.I'd certainly welcome a patch.
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Sep 24, 2012 at 8:55 AM, David Johnston <polobo@yahoo.com>
> wrote:
> > Marc,
> >
> > Since you have gotten this far, and seem to understand the issue
> > fairly well, are you in a position to contribute back to the project
> > and submit a patch for the community to review and hopefully apply
> > that will implement this feature?
> >
> > Even if the community agrees on the merits of the feature someone will
> > still have to take time to actually implement it.  It is not a bug,
> > contrary to your claims, for the reasons I've stated before and the
> > impact of the behavior on the community seems negligible since no one
> > has bothered to implement this and it has been around pretty much
> > since day one.  Most people likely just turn off JDBC escape
> > processing as being not worth the hassle.
> >
> > I'm actually considered doing it myself (the patch) but the better
> > solution for me is to do just that (turn off escaping) and given my
> > own priorities that is what I am going to do for now.
> >
> > @Dave Cramer
> > The main reason for turning off processing, and not just treating
> > dollar-quoting as a string literal, is to try and help any developer
> > who do wants to code generically using JDBC escapes but inadvertently
> > uses dollar-quoting.  In that situation their attempt is going to fail
> > on all other databases and thus it should arguably fail on PostgreSQL
> > as well.  The solution for them is to use normal quoting if they want
> > to use JDBC escaping or forgo escaping if they want to use
> > dollar-quoting.  Enabling both has some merit but IMO it makes the
> > driver to forgiving in the face of ambiguity.  That said there are
> > transition concerns to be discussed.  Also, a flag to raise some form
> > of warning if an escape sequence is found inside dollar-quoting is
> > probably worthwhile as well.  That should help in identifying problem
SQL in
> existing "static" code.
> >
> > David J.
> >
> >> -----Original Message-----
> >> From: GEISINGER Marc - Contractor
> >> [mailto:Marc.GEISINGER@external.thalesgroup.com]
> >> Sent: Monday, September 24, 2012 7:54 AM
> >> To: Dave Cramer
> >> Cc: David Johnston; pgsql-jdbc@postgresql.org
> >> Subject: AW: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing
> >> when using dollar quoting?
> >>
> >> Hi Dave,
> >> The comment i am refering to is in
> >> org.postgresql.jdbc2.AbstractJdbc2Statement and it says:
> >>
> >>     /*
> >>      * Filter the SQL string of Java SQL Escape clauses.
> >>      *
> >>      * Currently implemented Escape clauses are those mentioned in 11.3
> >>      * in the specification. Basically we look through the sql string
for
> >>      * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string
sql
> >>      * code. When we find them, we just strip the escape part leaving
only
> >>      * the xxx part.
> >>      * So, something like "select * from x where d={d '2001-10-09'}"
would
> >>      * return "select * from x where d= '2001-10-09'".
> >>      */
> >>     protected String replaceProcessing(String p_sql) throws
> >> SQLException
> >>
> >> And I don't want escape processing to be turned off just for me, I
> >> just
> > want
> >> dollar quoted strings to be treated the same as strings in single
> >> quotes
> > would
> >> be. In the replaceProcessing method there is another comment:
> >>
> >> // Since escape codes can only appear in SQL CODE, we keep track //
> >> of if
> > we
> >> enter a string or not.
> >>
> >> Everything between dollar quotes is not sql code.
> >>
> >> Marc
> >>
> >> -----Ursprüngliche Nachricht-----
> >> Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag
> >> von Dave Cramer
> >> Gesendet: Montag, 24. September 2012 13:42
> >> An: GEISINGER Marc - Contractor
> >> Cc: David Johnston; pgsql-jdbc@postgresql.org
> >> Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing
> >> when using dollar quoting?
> >>
> >> Marc,
> >>
> >> Where is this comment you are referring to ? What I suspect you want
> >> is once a dollar quote is found you want escape processing to be turned
> off?
> >> What do I do with the next guy who wants it back on ?
> >>
> >>
> >> Dave Cramer
> >>
> >> dave.cramer(at)credativ(dot)ca
> >> http://www.credativ.ca
> >>
> >>
> >> On Mon, Sep 24, 2012 at 6:43 AM, GEISINGER Marc - Contractor
> >> <Marc.GEISINGER@external.thalesgroup.com> wrote:
> >> > Hi Dave,
> >> > I don't want this feature removed. I just would like to see this
> >> > fixed
> > so that
> >> dollar quoted strings are correctly seen as strings, and therefor
> >> without
> > sql
> >> escape clauses replacing. Like the comment for replaceProcessing says
> > "look
> >> through non-string sql code". Dollar quoted strings are strings for
> > postgresql.
> >> >
> >> > Marc
> >> >
> >> > -----Ursprüngliche Nachricht-----
> >> > Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im
> Auftrag
> >> von
> >> > Dave Cramer
> >> > Gesendet: Montag, 24. September 2012 11:28
> >> > An: GEISINGER Marc - Contractor
> >> > Cc: David Johnston; pgsql-jdbc@postgresql.org
> >> > Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing
> >> > when
> >> using dollar quoting?
> >> >
> >> > Marc,
> >> >
> >> > I haven't had time to look at this in detail but I suspect that
> >> > what is happening is that a JDBC *feature* which is here
> >> >
> >>
> http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlesca
> >> p
> >> > e.html explains why {d} is replaced by DATE. I'm not sure how you
> >> > would like to see this fixed ? This is the JBDC spec, replace {d}
> >> > with
> > DATE. As
> >> someone suggested, turn escape processing off and it will work fine.
> >> >
> >> >
> >> > dave.cramer(at)credativ(dot)ca
> >> > http://www.credativ.ca
> >> >
> >> >
> >> > On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor
> >> <Marc.GEISINGER@external.thalesgroup.com> wrote:
> >> >> 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@yahoo.com]
> >> >> Gesendet: Freitag, 21. September 2012 18:43
> >> >> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> >> >> Cc: pgsql-jdbc@postgresql.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@postgresql.org [mailto:pgsql-jdbc-
> >> >>> owner@postgresql.org] On Behalf Of David Johnston
> >> >>> Sent: Friday, September 21, 2012 12:22 PM
> >> >>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
> >> >>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To
> >> >>> make
> >> >> changes
> >> >>> to your subscription:
> >> >>> http://www.postgresql.org/mailpref/pgsql-jdbc
> >> >>
> >
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

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

From

GEISINGER Marc - Contractor

Date:

24 September 2012, 17:08:00

Hi Dave,
I don't want this feature removed. I just would like to see this fixed so that dollar quoted strings are correctly seen
asstrings, and therefor without sql escape clauses replacing. Like the comment for replaceProcessing says "look through
non-stringsql code". Dollar quoted strings are strings for postgresql. 

Marc

-----Ursprüngliche Nachricht-----
Von: davecramer@gmail.com [mailto:davecramer@gmail.com] Im Auftrag von Dave Cramer
Gesendet: Montag, 24. September 2012 11:28
An: GEISINGER Marc - Contractor
Cc: David Johnston; pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

Marc,

I haven't had time to look at this in detail but I suspect that what is happening is that a JDBC *feature* which is
herehttp://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/sqlescape.html 
explains why {d} is replaced by DATE. I'm not sure how you would like to see this fixed ? This is the JBDC spec,
replace{d} with DATE. As someone suggested, turn escape processing off and it will work fine. 


dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 3:13 AM, GEISINGER Marc - Contractor <Marc.GEISINGER@external.thalesgroup.com> wrote:
> Hi,
> when using dollar quoting in my statements I knew it would be postgresql specific. That was ok since our project
isn'tlikely to be ported to another db.  It wasn't meant to be multiy db platform compatible. If anybody want's to
writedbms 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
isa documented postgresql feature and it is understood by PgAdmin itself (and other postgresql drivers), I think the
postgresqljdbc 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@yahoo.com]
> Gesendet: Freitag, 21. September 2012 18:43
> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> Cc: pgsql-jdbc@postgresql.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
shouldhopefully 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
someonenot 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
invaliddata). 
>
> David J.
>
>
>> -----Original Message-----
>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>> owner@postgresql.org] On Behalf Of David Johnston
>> Sent: Friday, September 21, 2012 12:22 PM
>> To: 'GEISINGER Marc - Contractor'; 'Dave Cramer'
>> Cc: pgsql-jdbc@postgresql.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@postgresql.org) To make
> changes
>> to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>

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

From

Maciek Sakrejda

Date:

24 September 2012, 18:16:24

On Mon, Sep 24, 2012 at 6:54 AM, David Johnston <polobo@yahoo.com> wrote:
> Is there any SQL syntax that allows for a dollar-sign outside of a string
> literal?  If not then if we are not in a string and we encounter a
> dollar-sign we could assume that we are dealing with a PostgreSQL string and
> immediately return the original input unchanged.

Identifiers can also contain "$" when quoted:

=> create table "$"("$" int);
CREATE TABLE

Not to mention comments.

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

From

"David Johnston"

Date:

24 September 2012, 18:49:52

> Hi Dave,
> I don't want this feature removed. I just would like to see this fixed so
that
> dollar quoted strings are correctly seen as strings, and therefor without
sql
> escape clauses replacing. Like the comment for replaceProcessing says
"look
> through non-string sql code". Dollar quoted strings are strings for
postgresql.
>

But they are not "strings" for JDBC.  The JDBC syntax is a distinct entity
and if you use JDBC features you are expected to adhere to its standards.

Furthermore, the code comment in the driver does not constitute the
canonical behavior of the JDBC specification.

Regardless of whether the JDBC specification addresses what a valid "string
delimiter" is the fact remains that such a definition has to be made at the
JDBC level if the definition is used to implement a JDBC-only feature.

Since unquoted identifiers can include the dollar-sign in PostgreSQL a
broader analysis of the entire query is going to be required.  It would
behoove whoever decides to implement this to look and see exactly how
PostgreSQL itself handles identification of dollar-quoting versus
dollar-including-identifiers.

The semantics and definition, though, are pretty much meaningless.  Someone
still has to actually code an implementation regardless of whether it is a
"bug" or a "enhancement".  Or, as mentioned before, you can simply fix your
own environment so that you can enable or disable escape processing as you
deem necessary.  I still feel that mixing the two behaviors is wrong and no
attempt to do so should be made.  At best identify that mixing has occurred
and thrown an exception.  At worse ignore the escapes and let the database
inform the user of their error.

From the lack of other responses so far I do not see that you have much
choice other than to code something yourself.  It is up to you to decide
whether to simply fix the artificial limitations imposed by your own
development environment (and thus disable escape processing for your
queries) or whether you want to attempt to improve the driver and thus share
your efforts with the rest of the community.  Or maybe fund the improvement
of the driver by someone else.  At worse even if you cannot code it yourself
you can propose pseudo-code/algorithm that people could review and then
someone could just code and deploy.

The main issue I see here is that the behavior change probably isn't that
bad to code.  The performance implications of such a change - which impacts
every single query to determine whether it uses dollar-quoting - would be
the biggest concern.  To avoid this impact it is reasonable for the driver
to simply provide an API so that user can tell it when dollar-quoting is in
use and when it is not (again, a hybrid approach is not worthwhile).  If the
user has to deal with queries where it is unknown (and the decision is
important) they can code whatever pre-processor algorithm they feel is
appropriate to make that determination for their own project.

David J.

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

From

Tom Lane

Date:

24 September 2012, 21:34:08

Maciek Sakrejda <m.sakrejda@gmail.com> writes:
> On Mon, Sep 24, 2012 at 6:54 AM, David Johnston <polobo@yahoo.com> wrote:
>> Is there any SQL syntax that allows for a dollar-sign outside of a string
>> literal?  If not then if we are not in a string and we encounter a
>> dollar-sign we could assume that we are dealing with a PostgreSQL string and
>> immediately return the original input unchanged.

> Identifiers can also contain "$" when quoted:

Identifiers can contain "$" even when not quoted ...

regression=# create table foo$ (bar$ int);
CREATE TABLE

I don't think that's per SQL spec, but PG allows it, mainly because some
other databases do.

            regards, tom lane

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

From

Dave Cramer

Date:

24 September 2012, 21:44:04

Right so the parser basically has to look for $<optionalstring>$ in
order to determine if it is in dollar quoting. As David Johnstone
mentioned this is not part of the JDBC spec and would have an impact
on all queries. I'm not getting terribly motivated to implement this.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Sep 24, 2012 at 2:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Maciek Sakrejda <m.sakrejda@gmail.com> writes:
>> On Mon, Sep 24, 2012 at 6:54 AM, David Johnston <polobo@yahoo.com> wrote:
>>> Is there any SQL syntax that allows for a dollar-sign outside of a string
>>> literal?  If not then if we are not in a string and we encounter a
>>> dollar-sign we could assume that we are dealing with a PostgreSQL string and
>>> immediately return the original input unchanged.
>
>> Identifiers can also contain "$" when quoted:
>
> Identifiers can contain "$" even when not quoted ...
>
> regression=# create table foo$ (bar$ int);
> CREATE TABLE
>
> I don't think that's per SQL spec, but PG allows it, mainly because some
> other databases do.
>
>                         regards, tom lane

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

From

David Johnston

Date:

24 September 2012, 22:17:31

On Sep 24, 2012, at 14:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Maciek Sakrejda <m.sakrejda@gmail.com> writes:
>> On Mon, Sep 24, 2012 at 6:54 AM, David Johnston <polobo@yahoo.com> wrote:
>>> Is there any SQL syntax that allows for a dollar-sign outside of a string
>>> literal?  If not then if we are not in a string and we encounter a
>>> dollar-sign we could assume that we are dealing with a PostgreSQL string and
>>> immediately return the original input unchanged.
>
>> Identifiers can also contain "$" when quoted:
>
> Identifiers can contain "$" even when not quoted ...
>
> regression=# create table foo$ (bar$ int);
> CREATE TABLE
>
> I don't think that's per SQL spec, but PG allows it, mainly because some
> other databases do.
>
>            regards, tom lane
>

This is true but I just realized that for dollar-quoting the dollar sign must start the character sequence while for an
unquotedidentifier it must not begin the character sequence.  Since we know whether our current position qualifies for
start-of-sequenceupon encountering a valid dollar sign we should know whether we are continuing an identifier or
startinga string literal. 

David J.

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

From

Kris Jurka

Date:

25 September 2012, 06:17:31

On Thu, 20 Sep 2012, GEISINGER Marc - Contractor wrote:

>
> I think there is a bug in AbstracJdbc2Statement.replaceProcessing.  When I
> am trying to execute the following sql command with a java.sql.Statement the
> data written to the database is not what it should be:
>
> INSERT INTO dev.stringtest (id, val) VALUES (1, $a$ {a}{f} {b} {f}$a$)
>
> The replaceProcessing method removes {f} and cuts the string.

Yes, this is a bug in the JDBC driver.  The fact that it is a silent
data loss bug makes it more severe.

There is no reason it shouldn't work other than people not having time to
make the driver smart enough to handle dollar quotes in this context.

Kris Jurka

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

From

"David Johnston"

Date:

26 September 2012, 17:59:09

> -----Ursprüngliche Nachricht-----
> Von: David Johnston [mailto:polobo@yahoo.com]
> Gesendet: Montag, 24. September 2012 17:49
> An: GEISINGER Marc - Contractor; 'Dave Cramer'
> Cc: pgsql-jdbc@postgresql.org
> Betreff: RE: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
>
> > At worse even if you cannot code it yourself you can propose pseudo-
> code/algorithm that people could review and then someone could just code
> > and deploy.
>
> Looking at
> http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
>
> 4.1.1. Identifiers and Key Words
> ...
> SQL identifiers and key words must begin with a letter (a-z, but also
letters
> with diacritical marks and non-Latin letters) or an underscore (_).
Subsequent
> characters in an identifier or key word can be letters, underscores,
digits (0-
> 9), or dollar signs ($). Note that dollar signs are not allowed in
identifiers
> according to the letter of the SQL standard, so their use might render
> applications less portable.
>
> it seems to me, that $ are not allowed as identifiers by the SQL standard.
But
> they are allowed in postgresql. But also in postgresql they are not
allowed at
> the beginning of an identifier.
> As far as I understand the parseSql method (line 864) in
> AbstractJdbc2Statement class, the looking for a dollar quoted string could
be
> done similar to the looking for escape codes (line 894).
> If we are IN_SQLCODE and we find a '$' as character, look through the
> following chars until the next '$' is found. Anything between the two '$'
is the
> tag. Then we would have a state IN_DOLLARQOUTE, until we find this '$tag$'
> again.
> The only problem I see with this idea might come from nested dollar
quotes.
>
> Marc

Which is why my suggestion is possibly superior and at least simpler (thus
less prone to bugs).

Once you find the non-conforming dollar-quote start tag in the query simply
abort the JDBC specific code and return the query unmodified.

As I mentioned in a separate post a dollar-sign cannot begin the identifier
while it must begin the dollar-quote so we should be able to unambiguously
determine which of the two options is valid upon encountering a dollar-sign
without having to perform any further scan.

You now remove the "information losing" quality of the current behavior.
I'm personally willing to risk the breakage of existing mixed code on the
basis that such mixing is inconsistent with the goal of the standard in the
first place and thus is likely to be minimal at best.  As a middle ground
the rest of the query should be scanned for JDBC escapes and, if found
(anywhere, even inside a dollar-quoted string), a RuntimeException sub-class
should be thrown pointing out the use of "mixed syntax".

That aside, nested quoting is not a concern; the string literal does not end
until the matching ending tag is found (i.e., at no point after encountering
a dollar-quote start tag does the parser state become IN_SQLCODE - until the
closing quote is matched).

David J.

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

From

GEISINGER Marc - Contractor

Date:

26 September 2012, 18:29:51


-----Ursprüngliche Nachricht-----
Von: David Johnston [mailto:polobo@yahoo.com]
Gesendet: Montag, 24. September 2012 17:49
An: GEISINGER Marc - Contractor; 'Dave Cramer'
Cc: pgsql-jdbc@postgresql.org
Betreff: RE: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?


> At worse even if you cannot code it yourself you can propose pseudo-code/algorithm that people could review and then
someonecould just code > and deploy. 

Looking at
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html

4.1.1. Identifiers and Key Words
...
SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin
letters)or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits
(0-9),or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL
standard,so their use might render applications less portable. 

it seems to me, that $ are not allowed as identifiers by the SQL standard. But they are allowed in postgresql. But also
inpostgresql they are not allowed at the beginning of an identifier. 
As far as I understand the parseSql method (line 864) in AbstractJdbc2Statement class, the looking for a dollar quoted
stringcould be done similar to the looking for escape codes (line 894).  
If we are IN_SQLCODE and we find a '$' as character, look through the following chars until the next '$' is found.
Anythingbetween the two '$' is the tag. Then we would have a state IN_DOLLARQOUTE, until we find this '$tag$' again. 
The only problem I see with this idea might come from nested dollar quotes.

Marc

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

From

GEISINGER Marc - Contractor

Date:

26 September 2012, 18:29:53


-----Ursprüngliche Nachricht-----
Von: David Johnston [mailto:polobo@yahoo.com]
Gesendet: Mittwoch, 26. September 2012 16:58
An: GEISINGER Marc - Contractor; 'Dave Cramer'
Cc: pgsql-jdbc@postgresql.org
Betreff: RE: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?


> Which is why my suggestion is possibly superior and at least simpler (thus less prone to bugs).
> Once you find the non-conforming dollar-quote start tag in the query simply abort the JDBC specific code and return
thequery unmodified. 

Since $ in identifiers are postgres-only, there is no reason to try to be postgres-indepentend with SQL escape codes. I
thinkyour suggestion would be ok to handle dollar quotes. 

Marc

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

From

Florent Guillaume

Date:

27 September 2012, 16:02:24

On Wed, Sep 26, 2012 at 5:07 PM, GEISINGER Marc - Contractor
<Marc.GEISINGER@external.thalesgroup.com> wrote:
> Von: David Johnston [mailto:polobo@yahoo.com]
>> Which is why my suggestion is possibly superior and at least simpler (thus less prone to bugs).
>> Once you find the non-conforming dollar-quote start tag in the query simply abort the JDBC specific code and return
thequery unmodified. 
>
> Since $ in identifiers are postgres-only, there is no reason to try to be postgres-indepentend with SQL escape codes.
Ithink your suggestion would be ok to handle dollar quotes. 

Oracle is well known for its V$SESSION and other similar tables.

Florent

--
Florent Guillaume, Director of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87

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

From

Marc Geisinger

Date:

09 October 2012, 18:15:39

Even if there is 'unknown' as author, I was using my google account now to post this answer.

Marc

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

From

marc.mg75@googlemail.com

Date:

09 October 2012, 18:16:18

Am Donnerstag, 27. September 2012 15:02:28 UTC+2 schrieb Florent Guillaume:

> Oracle is well known for its V$SESSION and other similar tables.

Hi,
even if Oracle uses V$SESSIONs, the $ here is not at the beginning, and it also does not fit the sql standard.

I made a small patch doing what was said above. While parsing the sql code it checks if there is a $ while in the state
IN_SQLCODE,if so it stops the parsing and returns the original sql. 

I ran the testsuite and it passed with no error. I also checked my example and the result returned is what would be
expected.


--- /org/postgresql/jdbc2/AbstractJdbc2Statement.java_orig    Tue Oct 09 10:25:10 2012
+++ /org/postgresql/jdbc2/AbstractJdbc2Statement.java    Tue Oct 09 10:31:50 2012
@@ -895,6 +895,7 @@
             int len = p_sql.length();
             StringBuffer newsql = new StringBuffer(len);
             int i=0;
+            try {
             while (i<len){
                 i=parseSql(p_sql,i,newsql,false,connection.getStandardConformingStrings());
                 // We need to loop here in case we encounter invalid
@@ -907,6 +908,11 @@
                     i++;
                 }
             }
+        } catch (final PGDollarQuoteParsingException e) {
+                // found dollar quoting in the sql string. do not parse for
+                // escape clauses and return the original sql.
+                return p_sql;
+        }
             return newsql.toString();
         }
         else
@@ -929,7 +935,7 @@
      * @return the position we stopped processing at
      */
     protected static int parseSql(String p_sql,int i,StringBuffer newsql, boolean stopOnComma,
-                                  boolean stdStrings)throws SQLException{
+                                  boolean stdStrings)throws SQLException, PGDollarQuoteParsingException {
         short state = IN_SQLCODE;
         int len = p_sql.length();
         int nestedParenthesis=0;
@@ -955,6 +961,10 @@
                         endOfNested=true;
                         break;
                     }
+            } else if (c == '$') { // start of a dollar quoted string
+                // dollar quoted strings are postgreSql only, throw an
+                // exception to stop parsing for db indepentend syntax.
+                throw new PGDollarQuoteParsingException();
                 } else if (stopOnComma && c==',' && nestedParenthesis==0) {
                     endOfNested=true;
                     break;
@@ -1066,7 +1076,7 @@
      * @param stdStrings whether standard_conforming_strings is on
      * @return the right postgreSql sql
      */
-    protected static String escapeFunction(String functionName, String args, boolean stdStrings) throws SQLException{
+    protected static String escapeFunction(String functionName, String args, boolean stdStrings) throws SQLException,
PGDollarQuoteParsingException{
         // parse function arguments
         int len = args.length();
         int i=0;




And i added an exception for this:


package org.postgresql.core;

public class PGDollarQuoteParsingException extends Exception {

}


Using this exception i handle to stop the parsing. It could also be used to print a warning or whatever.

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

From

"David Johnston"

Date:

09 October 2012, 20:49:13

Hi,

See below for my thoughts on how this is limited and inefficient.

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of marc.mg75@googlemail.com
> Sent: Tuesday, October 09, 2012 7:15 AM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> Am Donnerstag, 27. September 2012 15:02:28 UTC+2 schrieb Florent
> Guillaume:
>
> > Oracle is well known for its V$SESSION and other similar tables.
>
> Hi,
> even if Oracle uses V$SESSIONs, the $ here is not at the beginning, and it
also
> does not fit the sql standard.
>
> I made a small patch doing what was said above. While parsing the sql code
it
> checks if there is a $ while in the state IN_SQLCODE, if so it stops the
parsing
> and returns the original sql.
>
> I ran the testsuite and it passed with no error. I also checked my example
and
> the result returned is what would be expected.
>
>
> --- /org/postgresql/jdbc2/AbstractJdbc2Statement.java_orig    Tue Oct 09
> 10:25:10 2012
> +++ /org/postgresql/jdbc2/AbstractJdbc2Statement.java    Tue Oct 09
> 10:31:50 2012
> @@ -895,6 +895,7 @@
>              int len = p_sql.length();
>              StringBuffer newsql = new StringBuffer(len);
>              int i=0;
> +            try {
>              while (i<len){
>
> i=parseSql(p_sql,i,newsql,false,connection.getStandardConformingStrings())
> ;
>                  // We need to loop here in case we encounter invalid @@
-907,6
> +908,11 @@
>                      i++;
>                  }
>              }
> +        } catch (final PGDollarQuoteParsingException e) {
> +                // found dollar quoting in the sql string.
do
> not parse for
> +                // escape clauses and return the original
sql.
> +                return p_sql;
> +        }
>              return newsql.toString();
>          }
>          else
> @@ -929,7 +935,7 @@
>       * @return the position we stopped processing at
>       */
>      protected static int parseSql(String p_sql,int i,StringBuffer newsql,
boolean
> stopOnComma,
> -                                  boolean stdStrings)throws SQLException{
> +                                  boolean stdStrings)throws
> + SQLException, PGDollarQuoteParsingException {
>          short state = IN_SQLCODE;
>          int len = p_sql.length();
>          int nestedParenthesis=0;
> @@ -955,6 +961,10 @@
>                          endOfNested=true;
>                          break;
>                      }
> +            } else if (c == '$') { // start of a dollar quoted
string
> +                // dollar quoted strings are postgreSql
only,
> throw an
> +                // exception to stop parsing for db
> indepentend syntax.
> +                throw new
> PGDollarQuoteParsingException();
>                  } else if (stopOnComma && c==',' && nestedParenthesis==0)
{
>                      endOfNested=true;
>                      break;
> @@ -1066,7 +1076,7 @@
>       * @param stdStrings whether standard_conforming_strings is on
>       * @return the right postgreSql sql
>       */
> -    protected static String escapeFunction(String functionName, String
args,
> boolean stdStrings) throws SQLException{
> +    protected static String escapeFunction(String functionName, String
> + args, boolean stdStrings) throws SQLException,
> + PGDollarQuoteParsingException{
>          // parse function arguments
>          int len = args.length();
>          int i=0;
>
>
>
>
> And i added an exception for this:
>
>
> package org.postgresql.core;
>
> public class PGDollarQuoteParsingException extends Exception {
>
> }
>
>
> Using this exception i handle to stop the parsing. It could also be used
to print
> a warning or whatever.
>

This patch appears to throw the exception when an unquoted identifier
includes a dollar-sign.  I cannot speak to the standard but it would seem
that presence of the dollar-sign should only be evaluated if we are
"IN_SQLCODE" and the preceding character is NOT (alphanumeric or underscore)
{specifically whatever characters are allowed to begin an unquoted
identifier}.

Also, with the logic provided the addition of an exception and a try/catch
block appears to add unnecessary overhead.  At the point the exception is
thrown I would suggest that we instead:

"return p_sql;"

and let the replaceProcessing method remain ignorant of whether the SQL it
is getting back is the original SQL or a modified version.

The fact is the presence of dollar-quoting with enabled escaping is going to
be the common situation.  Combine that with the fact we are not prompting
the user to "fix" anything means that using the exception mechanism a poor
choice - it is not Exceptional but rather ordinary.  Everything that needs
to be checked and confirmed can be done in the parseSQL method and since it
has access to the original statement it can return the original when
necessary.

David J.

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

From

Marc Geisinger

Date:

10 October 2012, 19:00:59

Am Dienstag, 9. Oktober 2012 19:49:17 UTC+2 schrieb "David Johnston":
> Hi,
>
>
>
> See below for my thoughts on how this is limited and inefficient.
>
...
>
> This patch appears to throw the exception when an unquoted identifier
>
> includes a dollar-sign.  I cannot speak to the standard but it would seem
>
> that presence of the dollar-sign should only be evaluated if we are
>
> "IN_SQLCODE" and the preceding character is NOT (alphanumeric or underscore)
>
> {specifically whatever characters are allowed to begin an unquoted
>
> identifier}.
>
>
>
> Also, with the logic provided the addition of an exception and a try/catch
>
> block appears to add unnecessary overhead.  At the point the exception is
>
> thrown I would suggest that we instead:
>
>
>
> "return p_sql;"
>
>
>
> and let the replaceProcessing method remain ignorant of whether the SQL it
>
> is getting back is the original SQL or a modified version.
>
>
>
> The fact is the presence of dollar-quoting with enabled escaping is going to
>
> be the common situation.  Combine that with the fact we are not prompting
>
> the user to "fix" anything means that using the exception mechanism a poor
>
> choice - it is not Exceptional but rather ordinary.  Everything that needs
>
> to be checked and confirmed can be done in the parseSQL method and since it
>
> has access to the original statement it can return the original when
>
> necessary.
>
>
>
> David J.

Hi,
I know that throwing that exception is a bit... uncommon. But I did that because of the while loop in
replaceProcessing.The code there does call parseSql several times,  at least it can. And therefor I don't know if it is
thefirst call when i found a dollar quote. If i just return the sql unchanged it might have had changes before so the
resultwould be replaced sql code together with original sql code. 
What I wanted to achieve was that replaceProcessing get's to know that there is a dollar quote and stop the
replaceProcessingand return the original sql string (just like replaceProcessingEnabled would have been set to false).
Iwanted to have a kind of stateful exit of the parseSql method. But since it only returns an int value, and might have
beencalled before, throwing an exception was the only thing I found to be able to do that.

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

From

"David Johnston"

Date:

10 October 2012, 19:56:46

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Marc Geisinger
> Sent: Wednesday, October 10, 2012 3:55 AM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bug in AbstracJdbc2Statement.replaceProcessing when
> using dollar quoting?
>
> Am Dienstag, 9. Oktober 2012 19:49:17 UTC+2 schrieb "David Johnston":
> > Hi,
> >
> >
> >
> > See below for my thoughts on how this is limited and inefficient.
> >
> ...
> >
> > This patch appears to throw the exception when an unquoted identifier
> >
> > includes a dollar-sign.  I cannot speak to the standard but it would
> > seem
> >
> > that presence of the dollar-sign should only be evaluated if we are
> >
> > "IN_SQLCODE" and the preceding character is NOT (alphanumeric or
> > underscore)
> >
> > {specifically whatever characters are allowed to begin an unquoted
> >
> > identifier}.
> >
> >
> >
> > Also, with the logic provided the addition of an exception and a
> > try/catch
> >
> > block appears to add unnecessary overhead.  At the point the exception
> > is
> >
> > thrown I would suggest that we instead:
> >
> >
> >
> > "return p_sql;"
> >
> >
> >
> > and let the replaceProcessing method remain ignorant of whether the
> > SQL it
> >
> > is getting back is the original SQL or a modified version.
> >
> >
> >
> > The fact is the presence of dollar-quoting with enabled escaping is
> > going to
> >
> > be the common situation.  Combine that with the fact we are not
> > prompting
> >
> > the user to "fix" anything means that using the exception mechanism a
> > poor
> >
> > choice - it is not Exceptional but rather ordinary.  Everything that
> > needs
> >
> > to be checked and confirmed can be done in the parseSQL method and
> > since it
> >
> > has access to the original statement it can return the original when
> >
> > necessary.
> >
> >
> >
> > David J.
>
> Hi,
> I know that throwing that exception is a bit... uncommon. But I did that
> because of the while loop in replaceProcessing. The code there does call
> parseSql several times,  at least it can. And therefor I don't know if it
is the
> first call when i found a dollar quote. If i just return the sql unchanged
it
> might have had changes before so the result would be replaced sql code
> together with original sql code.
> What I wanted to achieve was that replaceProcessing get's to know that
> there is a dollar quote and stop the replaceProcessing and return the
original
> sql string (just like replaceProcessingEnabled would have been set to
false). I
> wanted to have a kind of stateful exit of the parseSql method. But since
it
> only returns an int value, and might have been called before, throwing an
> exception was the only thing I found to be able to do that.
>
>

This is probably a situation where returning "-1" would have merit.  I
dislike the fact this was implemented as a "protected static" method though
- but changing the method signature is almost just as bad from that
perspective.

I'm also concerned that re-entry presumes that one is always in the
"IN_SQLCODE" state; since the invalid character that is being protected
against could theoretically appear anywhere.  But I missed the implication
of the re-entry on my first scan and need more time than I have now to
ponder this further.

I do at least like the fact that adding the checked exception will cause
existing code to fail to compile since we are changing the mechanics of how
the processing works.  But as I said before the overhead is something to be
concerned about given the fact this particular code path is one of the most
heavily used ones in the driver.

David J.