CODEWITHSUNDEEP
node.js
Danielle
Danielle

Reputation: 1496

Node.JS - SQL server query with parameters to prevent SQL injection

Im using the following which works perfect when querying the database:

let conn = await sql.connect(process.env.connectionSetup)
    let recordset = await conn.query("select * from POLICIES where policyid=912")
    res.json(recordset)

The problem is when adding a parameter, to prevent SQL injection:

let conn = await sql.connect(process.env.connectionSetup)
    let recordset = await conn.query("select * from POLICIES where policyid=:policy", { policy: 912})
    res.json(recordset)

In that case, Im getting the error

UnhandledPromiseRejectionWarning: RequestError: Incorrect syntax near ':'.

I also tried utilizing ? but I get the same error. Does that have something to do with tedious?!

Thanks.

Upvotes: 4

Views: 3669

Answers (3)

PA.
PA.

Reputation: 29339

To prevent SQL injection attacks, the safe way to insert values into an SQL query is using SQL parameters.

The actual syntax may vary depending on the SQL dialect your database uses and depending on the server driver API.

For nodejs mssql, you have tagged template literals in the form of

  sql.query`select * from POLICIES where policyid = ${policy}`

According to the mssql documentation

All values are automatically sanitized against sql injection. This is because it is rendered as prepared statement, and thus all limitations imposed in MS SQL on parameters apply. e.g. Column names cannot be passed/set in statements using variables.

Upvotes: 6

Swap
Swap

Reputation: 349

U can use

var request =  new sql.Request();
request.input('policyid',sql.VarChar,policy);
request.query("select * from POLICIES where policyid = @policyid");

refer below links for more details

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

https://www.npmjs.com/package/mssql#input-name-type-value

Upvotes: 1

David H
David H

Reputation: 35

select * from POLICIES where policyid = ${policy} is just a string . This is not a request with params. If you do this you are open to sql injection.

Look this https://tediousjs.github.io/tedious/parameters.html

Upvotes: 1

Related Questions