[HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Normal021false falsefalseDEX-NONEX-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
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
On Wed, Aug 03, 2011 at 03:19:06PM +0200, Petro Meier wrote: Normal021false falsefalseDEX-NONEX-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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Petro Meier petr...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
On Aug4, 2011, at 22:54 , Tom Lane wrote: Petro Meier petr...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Florian Pflug f...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Normal021false falsefalseDEX-NONEX-NONE MicrosoftInternetExplorer4 If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error (Invalid byte sequence...). If I add the second backslash manually everything works fine. When connecting to a PG9.0 server and using this function, the return value is correct (with two backslashes): \\xea2abd8ef31...( and so on.) This should be a bug in PG9.1 Beta3 Regards Petro -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
On Jul27, 2011, at 08:51 , Petro Meier wrote: If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error (Invalid byte sequence...). If I add the second backslash manually everything works fine. When connecting to a PG9.0 server and using this function, the return value is correct (with two backslashes): \\xea2abd8ef31...( and so on.) This should be a bug in PG9.1 Beta3 Sounds as if PQescapeByteaConn() is confused about whether standard_conforming_strings is on or off. What value does that setting have in your 9.0 and 9.1 instances? BTW, I think 9.1 is the first release where that settings defaults to on, so maybe that adds to PQescapeByteaConn()'s confusion. In theory it shouldn't since PQescapeByteaConn() should simply detect the server's setting and react accordingly, but who knows... best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011: If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error (Invalid byte sequence...). If I add the second backslash manually everything works fine. You're just being bitten by the fact that the standard_conforming_strings setting changed its default from false to true. If you want the old behavior, you can just flip the switch, but the recommended action is to change your expectations. You can use E'' if you want backslashes to continue working without changing the switch. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011: If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. You're just being bitten by the fact that the standard_conforming_strings setting changed its default from false to true. Well, the question is why is it actually failing for him. AFAICS the value being emitted is correct for the 9.1 server. Perhaps we need to see a complete example... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
On Jul27, 2011, at 20:05 , Alvaro Herrera wrote: Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011: If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error (Invalid byte sequence...). If I add the second backslash manually everything works fine. You're just being bitten by the fact that the standard_conforming_strings setting changed its default from false to true. If you want the old behavior, you can just flip the switch, but the recommended action is to change your expectations. You can use E'' if you want backslashes to continue working without changing the switch. Hm, but PQescapeByteaConn() shouldn't produce a literal that the server later rejects, no matter what standard_conforming_strings is set to. It looks like PQescapeByteaConn() does the right thing here, though - it doesn't escape the backslash in it's result when dealing with 9.1, presumably because that server has wstandard_conforming_strings set to on. But why then is the server rejecting the result? The only way I can see that make that happend would be to prefix the string returned by PQescapeByteaConn() with 'E'. @OP: Could you post the code fragment that causes the error? best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQescapeByteaConn - returns wrong string for PG9.1 Beta3
On 07/27/2011 02:05 PM, Alvaro Herrera wrote: Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011: If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) \xea2abd8ef31...(and so on.) Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error (Invalid byte sequence...). If I add the second backslash manually everything works fine. You're just being bitten by the fact that the standard_conforming_strings setting changed its default from false to true. If you want the old behavior, you can just flip the switch, but the recommended action is to change your expectations. You can use E'' if you want backslashes to continue working without changing the switch. Or even better don't interpolate it into SQL at all, but use a statement placeholder. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
