node js mysql多变量插入查询er\u parse\u错误

jv2fixgn 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(202)

我创建了一个webhook，在这里使用node.js接收一些数据。我使用mysql存储数据，到目前为止，服务器能够连接到数据库，接收客户机数据，并在接收数据时做出适当的响应。我只是在格式化mysql的查询字符串时遇到了问题。
我尝试了各种引号配置，例如不将列名括在单引号中，不将表名括起来，以及在查询字符串的每个部分上切换双引号和单引号。
如有任何建议，将不胜感激！谢谢您！
我的问题：

var sql = "INSERT INTO 'cms'('ts', 'orgid', 'orgname', 'deviceid', 'devicename', 'event', 'setid', 'setname', 'viewid', 'viewname', 'duration', 'hotspotid', 'hotspotname', 'playlistid', 'playlistname', 'playlisttype', 'playlistassetid', 'playlisttitle') VALUES ("+timestamp+", "+orgid+", "+orgname+", "+deviceid+", "+devicename+", "+eventname+", "+setid+", "+setname+", "+viewid+", "+viewname+", "+duration+", "+hotspotid+", "+hotspotname+", "+playlistid+", "+playlistname+", "+playlisttype+", "+playlistassetid+", "+playlisttitle+")";
                            con.query(sql, function (err, result) { //Queries String with MySQL connection variable con
                                    if (err) throw err;
                                    var date = new Date();
                                    var currHour = date.getHours();
                                    console.log("Record Inserted at " + currHour); //Logs time of db insertion on console
                            });

错误也在这里：

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 ''cms'('ts', 'orgid', 'orgname', 'deviceid', 'devicename', 'event', 'setid', 'set' at line 1
at Query.Sequence._packetToError (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/Protocol.js:278:23)
at Parser.write (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/home/webhook/httpHandler/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/home/webhook/httpHandler/node_modules/mysql/lib/Connection.js:502:10)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
--------------------
at Protocol._enqueue (/home/webhook/httpHandler/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/home/webhook/httpHandler/node_modules/mysql/lib/Connection.js:200:25)
at IncomingMessage.<anonymous> (/home/webhook/httpHandler/server.js:70:9)
at emitNone (events.js:106:13)
at IncomingMessage.emit (events.js:208:7)
at endReadableNT (_stream_readable.js:1064:12)
at _combinedTickCallback (internal/process/next_tick.js:139:11)
at process._tickCallback (internal/process/next_tick.js:181:9)

mysql JavaScript node.js server String

来源：https://stackoverflow.com/questions/53751902/node-js-mysql-insert-query-of-multiple-variables-er-parse-error

3条答案

按热度按时间

8xiog9wr1#

我的第一个建议是创建一个接受参数的存储过程，而不是向sql注入敞开大门。
我的第二个建议是规范你们的table。如果要为多个表的键数据同时添加id和name，那么这是一个糟糕的设计。
除此之外，投资回报率是正确的。您需要将几个值用转义引号括起来：
var sql=“插入cms（ts，orgid，orgname，deviceid，event，setid，setname，viewid，viewname，duration，HOTSOTID，HOTSOTNAME，PLAYLIID，PLAYNAME，PLAYLTYPE，PLAYLASSETID，PLAYTITLITLE）值（'+timestamp.tostring（）+“'，”+orgid+“，”+orgname+“，”+deviceid+“，'+devicename+”，“+eventname+”、“+setid+”、“+setname+”、“+viewid+”、“+viewname+”、“+duration+”、“+hotspotid+”、“+hotspotname+”、“+PlayId+”、“+PlayName+”、“+PlayType+”、“+PlayAssetId+”、“+PlayTitle+”）”

赞(0）回复(0）举报 2021-06-18

x759pob22#

我会做这样的事。

var sql = `INSERT INTO 'cms'('ts', 'orgid', 'orgname', 'deviceid', 'devicename', 'event', 'setid', 'setname', 'viewid', 'viewname', 'duration', 'hotspotid', 'hotspotname', 'playlistid', 'playlistname', 'playlisttype', 'playlistassetid', 'playlisttitle') VALUES ("${timestamp}", "${orgid}" ...)`;

赞(0）回复(0）举报 2021-06-18

dtcbnfnu3#

我想问题出在这个代码上：