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) {
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) {
});
});
connection.query('INSERT INTO table VALUES (?)', flightNo, function(err, result) { });
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
