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.

Answer 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])

Answer 2

i've had success with something like

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