Mysql with node and express strange query error

Question

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!

mousto090 · Accepted Answer · 2020-06-28 11:53:46Z

2

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

answered Jun 28, 2020 at 11:53

mousto090

2,0391 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mousto090 Over a year ago

Don't quote the placeholders, VALUES('?','?','?','?','?','?') should be VALUES(?,?,?,?,?,?)

Collectives™ on Stack Overflow

Mysql with node and express strange query error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related