[SOLVED] Binding dynamic array parameter values for SQL with NodeJS

Binding dynamic array parameter values for SQL with NodeJS

To avoid SQL injection with MS SQL Server and Node.js, I typically bind parameters, with something like the following:

let pool = await sql.connect(config)
await pool.request()
  .input('ID', sql.Int, ID)
  .input('Name', sql.NVarChar(100), Name)
  .query(`UPDATE Table SET Name = @Name WHERE ID = @ID;`)

However, sometimes I have an array of dynamically varying length that I need to supply to a query.

So for instance, I might have a list of references that I want to pull data for. Example

const references = ['B1', 'C2', 'D4', 'Z8']; // Sample, but number of entries can be variable

And I'd like to do something like this (with pseudo code):

let pool = await sql.connect(config)
await pool.request()
  .input('ReferenceList', sql.NVarChar(5), references) // but can't because this is an array
  .query(`SELECT * FROM Table WHERE Reference = IN(@ReferenceList);`)

I would think this would be a fairly common scenario; what have I missed that I'm not seeing such functionality standard?

For example, a non-parameter binding method would be: (example only - risky to use in internet-facing production systems)

  var references_clause = ""
  if(references && references.length){
    references.forEach(function (item) {
      references_clause += "'"+item+"',"
    });
  } else {
    references_clause += "'NA'" // If length false
  }
  references_clause = references_clause.replace(/,(\s+)?$/, '')

let pool = await sql.connect(config)
    await pool.request()
      .query(`SELECT * FROM Table WHERE Reference = IN(@references_clause);`)

Other frameworks / languages have some methods for this (e.g. Doctrine - How to bind array to the SQL?) what is the recommended method for Node.js?

Solution

Got an answer that this can be done with the tagged template literal support.

const pool = new ConnectionPool(config);
const req = pool.request();
const query = req.query`select * from [table] where id in (${[1,2,3]})`
query.then((res) => console.log(res);

Source: https://github.com/tediousjs/node-mssql/issues/1575#issuecomment-1798341849