Preventing Nodejs MySQL SQL Injection

Question

How do I prevent SQL injection for NodeJS? I am trying to prevent SQL Injection using the ? symbol and the req.param. But I am not able to get to work. How should I use the req.param.id correctly? Many thanks in advance.

    app.get('/products/:id', (req, res) => {
    conn.getConnection(function (err, connection) {
        if (err) throw err;
    const SELECT_WHERE_PRODUCT_ID_QUERY = `SELECT * FROM products WHERE id = ?, $[req.param.id]`
    connection.query(SELECT_WHERE_PRODUCT_ID_QUERY, function (error, results, fields) {
    connection.release()
      if (error) throw error;
      return res.send(results)     
    });
  }); 
});

Slava Semushin · Accepted Answer · 2020-07-17 09:21:30Z

2

Yes, we should use prepared statements for that and ? as placeholders. In order to make it work, we should pass parameters as a separate argument:

const query = 'SELECT * FROM products WHERE id = ?';
const params = [req.param.id];
connection.query(query, params, function (error, results, fields) {

Another form:

connection.query(
    {
        sql: 'SELECT * FROM products WHERE id = ?',
        values: [req.param.id]
    },
    function (error, results, fields) {

See documentation for more examples.

answered Jul 17, 2020 at 9:21

Slava Semushin

15.2k7 gold badges56 silver badges69 bronze badges

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

2 Comments

program_bumble_bee Over a year ago

How does SQL injection will work in case of query like this: SELECT * from TABLE. No 'WHERE' clause or any conditions. Just simply preventing injection in queries that fetch complete data.

Slava Semushin Over a year ago

In your example SQL injection isn't possible. It doesn't matter whether a query has WHERE clause or other conditions because it can only happen when we use user-supplied data in a SQL-query. If we don't pass any arguments or we have a full control over arguments, then there is no SQL-injection.

Nat · Accepted Answer · 2020-07-17 09:19:26Z

1

This should prevent SQL injection.

const SELECT_ALL_PRODUCT_QUERY = 'SELECT * FROM products WHERE id = ?'
connection.query(SELECT_ALL_PRODUCT_QUERY,[req.params.id], function (error, results)

answered Jul 17, 2020 at 9:19

Nat

7492 gold badges13 silver badges34 bronze badges

Collectives™ on Stack Overflow

Preventing Nodejs MySQL SQL Injection

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related