Like queries failing on postgres with standard_conforming_strings turned on.

I have been spending some time looking at the database abstraction layer and what goes on when querying postgreSQL with like queries. The problem arose from a query created by the redirect module, so I'm using that as example, though this problem is not specific to the redirect module.

The query in question is this one:

$query = db_select('redirect');
$query->addField('redirect', 'rid');
$query_or = db_or();
$query_or->condition('source', db_like($path), 'LIKE');
$query_or->condition('source', db_like($path . '/') . '%', 'LIKE');
$query_or->condition('redirect', db_like($path), 'LIKE');
$query_or->condition('redirect', db_like($path . '/') . '%', 'LIKE');
$query->condition($query_or);
$rids = $query->execute()->fetchCol();

With standard_conforming_strings turned of, this works fine, but when turning standard_conforming_strings on a PDO exception is returned:

PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens: SELECT redirect.rid AS rid FROM {redirect} redirect WHERE ( (source ILIKE :db_condition_placeholder_0 ESCAPE '\') OR (source ILIKE :db_condition_placeholder_1 ESCAPE '\') OR (redirect ILIKE :db_condition_placeholder_2 ESCAPE '\') OR (redirect ILIKE :db_condition_placeholder_3 ESCAPE '\') ); Array ( [:db_condition_placeholder_0] => node/29 [:db_condition_placeholder_1] => node/29/% [:db_condition_placeholder_2] => node/29 [:db_condition_placeholder_3] => node/29/%

Looking through the info it's clear that the number of variables and tokens are the same. I tried changing the query to not use or or use fewer conditions but the result was the same. The best that I could get was that the problem had something to do with the ESCAPE '\' part. I tried using double double quotes which produces a different error: Invalid escape sequence: 7 ERROR: invalid escape string HINT: Escape string must be empty or one character.

After looking through the PostgreSQL documentation I wondered why we actual bother setting the escape character to \ this is the default escape character so it doesn't make much sense to me why we want to set it. Deleting this from the query actually produced working query.

This issue is related to #910388: Installation fails on PostgreSQL >=8.4: Invalid escape sequence.

I've attached the patch that fixes the issue for me. Feedback welcomed.