Node MySQL escape LIKE statement
25,831
Solution 1
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 [email protected]
)
Solution 2
i've had success with something like
"SELECT * FROM card WHERE name LIKE " + connection.escape('%'+req.body.search+'%')
Solution 3
How about
mysql.format("SELECT * FROM card WHERE name LIKE CONCAT('%', ?, '%')", req.body.search)
?
Author by
dtsn
Updated on August 29, 2021Comments
-
dtsn over 2 years
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.
-
Parth about 5 yearsThis 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!
-
Nico Haase over 4 yearsWhat do you mean by that? Can you add some explanation to your code such that others can learn from it?
-
Rodrigo Pazzini Jacques about 4 yearslets 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 * FROMusers
WHEREname
LIKE ?;", [name], (erro, callback)) }` -
Rodrigo Pazzini Jacques about 4 yearsThe point is to use javascript template strings to have the % operator inside the string that the LIKE uses to search.
-
Nico Haase about 4 yearsPlease add all explanation to your answer by editing it
-
zealouscoder over 3 yearsWhat 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 -
Aminadav Glickshtein over 2 yearsDo not use it. SQL INJECTION.
-
Michael about 2 yearsI 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!