Search code examples
mysqlnode.jssecuritysql-injection

Avoid injection in SQL LIKE query in Node js

Several questions were asked on this topic. But I couldn't find the exact answer anywhere. Will the code below cause sql injection? If yes how to escape it?

db.query('SELECT USER.id, USER.username FROM USER LEFT JOIN user_photo photo ON USER.id = photo.user_id WHERE USER.username LIKE "%' + search + '%"', (err, result) => {
   if (err) throw err
   res.send(result)
})

I've tried the code below with mysql.escape(). But SQL query is not working in this case:

db.query('SELECT USER.id, USER.username FROM USER LEFT JOIN user_photo photo ON USER.id = photo.user_id WHERE USER.username LIKE "%' + mysql.escape(search) + '%"', (err, result) => {
   if (err) throw err
   res.send(result)
})
Solution

  • You can specify the LIKE pattern in the parameter that you pass to the query like so, the parameter will be safely escaped as documented here

    let search = "Terry";

let sql = `SELECT USER.id, USER.username FROM USER 
           LEFT JOIN user_photo photo 
           ON USER.id = photo.user_id WHERE USER.username LIKE ?`;

db.query(sql, [`%${search}%`], (err, result) => {
    if (err) throw err
    res.send(result);
})