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

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 when the desired string
> contains many single quotes or backslashes, since each of those must be doubled. To allow more readable queries in such situations,
> PostgreSQL provides another way, called "dollar quoting", to write string constants. A dollar-quoted string constant consists of a dollar
> sign ($), an optional "tag" of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string
> content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. For example, here are two different ways 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 since I 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 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 :) )

Marc

-----Ursprüngliche Nachricht-----
Von: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] Im Auftrag von Dave Cramer
Gesendet: Donnerstag, 20. September 2012 20:37
An: GEISINGER Marc - Contractor
Cc: pgsql-jdbc(at)postgresql(dot)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(dot)GEISINGER(at)external(dot)thalesgroup(dot)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
>
>