1

I am trying to use node-mysql Escaping query values but when I try and insert it into my MySQL query I am getting errors and can't seem to find the problem. I have tried laying out the code in different ways as well, like the one seen in github example I linked.

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 'don\\'t spam!''' at line 1

banReason = Don't spam!

var banr = con.escape(banReason)

let sql
sql = `INSERT INTO modlog (senderid, victimid, duration, releasedate, reason) VALUES ('${message.author.id}', '${user}', '${timeCode}', '${moment().add(timeValue, timeType)}', '${banr}'`
con.query(sql)
user13862056
  • 13
  • 5
  • 1
    use prepared statements with parameters, that soves your problems and gets you secure against sql injection. see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Jul 03 '20 at 23:45
  • I have seen that but I didn't know how to do that in node as I believe that was for PHP? or am I getting confused (I am not a very good programmer as I just do this as a hobby) – user13862056 Jul 03 '20 at 23:49
  • oh sorry force of habit this is the link https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js – nbk Jul 03 '20 at 23:52
  • That's ok but that just takes me to the same GitHub page I linked in my question. I have tried the example in the node-MySQL documentation for con.escape and ? but both have given the same error that I have shown above? – user13862056 Jul 03 '20 at 23:58
  • you shouldn't escape, that is not what i tried to show you https://stackoverflow.com/a/15779796/5193536 you see INSERT INTO posts SET ? the vital part is the question mark. – nbk Jul 04 '20 at 00:02
  • Thank you I managed to get it working now. Sorry for not really understanding what you meant, I am new to StackOverflow so I am still getting used to how it all works. – user13862056 Jul 04 '20 at 00:18
  • please answer your own question and accept it, and write down what you have done, so other can find it and get also help – nbk Jul 04 '20 at 00:25

1 Answers1

0

To answer the question the correct method is to use ? that can be seen on the node-mysql GitHub Fixed code:

var values = {senderid: message.author.id, victimid: user, duration: timeCode, releasedate: releaseDate, reason: banReason} 
con.query('INSERT INTO modlog SET ?', values, function(err, result) {})

See example from referenced answere

user13862056
  • 13
  • 5