Re: Bug in JDBC.driver CopyManager method when copying json output

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(at)postgresintl(dot)com
www.postgresintl.com

On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson(dot)Stadlmann(at)ifco(dot)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
>
>
>
> [image: IFCOGlobal_logo_RGB - email v2]
>
>
>
> IFCO Systems Austria GmbH
>
> Unterthalham Straße 2 | 4694 Ohlsdorf | Austria
> T: +43 (0)7612-787 782 | M: +43 664 3855 154
> gerson(dot)stadlmann(at)ifco(dot)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
>