Re: Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting? - Mailing list pgsql-jdbc

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