INSERT INTO error with mysql-node

Question

This seems like it should be super easy, and I have been stuck for about two hours now. Four separate people have looked at and not found an obvious problem. So again I turn to the SO community.

Real simple - I am just trying to insert data in a mysql database via mysql-node. I am getting no connection errors, and SELECT works just fine. The code being used is:

exports.postNewCast = function(data, res) {
  var query = "INSERT INTO cast (name, portrait, role, bio) VALUES ('" + data.name + "', '" + data.portrait + "', '" + data.role + "', '" + data.bio + "');";
  console.log(query);
  dbConnection.query(query, data, function(err, result) {
    if (err) {
      console.log(err);
    } else {
      sendResponse(res, "Cast Member Added", 201);
    }
  });
};

The error being logged is:

{ [Error: ER_PARSE_ERROR: 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 'cast (name, portrait, role, bio) VALUES ('Jessie', 'images/cast/marissa.jpg', 'L' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

The weird part (for me) is that I can copy from my terminal window (where the server is running) the console.logged query string, and paste it into the mysql command line, and it works just fine. I have tried using GRANT to make sure the user server is running has permissions, and this did nothing. I have tried copying / pasting INSERT INTO syntax straight from working sources, and only replacing my data-specific fields. I have tried using the VALUES ? option, followed by a data object, and got the same result.

So what stupid mistake am I making?

Thanks.

Answer 1

Ilya Bursov had it correct, adding this answer for posterity. I am not sure if 'cast' is a reserved word or what, but I needed back ticks (" ` ") around the table name to get it working.

Answer 2

Try to put `` around each column name like this

"INSERT INTO cast (`name`, `portrait`, `role`, `bio`) VALUES ('" + data.name + "', '" + data.portrait + "', '" + data.role + "', '" + data.bio + "');";