Binding dynamic array parameter values for SQL with NodeJS

Question

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?

Ben in CA · Accepted Answer · 2023-11-07 14:56:33Z

1

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

answered Nov 7, 2023 at 14:56

Ben in CA

90916 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Binding dynamic array parameter values for SQL with NodeJS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related