1

i'm trying to update a mysql db but when i execute the query i get an error where var id (used as filed in WHERE) is used as column name. Am i reading it wrong? what i have to fix to let this work?

Console Log

var message = '';
var id = req.session.user.id;
console.log(req.session.user.id);
var post  = req.body;
var worker= post.worker;
var farmer= post.farmer;
var soldier= post.soldier;
var defender= post.defender;
         
var sql= "UPDATE stats SET worker = `"+worker+"`, farmer = `"+farmer+"`, soldier = `"+soldier+"`, defender = `"+defender+"` WHERE `stats`.`id` = `"+id+"` ";
      
db.query(sql, function(err, results){
  if(results){
    res.send('/home/dashboard.ejs')
  };
            
  if(err){console.log(err)};
 });
}
Vincenzo Aucello
  • 21
  • 5
  • @David That's not an exact duplicate, because it doesn't say what the correct procedure is when doing MySQL queries from Node. – Tim Biegeleisen Aug 04 '18 at 13:41
  • 1
    @TimBiegeleisen: Is the syntax different when used from Node? Backticks are still treated as backticks, no? – David Aug 04 '18 at 13:45
  • In any case, that link wasn't specific enough, because it implies that all the OP needs to do is replace the backticks with single quotes, which is basically wrong. – Tim Biegeleisen Aug 04 '18 at 13:48
  • I agree that there are a variety of things wrong here. Invalid syntax is what's causing the error. The OP is also subject to SQL injection and generally using the tools incorrectly, and the console output also implies that the expected values aren't being sent to the server in the first place. There's a lot to be fixed. Fixing the syntax should at least move him to the next step though. – David Aug 04 '18 at 13:49
  • I am sorry i wrong log, i fixed lik to the right one – Vincenzo Aucello Aug 04 '18 at 13:50
  • @TimBiegeleisen is put a wrong log, now it is fixed, sorry. Could you check it again? – Vincenzo Aucello Aug 04 '18 at 13:51

2 Answers2

2

The immediate cause of your error is that you are trying to escape literal values using backticks instead of single quotes. The error message you see is hinting at this. But, rather than just replacing those backticks with single quotes, you should instead use a prepared statement:

var sql = "UPDATE stats SET worker = ?, farmer = ?, soldier = ?, defender = ? WHERE id = ?";
db.query(sql, [worker, farmer, soldier, defender, id], function(err, results) {
    if (results) {
        res.send('/home/dashboard.ejs')
    }

    if (err) {
        console.log(err)
    }
});
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
2

Try this:

var sql= "UPDATE stats SET worker = '"+worker+"', farmer = '"+farmer+"', soldier = '"+soldier+"', defender = '"+defender+"' WHERE id = "+id+" ";

I removed the back ticks from the id column name at the end (unnecessary) and properly surrounded the strings with single quote instead of back ticks. Also no need for the table name.

admdev
  • 448
  • 2
  • 9
  • it update field! thank you! Now i have to fight with routes, but we are not here for this XD – Vincenzo Aucello Aug 04 '18 at 13:55
  • This answer is still not optimal, though it might fix the immediate problem, because the update query might be open to _SQL injection_, especially if the fields are coming from the UI and are not being sterilized. – Tim Biegeleisen Aug 04 '18 at 15:17