尝试在 NodeJS 中使用 mySQL 进行预先准备的语句。此代码的哪一部分不起作用？

Question

所以我试图插入数据到我的数据库中，连接很好，但由于某种原因，当我试图创建准备好的语句时，它不工作。

我的数据库中的所有值都是varchar(255)，除了描述是文本。发送的数据都是字符串。但这就是问题所在吗?我如何使这个执行没有任何错误?

const app = express();
const http = require('http').Server(app);
const io = require('socket.io')(http);
const path = require('path');
const crypto = require('crypto');
const mysql = require('mysql');
const db = mysql.createConnection({
host:   'localhost',
user:   'root',
password:   '',
database:   'dos-bros-it',
});
db.connect((err) => {
if(err) {
console.log(err.code);
console.log(err.fatal);
}else{
console.log("Connection has been succesfully initiated!")
}
})
const PORT = 7072;
app.use(express.static(path.join(__dirname, "client/build/")));
app.use(express.urlencoded({extended: true}));
app.use(express.json());
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, "client/public/", "index.html"));
});
app.post('/repair', (req, res, next) => {
$query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?, ?, ?, ?, ?)";
$data = [
[req.body.firstName], 
[req.body.lastName],
[req.body.email], 
[req.body.phone], 
[req.body.request]
]
db.query($query, 
[$data], (err, rows, fields) => {
if (!err) { 
console.log('Repair was succesfully sent to the servers database! n Records: ' + rows);
}else{
console.log(err);
}
});
console.log(req.body.firstName, req.body.lastName, req.body.email, req.body.phone, req.body.request);
res.send("<h1>FORM SENT</h1>")
next();
})
io.on("connection", (socket) => {
console.log('Client has connected to the server!!!');
socket.on('test', (msg)=>{
console.log('recieved test message!!!', msg);
})
})
http.listen(PORT, ()=>{
console.log('Server Started using port:', PORT);
})

下面我提供了错误代码。

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?, ?, ?, ?, ?' at line 1",
sqlState: '42000',
index: 0,
sql: "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES ('bobby'), ('mcboba'), ('[e-mail]'), ('1234567890'), ('haww ahagor naou rngoanr ogaeo gw'), ?, ?, ?, ?, ?;"
}

Answer 1

SQL插入语法关闭。您指定了5列，因此应该只有5个?占位符。同样，VALUES后面的需要是括号(...)中的元组。使用这个版本:

$query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?, ?, ?, ?, ?)";

Answer 2

我能够弄清楚，显然它将所有的$data对象放在一个"?"中，所以我删除了所有的"?"这似乎奏效了。希望这对将来的人有所帮助。

$query = "INSERT INTO tickets (firstName, lastName, email, phone, description) VALUES (?)";
$data = [
[req.body.firstName], 
[req.body.lastName],
[req.body.email], 
[req.body.phone], 
[req.body.request]
]
db.query($query, 
[$data], (err, rows, fields) => {
if (!err) { 
console.log('Repair was succesfully sent to the servers database! n Records: ' + rows);
}else{
console.log(err);
}
});