Issue Bug Closed

#100874Like query with escaped values containing underscores does not match values mit underscores

Stefan Bürk 05/14/23, 02:37 updated 07/09/24, 12:56

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