sql server - Node MSSQL - How to use the built in SQL injection protection

4
votes

I'm looking at the built in SQL injection protection in the MSSQL module for Node:

https://www.npmjs.com/package/mssql#injection

But I don't totally get their example of how to sanitize values for my queries. I guess the request.input('myval', sql.VarChar, '-- commented') statement sanitizes "myvar" against the rest of the query being commented out.

What if I want to sanitize against something else as well? Such as drop table statements etc.

Could anyone help me out?

1
sql-servernode.jssql-injection
Bump. Help anyone? :) – Petter
Why are you trying to implement black list anyway? It's a lost battle to begin with... Input validation should be implemented using a white list approach. If you still insist on black lists as an additional defense mechanism, don't try to implement one by yourself, use WAF such as Imperva, F5 or ModSecurity (free), or if you want to implement it in the DB level, use DB firewall (Sentrigo or GreenSQL-hexatier). – Gil Cohen

1 Answers

2
votes

Just figured out how to work this. request.input() accepts three parameters described below from mssql docs:

input (name, [type], value) Add an input parameter to the request.

Arguments

name - Name of the input parameter without @ char.

type - SQL data type of input parameter. If you omit type, module automatically decide which SQL data type should be used based on JS data type.

value - Input parameter value. undefined ans NaN values are automatically converted to null values.

So the way you'd use this is as follows;

let id= 123456
const stmt = 'SELECT * FROM Users WHRE User=@userId'
request.input('userId', sql.Int, id);
request.query(stmt)

Note that:

  • id is the variable that holds my value and is used as the third parameter in request.input(name, type, value).
  • userId is just a name I've specified for the variable that will be used in the sql statement using @userId. In the request.input(), id value is assigned to userId in the sql statement.
  • Lastly sql.Int is just a data type that will be used to validate the date coming into userId. You use this by importing sql in the same file: const sql = require('mssql')