在nodejs中使用准备好的语句(mssql)，但不工作

Question

我将这个函数重写为准备好的语句

let products = await pool.request().query("SELECT TOP (1000) article " +
"FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] " +
"where article in (" + skus + ")");
return products.recordsets;

上面的函数成功返回结果，我尝试使用prepare语句

let pool = await sql.connect(config);
let products = await pool.request()
.input('skus', sql.VarChar(8000), skus)
.query("select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (@skus)");
return products.recordsets;

但是没有找到记录。

这里是完整的源代码

var config = require('./dbconfig');
const sql = require('mssql');
//create a get product function form the database
async function getProducts(skus) {
try {
/*let pool = await sql.connect(config);
let products = await pool.request()
.input('skus', sql.VarChar(8000), skus)
.query("select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (@skus)");
return products.recordsets;*/

/*let products = await pool.request().query("SELECT TOP (1000) article " +
"FROM [ProductCatalogue_brand].[dbo].[CatalogueItems] " +
"where article in (" + skus + ")");
return products.recordsets;*/
} catch (error) {
console.log(error);
}
}
//export getProducts function
module.exports = {
getProducts: getProducts
}

skus是分隔字符串'skus'，'sku2'，'sku3'

有谁知道是什么问题吗?------------------ 更新仍然不工作 -------------------

let pool = await sql.connect(config);
var ps = new sql.PreparedStatement(pool);
//convert skus to array
var skuArray = skus.split(",");
// Construct an object of parameters, using arbitrary keys
var paramsObj = skuArray.reduce((obj, val, idx) => {
obj[`id${idx}`] = val;
ps.input(`id${idx}`, sql.VarChar(200));
return obj;
}, {});
console.log(Object.keys(paramsObj).map((o) => {return '@'+o}).join(','));
// Manually insert the params' arbitrary keys into the statement
var stmt = 'select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (' + Object.keys(paramsObj).map((o) => {return '@'+o}).join(',') + ')';
ps.prepare(stmt, function(err) {
ps.execute(paramsObj, function(err, data) {
console.log(data);
ps.unprepare(function(err) {
});
});
});

Answer 1

预处理语句中的参数被安全地替换为一个项。

所以如果你使用(" + skus + ")，你得到... in ('skus','sku2','sku3')，如果你使用(@skus)，你得到... in ("'skus','sku2','sku3'")(双引号用于可见性和无效语法)，如果文章等于"'skus','sku2','sku3'"，它匹配为真。

尝试根据skus中的项目数量在语句中放置多个@params。假设skus是字符串列表:

const params = skus.map((x,i) => `@sku${i}`).join(", ") // If skus is string just replace skus with skus.split(",")
const stmt = `select article from [ProductCatalogue_brand].[dbo].[CatalogueItems] where Article in (${params})`

然后将skus中的第一项绑定到@sku0，第二项绑定到@sku1，…