如何在SQL查询中输入NodeJS变量 [英] How to input a NodeJS variable into an SQL query
问题描述
我想编写一个包含NodeJS变量的SQL查询.当我这样做时,它给我一个未定义"的错误.
I want to write an SQL query that contains a NodeJS variable. When I do this, it gives me an error of 'undefined'.
我希望下面的SQL查询能够识别flightNo
变量.如何将NodeJS变量输入到SQL查询中?它周围是否需要特殊字符,例如$
或?
.
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) {
推荐答案
您需要将变量的值放入SQL语句中.
You will need to put the value of the variable into the SQL statement.
这不好:
"SELECT * FROM arrivals WHERE flight = 'flightNo'"
这可以工作,但是从SQL注入攻击中是不安全的:
This will work, but it is not safe from SQL injection attacks:
"SELECT * FROM arrivals WHERE flight = '" + flightNo + "'"
为了防止SQL注入,您可以像这样转义您的值:
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) {
});
});
这篇关于如何在SQL查询中输入NodeJS变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!