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
--
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

Search Discussions

  • Ktm at Aug 4, 2011 at 8:37 pm

    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
  • Tom Lane at Aug 4, 2011 at 8:54 pm

    "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
  • Florian Pflug at Aug 4, 2011 at 10:01 pm

    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
  • Tom Lane at Aug 5, 2011 at 3:22 am

    Florian Pflug 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 3, '11 at 1:19p
activeAug 5, '11 at 3:22a
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase