How to pass parameter to mssql query in node js

Question

I am changing my application's database from MySQL to MSSQL. I send parameters like below for MySQL database.

var sql = require('./db.js');
sql.query("select * from table where field1 = ? or field2 = ? ", [field1val, field2val], function (error, results) {
   if(error){
       //handle error
   }
   else{
      //handle the results
   } 
}

How to do this in MSSQL or is it even possible to do this way? (I am using mssql module).

The stored procedure is the only way to achieve it? If so, how to do that in Nodejs mssql module?

What is the best practice to run a query in SQL server if we can't send parameter (which escapes the string in mysql automatically)?

Please share your db.js script, so we will know what's happening in your sql.query function — Ed Bangga, Nov 14 '19 at 07:48
sql.query("select * from table where field1 = "+field1val+" or field2 = "+field2val, function (error, results) { — Jayakumar Thangavel, Nov 14 '19 at 07:51
https://stackoverflow.com/questions/31822891/how-to-build-dynamic-query-by-binding-parameters-in-node-js-sql — Jayakumar Thangavel, Nov 14 '19 at 07:52

KZander · Answer 1 · 2019-11-15T07:07:24.477

14

I do parameterized SQL queries like this:

var sql = require('mssql');
var myFirstInput = "foo bar";
var mySecondInput = "hello world";

sql.input('inputField1', sql.VarChar, myFirstInput);
sql.input('inputField2', sql.VarChar, mySecondInput);

sql.query("SELECT * FROM table WHERE field1 = @inputField1 OR field2 = @inputField2")
.then(function(results)
{
    //do whatever you want with the results
    console.log(results)
})
.catch(function(error)
{
   //do whatever when you get an error
   console.log(error)
})

What happens here is that the sql.input('inputField1', sql.VarChar, myFirstInput) will change out the @inputField1 with the variable named myFirstInput. Same thing will happen for @inputField2 with mySecondInput.

This will help it from SQL injections

edited Nov 15 '19 at 07:07

answered Nov 14 '19 at 07:55

KZander

300
4
16

Do the inputs get "reset" after each query? – workwise Sep 10 '21 at 10:11
@workwise as long as you assign 'myFirstInput' and 'mySecondInput' with different variables every time you query, then yes the inputs would have a different value every time the function is called. – KZander Sep 14 '21 at 11:14

score -4 · Answer 2 · answered Nov 14 '19 at 09:26

-4

var sql = require('./db.js');
sql.query(`select * from table where field1 = ${field1val} or field2 = ${field2val}`, function (error, results) {
   if(error){
       //handle error
   }
   else{
      //handle the results
   } 
}

I think, it works for you :)

answered Nov 14 '19 at 09:26

Xander Le

44
5

10

Careful with sql injection. – ssoward Oct 16 '20 at 21:13
2

mssql does provide a tagged template approach that they claim guards against sql injection: https://github.com/tediousjs/node-mssql#es6-tagged-template-literals (though to be clear, this example doesn't use it) – starwed Sep 28 '21 at 22:03
+1 to **ES6 Tagged template literals** if we can trust [node-mssql documentation](https://github.com/tediousjs/node-mssql#es6-tagged-template-literals) – Yurii Holskyi Nov 16 '21 at 16:18

score -5 · Answer 3 · answered Nov 14 '19 at 07:54

You can use try like this

var id = 1234

var query = (SQL
            `SELECT fname, lname, email
             FROM users
             WHERE id = ${id}`
            )

var sql = require('./db.js');
sql.query("select * from table where field1 = ${field1val} or field2 = ${field2val} ", [field1val, field2val], function (error, results) {
   if(error){
       //handle error
   }
   else{
      //handle the results
   } 
}

How to pass parameter to mssql query in node js

3 Answers3

Linked

Related