How to input a NodeJS variable into an SQL query

Question

I want to write an SQL query that contains a NodeJS variable. When I do this, it gives me an error of 'undefined'.

I want the SQL query below to recognize the flightNo variable. How can a NodeJS variable be input into an SQL query? Does it need special characters around it like $ or ?.

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

connection.query("SELECT * FROM arrivals WHERE flight = 'flightNo'", function(err, rows, fields) {

Dave · Accepted Answer · 2016-12-15T20:24:21.030

27

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

edited Dec 15 '16 at 20:24

answered Dec 15 '16 at 20:19

Dave

1,918
1
16
25

Should I use connection.escape even when I want to use "INSERT INTO table" command ? So it should be like this: "INSERT INTO table VALUES ('" + connection.escape(flightNo) + "'")" – Christos Michael Feb 09 '19 at 08:38
You should protect against SQL injection with every query. Better to use `connection.query('INSERT INTO table VALUES (?)', flightNo, function(err, result) { });` – Dave Feb 10 '19 at 11:37
This is how am doing it: var sql = "INSERT INTO admins VALUES(null, '" + adminUser +"', '" + adminPass + "', '" + adminName + "' , '"+ adminSurname +"') "; con.query(sql,function(error, rows, fields){ if(!!error) { console.log('Query Failed' + error.message); } else { console.log('Query Successful'); console.log(rows.insertId); next(); } }); – Christos Michael Feb 11 '19 at 12:21
@ChristosMichael You should be escaping those values. I'd do it with parameter substitution: https://codeshare.io/2WbdvY – Dave Feb 12 '19 at 15:54
IBM_DB requires the parameters be an array even if there's only one of them. – Clarius Aug 24 '21 at 07:21

score 0 · Answer 2 · answered Apr 27 '23 at 18:16

From ES6 you can use template literals with ${expression}.

Template literals are literals delimited with backtick (`) characters, allowing multi-line strings and interpolation with expressions.

Use like this:

connection.query(`
SELECT * 
FROM arrivals 
WHERE flight = ${flightNo}
`, //rest of your code

How to input a NodeJS variable into an SQL query

2 Answers2

Linked