| Subject: | DBD::Pg escaping corrupts strings with PostgreSQL 8.2 and standard_conforming_strings |
PostgreSQL 8.2 added the standard_conforming_strings configuration
parameter which causes backslashes to be treated literally in string
literals. This corrupts strings in combination with DBD::Pg escaping.
DBD::Pg escapes '\a' to '\\a'; that is stored in the database as '\\a'.
This corruption seems to happen most obviously with the bytea columns.
The PostgreSQL developers have talked about making
standard_conforming_strings on the default. The escape_string_warning
parameter turned on will warn if the normal string literal is used with
backslahs escape with standard_conforming_strings off.
One solution is to detect standard_conforming_strings and not escape
backslashes. Another is to use the escape string literal syntax
(E'\\a') when supported.
Until then, it might be good to have a mention that
standard_conforming_strings = on should not be used.