时间戳

首页 > 解决方案 > Mysql with node 并表示奇怪的查询错误

mysql - Mysql with node 并表示奇怪的查询错误

问题描述

我构建了自己的 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;

为什么我使用正确的查询会出错？任何想法？谢谢！

标签： mysqlnode.jsexpress

解决方案

出于安全原因，禁用了对多个语句的支持（如果值未正确转义，则允许 SQL 注入攻击）。要使用此功能，您必须为您的连接启用它。

https://www.npmjs.com/package/mysql#multiple-statement-queries

尝试这个

const connection = mysql.createConnection({
    user: 'user',
    password: 'password',
    database: 'db',
    multipleStatements: true
});

推荐阅读