[BUG] LikeFilter does not perform case-insensitive matching

[rixvet]

Current Behavior The https://github.com/hashtopolis/server/blob/dev/src/dba/LikeFilter.class.php#L38 is using LIKE as operator, how-ever this default to case-insensitive matching, causing too many items to be returned.

Expected Behavior I would the LikeFilter to be case-sensitive, since the corresponding LikeFilterInsensitive is meant to handle the case-insensitive searching.

Background The following statements illustrate that string comparisons are not case-sensitive unless one of the operands is case-sensitive (uses a case-sensitive collation or is a binary string):https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like

Additional information a) PoC attached b) check whether currently used LikeFilter needs to be case-sensitive or case-insensitive.

diff --git a/src/dba/LikeFilter.class.php b/src/dba/LikeFilter.class.php
index 7240aff9..6ddd2036 100755
--- a/src/dba/LikeFilter.class.php
+++ b/src/dba/LikeFilter.class.php
@@ -35,7 +35,7 @@ class LikeFilter extends Filter {
       $inv = " NOT";
     }

-    return $table . $this->key . $inv . " LIKE ?";
+    return $table . $this->key . $inv . " LIKE BINARY ?";
   }

   function getValue() {

• [ ] Test if the collation caused more issues or only this one.
• [ ] Tests if all places that use this code are working as intended.
• [ ] Re-enable skipped tests in test_filter_and_ordering.

[zyronix]

@rixvet could you tell me the impact of the change? I though there were 3 places were this call was used?

[zyronix]

Also @s3inlc and me went over this issue. The issue was introduced by switched to the Docker mysql. The docker mysql changes the default collation to utf8_general_ci. Orginally the collation used by ubuntu with mysql was utf8mb4.

We have to see if the change of the collation might have caused more issue's then only this LIKE filter. Example: bcrypt hashes with mixed cases (two of the sames hashes, but one uppercase and one lowercase). And searching for plaintext, "Ü" would match "U".

[rixvet]

Even with the utf8mb4 encoding this behavior is not consistent. The LikeFilter is currently basically acting as an LikeFilterInsensitive, hence the requested change. "The default character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, so nonbinary string comparisons are case-insensitive by default." (source: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html)

Also @s3inlc and me went over this issue. The issue was introduced by switched to the Docker mysql. The docker mysql changes the default collation to utf8_general_ci. Orginally the collation used by ubuntu with mysql was utf8mb4.

We have to see if the change of the collation might have caused more issue's then only this LIKE filter. Example: bcrypt hashes with mixed cases (two of the sames hashes, but one uppercase and one lowercase). And searching for plaintext, "Ü" would match "U".

If such behavior (instead of the current behaviour) is required, the suggested change (adding BINARY to the LIKE operator) will fix this.