Trying to pass the parameter in a query in Node.js mssql

Question

I'm trying to query the table on some condition:

  try {
    var pool = await sql.connect(config);
    var data = await sql.query`SELECT * FROM mytable WHERE ${types}`;
  } catch (err) {
    res.send(err);
  }

Where types is "ContractType = 'AllRisks' "

For some reason it's not returning anything. When I put hardcode types string directly inside the sql.query then it actually returns results and works.

Not too sure what is happening.

I try the other way:

var result1 = await pool.request()
  .input('types', sql.VarChar(50), types)
  .query('SELECT * FROM dbo.denormalized WHERE @types');

And this doesn't work either.

Would really appreciate some help!

Catalyst · Accepted Answer · 2018-03-25 05:16:53Z

0

First

var data = await sql.query`SELECT * FROM mytable WHERE ${types}`;

Is missing parenthesis, so it isn't actually calling .query, but you really should be doing the second method anyways, for security (to prevent sql injection).

But the second way is probably throwing an error the way it is -

In sending a prepared statement, the sql has to be interpreted/understood without the @types literal being given - it considers it a parameter. select * from x where 'hello world' isn't valid sql, and everything within @types is being bound as a Varchar literal.

do

SELECT * FROM dbo.denormalized WHERE ContractType = @types

and make the javascript types variable only contain AllRisks. Note you will not need to escape the quotes around AllRisks, the value "AllRisks" as a string should be sufficient; e.g. let types = 'AllRisks'. You already told the library you were binding a Varchar.

--- Edit

Since you want to do an array of types, I looked into doing WHERE IN using the mssql package and turned up this related question - NodeJS MSSQL WHERE IN Prepared SQL Statement

I would throw in the towel on using the mssql module directly at this point and use http://knexjs.org/ which is common, and uses mssql underneath the hood. It will handle this sort of thing for you with something like knex.select().from('table').whereIn('ContractTypes', types).

edited Mar 25, 2018 at 5:16

answered Mar 25, 2018 at 4:40

Catalyst

3,23720 silver badges21 bronze badges

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

3 Comments

Jack Over a year ago

Thanks for getting back to me! The reasons I needed to concatenate the string in such a way because the number of contract types is dynamic. It might be several values. Wasn't sure how to do that and right now I'm just concatenating these multiple values

Catalyst Over a year ago

@Jack I updated the answer, there's a related question for helping with WHERE IN, and I would recommend using knex.js instead of going that route - looking at the answer its a little heavy.

Jack Over a year ago

Thanks! Just looked at knexjs and this is exactly what I've been looking for!

Collectives™ on Stack Overflow

Trying to pass the parameter in a query in Node.js mssql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related