Trying to INSERT new row into MySQL table with Node.js getting cryptic error. Can't get working

Question

I'm trying to insert a new row into a table called authentication, but despite all my tinkering, I just keep getting this vague error that doesn't get me anything specific in google searches. Error:

 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 '`token` = '1d281f4a-7079-40f9-a9ff-d7425df9315f', `ip` = '::1', `device_name` = ' at line 1",
      sqlState: '42000',
      index: 0,
      sql: "INSERT INTO `authentication` (token, ip, device_name, expiration_date) VALUES `token` = '1d281f4a-7079-40f9-a9ff-d7425df9315f', `ip` = '::1', `device_name` = 'desktop', `expiration_date` = '2020-09-20T02:06:59.658Z'"

The code:

con.connect((err) =>
{
    if (err) throw err;
    console.log("Connected to " + db_host);
    let sql = "INSERT INTO `authentication` (token, ip, device_name, expiration_date) VALUES ?";
    con.query(sql, {"token":token, "ip":user_ip, "device_name":device_type, "expiration_date":iso_expiration_date}, (err, result) =>
    {
        if (err) throw err;
        console.log("One token added to " + db_host);
    });
});

The table "authentication" has 4 columns:

token           | VARCHAR(36)
ip              | VARCHAR(15)
device_name     | VARCHAR(7)
expiration_date | VARCHAR(24)

Does this answer your question? [Node-mysql insert query with two values?](https://stackoverflow.com/questions/40350747/node-mysql-insert-query-with-two-values) — kmoser, Aug 21 '20 at 03:07

score 0 · Accepted Answer · answered Aug 21 '20 at 03:05

The query should use ? as a placeholder for every value:

let sql = "INSERT INTO `authentication` (token, ip, device_name, expiration_date) VALUES ( ?, ?, ?, ? )";

And to bind the values:

con.query(sql, [token, user_ip, device_type, iso_expiration_date], (err, result)

From the documentation: https://github.com/mysqljs/mysql#escaping-query-values

Trying to INSERT new row into MySQL table with Node.js getting cryptic error. Can't get working

1 Answers1