如何将参数传递给节点js中的mssql查询 [英] How to pass parameter to mssql query in node js

问题描述

我正在将应用程序的数据库从MySQL更改为MSSQL.我向MySQL数据库发送以下参数.

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
   } 
}

如何在MSSQL中做到这一点，或者甚至有可能做到这一点? (我正在使用mssql模块).

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

存储过程是实现它的唯一方法吗?如果是这样，如何在Nodejs mssql模块中执行此操作?

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

如果我们无法发送参数(自动转义mysql中的字符串)，那么在SQL Server中运行查询的最佳实践是什么?

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

推荐答案

我这样做像这样的参数化SQL查询:

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)
})

这里发生的是，sql.input('inputField1', sql.VarChar, myFirstInput)将使用名为myFirstInput的变量替换为@inputField1.对于@inputField2和mySecondInput也会发生同样的事情.

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.

这将有助于SQL注入

这篇关于如何将参数传递给节点js中的mssql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！