Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – 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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    (Without ESCAPE) The number of backslashes () you need depends on the number of layers of software (app, connector, etc) it has to go through. Each layer will turn \ into . When the SQL finally gets it there probably needs there to be 1 backslash. So, try with 1, 2, 4, 8 to see what works.

    (With ESCAPE '#'), then that replaces some, but not all of the backslashes. Play around until you get ir right. # or #\ or I don’t know what.

    Further, be aware that "fiddle" is one of those "layers". So whatever works there may not work in your code.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search