CODEWITHSUNDEEP
mysqlnode.jsexpress
Toj19
Toj19

Reputation: 139

MySQL 5.6.41 errno 1064: Creating a MySQL query with variables

I have a (currently localhost, but soon to be through AWS) Node.JS server with Express and I'm trying to update an RDS instance through a MySQL query when I'm getting the following error:

{ [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 ''history0' = 'http://localhost:3000/' WHERE id = 1' at line 1]
  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 \'\'history0\' = \'http://localhost:3000/\' WHERE id = 1\' at line 1',
  sqlState: '42000',
  index: 0,
  sql: 'UPDATE infected SET \'history0\' = \'http://localhost:3000/\' WHERE id = 1;' }

The POST request causing the error:

app.post('/history', function(req, res) {
  var hist = 'history' + 0;
  var sql = 'UPDATE infected SET ? = ? WHERE id = ?;';
  connection.query(sql,  [hist, req.body[0].url, 1]);
});

I'm using hist as a variable because I plan to have it in a loop, but I wasn't sure if the way I'm declaring it here is causing the issue so I left it as is. req.body is the output of JSON.stringify() called on call to chrome.history.search(). So I'm trying to get the URL of the entry at index 0.

I've tried a direct call to connection.query with a hard-coded string as follows:

connection.query("UPDATE infected SET history0='google.com' WHERE id='1'");

and it successfully updates the database, so I figure there's an issue with how I'm using the question marks to insert variables hist and req.body[0].url into the query, but I can't figure out what the issue is.

Upvotes: 0

Views: 113

Answers (1)

Alejandro Lariccia
Alejandro Lariccia

Reputation: 369

try with double "??" for the keys, this way:

app.post('/history', function(req, res) {
  var hist = 'history' + 0;
  var sql = 'UPDATE infected SET ?? = ? WHERE id = ?;';
  connection.query(sql,  [hist, req.body[0].url, 1]);
});

Upvotes: 1

Related Questions