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

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.

CodePudding user response：

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 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.