Node and SQL Injection by using ${variable} on query string-CodePudding

I was told on a question that Im having a SQL Injection problem.

Here is the question

Node with SQL Server - response with for json path query not responding as expected

and here is my code

let sqlString = `
SELECT        codeid, code, validFrom, validTo,
(SELECT        dbo.PLprospectAgentCodesComp.productIdentifier, dbo.masterGroupsProducts.productName, dbo.PLprospectAgentCodesComp.compensation
FROM            dbo.PLprospectAgentCodesComp INNER JOIN
dbo.masterGroupsProducts ON dbo.PLprospectAgentCodesComp.productIdentifier = dbo.masterGroupsProducts.productIdentifier
WHERE        (dbo.PLprospectAgentCodesComp.codeid = dbo.PLprospectAgentCodes.codeid) for json path ) as products
FROM            dbo.PLprospectAgentCodes
WHERE        (plid = ${userData.plid}) for json path`


let conn = await sql.connect(process.env.DB_CONNSTRING)
let recordset = await conn.query(sqlString)

But I've read at Microsoft, and even on a question on this site, that that format prevents SQL injection.

From MS:

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

I was trying to use the declare @parameter for the above code, but since my code has several queries that depend one of another, Im using await for each Query... and @parameter is not working. After I process the recordset, other queries will execute.

If my code actually is dangerous for SQL injection, is it possible to sanitize sqlString before the following two lines? The reason I ask is not to change the method in about 50 routes.

let sqlString = `select * from table where userid=${userId}`

Sanitizing code here

let conn = await sql.connect(process.env.DB_CONNSTRING)
    let recordset = await conn.query(sqlString)

Thanks.

CodePudding user response：

According to https://tediousjs.github.io/node-mssql/ , "All values are automatically sanitized against sql injection." applies only when you use the ES6 Tagged template literals. You should add the tag sql.query before the template string.

let sqlString = sql.query`select * from mytable where id = ${value}`

For more information on tagged template literals: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates