0

I'm sending a query to create a user in the DB by doing:

let sql = `INSERT INTO Users (Username, Password, FirstName, LastName, Permissions) VALUES ('${req.body.username}', '${hashedPassword}','${req.body.FirstName}','${req.body.LastName}', 'basic_client'); SELECT * FROM Users WHERE UserID = LAST_INSERT_ID();`;

I haven't yet implemented protection for SQL injection but will later (suggestions welcome).

The issue is that my server gets code: 'ER_PARSE_ERROR', errno: 1064, 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 'SELECT * FROM Users WHERE UserID = LAST_INSERT_ID()' at line 1",

Any help is appreciated!

I've tried:

  • I tried multi-line string, and the last line seems to cause the issue, normal INSERT INTO is working.
  • I tried logging the output and when I try running the exact same query via the Workbench, it works just fine.
  • LAST_INSERT_ID() alone also works in the Workfbench
  • I replaced the whole SQL code with just SELECT LAST_INSERT_ID() and it worked via the server. But nothing else works
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mel Habip
  • 46
  • 4

1 Answers1

0

I found the missing link in this post --> node-mysql multiple statements in one query

Its not because the SQL is invalid, but rather because Node --> DB queries are by default limited to a single operation in order to combat SQL injection. It is possible to override this, but I'll play it safe and send 2 requests instead.

On that note, the setup works when 2 requests are sent, it works beautifully!

Mel Habip
  • 46
  • 4