How to solve this syntax error in NodeJS MySQL?

Question

Getting a syntax error here but can't figure out why?

Have tried using con.escape as well. Gives the same error.

var sql1 = "INSERT INTO Captcha (Captcha_Image) VALUES ('"+imgBase64+"') WHERE Session_ID = '"+x+"'";
  await con.query(sql1, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });

//Both imgBase64 and x are varchar values and are being stored in correctly

how to solve this 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 'WHERE Session_ID = '93e23e3f7d17b1c50107aa6277cb303985e38e1a5faa0a505064806c291a' at line 1

Tim Biegeleisen · Accepted Answer · 2019-08-01 04:48:28Z

2

Insert statements in SQL don't have a WHERE clause, so the following should work without error:

var mysql = require('mysql');
var con = mysql.createConnection({ ... });

var sql1 = "INSERT INTO Captcha (Captcha_Image) VALUES (?)";
con.query({
    sql: sql1,
    timeout: 40000,
},
    [imgBase64],
    function (error, results, fields) {
    }
);

However, a WHERE clause might make sense if you were doing an INSERT INTO ... SELECT, with a select query serving as the source of data to be inserted. Something like this:

INSERT INTO Captcha (Captcha_Image)
SELECT Captcha_Image
FROM some_other_table
WHERE Session_ID = '93e23e3f7d17b1c50107aa6277cb303985e38e1a5faa0a505064806c291a';

If you instead want to change the Captcha image for records which already exist in the table, based on the session id, then you should be doing an update, not an insert:

con.query('UPDATE Captcha SET Captcha_Image = ? WHERE Session_ID = ?',
    [imgBase64, x],
    function (error, results, fields) {
    if (error) throw error;
    console.log('changed ' + results.changedRows + ' rows');
    }
);

edited Aug 1, 2019 at 4:48

answered Aug 1, 2019 at 4:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

5 Comments

Sayyam Kapoor Over a year ago

So is there no way to add the value while checking for a particular ID?

Tim Biegeleisen Over a year ago

What are you trying to check? By definition, the records you are trying to insert aren't even in the database table yet.

Sayyam Kapoor Over a year ago

They are inserted earlier. This isn't the full code.

Tim Biegeleisen Over a year ago

If the records are already in the table, and you want to change their values, then you should be doing an update, not an insert. Let me edit my answer again.

Sayyam Kapoor Over a year ago

I'm sorry for not being clearer. I have a table setup where one column value is filled up while the other fields remain empty. Now I want to select this row using the already entered ID and fill up the rest of the fields.

Collectives™ on Stack Overflow

How to solve this syntax error in NodeJS MySQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related