#100874Like query with escaped values containing underscores does not match values mit underscores
Description
- TYPO3 11.5.27 / MariaDB 10.5.6 / PHP8.1
- TYPO3 12.4.1 sqlite
- TYPO3 main sqlite
- not tested more vendors and versions
For example, if a database field contains a value with an
underscore like `some_table_name` and a like query is build
using `escapeLikeWildcard()` the value is not matched.
$result = $queryBuilder
->select('*')
->from('some_table')
->where(
$queryBuilder->expr()->like(
'fieldname',
$queryBuilder->createNamedParameter(
'%' . $queryBuilder->escapeLikeWildcard('some_table_name') . '%'
)
)
)
->executeQuery()
->fetchAllAssociative();
// 0 records, if only the one record is in the table
var_dump(count($result));
The php method `addcslashes()` is used in the `escapeLikeWildcard()`
method, which escaped wildcard charactes (`%_`) with an backslash `\`-
The generated LIKE or NOT LIKE expressions does not contain the
SQL Keyword `ESCAPE` to define which escape character is used.
In some database version and vendords, this fallbacks to the default
`\` which is not the defined standard. And not all doing this fallback
for the user.
Therefore, at least the hardcoded ESCAPE keyword with `\` should be
added.
Additionally, in dedicated investigations it must be checked if:
- are the wildcards the same for all dbms ? If not, can we use the doctrine platform to determine which are the wildcards which should be escaped ?
- can we safly make this configurable as bugfix backport – otherwise, for main (v13) this will be done as breaking change anway to get doctrine/dbal 4 raise in.
Reviews 3
History 417
-
Stefan Bürk changed description 05/14/23, 02:43
-
Gerrit Code Review pushed patch set 2 for main to change 79027 status
NewUnder Review 05/14/23, 02:45 -
Gerrit Code Review pushed patch set 3 for main to change 79027 05/14/23, 03:01
-
Gerrit Code Review pushed patch set 4 for main to change 79027 05/14/23, 03:37
-
Gerrit Code Review pushed patch set 5 for main to change 79027 05/14/23, 03:56
-
Gerrit Code Review pushed patch set 6 for main to change 79027 05/14/23, 04:18
-
Gerrit Code Review pushed patch set 7 for main to change 79027 05/14/23, 04:29
-
Gerrit Code Review pushed patch set 8 for main to change 79027 05/15/23, 20:30
-
Gerrit Code Review pushed patch set 9 for main to change 79027 06/08/23, 15:00
-
Gerrit Code Review pushed patch set 10 for main to change 79027 06/08/23, 15:00
-
Gerrit Code Review pushed patch set 11 for main to change 79027 06/12/23, 11:27
-
Gerrit Code Review pushed patch set 1 for 12.4 to change 79226 06/12/23, 21:33
-
Stefan Bürk commented and changed status
Under ReviewResolved done0 %100 % 06/12/23, 21:40 -
Gerrit Code Review pushed patch set 1 for 11.5 to change 79491 status
ResolvedUnder Review 06/25/23, 22:03 -
Gerrit Code Review pushed patch set 2 for 11.5 to change 79491 06/26/23, 04:35
-
Stefan Bürk commented and changed status
Under ReviewResolved 06/26/23, 05:30Applied in changeset commit:cd000ec93ddb7cbddef154c2afafd5eb0f8cde70.
-
Benni Mack changed status
ResolvedClosed 07/09/24, 12:56
Applied in changeset commit:21cdffccf4f9eb42ece725ddf98619833532c726.