How to Input a Nodejs Variable into an SQL Query

input a NodeJS variable into an SQL query

There is no point using connection.escape() and appending your own single quotes because both of them end up doing the same thing and using together means escaping is done twice.

If you go through my code below you can notice that I have made use of template literals saving you from hassle of forming the main string by appending smaller parts. Also connection.escape() would take care of escaping part. The ticket mentioned by Ryan is something that I would also like to point you to, because there was a discussion surrounding connection.escape()

app.get("/Getcompany", function(request, response) {
  const cname = request.query.cname, query = `select * from clientdata_nsw where companyname  = ${connection.escape(
    cname
  )}`;
  connection.query(query, function(err, rows) {
    if (err) {
      console.log(err);
      return;
    }

    rows.forEach(function(result) {
      console.log(
        result.companyname,
        result.service,
        result.phone,
        result.open_times,
        result.rating_facebook,
        result.rating_goggle
      );
    });
  });
});

How to input a NodeJS variable into an SQL query

You will need to put the value of the variable into the SQL statement.

This is no good:

"SELECT * FROM arrivals WHERE flight = 'flightNo'"

This will work, but it is not safe from SQL injection attacks:

"SELECT * FROM arrivals WHERE flight = '" + flightNo + "'"

To be safe from SQL injection, you can escape your value like this:

"SELECT * FROM arrivals WHERE flight = '" + connection.escape(flightNo) + "'"

But the best way is with parameter substitution:

app.get("/arrivals/:flightNo", cors(), function(req, res) {
  var flightNo = req.params.flightNo;

  var sql = "SELECT * FROM arrivals WHERE flight = ?";
  connection.query(sql, flightNo, function(err, rows, fields) {
  });
});

If you have multiple substitutions to make, use an array:

app.get("/arrivals/:flightNo", cors(), function(req, res) {
  var flightNo = req.params.flightNo;
  var minSize = req.query.minSize;

  var sql = "SELECT * FROM arrivals WHERE flight = ? AND size >= ?";
  connection.query(sql, [ flightNo, minSize ], function(err, rows, fields) {
  });
});

Nodejs and MySQL, How to use NodeJs variable in MySQL INSERT statement

DatabaseConnection.query('INSERT INTO Air_Pollution_Record (Air_Pollution_Reading) VALUES ('+Pollution_Reading+')');

Passing in a variable to a query string using node/js/MySQL workbench

Use this as insert query

var sql = `INSERT INTO loginInfo 
            VALUES
            (
                ?, ?, ?, ?
            )`;
connection.query(sql, [firstName, lastName, email, passW], function (err, res) {
    if (err) throw err;
    console.log("Inserted ...")
  });

How to insert query in NodeJS with variables

You want to use prepared statements.

Consider:

var query = connection.query(
    'INSERT INTO stats_tmp (
        id, 
        timestamp, 
        campaign_nm, 
        calls, 
        seconds, 
        answered, 
        failure, 
        dnc, 
        amd, 
        transfers, 
        transfer_seconds, 
        cost
    ) VALUES (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
    )',
    [
        estarr[0].id,
        estarr[0].timestamp,
        estarr[0].name,
        estarr[1].calls,
        estarr[1].seconds,
        estarr[1].answers,
        estarr[1].failures,
        estarr[1].dncs,
        estarr[1].amd,
        estarr[1].transfers,
        estarr[1].transfers,
        estarr[1].transferseconds,
        estarr[1].cost
    ],
    function(err, results) {    
      ...
});