CODEWITHSUNDEEP
mysqlescaping
Magnar Myrtveit
Magnar Myrtveit

Reputation: 2760

Escape column for use in LIKE expression

My server can be used as a file storage. The files cannot be accessed directly where they are stored, but a url where each file can be downloaded is stored in a database.

When I receive a request, I want to check whether it matches a file's url. The url does not have to exactly match the file's url, but can contain additional information at the end. Therefore I must run a LIKE query in order to find any matching files:

SELECT * FROM `files` WHERE '<request-url>' LIKE CONCAT(`url`, '%')

This kind of works, but it produces false positives. If for example a file's url is /file/my_file.pdf, urls like /file/my1file.pdf can be used to access the file, since _ is a wildcard in LIKE.

How can I escape the column url so that it can be reliably used in LIKE expressions?

Bonus question: What if I needed to use a REGEXP expression instead?

Upvotes: 1

Views: 69

Answers (1)

Barmar
Barmar

Reputation: 782693

You can replace _ and % with \_ and \% to escape them.

LIKE CONCAT(REPLACE(REPLACE(url, '_', '\\_'), '%', '\\%'), '%')

Another option is to not use LIKE

WHERE LOCATE(url, '<request-url>') = 1

Upvotes: 2

Related Questions