Review 11.5 Merged

76085[BUGFIX] Ensure case-insensitive `like()` and `notLike()` expression

Stefan Bürk CR +2 V +2 Normal +228 −12 updated 10/11/22, 07:10

Commit message

Search- and filter functionaly usually uses `LIKE` or `NOT LIKE`
SQL statements to search a provided search term in the database.
Different dbms behave differently regarding search term casing.

MySQL/MariaDB relies on chosen collation of the database-, table-
and/or column level. Typical and recommended defaults on db
side and by TYPO3 default settings are case-insensitive collations,
which is the most common spread configuration. Based on
this, most people assume search terms are searched case-insensitive,
when using `LIKE` or `NOT LIKE` SQL statements.

PostgresSQL on the other hand knows the keywords `LIKE` and `ILIKE`.
The `ILIKE` keyword has to be used, when a case-insensitive search is
wanted. The current implementation simply uses `LIKE` for all dbms,
so searching with postgres is case sensitive, while it is usually
case insensitive with MySQL/MariaDB.

SQLite states `LIKE` is case-insensitive, which is basically correct.
The limitation is, this only works for the 26 chars in the ASCII
charset range, and not only for `LIKE` but also for the `UPPER()` and
`LOWER()`. There should be a way to compile php extension sqlite with
ICU support, enabling case awareness for more chars, but that's
nothing we can expect people do and have by default. We cannot do
much, so we leave some notes in the code regarding this detail.

The patch changes ExpressionBuilder to use ILIKE on PostgresSQL
to be in-line with the default MySQL/MariaDB behavior.

Review

  • Code review CR +2

    Imported before Forger kept the single votes, the next import adds them.

  • Verified V +2

    Imported before Forger kept the single votes, the next import adds them.

Issues 1

Files 5