Review 12.4 Merged

79226[BUGFIX] Add `ESCAPE` keyword for `like()` and `notLike() expressions

Stefan Bürk CR +2 V +2 Normal +142 −6 updated 06/12/23, 21:42

Commit message

Values for `like` expressions should be escaping the
corresponding like wildcards ($ and _). TYPO3 core
provides a `escapeLikeWildcards()` in `QueryBuilder`
and on the `Connection`. Under the hood, the php
`addcslashes()` method is used to escape wildcards
in the value before appending/prepending wildcards.

It has been assumed, that `\` as escape character
is always the default character throughout all
database server vendors and versions - which makes
`addcslashes()` the one-shot to use.

It has been recently discovered, that if values in
the database contains one of these wildcards, for
example the underscore `_` and a like expression
is built using the escape method, the row cannot
be matched in all databases.

This relates to the fact, that the generated like
expressions do not contains the `ESCAPE` keyword
to define which escape character has been used.

`doctrine/dbal` has added a corresponding argument
to the doctrine/dbal query ExpressionBuilder like()
and notLike() method, so this can be set.

TYPO3 uses a custom ExpressionBuilder, not extending
the doctrine ExpressionBuilder (which will change
with upcoming doctrine/dbal v4).

This change always adds the `ESCAPE` keyword to
like and not like expressions with the hardcoded
`\` escape character - except for PostgresSQL.
PostgresSQL doesn't like it when ILIKE/NOT ILIKE
is used, which the ExpressionBuilder does to mimic
case insensitive LIKE/NOT LIKE similar to MySql.

This can be made configurable in a dedicated patch
and must be done for upgrading to doctrine/dbal 4.x
anyway.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
  • Verified V +2 Done
    • Stefan Bürk +2
    • core-ci +1 CI

Issues 1

Files 5

Review log 34

  1. Stefan Bürk commented on patch set 1 06/12/23, 21:33

    Cherry Picked from branch main.
  2. core-ci voted on patch set 1 V +1 06/12/23, 21:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/42412

  3. Stefan Bürk voted on patch set 1 V +2 CR +2 06/12/23, 21:42

  4. Stefan Bürk merged patch set 2 06/12/23, 21:42