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.
Comment | File | Size | Author |
---|---|---|---|
#2 | drupal-1184082-2.patch | 912 bytes | googletorp |
like-query-postgres.patch | 794 bytes | googletorp | |
Comments
Comment #2
googletorp CreditAttribution: googletorp commentedAbove patch explains why it's a bad idea to create patches at midnight.
Cleaned up the patch.
Comment #3
mikl CreditAttribution: mikl commentedYeah, I never understood why we bothered setting the escape character manually. Does anyone know?
Comment #4
mikl CreditAttribution: mikl commentedOkay, we've been running this in production across half a dozen Drupal sites using PostgreSQL for a month with no problems. I would consider this safe to commit.
Comment #5
xjmTagging.
Comment #6
Damien Tournoud CreditAttribution: Damien Tournoud commentedThat looks like a PDO bug. So please open an issue or link to an existing PDO issue before we can commit this workaround.
Comment #7
googletorp CreditAttribution: googletorp commented@6 I'm not convinced this is a PDO bug:
Produces error:
db_select('node')->fields('node')->condition('title', 'test', 'LIKE')->condition('title', 'test', 'LIKE')->execute();
No error:
I can be mistaken as I don't know much of the internals of the Drupal PDO integration and database backend. I don't actually know if the above comparison i viable. I don't mind filing a bug report for PDO if that is the cause, but I would like a little more than "this look like...". It should be fairly simple to write a LIKE query like I've demonstrated above that should fail if PDO is the cause.
Comment #8
Damien Tournoud CreditAttribution: Damien Tournoud commentedCan you try:
... which is closer to what we are doing?
Postponed until we get to the bottom of this, as this patch is clearly nothing more then a workaround.
Comment #9
googletorp CreditAttribution: googletorp commentedOk, confirmed this is a bug in PDO, the test from @8 produces the same error.
Bug report at php.net : https://bugs.php.net/bug.php?id=55335
I suggest we get this workaround in for now to eliminate fatal errors.
Comment #10
mikl CreditAttribution: mikl commentedIt seems that all the layers of abstraction we have piled up here makes it rather hard to send a literal backslash through.
However, I think this issue is fairly simple. As far as I can tell, there's no reason to try and set the ESCAPE character to `\` as that is already the default value. So why not just remove the declaration as per the patch in #2?
Comment #11
mikl CreditAttribution: mikl commentedBah, crosspost.
Comment #12
Dries CreditAttribution: Dries commentedCommitted to 7.x and 8.x. Thanks.