Passing input parameters to node mssql query function

Question

I'm using a Node.js server to make requests to an Azure sql database.

As far as I understand the following function does not prevent sql injection:

Current code: (working but unsafe)

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

I would like to have the executeQuery function standalone, but I did not find an answer for that yet. Anyway, this is the code I constructed from mssql documentation:

New Code (not working)

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

})

This version should be injection proof, but It does not return anything. Is there an option to pass the input values to the executeQuery function as in the current code?

You may want to use some ORM such as Sequelize or Knex. It makes you life easier, code more readable, type checking and validation as well as injection proofing all in one package — Molda
– Molda, Commented Jan 8, 2020 at 12:54
@Florian so you want to get value of req.params.workOrderId inside your async function right?? — Yogesh.Kathayat
– Yogesh.Kathayat, Commented Jan 8, 2020 at 12:58
@Yogesh.Kathayat Exactly, I need an injection safe way to do this. — Florian
– Florian, Commented Jan 8, 2020 at 13:12

Yogesh.Kathayat · Accepted Answer · 2020-01-08 13:21:56Z

You can pass the value of req.params.workOrderId into your async function and then use that value inside. check the following code.

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

})

Collectives™ on Stack Overflow

Passing input parameters to node mssql query function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related