[exim-dev] Quoting of percent and underscore pgsql_quote()

Αρχική Σελίδα
Delete this message
Reply to this message
Συντάκτης: Micha Lenk
Ημερομηνία:  
Προς: exim-dev
Αντικείμενο: [exim-dev] Quoting of percent and underscore pgsql_quote()
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