Hi Exim developers,
in a project we are about to upgrade PostgreSQL from 8.4 to 9.2, which
changes the PostgreSQL default setting of standard_conforming_strings
from 'no' to 'yes', which means that the database now treats backslash
characters in a string as ordinary characters. This in turn breaks the
Exim implementation of pgsql_quote(), which apparently adds a backslash
in front of every percent and underscore character, leading to corrupted
SQL statements being sent to the SQL server.
As an example, just run the following command:
$ exim -be '${quote_pgsql:test_abc}'.
test\_abc
This seems also to be the issue that has been reported some time ago as
Exim Bug 932:
http://bugs.exim.org/show_bug.cgi?id=932
The source code contains the following comment:
/* The characters that always need to be quoted (with backslash) are
newline, tab, carriage return, backspace, backslash itself, and
the quote characters. Percent and underscore are only special in
contexts where they can be wild cards, and this isn't usually the
case for data inserted from messages, since that isn't likely to
be treated as a pattern of any kind. However, pgsql seems to allow
escaping "on spec". If you use something like "where id="ab\%cd"
it does treat the string as "ab%cd". So we can safely quote
percent and underscore. [This is different to MySQL, where you
can't do this.]
The original code quoted single quotes as \' which is documented
as valid in the O'Reilly book "Practical PostgreSQL" (first
edition) as an alternative to the SQL standard '' way of
representing a single quote as data. However, in June 2006 there
was some security issue with using \' and so this has been changed.
[Note: There is a function called PQescapeStringConn() that quotes
strings. This cannot be used because it needs a PGconn argument
(the connection handle).
Why, I don't know. Seems odd for just string escaping...]
>From this comment I still don't get why the percent and underscore
characters get escaped at all. Does anybody of the developers know a
reason for the escaping of percent and underscore characters?
Just for the reference: The Perl library DBD::Pg apparently had a
similar issue which got fixed some years ago:
https://rt.cpan.org/Public/Bug/Display.html?id=27538
Any comments would be greatly appreciated.
Regards,
Micha
