[TYPO3-core] RFC: Bug #9442: SQL LIKE wildcards are not escaped correctly for list queries

[Oliver Hader]

Hi Masi,

Martin Kutschker schrieb:
> Oliver Hader schrieb:
>> This is a SVN patch request.
>>
>> Problem:
>> Using t3lib_db::listQuery('some_Tag') - and knowing that "_" and "%" are
>> wildcard characters in SQL LIKE statements - will produce wrong result sets.
>>
>> Solution:
>> Escape "%" and "_" correctly when they are used in a LIKE statement.
> 
> What is the difference between the old and the new version? Th old one
> uses one assignement less. Both use a variation of "command" (which it
> isn't) and both have a superfluos comparisons: % is like * (none or any)
> not like + (any, but at least one).
> 
> http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

Just noticed that I added a wrong patch which only worked in the 
Hackontest branch - will fix this later.

However, I didn't get the point with the wildcards you mentioned.

Old version:
listQuery('field', 'my%Tag', 'table');
-> ... WHERE (field LIKE '%,my%Tag,%' OR field LIKE 'my%Tag,%' OR field 
LIKE '%,my%Tag' OR field = 'my%Tag')
-> this would also match, if the field value on the table is 
"my1Tag,my2Tag" which is wrong

New version:
listQuery('field', 'my%Tag', 'table');
-> ... WHERE (field LIKE '%,my\%Tag,%' OR field LIKE 'my\%Tag,%' OR 
field LIKE '%,my\%Tag' OR field = 'my%Tag')
-> as you can see we have two patterns "my\%Tag" for LIKE and "my%Tag" 
for direct matching

The long LIKE condition could be reduced by using a REGEXP like this:
-> ... WHERE field REGEXP '(^|,)my%Tag(,|$)'

I've tested the speed of the queries with 10.000 records in a table. The 
LIKE statement finished after 13ms - the REGEXP finished after 36ms.

olly
-- 
Oliver Hader
TYPO3 4.3 Release Manager