Show SQL value using Node

Question

I'm using Node.js to create a Discord Bot. I want to display all the custom commands when a user types !commands all, the Table looks as follows

| | guildname | guildid | commandname | commandreply | username | userid
------------------------------------------------------------------------
| | MyServer    1002      !test         This is test   Rusty      890

I have got this code:

var info = {
  "guildname": message.guild.name,
  "guildid": message.guild.id,
  "commandname": args[0],
  "commandreply": args.join(" ").slice(args[0].length),
  "username": message.author.username,
  "userid": message.author.id
}

connection.query("SELECT * FROM commands WHERE guildid = '" + message.guild.id + "'", info, function(error, commandExists) {
  if (error) throw error;

  if (commandExists.length) {
    console.log(commandname); // commandname is not defined
  }
}

How do I access one of those values, for example, the commandname?

Do try and get out of the habit of concatenating SQL strings and instead use placeholder values whenever possible. That is `WHERE guildid=?` and then include the appropriate values as directed by your driver. It's also probably a super bad plan to throw errors inside asynchronous code. That screws up a lot of things. — tadman, Apr 25 '17 at 23:52

Marcos Casagrande · Accepted Answer · 2017-06-30T02:02:05.350

results will contain an array of objects, where each object will contain the selected fields with their values.

connection.query("SELECT * FROM commands WHERE guildid = '" + message.guild.id + "'", info, (error, results, fields) => {

    // error will be an Error if one occurred during the query 
    // results will contain the results of the query 
    // fields will contain information about the returned results fields (if any) 

    if (error) throw error;

    if (results.length) {
        results.forEach(command => {
            console.log(command.commandname);
        });
    }
});

The query can be improved using placeholders.

If you're using mysql or mysql2 here's how:

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("SELECT * FROM commands WHERE guildid = :guildId", { guildId: message.guild.id });

Check this question:

How can prepared statements protect from SQL injection attacks?

EDIT

In mysql2 there's built in support for named placeholders.

there is built in support for this - https://github.com/sidorares/node-mysql2/blob/master/documentation/Extras.md#named-placeholders — Andrey Sidorov, Jun 30 '17 at 01:45
You're right for mysql2 (Never saw it, thanks!), but mysql package does not have it. — Marcos Casagrande, Jun 30 '17 at 01:51

Show SQL value using Node

1 Answers1