Search code examples
laravelmariadbescapingsql-like

MariaDB LIKE with Escape Character - Unexpected Behavior

I'm working on a Laravel application with a jobs table containing job data in the payload column. The payload is a JSON string similar to this:

{"displayName":"App\\Jobs\\V1\\ProcessOrdersPendingCourier"}

I want to fetch records where the displayName key in the JSON payload has the value "App\\Jobs\\V1\\ProcessOrdersPendingCourier". Logically, I used this query:

SELECT * FROM `jobs`
WHERE `payload`
  LIKE '%"displayName":"App\\\\Jobs\\\\V1\\\\ProcessOrdersPendingCourier"%';

However, this query returns no results! After struggling for a while, I discovered that adding ESCAPE '#' to the query magically fetches the expected rows:

SELECT * FROM `jobs`
WHERE `payload`
  LIKE '%"displayName":"App\\\\Jobs\\\\V1\\\\ProcessOrdersPendingCourier"%' ESCAPE '#';

My questions are:

  1. Why does the first LIKE query not work as expected?
  2. Why is the ESCAPE character necessary in this case?

Here's a fiddle demonstrating the issue. Any insights on this would be greatly appreciated!

Notes:

The # in escape '#' can be replaced with other characters like @ or & to act as the escape character. However, using \\ (backslash) as the escape character won't work in this scenario.

Solution

  • One level of backslashes is removed by the parser (unless sql_mode=NO_BACKSLASH_ESCAPES is given) when scanning string literals from the query.

    Another level of backslashes is removed by the LIKE itself (no matter what sql_mode is).

    So to match you need to go 4x \ for every real one with a LIKE.