James Q Quick | March 3, 2022

Although the tooling around databases has come a long way, it is still your responsibility to protect them against attacks. In this article, you’ll learn to prevent SQL injection attacks in Node.js using the mysql2 npm package.

What is a SQL injection attack

A SQL injection attack happens when a user injects malicious bits of SQL into your database queries. Most commonly, this happens when allowing a user to pass input to a database query without validation which can alter the original intended query. By injecting their own SQL, the user can cause harm by:

• reading sensitive data
• modifying sensitive data
• deleting sensitive data

As you can probably imagine, these types of attacks can have negative impacts on your applications and your business. In fact, you’ve probably heard of some major companies being involved in data breaches in the past couple of years. This can lead to loss of customers, revenue, application uptime, and more.

Examples of SQL injection attacks

You now have a general understanding of what SQL injection attacks are, but I think it would be good to see a few specific examples.

Let’s explore a developer related-scenario where, hypothetically, you build an application that stores code repositories. Just like GitHub, these user-created repositories can be either public or private. Furthermore, a user has the ability to search public repositories by tag. For simplicity, let’s assume that each repository only has one tag.

In your application logic, you would use the user’s search term to generate the SQL query. In your Node.js code, you might be tempted to use ES6 template literal strings to interpolate that value directly to your query string like so:

const query = `SELECT * FROM Repository WHERE TAG = '${userQuery}' AND public = 1`

For a search of "javascript", your SQL query string might look like this:

SELECT * FROM Repository WHERE TAG = 'javascript' AND public = 1;

In this case, you are attempting to select all public repositories that have “javascript” as their tag. Assuming reasonable user input, this works fine, but what if the user were to search for something like this javascript';--. Now, things start to become dangerous.

The -- is the SQL code for a comment. This means that it would then shortcut the rest of the query. So, the unvalidated query would look like this.

SELECT * FROM Repository WHERE TAG = 'javascript';--' AND public = 1;

Since the part after the "--" would be ignored, the query that gets executed looks more like this.

SELECT * FROM Repository WHERE TAG = 'javascript';

As you can see, this removed the additional clause in the query which previously prevented private repositories from being included. You can imagine this being a significant problem for intellectual property.

One other type of SQL injection attack to be aware of is one that can add a secondary statement to the query. Let’s stay with the same example, but say the user searches for javascript'; DROP TABLE Repository;--. Then, the query would become:

SELECT * FROM Repository WHERE TAG = 'javascript'; DROP TABLE Repository;--' AND public = 1;

In this example, the original query is terminated with the ; but then followed by a second query that would drop the entire Repository table. NO GOOD!

For notes on a few other examples of SQL injection attacks, check the W3Schools SQL Injection page.

Configuring the mysql2 client in Node.js

For a quick reference, let’s take a look at how to set up the mysql2 client in Node.js. You’ll first want to install the package:

For an in-depth tutorial on creating an API with Node.js, mysql2, and PlanetScale, check out Create a Harry Potter API with Node.js, Express, and MySQL

Once you have this package installed, you can initialize the client.

import mysql from 'mysql2/promise'
const connection = await mysql.createConnection(process.env.DATABASE_URL)

This sample code uses environment variables for the database connection string. You’ll need to also install the dotenv package for testing this locally. It also uses the promises-based version of the library so that you can use modern async/await syntax.

From there, you can make queries like so:

    const query = 'SELECT * FROM Repository WHERE TAG = 'javascript' AND public = 1';
    const [rows] = await connection.query(query);

If you’re working with Express.js, you could then define an endpoint that accepts user input as userQuery, queries the database, and returns the repositories in JSON format.

import express from 'express';
import mysql from 'mysql2/promise';

const connection = await mysql.createConnection(process.env.DATABASE_URL);
 const app = express();

app.get('/repositories/:userQuery', async (req, res) => {
    const {userQuery} = req.params;
    const query = 'SELECT * FROM Repository WHERE TAG = '${userQuery}' AND public = 1';
    const [rows] = await connection.query(query);
   res.json(rows);
});

