Preventing SQL injection in Node.js (and other vulnerabilities)

The database is an essential part of a web application. It’s where you receive and store users’ data, which you can then use to provide personalized services. As such, database security is an important part of every web application to ensure the safety and integrity of data collected from users. In this post, we’ll be looking at SQL database vulnerabilities in Node.js, like SQL injection, and how to prevent them. 

SQL injection in Node.js

SQL injection is a code injection technique where an attacker targets SQL-like databases by entering malicious SQL code into input fields in the web app to gain access to or alter the data in the database. It’s a very common attack, but there are a few quick fixes that you can use to prevent it. With that said, let’s look at an example with a MySQL database to understand how SQL injection works. 

MySQL example

Imagine a hospital record system where staff enters the name and date of birth of a patient to retrieve their medical records. In Node.js, you can fetch the records like so: 

connection.query("SELECT * FROM health_records WHERE dob = '"+ req.body.dob +"' AND name = '"+ req.body.name +"'", (error, results) => {
  ...
});

The problem here is that the parameters are not filtered before being passed to the query. As a result, an attacker can exploit this weakness to gain unauthorized access to the data or even destroy it. Let’s look at each of these problems and how to resolve them. 

Data exfiltration

An attacker is able to gain unauthorized access to data through data exfiltration. Using the example from the previous section, let’s say staff entered the following in the date of birth and name fields: 

dob = 20/06/1971';--
name = John Doe

As a result of the way MySQL works, this will be interpreted as 

SELECT * FROM health_records WHERE dob = '20/06/1971'; -- ' AND name = 'John Doe'

The second part of the query gets ignored because MySQL sees the semicolon and assumes that’s the end of the query due to the -- sign, which denotes a comment. As a result, MySQL returns the health records of every patient with the date of birth specified. 

Data destruction

An attacker can also use SQL injection to alter or destroy data. Let’s say the following was entered in the date of birth field: 

20/06/1971'; DROP TABLE health_records; --

MySQL interprets this as 

SELECT * FROM health_records WHERE dob ='20/06/1971'; DROPTABLE health_records;-- ' AND name = 'John Doe'

MySQL interprets this as two queries, and the second one essentially deletes all patient health records from the database. 

How to prevent SQL injection in Node.js

In the previous section, we saw how an attacker can use SQL injection to alter and even destroy the data in a SQL database. There are several code-level tweaks you can make to prevent SQL injection vulnerabilities. The idea is to make sure the data being passed to the query is escaped before being executed. Let’s look at a couple of ways you can do this. 

Use placeholders

We can use the array syntax to pass values to the query. You should also note the order in which they are passed so the values are mapped correctly. Here’s an example: 

connection.query("SELECT * FROM health_records WHERE dob = ? AND name = ?",[
  req.body.dob,
  req.body.name
], (error, results) => {
  ...
});

The first parameter maps to the first question mark, and the second parameter maps to the second question mark. This minimal change ensures that the input values are escaped and, as such, prevents a SQL injection attack here. 

Use named placeholders

Similar to the example above, named placeholders use an object to map the parameters to the query variables: 

connection.query("SELECT * FROM health_records WHERE dob = :dob AND name = :name",{
  dob: req.body.dob,
  name: req.body.name
}, (error, results) => {
  ...
});

In the code snippet above, :dob maps to req.body.dob, and :name maps to req.body.name. 

Use the node-mysql module

The node-mysql module has a built-in method to escape query values that can be used like so: 

connection.query('SELECT * FROM health_records WHERE dob = ' + connection.escape(req.body.dob), (error, results) => {
  if (error) throw error;
  // ...
});

It has a number of other helpful methods that you can use to sanitize the values, which prevents SQL injection. 

Avoid insecure packages

A simple command that gives you access to a variety of third-party packages in the npm registry is npm install xyz-package. These packages bring additional functionality to your Node.js apps. However, you must take some precautions before adding a third-party tool to your application. This is because if there is an issue with the package, then your application becomes vulnerable as well.

An attacker may also use an insecure package to infiltrate the database to modify or steal data. They could even use it to store unrelated data or mine data from the database! As seen in the previous section, this could result in data exfiltration or data loss. Hence, you have to make sure your database is secure and resistant to these attacks to guarantee user trust. 

A small mistake such as a misspelling can result in the wrong package being added to your app. So, make sure to always double-check packages before adding them. Additionally, always keep your third-party packages up to date to stay current with the latest security patches.

Use application security monitoring and protection tools

Regardless of how strong your secure coding practices are, as your application scales up, vulnerabilities will wind up in production. It’s hard to keep track of the changes or additions of every new code or package. Many people are adopting DevOps and including it in modern pipelines for building web apps tospeed up the process of tracking down bugs and issues. 

With Sqreen’s Next-Gen RASP, you can monitor and protect your apps in production to block any attacks as they happen. Sqreen works by adding a small library to your Node.js app that monitors application behavior in real-time as users interact with your app. It immediately flags and/or blocks any exploits (including SQL injections), and promptly notifies the parties involved. This approach is a smart and efficient way of handling SQL injections and other security issues that may occur while the application is in use. 

Create secure coding practices and practice threat modeling

As you develop your Node.js app, it’s helpful to create a threat model ahead of time to help guide the development process. You can further break down the threat model into test cases that are integrated into the CI/CD process. This ensures that when test cases are not passed, any issues are flagged before they ever make it to production. 

You should also properly review third-party packages before adding them to the application. If you don’t do this, it leaves the application exposed to malicious code that an attacker may have hidden in one of the packages. The malicious code could cause a number of problems, such as consuming a lot of memory or even stealing sensitive information from the database. 

Finally, automating the process of detecting and defending against SQL injections and vulnerabilities is a more efficient way of ensuring the security of your Node.js apps. Tools like Sqreen were built for just this purpose. It integrates well with CI/CD tools, which you can use to add security checks during development as well. Once set up, Sqreen detects and stops breaches in production while users are interacting with the application. 

This post was written by Kelvin Gobo. Kelvin is a Frontend Developer with a demonstrated knowledge in building web applications. He is skilled in crafting beautiful user interfaces, web tooling and performance optimization, and progressive web apps.