SQL Injection

SQL Injections occur when a SQL query is generated by concatenating strings together and the contents of these strings are improperly escaped.

The following is an example of how not to generate an SQL query:

// WARNING: Dangerous Anti-pattern
const sql = `SELECT * FROM table WHERE name = "${userString}"`

In this case we’ve taken a string provided to us by the user and placed it inside of a string representing a SQL query. With this code sample we’re erroneously assuming the input string will always contain a simple name containing alphabetical characters. The reason this is dangerous is because a user could supply us with the following value:

Bobby T"; DROP TABLE table; --

As you can see a malicious user has broken our assumption! The provided string is obviously not a valid name. After the user-supplied string has been concatenated with our query string, we’re left with the following SQL query string:

SELECT * FROM table WHERE name = "Bobby T"; DROP TABLE table; --"

Once we send this string to the database we’re actually now sending two queries. The first query select a user from the table, and the second query destroys the table (the -- is a comment and anything which follows it will be ignored).

Whether it be an ORM or a simpler database library, the Node.js ecosystem is full of great packages for communicating with databases. Every one of these great database packages offers an idiomatic way for generating queries without the need to manually concatenate SQL strings by hand.

Here are three examples for generating queries taken from the documentation of some popular database modules on npm:

// pg
client.query('SELECT $1::text as message', ['Hello world!'])
// knex
knex('users').where('id', 1).orWhere({votes: 100, user: 'knex'})
// sqlite3
db.run("UPDATE tbl SET name = $name WHERE id = $id", {$id: 2, $name: "bar"})

In each of these situations we’re passing the values directly to the database module without performing any concatenation or sanitization of our own. We then leave it up to the database module to handle the escaping for us. Some of these modules, such as knex, actually support multiple database back-ends. This means the same object/method chaining will result in different query escaping.

Also, keep in mind that even though you’re now generating an intentional SQL query that the data you’re inserting could still have other issues. For example, the data you’re storing could still trigger Cross Site Scripting (XSS) when rendered in a browser.

Mitigation

You should never manually concatenate strings or perform manual escaping of user-supplied input to generate SQL queries. Node.js has many great database and ORM modules and you should be using their idiomatic query generation syntax. For example, consider using pg for making PostgreSQL queries, sqlite3 for making SQLite queries, or even knex for making generic queries which are compatible with most popular SQL databases.

This article was written by me, Thomas Hunter II. I work at a company called Intrinsic where we specialize in writing software for securing Node.js applications. We currently have a product called Intrinsic for Lambda which follows the Least Privilege model for securing applications. Our product proactively protects Node.js applications from attackers, and is surprisingly easy to implement. If you are looking for a way to secure your Node.js applications, give me a shout at tom@intrinsic.com.