Avoid injection in SQL LIKE query in Node js

Question

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

Try replacement, most library has it. safest way would be parameterized query, but of course slower. — AnonyMouze
– AnonyMouze, Commented Jul 31, 2020 at 10:05
@AnonyMouze, Parameterized queries are slower? You have measured this? — Bill Karwin
– Bill Karwin, Commented Jul 31, 2020 at 14:45
@BillKarwin am not sure about the database execution, but there is extra steps in doing parameterized query most of languages i uses. So it is logically slower. — AnonyMouze
– AnonyMouze, Commented Jul 31, 2020 at 15:07
In practice, the difference is much smaller than the query execution time. It's not a good reason to avoid using parameterized queries. — Bill Karwin
– Bill Karwin, Commented Jul 31, 2020 at 17:22

Terry Lennox · Accepted Answer · 2020-07-31 10:56:59Z

3

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

edited Jul 31, 2020 at 10:56

answered Jul 31, 2020 at 10:43

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Avoid injection in SQL LIKE query in Node js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related