Insert request parameters using node js and mssql

Question

I'm having a problem while trying to make an insert statement using the code below and I tried to separate the request body parameters but also not working:

//Initiallising node modules
var express = require('express');
var bodyPasrser = require('body-parser');
var sql = require('mssql');
var app = express();


//Body Parser to parse to JSON
app.use(bodyPasrser.json());


//CORS Middleware
app.use(function(req,res,next){
    res.header("Access-Control-Allow-Origin","*");
    res.header("Access-Control-Allow-Methods","GET,HEAD,POST,PUT,OPTIONS");
    res.header("Access-Control-Allow-Headers","Origin,X-Requested-With,contentType,Content-Type,Accept,Authorization");
    next();
});


//Setting up server
var server = app.listen(process.env.PORT||8080,function(){
    var port = server.address().port;
    console.log("App now running on port ",port);
});


//setup database connection
var dbconfig = {
    user:"sa",
    password:"...",
    server : "localhost",
    database: "supermarket"  
};


// ConnectionPool


//connect to the database 
var executeQuery = function(res,query){
    sql.connect(dbconfig,function(err){
        if(err){
            console.log("there is a database connection error -> "+err);
            res.send(err);
        }
        else{
            // create request object
            var request = new sql.Request();
            // query to the database
            request.query(query,function(err,result){
                if(err){
                    console.log("error while querying database -> "+err);
                    res.send(err);
                }
                else{
                    res.send(result);
                    sql.close();
                }
            });
        }
    });
}


//POST API
app.post("/api/Category", function(req , res){
    var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (req.body.CName,req.body.CSubCategory)";
    executeQuery (res, query);
});

The error displayed while testing through postman is like the following:

"The multi-part identifier \"req.body.CSubCategory\" could not be bound."

Terry Lennox · Accepted Answer · 2018-01-08 14:10:53Z

6

You're accessing the req.body.CName directly in the string, this won't work, you'll need to use parameters for your query:

// Change execute query to accept parameters.
var executeQuery = function(res,query,parameters){
    sql.connect(dbconfig,function(err){
        if(err){
            console.log("there is a database connection error -> "+err);
            res.send(err);
        }
        else{
            // create request object
            var request = new sql.Request();

            // Add parameters
            parameters.forEach(function(p) {
                request.input(p.name, p.sqltype, p.value);
            });

            // query to the database
            request.query(query,function(err,result){
                if(err){
                    console.log("error while querying database -> "+err);
                    res.send(err);
                }
                else{
                    res.send(result);
                    sql.close();
                }
            });
        }
    });
}



//POST API
app.post("/api/Category", function(req , res){

    var parameters = [
      { name: 'CName', sqltype: sql.NVarChar, value: req.body.CName},
      { name: 'CSubCategory', sqltype: sql.NVarChar,  value: req.body.CSubCategory},
    ];

    var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (@CName, @CSubCategory)";
    executeQuery (res, query, parameters);
});

edited Jan 8, 2018 at 14:10

answered Jan 8, 2018 at 12:23

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mohammed Ehab Over a year ago

"message": "Must declare the scalar variable \"@CName\"

Terry Lennox Over a year ago

I'll review and fix!

Mohammed Ehab Over a year ago

fixed by just getting the request parameters out of the string and adding single quote after and before each string as following var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES ('" +req.body.CName+"','"+req.body.CSubCategory+"')";

Terry Lennox Over a year ago

Hi @MohammedEhab that will work, but you need to be careful about SQL Injection attacks using this method, an attacker could include SQL commands with the POST>

Terry Lennox Over a year ago

I've updated the code, including a change to executeQuery, this will add parameters to the query.

Collectives™ on Stack Overflow

Insert request parameters using node js and mssql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related