CODEWITHSUNDEEP
mysqlnode.js
Sayyam Kapoor
Sayyam Kapoor

Reputation: 175

How to solve this syntax error in NodeJS MySQL?

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

Upvotes: 0

Views: 708

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

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');
    }
);

Upvotes: 2

Related Questions