Обсуждение: Bug in JDBC.driver CopyManager method when copying json output
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 PRINTDear 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.
Вложения
Re: ⚠ Re: Bug in JDBC.driver CopyManager method when copying json output
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⚠ 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\\\\\\""On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson.Stadlmann@ifco.com> wrote:Board of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINTDear 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.
Вложения
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.
Hello,Even your sample clearly shows duplication of escape characters.My backend was a test program to show the issue clearly.I solved it for my use-case using csv format instead of text.Kind regardsBoard of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINT⚠ 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\\\\\\""On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson.Stadlmann@ifco.com> wrote:Board of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINTDear 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.
Вложения
"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