How can I use LIKE in MySQL without removing sanitization?

Question

I'm using the mysql2 package to run my SQL queries and I'm trying to use the LIKE syntax. My issue is that I need to add %% around the variable, but I can't do that since when I inject the variable it injects with SQL injection proof, therefore won't work. The only way I found to do it is putting the variable directly into the query (can be SQL injected to). I even tried wrapping the string with %% but would work either.

WITH SQL INJECTION PROTECTION (WONT RETURN ANYTHING):
await database.execute('SELECT * FROM products WHERE title LIKE %?%', [req.body.query]);

WITHOUT SQL INJECTION PROTECTION (RETURNS):
await database.execute(`SELECT * FROM products WHERE title LIKE %${req.body.query}%`);

Does this answer your question? [Node MySQL escape LIKE statement](https://stackoverflow.com/questions/17922587/node-mysql-escape-like-statement) — kmoser, Dec 25 '20 at 17:27
@kmoser That is a bit similar, but doesn't cover how to prevent sql injection. I believe people can find this useful. — Alhassan Raad, Dec 25 '20 at 17:50

score 2 · Accepted Answer · answered Dec 25 '20 at 17:07

2

Actually, the correct way to do this is to bind the wildcard string to a bare ? placeholder:

var query = 'SELECT * FROM products WHERE title LIKE ?';
await database.execute(query, ['%' + req.body.query + '%']);

answered Dec 25 '20 at 17:07

Tim Biegeleisen

502,043
27
286
360

Yep, that seemed to work. I tried to do it opposite, adding the %% on the client's side. Thanks :) – Alhassan Raad Dec 25 '20 at 17:16

How can I use LIKE in MySQL without removing sanitization?

1 Answers1