如何将 NodeJS 变量输入到 SQL 查询中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41168942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to input a NodeJS variable into an SQL query
提问by GleneaMan
I want to write an SQL query that contains a NodeJS variable. When I do this, it gives me an error of 'undefined'.
我想编写一个包含 NodeJS 变量的 SQL 查询。当我这样做时，它给了我一个“未定义”的错误。
I want the SQL query below to recognize the flightNovariable. How can a NodeJS variable be input into an SQL query? Does it need special characters around it like $or ?.
我希望下面的 SQL 查询能够识别flightNo变量。如何将 NodeJS 变量输入到 SQL 查询中？它周围是否需要特殊字符，例如$或?.
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) {
回答by Dave
You will need to put the valueof the variable into the SQL statement.
您需要将变量的值放入 SQL 语句中。
This is no good:
这不好：
"SELECT * FROM arrivals WHERE flight = 'flightNo'"
This will work, but it is not safe from SQL injection attacks:
这会起作用，但它对 SQL 注入攻击并不安全：
"SELECT * FROM arrivals WHERE flight = '" + flightNo + "'"
To be safe from SQL injection, you can escape your value like this:
为了避免 SQL 注入，您可以像这样转义您的值：
"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) {
});
});
回答by DeadEye
If you are using > ES6 :
如果您使用 > ES6 ：
connection.query(`SELECT * FROM arrivals WHERE flight = ${flightNo}`, function(err, rows, fields) {
If you are < ES6 :
如果你是 < ES6 ：
connection.query("SELECT * FROM arrivals WHERE flight = " + flightNo, function(err, rows, fields) {
Please note that this is VERY BAD practiceas you will be vulnerable to SQL-injection attacks.
请注意，这是非常糟糕的做法，因为您很容易受到 SQL 注入攻击。
