CODEWITHSUNDEEP
mysqlnode.jsexpress
Marco Disco
Marco Disco

Reputation: 565

Mysql with node and express strange query error

I built my own API to use MySql. It works but now I wouldlike to update two tables and I have this query:

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");
  });
});

I get and error:

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,

if I test this on myPHPAdmin it works perfectly:

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;

Why I get and error using a correct query? Any idea? Thanks!

Upvotes: 0

Views: 33

Answers (1)

mousto090
mousto090

Reputation: 2039

Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped). To use this feature you have to enable it for your connection.

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

Try this

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

Upvotes: 2

Related Questions