On Tue, Oct 27, 2015 at 10:10:21PM +0000, admin@??? wrote:
> When using "_" or "%" characters in lookups to PostgreSQL databases, the
> characters are escaped when they shouldn't, thus causing issues with PostgreSQL
> 9.x (or any version where standard_conforming_strings = on , which has been the
> default for quite a few years now)
>
>
> This issue was already coming up in 2012 on the dev-list:
>
> https://lists.exim.org/lurker/message/20120921.150112.993f28a2.en.html
>
>
> Using E'' for the queries as suggested in bug 932 is also not a good idea, see
>
> https://lists.exim.org/lurker/message/20120926.153049.a091c952.en.html
>
> And a simple solution was suggested and somehow probably forgotten..:
Postfix quotes input to Postgres queries by using the corresponding
Posgres interface, you can ignore the "vstring" bits, that's just
a bit of Postfix-specific buffer management:
src/global/dict_pgsql.c:
/*
* Escape the input string, using PQescapeStringConn(), because the older
* PQescapeString() is not safe anymore, as stated by the documentation.
*
* From current libpq (8.1.4) documentation:
*
* PQescapeStringConn writes an escaped version of the from string to the to
* buffer, escaping special characters so that they cannot cause any
* harm, and adding a terminating zero byte.
*
* ...
*
* The parameter from points to the first character of the string that is to
* be escaped, and the length parameter gives the number of bytes in this
* string. A terminating zero byte is not required, and should not be
* counted in length.
*
* ...
*
* (The parameter) to shall point to a buffer that is able to hold at least
* one more byte than twice the value of length, otherwise the behavior
* is undefined.
*
* ...
*
* If the error parameter is not NULL, then *error is set to zero on
* success, nonzero on error ... The output string is still generated on
* error, but it can be expected that the server will reject it as
* malformed. On error, a suitable message is stored in the conn object,
* whether or not error is NULL.
*/
VSTRING_SPACE(result, buflen);
PQescapeStringConn(active_host->db, vstring_end(result), name, len, &err);
if (err == 0) {
VSTRING_SKIP(result);
} else {
/*
* PQescapeStringConn() failed. According to the docs, we still have
* a valid, null-terminated output string, but we need not rely on
* this behavior.
*/
msg_warn("dict pgsql: (host %s) cannot escape input string: %s",
active_host->hostname, PQerrorMessage(active_host->db));
active_host->stat = STATFAIL;
VSTRING_TERMINATE(result);
}
Bottom line, instead of hand-coding the quoting, use PQescapeStringConn(),
which does correct quoting in the context of the current database
connection.
--
Viktor.
