Thread: PQescapeByteaConn - returns wrong string for PG9.1 Beta3
<p class="MsoNormal">Please let me clarify the bug:<p class="MsoNormal"> <p class="MsoNormal">CREATE TABLE
"testtable"<pclass="MsoNormal">(<p class="MsoNormal"><span style="mso-spacerun:yes"> </span>"ID" integer NOT NULL,<p
class="MsoNormal"><spanstyle="mso-spacerun:yes"> </span>"BinaryContents" bytea<p class="MsoNormal">);<p
class="MsoNormal"> <pclass="MsoNormal">INSERT INTO "testtable" ("ID", "BinaryContents") values (1, E'\xea2abd8ef3');<p
class="MsoNormal"> <pclass="MsoNormal">returns "invalid byte sequence". <p class="MsoNormal"> <p
class="MsoNormal">'\xea2abd8ef3'is the string delivered by the PG 9.1 Beta3 server when calling PQescapeByteaConn(). It
cannotbe further processed by the server itself afterwards! There is a leading '\' missing. <p class="MsoNormal"> <p
class="MsoNormal">Whencalling the function for a PG 9.0.1 server, then the result (correctly) is '\\xea2abd8ef3' (with
adouble-backslash!), and then the insert works fine, both, with PG9.1 Beta3 and PG9.0.1<p class="MsoNormal"> <p
class="MsoNormal">Itis a serious issue, as it will break all existing PostgreSQL applications that deal with binary
contentsand use PQescapeByteaConn().<p class="MsoNormal"> <p class="MsoNormal">Best regards<p
class="MsoNormal">Petro<divclass="signature"><br /><br /><br />-- <br />Empfehlen Sie GMX DSL Ihren Freunden und
Bekanntenund wir<br />belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de</div>
On Wed, Aug 03, 2011 at 03:19:06PM +0200, Petro Meier wrote:
> Normal 0 21 false
> false false DE X-NONE X-NONE
>
> MicrosoftInternetExplorer4
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Please let me clarify the bug:
>
> CREATE TABLE "testtable"
>
> (
>
> "ID" integer NOT NULL,
>
> "BinaryContents" bytea
>
> );
>
> INSERT INTO "testtable" ("ID", "BinaryContents") values (1,
> E'\xea2abd8ef3');
>
> returns "invalid byte sequence".
>
> '\xea2abd8ef3' is the string delivered by the PG 9.1 Beta3 server
> when calling PQescapeByteaConn(). It cannot be further processed by the
> server itself afterwards! There is a leading '\' missing.
>
> When calling the function for a PG 9.0.1 server, then the result
> (correctly) is '\\xea2abd8ef3' (with a double-backslash!), and then the
> insert works fine, both, with PG9.1 Beta3 and PG9.0.1
>
> It is a serious issue, as it will break all existing PostgreSQL
> applications that deal with binary contents and use PQescapeByteaConn().
>
>
> Best regards
>
> Petro
That looks correct for the new default for SQL conforming strings set to
true in 9.1+. The command you should be using is:
INSERT INTO "testtable" ("ID", "BinaryContents") values (1, '\xea2abd8ef3');
Regards,
Ken
"Petro Meier" <Petro85@gmx.de> writes:
> INSERT INTO "testtable" ("ID", "BinaryContents") values (1,
> E'\xea2abd8ef3');
> returns "invalid byte sequence".
> '\xea2abd8ef3' is the string delivered by the PG 9.1 Beta3 server
> when calling PQescapeByteaConn(). It cannot be further processed by the
> server itself afterwards! There is a leading '\' missing.
No, there isn't. What you are doing wrong is prepending an E to the
literal. You should not be doing that, neither in 9.1 nor any previous
version.
regards, tom lane
On Aug4, 2011, at 22:54 , Tom Lane wrote:
> "Petro Meier" <Petro85@gmx.de> writes:
>> INSERT INTO "testtable" ("ID", "BinaryContents") values (1,
>> E'\xea2abd8ef3');
>> returns "invalid byte sequence".
>
>> '\xea2abd8ef3' is the string delivered by the PG 9.1 Beta3 server
>> when calling PQescapeByteaConn(). It cannot be further processed by the
>> server itself afterwards! There is a leading '\' missing.
>
> No, there isn't. What you are doing wrong is prepending an E to the
> literal. You should not be doing that, neither in 9.1 nor any previous
> version.
Just to clarify what's going on here, in case the OP is still puzzled.
Postgres supports both a legacy mode where backslashes serve as an escape
character in single-quotes strings, and an SQL standard-compliant mode where
they don't. The mode is chosen by setting the GUC standard_conforming_strings
to either on of off. Independent of the current standard_conforming_strings
setting, once can always force a strings to be interpreted with legacy
semantics (i.e. with backslash as an escape character) by prefixing the string
literal with E.
Thus, assuming that standard_conforming_strings is set to on, a string containing
exactly one backslash can be written as either '\' or E'\\',
while with standard_conforming_strings set to off, you'd have to use '\\' or E'\\'
PQescapeByteaConn() emits one backslash if it detects that
standard_conforming_strings is set to "on" for the given connection, and two if
it detects "off". The string is thus always correctly interpreted by the backend as
long as you *don't* prefix it with E. If you do, you force the backend to always
interpret it with legacy semantics. Which of course causes trouble if
standard_conforming_strings is set to "on", because then PQescapeByteAConn()'s
expectation of the backend's behaviour (standard mode) and it's actual behaviour
(legacy mode) no longer match.
The reason that things appeared to work for you on 9.0 is that all versions before
9.1 have standard_conforming_strings set to "off" by default. If you try your code
on 9.0, but with standard_conforming_strings set to "on", you'll observe the same
breakage you observe on 9.1
Exactly the same is true for PQescapeStringConn().
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
> Just to clarify what's going on here, in case the OP is still puzzled.
> [ lots o detail snipped ]
Right. Thanks for writing out what I didn't have time for today...
regards, tom lane