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

History 417

  1. Stefan Bürk changed description 05/14/23, 02:43

  2. Gerrit Code Review pushed patch set 2 for main to change 79027 status New Under Review 05/14/23, 02:45

  3. Gerrit Code Review pushed patch set 3 for main to change 79027 05/14/23, 03:01

  4. Gerrit Code Review pushed patch set 4 for main to change 79027 05/14/23, 03:37

  5. Gerrit Code Review pushed patch set 5 for main to change 79027 05/14/23, 03:56

  6. Gerrit Code Review pushed patch set 6 for main to change 79027 05/14/23, 04:18

  7. Gerrit Code Review pushed patch set 7 for main to change 79027 05/14/23, 04:29

  8. Gerrit Code Review pushed patch set 8 for main to change 79027 05/15/23, 20:30

  9. Gerrit Code Review pushed patch set 9 for main to change 79027 06/08/23, 15:00

  10. Gerrit Code Review pushed patch set 10 for main to change 79027 06/08/23, 15:00

  11. Gerrit Code Review pushed patch set 11 for main to change 79027 06/12/23, 11:27

  12. Gerrit Code Review pushed patch set 1 for 12.4 to change 79226 06/12/23, 21:33

  13. Stefan Bürk commented and changed status Under Review Resolved done 0 % 100 % 06/12/23, 21:40

    Applied in changeset commit:21cdffccf4f9eb42ece725ddf98619833532c726.

  14. Gerrit Code Review pushed patch set 1 for 11.5 to change 79491 status Resolved Under Review 06/25/23, 22:03

  15. Gerrit Code Review pushed patch set 2 for 11.5 to change 79491 06/26/23, 04:35

  16. Stefan Bürk commented and changed status Under Review Resolved 06/26/23, 05:30

    Applied in changeset commit:cd000ec93ddb7cbddef154c2afafd5eb0f8cde70.

  17. Benni Mack changed status Resolved Closed 07/09/24, 12:56