Preventing SQL injection in Node.js

Is it possible to prevent SQL injections in Node.js (preferably with a module) in the same way that PHP had Prepared Statements that protected against them.

If so, how? If not, what are some examples that might bypass the code I've provided (see below).

---

Some Context:

I'm making a web application with a back-end stack consisting of Node.js + MySql using the node-mysql module. From a usability perspective, the module is great, but it has not yet implemented something akin to PHP's Prepared Statements (though I'm aware it is on the todo).

From my understanding, PHP's implementation of prepared statements, among other things, helped greatly in the prevention of SQL injections. I'm worried, though, that my node.js app may be open to similar attacks, even with the string escaping provided by default (as in the code snippet below).

node-mysql seems to be the most popular mysql connector for node.js, so I was wondering what other people might be doing (if anything) to account for this issue - or if it is even an issue with node.js to begin with (not sure how this wouldn't be, since user/client-side input is involved).

Should I switch to node-mysql-native for the time being, since it does provide prepared statements? I'm hesitant to do this, because it does not seem to be as active as node-mysql (though that may just mean that it is complete).

Here is a snippet of user registration code, which uses the sanitizer module, along with node-mysql's prepared statement-like syntax (which, as I mentioned above, does character escaping), to prevent cross site scripting and sql injections, respectively:

// Prevent xss
var clean_user = sanitizer.sanitize(username);

// assume password is hashed already
var post = {Username: clean_user, Password: hash};
       
// This just uses connection.escape() underneath
var query = connection.query('INSERT INTO users SET ?', post,
   function(err, results)
   {
       // Can a Sql injection happen here?
   });

The node-mysql library automatically performs escaping when used as you are already doing. See https://github.com/felixge/node-mysql#escaping-query-values

The library has a section in the readme about escaping. It's Javascript-native, so I do not suggest switching to node-mysql-native. The documentation states these guidelines for escaping:

Edit: node-mysql-native is also a pure-Javascript solution.

• Numbers are left untouched
• Booleans are converted to true / false strings
• Date objects are converted to YYYY-mm-dd HH:ii:ss strings
• Buffers are converted to hex strings, e.g. X'0fa5'
• Strings are safely escaped
• Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'
• Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')
• Objects are turned into key = 'val' pairs. Nested objects are cast to strings.
• undefined / null are converted to NULL
• NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

This allows for you to do things like so:

var userId = 5;
var query = connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  //query.sql returns SELECT * FROM users WHERE id = '5'
});

As well as this:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  //query.sql returns INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
});

Aside from those functions, you can also use the escape functions:

connection.escape(query);
mysql.escape(query);

To escape query identifiers:

mysql.escapeId(identifier);

And as a response to your comment on prepared statements:

> From a usability perspective, the module is great, but it has not yet implemented something akin to PHP's Prepared Statements.

The prepared statements are on the todo list for this connector, but this module at least allows you to specify custom formats that can be very similar to prepared statements. Here's an example from the readme:

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

This changes the query format of the connection so you can use queries like this:

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });
//equivalent to
connection.query("UPDATE posts SET title = " + mysql.escape("Hello MySQL");

In regards to testing if a module you are utilizing is secure or not there are several routes you can take. I will touch on the pros/cons of each so you can make a more informed decision.

Currently, there aren't any vulnerabilities for the module you are utilizing, however, this can often lead to a false sense of security as there very well could be a vulnerability currently exploiting the module/software package you are using and you wouldn't be alerted to a problem until the vendor applies a fix/patch.

1. To keep abreast of vulnerabilities you will need to follow mailing lists, forums, IRC & other hacking related discussions. PRO: You can often times you will become aware of potential problems within a library before a vendor has been alerted or has issued a fix/patch to remedy the potential avenue of attack on their software. CON: This can be very time consuming and resource intensive. If you do go this route a bot using RSS feeds, log parsing (IRC chat logs) and or a web scraper using key phrases (in this case node-mysql-native) and notifications can help reduce time spent trolling these resources.

2. Create a fuzzer, use a fuzzer or other vulnerability framework such as metasploit, sqlMap etc. to help test for problems that the vendor may not have looked for. PRO: This can prove to be a sure fire method of ensuring to an acceptable level whether or not the module/software you are implementing is safe for public access. CON: This also becomes time consuming and costly. The other problem will stem from false positives as well as uneducated review of the results where a problem resides but is not noticed.

Really security, and application security in general can be very time consuming and resource intensive. One thing managers will always use is a formula to determine the cost effectiveness (manpower, resources, time, pay etc) of performing the above two options.

Anyways, I realize this is not a 'yes' or 'no' answer that may have been hoping for but I don't think anyone can give that to you until they perform an analysis of the software in question.

Mysql-native has been outdated so it became MySQL2 that is a new module created with the help of the original MySQL module's team. This module has more features and I think it has what you want as it has prepared statements(by using.execute()) like in PHP for more security.

It's also very active(the last change was from 2-1 days) I didn't try it before but I think it's what you want and more.

Preventing SQL injections

SQL injections is a common web hacking technique to destroy or misuse your database. To prevent SQL injections, you should use escape the values when query values are variables provided by the user.

Escape query values by using the mysql.escape() method:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result);
});

Escape query values by using the placeholder ? method:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result) {
  if (err) throw err;
  console.log(result);
});

More Detail

The easiest way is to handle all of your database interactions in its own module that you export to your routes. If your route has no context of the database then SQL can't touch it anyway.