LIKE doesn't correctly escape escape value #1400
Comments
|
Changes by Michael Bayer (@zzzeek):
|
|
Anonymous wrote: 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. |
|
Michael Bayer (@zzzeek) wrote: 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. |
|
Michael Bayer (@zzzeek) wrote: as always, a fully working patch + unit tests will be committed as it is received, provided it passes on all current backends. |
|
Michael Bayer (@zzzeek) wrote: 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. |
|
Changes by Michael Bayer (@zzzeek):
|
|
Michael Bayer (@zzzeek) wrote: 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 87664ce . |
|
Changes by Michael Bayer (@zzzeek):
|
Migrated issue, originally created by Anonymous
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.
The text was updated successfully, but these errors were encountered: