CODEWITHSUNDEEP
node.js
Matthew John Miernik
Matthew John Miernik

Reputation: 21

Node JS SQL prevent sql injection

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

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

Upvotes: 0

Views: 7997

Answers (2)

Rob Streeting
Rob Streeting

Reputation: 1735

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

Upvotes: 3

Kuldeep Semwal
Kuldeep Semwal

Reputation: 245

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;

  // ...

});

Upvotes: 6

Related Questions