我正在尝试获取一个mysql语句,以免给我错误。
陈述:
app.get("/Getcompany", function(request, response) {
let cname = request.query.cname
var query = "select * from clientdata_nsw where companyname = '" + connection.escape(cname) + "'"
connection.query(query, function(err, rows) {
if (err) {
console.log(err);
return;
}
rows.forEach(function(result) {
console.log(result.companyname, result.service, result.phone, result.open_times, result.rating_facebook, result.rating_goggle)
})
});
错误信息:
Error: ER_PARSE_ERROR: 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 'ADBY IT''' at line 1
at Query.Sequence._packetToError (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolsequencesSequence.js:51:14)
at Query.ErrorPacket (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolsequencesQuery.js:83:18)
at Protocol._parsePacket (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolProtocol.js:280:23)
at Parser.write (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolParser.js:74:12)
at Protocol.write (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolProtocol.js:39:16)
at Socket.<anonymous> (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibConnection.js:109:28)
at Socket.emit (events.js:189:13)
at addChunk (_stream_readable.js:284:12)
at readableAddChunk (_stream_readable.js:265:11)
at Socket.Readable.push (_stream_readable.js:220:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
--------------------
at Protocol._enqueue (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibprotocolProtocol.js:141:48)
at Connection.query (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesmysqllibConnection.js:214:25)
at C:UsersAdam.WolarczukDesktopProjectsnodetestdb.js:34:13
at Layer.handle [as handle_request] (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterlayer.js:95:5)
at next (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterroute.js:137:13)
at Route.dispatch (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterroute.js:112:3)
at Layer.handle [as handle_request] (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterlayer.js:95:5)
at C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterindex.js:281:22
at Function.process_params (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterindex.js:335:12)
at next (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesexpresslibrouterindex.js:275:10)
at SendStream.error (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulesserve-staticindex.js:121:7)
at SendStream.emit (events.js:189:13)
at SendStream.error (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulessendindex.js:270:17)
at SendStream.onStatError (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulessendindex.js:421:12)
at next (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulessendindex.js:736:16)
at onstat (C:UsersAdam.WolarczukDesktopProjectsnodetestnode_modulessendindex.js:725:14)
at FSReqWrap.oncomplete (fs.js:153:21)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
index: 0 }
使用 connection.escape()
并附加自己的单引号是没有意义的,因为它们最终都会做同样的事情,一起使用意味着转义会完成两次。
如果您浏览下面的代码,您会注意到我使用了模板文字,使您免于通过附加较小的部分来形成主字符串的麻烦。connection.escape()
也会负责逃脱部分。Ryan提到的票证也是我想指出的,因为有一个关于连接的讨论.escape((
app.get("/Getcompany", function(request, response) {
const cname = request.query.cname, query = `select * from clientdata_nsw where companyname = ${connection.escape(
cname
)}`;
connection.query(query, function(err, rows) {
if (err) {
console.log(err);
return;
}
rows.forEach(function(result) {
console.log(
result.companyname,
result.service,
result.phone,
result.open_times,
result.rating_facebook,
result.rating_goggle
);
});
});
});
格式错误,您需要先修复
app.get("/Getcompany", function(request, response) {
let cname = request.query.cname
var query = "select * from clientdata_nsw where companyname = '" + connection.escape(cname) + "'"
connection.query(query, function(err, rows) {
if (err) {
console.log(err);
return;
}
rows.forEach(function(result) {
console.log(result.companyname, result.service, result.phone, result.open_times, result.rating_facebook, result.rating_goggle)
})
});
});
如果您查看错误消息,则无法正确转义变量。错误消息非常有用,如果您注意到尾随'ADBY IT'''
Error: ER_PARSE_ERROR: 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 'ADBY IT'''
您可以使用 ? 字符作为您希望转义的值的占位符,而不是使用转义,如下所示:
数组变成列表,例如 ['a', 'b'] 变成 'a', 'b'
嵌套数组被转换为分组列表(用于批量插入(,例如[['a'
, 'b'], ['c', 'd']] 变成 ('a', 'b'(, ('c', 'd'(
例:
let cname = request.query.cname;
let sql = mysql.format("SELECT * FROM clientdata_nsw WHERE companyname=?", [cname]);
connection.query(sql, function(err, rows) {
if (err) {
console.log(err);
return;
}
});
多个占位符按传递的顺序映射到值。例如,在下面的查询中,foo 等于 a,bar 等于 b,baz 等于 c,id 将是 userId:
connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
if (error) throw error;
// ...
});
引用和有用:https://github.com/mysqljs/mysql
使用 connection.escape
时不应提供自己的引号。这是编写查询的方法:
var query = "select * from clientdata_nsw where companyname = " + connection.escape(cname)
您还可以像这样使用占位符:
connection.query('select * from clientdata_nsw where companyname = ?', [cname],
function(err, rows) {
// ...
}
);
本页介绍如何使用 connection.escape: https://www.w3resource.com/node.js/nodejs-mysql.php
顺便说一句,你不是第一个有这种困惑的人。有人围绕这种行为开了一张票:
https://github.com/mysqljs/mysql/issues/594
他们解释说,由于数值不需要引号,因此添加引号的责任应该落在转义函数身上。