mysql - 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)?

Answer 1

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