Node MySQL escape LIKE statement

Question

How do escape a MySQL LIKE statement in node-mysql?

Something along the lines of

"SELECT * FROM card WHERE name LIKE '%" + connection.escape(req.body.search) + "%'"

Results in

'SELECT * FROM card WHERE name LIKE \'%\'hello\'%\''

Which is a syntax error. If I use the alternative syntax of

connection.query("SELECT * FROM card WHERE name LIKE '%?%'", req.body.search, function () {});

Results in a similar syntax error. I've also tried

connection.query("SELECT * FROM card WHERE name LIKE ?", '%' + req.body.search + '%', function () {});

Which just ends up escaping the '%' sign.

Third example is correct, what is the exact problem you are seeing? Note that if you want to search for strings that literally contain percent signs that's [trickier](http://stackoverflow.com/questions/2106207/escape-sql-like-value-for-postgres-with-psycopg2/2106443#2106443). — bobince, Jul 29 '13 at 11:46

score 44 · Accepted Answer · answered Jul 29 '13 at 11:22

44

Not sure why it's escaping the % in your last example, because that works fine for me:

// lifted from my code:
var value = 'ee20e966289cd7';
connection.query('SELECT * from django_session where session_key like ?', '%' + value + '%', ...)

// Result:
[ { session_key: '713ee20e966289cd71b936084a1e613e', ... } ]

When I turn on debugging in the driver (pass debug:true as argument to mysql.createConnection), it doesn't escape the percent sign:

{ command: 3,
  sql: 'SELECT * from django_session where session_key like \'%ee20e966289cd7%\'' }

(it does escape the single quote, but that's for display purposes only)

(using mysql@2.0.0-alpha8)

answered Jul 29 '13 at 11:22

robertklep

198,204
35
394
381

What if I want to do something like this? `query = 'SELECT * from Events e where e.Zipcode='+mysql.escape(req.body.near)+" and e.EventName LIKE %"+mysql.escape(req.body.find)+'%';` and then have this passed to query the db? I am getting the same error – zealouscoder Oct 12 '20 at 04:59
I get an error like this: `Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sometext'%')' at line 1. I don't understand why it's putting in the extra single quotes! – Michael Apr 28 '22 at 18:38

score 19 · Answer 2 · answered Jan 06 '16 at 04:57

19

i've had success with something like

"SELECT * FROM card WHERE name LIKE " + connection.escape('%'+req.body.search+'%')

answered Jan 06 '16 at 04:57

Gary

421
3
4

This works. connection.escape works in a weird manner. I was trying to put the % sign around the connection.escape and then get something like %'value'%. That would totally ruin the query. But got this correct now. Thanks to you. Cheers! – Parth May 03 '19 at 05:59

score 8 · Answer 3 · answered Nov 18 '18 at 18:19

8

How about

mysql.format("SELECT * FROM card WHERE name LIKE CONCAT('%', ?,  '%')", req.body.search)

?

answered Nov 18 '18 at 18:19

Lukasz Prus

343
1
5
10

score 0 · Answer 4 · edited Jan 14 '20 at 09:30

0

you can always do

variable = '%${variable}%'
"SELECT * FROM 'table' WHERE ('foo' LIKE ?);", 
[variable], callback =>

edited Jan 14 '20 at 09:30

Maximilian Ast

3,369
12
36
47

answered Jan 13 '20 at 21:14

Rodrigo Pazzini Jacques

11
3

What do you mean by that? Can you add some explanation to your code such that others can learn from it? – Nico Haase Jan 14 '20 at 07:21
lets say you have a method called searchUser that gets a name as param and returns the user data or false if the user does not exist `searchUser(user){ name = `%${name}%` db.query("SELECT * FROM `users` WHERE `name` LIKE ?;", [name], (erro, callback)) }` – Rodrigo Pazzini Jacques Feb 18 '20 at 22:14
The point is to use javascript template strings to have the % operator inside the string that the LIKE uses to search. – Rodrigo Pazzini Jacques Feb 18 '20 at 22:24
Please add all explanation to your answer by editing it – Nico Haase Feb 19 '20 at 06:46

score 0 · Answer 5 · answered Jan 09 '21 at 22:46

0

I had the same problem and solved it like this:

function search(searchTerm) {

    let replacement = `'%${searchTerm}%'`;

    let sqlStatement = `SELECT * from  clients where firstName LIKE ${replacement}`;

    const [rows, fields, error] = connection.query(sqlStatement);

    return rows;

}

answered Jan 09 '21 at 22:46

AML

1
1

2

Do not use it. SQL INJECTION. – Aminadav Glickshtein Oct 19 '21 at 07:10

score 0 · Answer 6 · answered Aug 28 '21 at 18:03

0

Simple and easy way:

`SELECT * FROM card WHERE name LIKE ` + connection.escape(`%${req.body.search}%`)

answered Aug 28 '21 at 18:03

Hammad Ali

331
3
12

Node MySQL escape LIKE statement

6 Answers6

Linked