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

[Martin Kutschker]

Oliver Hader schrieb:
> 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

This issue is resolved with the new patch.


> 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.

I meant that %value% will match "valueSUFFIX", "PREFIXvalue" and
"value", but I forgot the commas, which is the only point of the function.

Masi

Masi