LIKE doesn't correctly escape escape value

[Former user]

Seen in 0.4.7p1-2 and 0.5.3:

query.filter(Table.value.like('%z%', escape='\'))

That works as expected in SQLite, but on MySQL (tested in ANSI mode) it passes the database the SQL "table.value LIKE '%z%' ESCAPE '\'".

MySQL doesn't like the '\', for obvious reasons.

query.filter("value LIKE :pattern ESCAPE :escape").params(pattern='%z%', escape='\') works as expected in both DBs.

[Former user]

A short Ping.

I can still reproduce this behaviour under 0.6.1. As this bug is marked as minor severity "half an hour" I would like to politely ask for it to be fixed.

SQLite works well, MySQL needs the backslash to be escaped.

[Mike Bayer]

I have a vague recollection that this wasn't a one line fix and that more fundamental changes to the expression system were needed. Looking at the source code, re-figuring out why that was if it was in fact the case, and creating tests will take more than half an hour.

[Mike Bayer]

as always, a fully working patch + unit tests will be committed as it is received, provided it passes on all current backends.

[Mike Bayer]

So I don't actually know what the best solution is here. Would you like us to detect "\" and double it up on MySQL (in which case, we should use render_literal_value(), which needs to be implemented specifically for MySQL to include this escaping behavior, possibly checking for ANSI configuration, + tests)? Or just pass as bind params ? SQLite doesn't appear to accept "\" (error: "ESCAPE expression must be a single character").

It seems to me like the obvious solution is to not use "\" as an escape character, its a poor choice for platform agnostic behavior. That's why this bug has never been high priority.

[Mike Bayer]

MySQL's backslash behavior can be disabled using sql_mode NO_BACKSLASH_ESCAPES, and Postgresql has similar behavior. PG's documentation explcitly warns against using backslashes as an escape character as they are phasing them out,and the default value of "standard_conforming_strings" will soon be changed.

The need to research the behavior on all targeted platforms, add server detection code and test on a variety of platforms is why this bug was not a trivial fix. The fact that backslashes really shouldn't be used is why it was low priority.

The behavior of backslash and the fix has been tested on PG, SQLite, MySQL, Oracle, and Firebird so far and is in 87664ce88ab8931ccaacbac3357f484069efe6e9 .

[Mike Bayer]

Removing milestone: 0.6.2 (automated comment)