I am using Node.js to create a Discord bot. Some of my code looks as follows:

var info = {
  userid: message.author.id
}

connection.query("SELECT * FROM table WHERE userid = '" + message.author.id + "'", info, function(error) {
  if (error) throw error;
});

People have said that the way I put in message.author.id is not a secure way. How can I do this? An example?

I am using Node.js to create a Discord bot. Some of my code looks as follows:

var info = {
  userid: message.author.id
}

connection.query("SELECT * FROM table WHERE userid = '" + message.author.id + "'", info, function(error) {
  if (error) throw error;
});

People have said that the way I put in message.author.id is not a secure way. How can I do this? An example?

• javascript
• mysql
• sql
• node.js
• sql-injection

Share Improve this question Follow edited May 5, 2020 at 21:48 Mike 14.6k3232 gold badges120120 silver badges177177 bronze badges asked Apr 27, 2017 at 12:22 user7442152user7442152

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 9

The best way to is to use prepared statements or queries (link to documentation for NPM mysql module: https://github./mysqljs/mysql#preparing-queries)

var sql = "SELECT * FROM table WHERE userid = ?";
var inserts = [message.author.id];
sql = mysql.format(sql, inserts);

If prepared statements is not an option (I have no idea why it wouldn't be), a poor man's way to prevent SQL injection is to escape all user-supplied input as described here: https://www.owasp/index.php/SQL_Injection_Prevention_Cheat_Sheet#MySQL_Escaping

Use prepared queries;

var sql = "SELECT * FROM table WHERE userid = ?";
var inserts = [message.author.id];
sql = mysql.format(sql, inserts);

You can find more information here.

Here is the documentantion on how to properly escape any user provided data to prevent SQL injections: https://github./mysqljs/mysql#escaping-query-values . mysql.escape(userdata) should be enough.