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.
So taking an example from The Exim Specification, and switching "userx"
to something which would use quoting, we have:
----------------------------8< cut here >8------------------------------
${lookup pgsql{select home,name from users where id='${quote_pgsql:$local_part}'}\
{$value}}
----------------------------8< cut here >8------------------------------
See how the quotes are part of the syntax which you provide there? So
if you instead just write:
----------------------------8< cut here >8------------------------------
${lookup pgsql{select home,name from users where id=E'${quote_pgsql:$local_part}'}\
{$value}}
----------------------------8< cut here >8------------------------------
then, by my understanding of the current issue, this problem is solved?
Or does E'...' not undo _all_ backslash escapes?
As to both the code and the previous comments: they predate my being an
Exim maintainer, but we appear to have a problem here, because the
PostgreSQL docs:
http://www.postgresql.org/docs/9.2/static/libpq-exec.html
have deprecated `PQescapeString()`, so we can't use that sanely, leaving
us with only the connection-based escaping model, but Exim's quoting
framework means we don't have a connection at the time this is needed.
The only sane answer I have is a bit of a rewrite I was hoping to do,
for function handling, which would let us do prepared statements, but
work got busy and I'm not paid to work on Exim, so haven't progressed on
this.
If someone wants to hack on code to support this, my basic idea is to
have a "begin functions" section which introduces user-defined
functions, and then in the string expansion have:
${call{foo}{arg1}{arg2}}
Then we might have:
----------------------------8< cut here >8------------------------------
begin functions
foo:
model = pgsql
prepare = SELECT target FROM aliases WHERE lhs = ? AND domain = ?
get_home_name:
model = pgsql
prepare = SELECT home,name FROM users WHERE id = ?
# model provides both a "driver" and, for something like an SQL backend,
# a hint to scan the string to infer parameter counts, etc
my_normalise:
model = exim
result = ${sg{$arg1}{some_regex}{$arg2}}
param_min_max = 2 2
begin acls
#...
----------------------------8< cut here >8------------------------------
Patches to do this very welcome, and this would solve the current
issues, by getting rid of the need for escaping.
-Phil
