Обсуждение: Bug in JDBC.driver CopyManager method when copying json output

⚠ This email originated from outside of Brambles.  

I suggest this is a backend problem...

This is done with psql...

select to_json('my name is \"rabbit\"'::text);

           to_json

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

 "my name is \\\"rabbit\\\""

(1 row)

test=# COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS ''";

test"#

test=# COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS '';

"my name is \\\\\\"rabbit\\\\\\""

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson.Stadlmann@ifco.com> wrote:

Dear JDBC-Devs,

i think i found an escaping bug that causes duplicate escaping with backslash when using jdbc drivers CopyManager with JSON results.

 

Sample Query:

select to_json( 'my name is "rabbit"'::text);

 

Executing this query as Statement

<java>

Statement statement = connection.createStatement()

ResultSet resultSet = statement.executeQuery("select to_json('my name is \"rabbit\"'::text)");

String result=resultSet.next().getString(1);// => "my name is \"rabbit\""

</java>

 

Execution of this query using CopyManager (see result file attachment)

This Execution does the duplicate escaping for JSON output causing invalid json text.

<java>

BaseConnection bc=(BaseConnection) connection;                                     

final CopyManager cm=new CopyManager(bc);

File someFile=new File("test.json");

try(FileWriter fw=new FileWriter(someFile)){

                cm.copyOut("COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS ''", fw);

                // => my name is \\"rabbit\\" instead

}

</java>

 

 

My Driver:

<dependency>

           <groupId>org.postgresql</groupId>

           <artifactId>postgresql</artifactId>

           <version>42.2.5</version>

       </dependency>

 

My pg Server (Docker):

10.4 (Debian 10.4-2.pgdg90+1)

 

If that is required behavior I suggest to introduce another format “json” in addition to “text” and “csv”.

 

Kind regards

 

Gerson Stadlmann

Manager Software Development International

 

 

IFCO Systems Austria GmbH

Unterthalham Straße 2  |  4694 Ohlsdorf |  Austria
T: +43 (0)7612-787 782   |  M: +43 664 3855 154
gerson.stadlmann@ifco.com  |  www.ifco.com

 

A better supply chain serves us all. Let’s eat.

 

Board of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINT

I suggest this is a backend problem...

On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson.Stadlmann@ifco.com> wrote:

Dear JDBC-Devs,

i think i found an escaping bug that causes duplicate escaping with backslash when using jdbc drivers CopyManager with JSON results.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The backend is respecting the documented meanings and usage of the QUOTE
> and ESCAPE options of COPY.

Yeah, I don't see any bug here.  You've got one layer of backslash-quoting
that is required by JSON string syntax, and then a second layer that is
imposed by COPY's text format.

> Not sure what a JSON mode would look like though I’m thinking it’s not what
> was described here...

I can't see how that would do anything except move the pain somewhere
else.  Multiple quoting layers always result in "ugly" output (but if
the receipient is correctly coded and reverses the operations in the
right sequence, the same underlying data will emerge).

The OP might want to consider using COPY BINARY, which uses a length
prefix instead of character-escaping to keep fields separate.  Still,
though, this is fundamentally just cosmetic.  If you don't apply the
correct reverse conversion steps in the correct order, you won't get
the right results.

            regards, tom lane