MySQL Syntax error in the update statement, NodeJS(MySQL2) module

Question

I am using Node.JS along with the mysql2 module. It's basically like when I try to update a column with a JSON stingified data, I get the following error:

{ Error: You have an error in your SQL syntax; check the manual that corresponds
 to your MariaDB server version for the right syntax to use near '"1050":1}WHERE
 `user` = ?' at line 1

The stingified JSON data:

{"1050":1}

The query:

var sql = 'UPDATE `users` SET `furniture` = ' + 'concat(furniture,' + JSON.stringify(self.furniture) + ')' + 'WHERE `user` = ?';

self.furniture is related to something else, but I can assure you that self.furniture is returning that JSON data thus I get the mysql syntax error.

sqlMessage: 'You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near \'"1050":

Dev321 · Accepted Answer · 2018-05-12 11:07:27Z

1

Issue is resolved using backticks along with single quotes.

    var sql = 'UPDATE `users` SET `furniture` = ' + `concat(furniture, '${lol}')` + 'WHERE `user` = ?';

    var lol = JSON.stringify(self.furniture)

answered May 12, 2018 at 11:07

Dev321

115 bronze badges

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

Comments

O. Jones · Accepted Answer · 2018-05-12 10:54:06Z

0

Your query line says in part

 ...ingify(self.furniture) + ')' + 'WHERE `us...

It should have an extra space after the close parenthesis.

...ingify(self.furniture) + ') ' + 'WHERE `us...

Here's the thing about MySQL syntax errors: the message shows the erroneous query starting with the first character MySQL does not understand. A good way to troubleshoot this kind of thing is to use console.log() to output the entire query string, then look at it carefully. You will usually find something obvious wrong.

edited May 12, 2018 at 10:54

answered May 12, 2018 at 10:46

O. Jones

109k17 gold badges133 silver badges187 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

The better thing to do, if possible, would be to use statements.

Dev321 Over a year ago

Not really related to the issue, I can't figure out what is wrong with my queries. I tried it, it still spills out the same error.

Collectives™ on Stack Overflow

MySQL Syntax error in the update statement, NodeJS(MySQL2) module

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related