javascript - 使用 Express 和 MySQL 模块通过 NodeJS 更新记录时出现 SQL 语法错误
问题描述
当我尝试在 NodeJS 中更新 MySQL 记录时,我在 Visual Studio Code 的终端中收到“应用程序崩溃”错误。
app2.js:
const express = require('express');
const mysql = require('mysql');
// create connection
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'journey_test'
})
// connect to mysql
db.connect(err => {
if(err) {
throw err
}
console.log('MySQL Connected');
})
const app = express()
app.use(express.static('public'));
app.set('view engine', 'ejs');
// this pulls index.ejs to the root folder location of the site
app.get('/', function (req, res) {
res.render('index2');
});
app.post('/change/:artist/:id', (req, res) => {
let newArtist = req.params.artist;
let newID = req.params.id ;
// even hard-setting variables in my query causes failure
let sql = `UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'`
let query = db.query(sql, err => {
if(err) {
throw err
}
res.send('Entry updated');
})
})
app.listen('3000', () => {
console.log('Server Started on port 3000')
})
index2.ejs:
<!DOCTYPE html>
<html>
<head>
<script>
function testing() {
var xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
const text = this.responseText;
const obj = JSON.parse(text);
document.getElementById("Hello2").innerHTML = "Yes!";
};
xhttp.open("POST", "change/The Cure/104", true);
xhttp.send();
}
</script>
</head>
<body>
<button style= "height:22px";" type="button" onclick="testing()">Update</button>
<div id="Hello2">Did I do it?</div>
</body>
</html>
Powershell中的错误:
PS C:\Users\Mervius\Documents\NodeJS> nodemon app2.js
[nodemon] 2.0.12
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app2.js`
Server Started on port 3000
MySQL Connected
C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:437
throw err; // Rethrow non-MySQL errors
^
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 'UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'' at line 1
at Query.Sequence._packetToError (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Query.ErrorPacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
at Protocol._parsePacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket.<anonymous> (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:88:28)
at Socket.<anonymous> (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:375:28)
at addChunk (internal/streams/readable.js:290:12)
--------------------
at Protocol._enqueue (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Connection.query (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:198:25)
at C:\Users\Mervius\Documents\NodeJS\app2.js:36:24
at Layer.handle [as handle_request] (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\layer.js:95:5)
at next (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\layer.js:95:5)
at C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:281:22
at param (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:354:14)
at param (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:365:14) {
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "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 'UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'' at line 1",
sqlState: '42000',
index: 0,
sql: "UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'"
}
[nodemon] app crashed - waiting for file changes before starting...
在 MySQL 控制台中有效的查询: 您可以看到它有效。
使用 WAMP 服务器(因为我将以前的 Visual Basic WinForms 应用程序重新构建为 Web 应用程序): MySQL:5.7.9 Apache:2.4.17
我发誓上述方法几天前才有效,但当时环境可能发生了一些变化(例如安装更多节点模块,诸如此类,尽管当时我肯定使用的是 WAMP 服务器)。
任何帮助表示赞赏。
编辑:我在使用 NodeJS 选择或插入 MySQL 数据库时没有遇到问题。
添加(在约翰迈克尔曼卢皮格的建议最初奏效之后,至少在原始上下文中):问题在另一个地方仍然存在。我正在通过 tinymce 富文本编辑器将信息(作为“结果”)发布到 app.js,但在使用以下代码时以同样的方式失败:
const express = require('express');
const mysql = require('mysql');
//const dayjs = require('dayjs');
const multer = require('multer');
const upload = multer({ dest: 'uploads/' });
app.post('/result', upload.none(), function (req, res, next) { // when we save RTE info in tinymce
var newEntry = req.body.content
var newDate = activedate
const sql = `UPDATE main SET entry = ? WHERE dateID = ?`;
const query = db.query(sql, [newEntry, newDate], err => {
if(err) {
throw err
}
res.send('Entry updated');
})
});
我可以将接收到的“req.body.content”值发送到控制台,这很好,但是通过其余代码发送它会导致“应用程序崩溃”,尽管@建议的参数化方式与上述相同John Michael Manlupig,尽管查询文本包含正确/预期的信息,并且可以在 MySQL 控制台中按原样使用。
有什么明显的我忽略了吗?
解决方案
您应该对这些值进行参数化,而不是将其直接插入到字符串中。
const sql = `UPDATE tblbillboardcomplete SET artist = ? WHERE id = ?`;
const query = db.query(sql, ['The Cure', 104], err => {
if(err) {
throw err
}
res.send('Entry updated');
});
推荐阅读
- jquery - 鼠标移出后保持子菜单打开
- python - 如何使用python查询数据库
- c++ - 在 C++ 中使用 delete[] 不会释放所有内存
- android - 使用 Hive 保存在本地设备上的数据可以通过移动设备的备份移动到另一台设备吗?(Flutter)
- material-ui - 将 mui5 主题改回默认(紫色)v4 主题
- javascript - Angular2中给出动态id方法的区别
- r - 如果另一列中的值在 R 中重复,如何替换列中的不同值?
- jasper-reports - 当我做 Jasper 报告时,我的服务器中的字符错误
- react-native - 无法加载脚本。确保您正在运行 Metro 服务器反应本机
- sparql - 如何通过 SPARQL 仅知道 Protégé 中的个人和类名来检索数据属性值?