Review main Merged

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

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

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
    • Christian Kuhn +1
    • Stefan Froemken +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Christian Kuhn +1
    • Stefan Froemken +1
    • core-ci +1 CI

Issues 1

Files 5

Review log 2032

Show 47 earlier messages
  1. Stefan Bürk uploaded patch set 1 05/14/23, 02:17

  2. core-ci voted on patch set 1 V -1 05/14/23, 02:25 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41804

  3. Stefan Bürk uploaded patch set 2 V -1 05/14/23, 02:45

  4. core-ci voted on patch set 2 V -1 05/14/23, 02:56 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41805

  5. Stefan Bürk uploaded patch set 3 V -1 05/14/23, 03:01

  6. core-ci voted on patch set 3 V -1 05/14/23, 03:23 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41806

  7. Stefan Bürk uploaded patch set 4 V -1 05/14/23, 03:37

  1. core-ci voted on patch set 4 V -1 05/14/23, 03:47 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41808

  2. Stefan Bürk updated the commit message in patch set 5 V -1 05/14/23, 03:56

  3. core-ci voted on patch set 5 V -1 05/14/23, 04:06 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41809

  4. Stefan Bürk uploaded patch set 6 V -1 05/14/23, 04:18

  5. core-ci voted on patch set 6 V +1 05/14/23, 04:26 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41812

  6. Stefan Bürk updated the commit message in patch set 7 V +1 05/14/23, 04:28

  7. core-ci voted on patch set 7 V +1 05/14/23, 04:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41813

  8. Stefan Bürk commented on patch set 7 05/14/23, 04:38

  9. core-ci commented on patch set 7 05/14/23, 05:07 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41814

  10. Benni Mack updated the commit message in patch set 8 V +1 05/15/23, 20:30

  11. Benni Mack voted on patch set 8 CR +1 05/15/23, 20:30

  12. core-ci voted on patch set 8 V +1 05/15/23, 20:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/41854

  13. Christian Kuhn updated the commit message in patch set 9 CR +1 V +1 06/08/23, 15:00

  14. Christian Kuhn rebased onto patch set 10 06/08/23, 15:00

  15. core-ci voted on patch set 9 V +1 06/08/23, 15:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/42313

  16. core-ci voted on patch set 10 V +1 06/08/23, 15:10 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/42314

  17. Christian Kuhn voted on patch set 10 V +1 CR +1 06/08/23, 15:19

  18. Stefan Froemken commented on patch set 10 06/12/23, 06:55

    TestExpressionBuilderLikeAndNotLike.csv:7

    Just a nice to have: Can you also add a test for "%"?

    ExpressionBuilderTest.php:182

    Is that correct? We are in a text with double quotes. IMO there is no need to quote characters with backslash like in single quoted texts.
  19. Stefan Bürk uploaded patch set 11 CR +1 V +1 06/12/23, 11:27

  20. Stefan Bürk commented on patch set 10 06/12/23, 11:29

    TestExpressionBuilderLikeAndNotLike.csv:7 Reply

    Done

    ExpressionBuilderTest.php:182 Reply

    The point is, that the generated SQL syntax is: ``` ESCAPE "\" ``` So, the double-quotes must be escaped. You are right, normally a backslahs does not need to be escaped .. but a backslash before a escaped quote needs it. IMO thats the correct test for it.
  21. core-ci voted on patch set 11 V +1 06/12/23, 11:35 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/42383

  22. Christian Kuhn voted on patch set 11 V +1 CR +1 06/12/23, 13:56

  23. Stefan Froemken voted on patch set 11 V +1 CR +1 06/12/23, 20:24

  24. Stefan Bürk voted on patch set 11 V +2 CR +2 06/12/23, 21:35

  25. Stefan Bürk merged patch set 12 06/12/23, 21:35