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)
})
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);
})