How can I prevent SQL injection with Node.JS?

Question

How could I select a row of MS SQL server database with Node.JS with preventing SQL injection? I use the express framework and the package mssql.

Here is a part of my code I use now with a possibility to SQL injection written in ES 6.

const express = require('express'),
      app = express(),
      sql = require('mssql'),
      config = require('./config');

let connect = (f, next) => {
    sql.connect(config.database.connectionstring).then(f).catch((err) => {
        next(err);
    });
};

app.get('/locations/get/:id', (req, res, next) => {
    let f = () => {
        new sql.Request().query(`select * from mytable where id = ${req.params.id}`)
                         .then((recordset) => {
            console.dir(recordset);
        }).catch((err) => {
            next(err);
        });
    };

    connect(f, next);
});

Call a stored procedure rather than having a SELECT statement in the code. — R. Richards
– R. Richards, Commented Jan 19, 2017 at 12:53
No need to use stored procedures. It's not the sproc that prevents SQL Injections, it's the use of parameters. DON'T use string formatting or interpolation to create the query, use a parameterized query and pass the parameters separately. That means, no select * from mytable where id = ${req.params.id}. It should be select * from mytable where id = @id and pass @id as a parameter — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 19, 2017 at 13:14
From @JavaEvgen: Sry for writing my notice as an answer, can't write comments since my rep is below 50. I would prefer PreparedStatement answer, but if you just need something very simple just do validation on your back-end and make sure that id that you get is integer. Something like this: if(!isNaN(req.params.id)) and then do your select — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 19, 2017 at 13:15

R. Gulbrandsen · Accepted Answer · 2017-01-19 13:04:07Z

4

Use a PreparedStatement. Here is how you do it from the docs https://www.npmjs.com/package/mssql#prepared-statement :

var ps = new sql.PreparedStatement(/* [connection] */);
ps.input('id', sql.Int);
ps.prepare('select * from mytable where id = @id', function(err) {
  ps.execute({id: req.params.id}, function(err, recordset) {
    ps.unprepare(function(err) {
        // ... error checks 
    });

    // Handle the recordset
  });
});

Remember that each prepared statement means one reserved connection from the pool. Don't forget to unprepare a prepared statement!

You can also create prepared statements in transactions (new sql.PreparedStatement(transaction)), but keep in mind you can't execute other requests in the transaction until you call unprepare.

The docs are written in ES5 but I', sure you can Promisify it :)

answered Jan 19, 2017 at 13:04

R. Gulbrandsen

3,7982 gold badges26 silver badges38 bronze badges

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

1 Comment

Panagiotis Kanavos Over a year ago

You don't need prepared statements for this. SQL Injection is prevented by the use of parameters.

Collectives™ on Stack Overflow

How can I prevent SQL injection with Node.JS?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related