我构建了自己的API来使用MySQL。 它可以工作，但是现在我想更新两个表，我有这样的查询:
router.post("/addentry", (req, res) => {
let sql =
"BEGIN; INSERT INTO entries (title,kindof, image01,image02,image03, website) VALUES('?','?','?','?','?','?'); INSERT INTO categories (id,title,category) VALUES(LAST_INSERT_ID(),'?', '?'); COMMIT;";
let queryArray = [
"title",
"foodordesign",
"image01",
"image02",
"image03",
"website",
"title",
"category",
];
let query = connection.query(sql, queryArray, (err, results) => {
if (err) throw err;
console.log(results);
res.header("Access-Control-Allow-Origin", "*");
res.send("Entry added to DB");
});
});
我得到和错误:
code: 'ER_PARSE_ERROR',
[0] errno: 1064,
[0] 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 'INSERT INTO entries (title,kindof, image01,image02,image03, website) VALUES(''ti' at line 1",
[0] sqlState: '42000',
[0] index: 0,
如果我在myPHPAdmin上测试它，它可以完美地工作:
BEGIN;
INSERT INTO entries (title,kindof, image01,image02,image03, website) VALUES('test', 'test','test', 'test','test', 'test');
INSERT INTO categories (id,title,category) VALUES(LAST_INSERT_ID(),'test', 'test');
COMMIT;
为什么我得到和错误使用一个正确的查询？ 知道吗？ 谢啦！
出于安全原因，禁用了对多语句的支持（如果值没有正确转义，则允许SQL注入攻击）。 要使用此功能，您必须为您的连接启用它。
https://www.npmjs.com/package/mysql#多语句查询
试试这个
const connection = mysql.createConnection({
user: 'user',
password: 'password',
database: 'db',
multipleStatements: true
});
尽量避免对参数通配符使用单引号
INSERT INTO entries (title,kindof, image01,image02,image03, website) VALUES(?,?,?,?,?,?);
INSERT INTO categories (id,title,category) VALUES(LAST_INSERT_ID(),?, ?); COMMIT;
对于bining参数，数据类型匹配应由db驱动程序管理
