Node JS SQL prevent sql injection

Question

How to prevent sql injection. I have this sql query.

db.query('SELECT data FROM '+(server)+'.users WHERE user = 1');

There is already a thread about that : https://stackoverflow.com/a/15778841/7699045 — Anwar Benhamada, Jun 24 '19 at 09:56

score 6 · Answer 1 · edited Jun 24 '19 at 09:45

6

If you are using npm mysql you can use In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using themysql.escape(), connection.escape() or pool.escape() methods:

var userId = 'some user provided value';


var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);

connection.query(sql, function (error, results, fields) {

  if (error) throw error;

  // ...

});

Alternatively, you can use ? characters as placeholders for values you would like to have escaped like this:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {

  if (error) throw error;

  // ...

});

edited Jun 24 '19 at 09:45

Raymond Nijland

11,488
2
22
34

answered Jan 29 '19 at 12:02

Kuldeep Semwal

235
2
4

1

is there any way to write middleware to prevent SQL Injection in NodeJS – Usman Hafeez Jan 31 '20 at 12:11
Yours is the only answer I can find on this that defends against SQL injection to a variable query, i.e. a query whose fields and values vary with user input. This is useful if you are using the same script (and even the same query variable) to do backend validation or registration/login for form data like username, email and password. – Trunk Aug 24 '20 at 20:24

score 3 · Answer 2 · answered Jun 24 '19 at 10:01

In addition to parameterising queries and escaping user inputs, a good habit to get into is to immediately validate all user input values in the router to make sure you're getting what you expect to get from the user. If you're using express, the express-validator library is very handy for validating inputs. Here's an example from the documentation I've modified to apply to your problem:

const { check, validationResult } = require('express-validator');

app.post('/user', [
  // server must be a valid database
  check('server').isIn([... list of valid databases ...])
], (req, res) => {
  // Finds the validation errors in this request and wraps them in an object with handy functions
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(422).json({ errors: errors.array() });
  }

  db.query('SELECT data FROM '+(req.body.server)+'.users WHERE user = 1');
});

Node JS SQL prevent sql injection

2 Answers2