Opened 17 months ago

Closed 17 months ago

Last modified 17 months ago

#6566 closed defect (fixed)

PostGIS driver: wrong bytea encoding for OFTBinary

Reported by: jeromeG Owned by: warmerdam
Priority: normal Milestone: 2.0.3
Component: OGR_SF Version: 2.1.0
Severity: normal Keywords: PostGIS, binary, BLOB
Cc:

Description

I have a PostGIS table with a column from type "bytea" where I want to store an xml file.

First, using OGR_F_SetFieldBinary() I write:

<?xml version="1.0"?>

then using OGR_F_GetFieldAsBinary() I read it back and get:

<?xml\040version=\0421.0\042?>

I didn't expect the spaces and quotation marks to be encoded!

I had a look at the usage of the function GByteArrayToBYTEA in ogrpgdumplayer.cpp of GDAL 2.1. The code seems to generate the following sql statement:

INSERT INTO ... VALUES (..., '<?xml\\040version=\\0421.0\\042?>', ...)

According to https://www.postgresql.org/docs/9.0/static/datatype-binary.html this is no correct. Correct would be either without double "\":

INSERT INTO ... VALUES (..., '<?xml\040version=\0421.0\042?>', ...)

or with an "E" before:

INSERT INTO ... VALUES (..., E'<?xml\\040version=\\0421.0\\042?>', ...)

This is probably the cause of the problem.

Change History (6)

comment:1 Changed 17 months ago by Even Rouault

Resolution: fixed
Status: newclosed

In 34481:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:2 Changed 17 months ago by Even Rouault

In 34482:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:3 Changed 17 months ago by Even Rouault

In 34483:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:4 Changed 17 months ago by Even Rouault

Milestone: 2.0.3

comment:5 Changed 17 months ago by JeromeG

Thanks for the quick reaction.

I am not sure, but it might depends on the Progresql/PostGIS version. As stated at https://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html the setting standard_conforming_strings was changed from "off" to "on" in Progresql 9.1.

Some older versions do already have this setting and do understand the syntax with the "E". For example I could see that in the documentation of Progresql 8.1 (https://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS).

Much older versions might not understand it. Depending on the policy of GDAL about supporting older version of Progresql/PostGIS, it might necessary to react differently for older versions.

comment:6 Changed 17 months ago by Even Rouault

Yes, I'm aware this might break ancient Postgres. But I've verified this does work at least on Postgres 8.4 which is already quite ancient. Perhaps the 7.X series might be broken, but anyway I doubt folks still use that, and binary fields aren't commonly used.

Note: See TracTickets for help on using tickets.