Does anyone know how to use SELECT WHERE IN
in node-mysql?
有谁知道如何在node-mysql中使用SELECT WHERE IN?
I've tried the code below, but I get the following error message:
我已经尝试了下面的代码,但是我收到以下错误消息:
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(`PHP`,`apache`)'' at line 1'
This is my code:
这是我的代码:
whereIn = '(';
for ( var i in tagArray ) {
if ( i != tagArray.length - 1 ) {
whereIn += "`" + tagArray[i] + "`,";
}else{
whereIn += "`" + tagArray[i] + "`";
}
}
whereIn += ')';
console.log(whereIn);
client.query(
'SELECT tag_id FROM tag WHERE tag_name IN ?',
[whereIn],
function(err, result, fields) {
client.destroy();
if (err) {
throw err;
}
console.log(result);
res.redirect('/');
}
);
5 个解决方案
#1
14
You have to use IN (?)
and NOT IN ?
.
你必须使用IN(?)和NOT IN?
Any string manipulation may result in a SQL INJECTION backdoor.
任何字符串操作都可能导致SQL INJECTION后门。
#2
2
You need to quote your strings, not use backticks.
你需要引用你的字符串,而不是使用反引号。
whereIn = '(';
for ( var i in tagArray ) {
if ( i != tagArray.length - 1 ) {
whereIn += "'" + tagArray[i] + "',";
}else{
whereIn += "'" + tagArray[i] + "'";
}
}
whereIn += ')';
#3
1
For a more secure solution that avoids having to escape values, use ? params like you would normally do, but create the param placeholders dynamically like this:
对于避免必须转义值的更安全的解决方案,请使用?像往常一样的params,但动态创建param占位符:
var inlist = '';
for(var i=0; i<ids.length; i++) {
inlist += '?,';
}
inlist = inlist.substring(0,inlist.length-1);
var sql = 'SELECT a, b, c FROM mytable WHERE id in (' + inlist + ')';
conn.query( sql, ids, function(err, rows) {
. . .
})
#4
0
You simply need to pass the tagArray
of values to node-mysql and it will handle the rest for you:
您只需要将tagArray值传递给node-mysql,它将为您处理其余的事情:
client.query(
'SELECT tag_id FROM tag WHERE tag_name IN (?)',
[tagArray],
function(err, result, fields) {
client.destroy();
if (err) {
throw err;
}
console.log(result);
res.redirect('/');
}
);
For more information, see the section in the manual for how different values are automatically escaped: https://github.com/mysqljs/mysql#escaping-query-values
有关更多信息,请参阅手册中有关如何自动转义不同值的部分:https://github.com/mysqljs/mysql#escaping-query-values
#5
-1
A working solution:
工作解决方案:
client.query(
'SELECT tag_id FROM tag WHERE tag_name IN ?',
[tagArray],
function(err, result, fields) {
client.destroy();
if (err) {
throw err;
}
console.log(result);
res.redirect('/');
}
);
No need to manually wrap tagArray in quotes. It is escaped by the mysql module.
无需手动将tagArray包装在引号中。它由mysql模块转义。
#1
14
You have to use IN (?)
and NOT IN ?
.
你必须使用IN(?)和NOT IN?
Any string manipulation may result in a SQL INJECTION backdoor.
任何字符串操作都可能导致SQL INJECTION后门。
#2
2
You need to quote your strings, not use backticks.
你需要引用你的字符串,而不是使用反引号。
whereIn = '(';
for ( var i in tagArray ) {
if ( i != tagArray.length - 1 ) {
whereIn += "'" + tagArray[i] + "',";
}else{
whereIn += "'" + tagArray[i] + "'";
}
}
whereIn += ')';
#3
1
For a more secure solution that avoids having to escape values, use ? params like you would normally do, but create the param placeholders dynamically like this:
对于避免必须转义值的更安全的解决方案,请使用?像往常一样的params,但动态创建param占位符:
var inlist = '';
for(var i=0; i<ids.length; i++) {
inlist += '?,';
}
inlist = inlist.substring(0,inlist.length-1);
var sql = 'SELECT a, b, c FROM mytable WHERE id in (' + inlist + ')';
conn.query( sql, ids, function(err, rows) {
. . .
})
#4
0
You simply need to pass the tagArray
of values to node-mysql and it will handle the rest for you:
您只需要将tagArray值传递给node-mysql,它将为您处理其余的事情:
client.query(
'SELECT tag_id FROM tag WHERE tag_name IN (?)',
[tagArray],
function(err, result, fields) {
client.destroy();
if (err) {
throw err;
}
console.log(result);
res.redirect('/');
}
);
For more information, see the section in the manual for how different values are automatically escaped: https://github.com/mysqljs/mysql#escaping-query-values
有关更多信息,请参阅手册中有关如何自动转义不同值的部分:https://github.com/mysqljs/mysql#escaping-query-values
#5
-1
A working solution:
工作解决方案:
client.query(
'SELECT tag_id FROM tag WHERE tag_name IN ?',
[tagArray],
function(err, result, fields) {
client.destroy();
if (err) {
throw err;
}
console.log(result);
res.redirect('/');
}
);
No need to manually wrap tagArray in quotes. It is escaped by the mysql module.
无需手动将tagArray包装在引号中。它由mysql模块转义。