app.listen(3001, () =>{
  console.log('App is running');
});

Preventing SQL injection attacks

There are a few common ways to prevent SQL injection attacks:

1. Don’t allow multiple statements
2. Use placeholders instead of variable interpolation
3. Validate user input
4. Allowlist user input

Don’t allow multiple statements if you can avoid it

Conveniently, number 1 is handled by the mysql2 client (and many other database clients). It prevents multiple statements from being executed by default. So, even if the user submits an input that attempts to terminate a query and run a second one, the second one won’t run. This is the default configuration, but you can override that if you choose.

const connection = await mysql.createConnection({
  uri: process.env.DATABASE_URL,
  multipleStatements: true
})

To emphasize the need for more levels of protection, refer to the example above where injecting a comment (ex. javascript';--) into the SQL allowed the user to read from private repositories. Since that was done using only one statement, setting multipleStatements: false still wouldn’t be enough.

Use placeholders

Therefore, you should never accept raw input from a user and input it directly into your query string. Instead, you should use placeholders (?) (or parametrized queries) which would look like this (notice the ? as the placeholder):

const query = 'SELECT * FROM Repository WHERE TAG = ? AND public = 1'
const [rows] = await connection.query(query, [userQuery])

By using placeholders, the malicious SQL will be escaped and treated as a raw string, not as actual SQL code. The end result query would look like this:

  SELECT * FROM Repository WHERE TAG = `javascript';--` AND public = 1;

Thanks to using placeholders, the malicious SQL is not run and instead, is treated as a search query as intended.

Input validation

In addition to using placeholders, you can add logic in your applications to prevent invalid user input. Let’s stick with the example of querying public repositories by tag. For demo purposes, you can assume that you should not have a tag that includes special characters or numbers. In other words, tags should only use capital and lowercase letters (A-Z, a-z).

This means you can add logic to your application to validate that user input matches the correct formatting (no numbers and no special characters). To do this, you can create a regex pattern to match the user input. If it doesn’t match, return an error.

 app.get('/repositories/:userQuery', async (req, res) => {

    const {userQuery} = req.params;
    const onlyLettersPattern = /^[A-Za-z]+$/;

    if(!userQuery.match(onlyLettersPattern)){
      return res.status(400).json({ err: "No special characters and no numbers, please!"})
    }

    ...
  });

Now the code doesn’t even get to the SQL part unless a valid input is passed. You can apply this method with any sort of validation that is relevant to your data. For example, if you allow the user to query by an id property which should be a number, you can throw an error if the input isn’t a valid number.

  app.get('/repositories/:id', async (req, res) => {
    const {id} = req.params;

    if(isNaN(Number(id))) {
      return res.status(400).json({ err: "Numbers only, please!"})
    }
...

Allowlisting

One last option you have is to use allowlisting, a specific type of input validation. Allowlisting is useful if you know every possible valid user input. From there, you can easily reject anything else.

For example, let’s say for your repository tags, there are only three valid tags: “javascript”, “html”, and “css”. If that’s the case, then you can check whether or not the user input is "allowlisted" by comparing it against known valid inputs.

 app.get('/repositories/:userQuery', async (req, res) => {

    const {userQuery} = req.params;
    const validTags = ["javascript", "html", "css"];

    if(!validTags.includes(userQuery)){
      return res.status(400).json({err: "Valid tags only, please!"});
    }

    ...
  });

Yes, this example is a bit simplified with just three valid tags, but this works at scale as well. A more realistic scenario might be that you store all known tags in their own table in your database. Then, to validate the user input, you can check against all the tag records in your database.

Wrap up

Hopefully, this helped give you a good overview of what SQL injection attacks are and how to prevent them. They can be detrimental to your application and business, so it’s important to plan ahead when accepting user input for your database queries to prevent any negative side effects.