时间戳

首页 > 解决方案 > 将输入参数传递给节点 mssql 查询函数

node.js - 将输入参数传递给节点 mssql 查询函数

问题描述

我正在使用 Node.js 服务器向 Azure sql 数据库发出请求。

据我了解，以下功能不会阻止 sql 注入：

当前代码：（工作但不安全）

var executeQuery = async function(query, response) {
    const pool = new sql.ConnectionPool(dbConfig)

    pool.on('error', err => {
        console.log('sql errors', err);
    });

    try {
        await pool.connect();
        let result = await pool.request().query(query);
        response.send(result.recordset);
        return {success: result}
     } catch (err) {
        return {err: err};
     } finally {
        console.log('request complete')
        pool.close();    // closing connection after request is finished
     }
};

app.get("/api/workOrders/byId/:workOrderId", function(req, res) {
    console.log(req.params);
    var query = "SELECT * FROM [WorkOrder] WHERE [idWorkOrder]=" + req.params.workOrderId;
    executeQuery(query, res);
});

我想让 executeQuery 函数独立，但我还没有找到答案。无论如何，这是我从 mssql 文档构建的代码：

新代码（不工作）

app.get("/api/test/:workOrderId", function(req, res) {
console.log(req.params.workOrderId);

(async function() {
        const pool = new sql.ConnectionPool(dbConfig)
        pool.on('error', err => {
            console.log('sql errors', err);
        });

        try {
            await pool.connect();
            let result = await pool.request()
              .input('input_parameter', sql.VarChar(50), req.params.workOrderId)
              .query('SELECT * FROM [Quotation] WHERE [idWorkOrder]= @input_parameter');

              console.log(result);

            res.send(result.recordset);
            return {success: result}
         } catch (err) {
             return {err: err};
         } finally {
          console.log('request complete')
          pool.close();    // closing connection after request is finished
    }
  });

})

这个版本应该是防注入的，但它不返回任何东西。是否可以像当前代码一样将输入值传递给 executeQuery 函数？

标签： node.jssql-serversql-injection

解决方案

您可以将值传递req.params.workOrderId给异步函数，然后在内部使用该值。检查以下代码。

app.get("/api/test/:workOrderId", function(req, res) {
console.log(req.params.workOrderId);

(async function(workOrderId) {
        const pool = new sql.ConnectionPool(dbConfig)
        pool.on('error', err => {
            console.log('sql errors', err);
        });

        try {
            await pool.connect();
            let result = await pool.request()
              .input('input_parameter', sql.VarChar(50), workOrderId)
              .query('SELECT * FROM [Quotation] WHERE [idWorkOrder]= @input_parameter');

              console.log(result);

            res.send(result.recordset);
            return {success: result}
         } catch (err) {
             return {err: err};
         } finally {
          console.log('request complete')
          pool.close();    // closing connection after request is finished
    }
  })(req.params.workOrderId); // <===pass value to the function

})

推荐阅读