Re: [exim-dev] Quoting of percent and underscore pgsql_quote…

Hi Phil,

On 09/23/2012 02:35 AM CEST +02:00, Phil Pennock wrote:
> On 2012-09-21 at 17:01 +0200, Micha Lenk wrote:
>> 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
>
> Note, though, that ${quote_pgsql:...} does *not* provide the surrounding
> quote marks, and can't affect those. You can, though
> [... by marking the SQL strings as escaped strings ...]
>
> then, by my understanding of the current issue, this problem is solved?
> Or does E'...' not undo _all_ backslash escapes?

It does indeed solve the issue for me. However, that is terribly ugly.

The main reason why I believe this better needs to be worked on on
Exim's side is following finding. If you use the E character to mark the
strings as escaped strings, the LIKE wildcard protection does not work
anymore.

Example: Lets say you have a database with a table test populated with
the values as shown below.

--------------------8< SQL database setup >8----------------------
CREATE TABLE test (key varchar(3));
INSERT INTO test (key) VALUES ('foo');
INSERT INTO test (key) VALUES ('bar');
INSERT INTO test (key) VALUES ('%');
------------------------------------------------------------------

Then, if you use escaped strings in a PostgreSQL lookup as suggested
above, the used SQL queries might match more rows than intended due to
the wildcard matching. E.g. given the table created with the SQL
commands above, this (overly simplified) Exim lookup-style string expansion

${lookup pgsql{SELECT * FROM test WHERE key LIKE E'${quote_pgsql:%}'}}

will expand to all rows of the table. But as I understand it, the
percent and underscore characters being escaped using a backslash by
pgsql_quote were intended to prevent unintended wildcard matches by
percent or underscore in user-provided data.

So, having PostgreSQL configured with standard_conforming_strings = on,
pgsql_quote()'s escaping of percent and underscore characters
1.) cause trouble with existing Exim configurations, and
2.) doesn't yield anything, e.g. is useless.

Conclusion: The advice to simply change all SQL strings to escaped
strings by prefixing them with E is dangerous if used in queries with
LIKE matching.

So, given that the Perl module DBD::Pg dropped the escaping of these
characters already some years ago (CPAN RT #27538), what are the reasons
to keep this useless escaping of percent and underscore in future Exim code?

Regards,
Micha