Protect an API from SQL Injection attacks in Node.js with PostgreSQL

Federico Iarlori — Fri, 09 Dec 2022 15:28:04 +0000

Introduction
Initial Setup
- PostgreSQL
- Node.js
SQL Injection attack
Preventing SQL Injection attack
Conclusion
References

Introduction

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, modify or delete the data in the database. It's one of the most common attacks in web applications.

In this post I will show you how to prevent this type of attacks when using Node.js and a SQL-like database to implement the API for a web application. The SQL database chosen for demonstrating the example is PostgreSQL.

Initial Setup

First of all, I will show how to do the initial setup in order to have a working API without entering into great detail. We will need to have PosgreSQL and Node.js installed.

Note: since my operating system is in Spanish, some of the screenshots used in this post will be in Spanish too.

PostgreSQL

PostgreSQL can be downloaded following the steps in this link, chosing the operative system of your preference, in my case I'll be using Windows. When installing PostgreSQL, simply follow the steps provided in the installation guide. In my case, I chose the password "admin" and the default port "5432". Remember this information because it will be necessary when developing the API.

After installation, in case of using Windows make sure that the path of the "bin" folder of PostgreSQL is placed in the enviroment variables.

Once installed, open a command prompt, and execute the command psql -U postgres in order to log into PostgreSQL with the default user "postgres". Then you will be prompted with a password. Use the password chosen in the installation process, in my case it is "admin". If everything is OK, you should be seeing the following:

Once logged in, we need to create a database for the API to use. This can be done by using the command CREATE DATABASE api_example. Then we connect to this database with \c api_example.

With the command \conninfo we can see all the relevant information we will need when connecting the Node.js app with the recently created database.

Node.js

Node.js can be downloaded in the following link. Once installed, we create a new folder and a new project by opening a command prompt inside the folder and executing the command npm init. Then we install the following libraries that we will need when developing the API with the command npm install:

npm install express to ease us the process for developing the endpoints of the API
npm install nodemon so we don't need to restart the app every time we make changes in the code
npm install dotenv so we can use enviroment variables
npm install pg so we can connect to the PostgreSQL database
npm install pg-escape to prevent SQL Injection attacks.

After installing the libraries, we create a file called "index.js" with the following initial code:

const express = require('express');
require('dotenv').config()
const app = express();

app.get('/', (request, response) => {
    response.send("Welcome to the API!");
});

const PORT = process.env.PORT || 5000
app.listen(PORT, () => console.log('Listening on port '+PORT+'...'));

Then we open the "package.json" file, and in the "scripts" field we add the following field: "start": "nodemon src/index.js". After adding it, the file should look like this:

{
  "name": "api_sql-injection",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "nodemon src/index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.0.3",
    "express": "^4.18.2",
    "nodemon": "^2.0.20",
    "pg": "^8.8.0",
    "pg-escape": "^0.2.0"
  }
}

Then, we must set out .env file, initially only specifying the port number. For this purpose, we create a new file named ".env", and we add the following variable:

PORT=3000

Then, we can test our API with npm start. If everything went OK, we should be seeing the following text in the browser when accessing localhost in the port 3000, i.e http://127.0.0.1:3000/

Now that our API is working, let's add some more endpoints to give a little bit more of functionality, so then we can perform a SQL Injection attack.

First, we must connect to the api_database database in Node.js. For this purpose, we'll create the file "dbConnection.js" with the following content:

const dotenv = require('dotenv');
dotenv.config();

const { Pool } = require('pg');

let pool = new Pool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_DATABASE,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    ssl: false
});

module.exports = pool;

As we can see, we need to specify some enviroment variables in the .env file.

PORT=3000

DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=api_example
DB_USER=postgres
DB_PASSWORD=admin

Remember that you can access all this information using the \conninfo command in PostgreSQL. The password must be the same as the one that was specified in the installation of PostgreSQL.

Once configured the database, we will import it in "index.js"

const pool = require("./dbConnection.js");

Now we can make some more interesting endpoints. First, an endpoint to initialize the database with a table named "users" and some user examples.

app.get('/initialize_database', async (request, response) => {
    try{
        const query = `CREATE TABLE IF NOT EXISTS users (
                username varchar(45) NOT NULL,
                email varchar(450) NOT NULL,
                PRIMARY KEY (username) 
            )`;
        await pool.query(query);
        await pool.query("DELETE FROM users WHERE username = 'john' ");
        await pool.query("INSERT INTO users(username, email) VALUES ('john', 'john@mail.com')");
        await pool.query("DELETE FROM users WHERE username = 'jack' ");
        await pool.query("INSERT INTO users(username, email) VALUES ('jack', 'jack@mail.com')");
        await pool.query("DELETE FROM users WHERE username = 'peter' ");
        await pool.query("INSERT INTO users(username, email) VALUES ('peter', 'peter@mail.com')");
        response.status(200).json({"message": "table created succesfully."});
    } catch(error){
        response.status(500).json({"message": error.message});
    }
});

The "DELETE" commands are in case that we are initializing an already initialized database. After accessing this endpoint in the URL http://127.0.0.1:3000/initialize_database, we will get the following message

And if we go to PostgreSQL and execute the query SELECT * from users; we will see the following table

In addition to this endpoint, we will make another one for recovering one particular user from the database.

app.get('/users/:username', async (request, response) => {
    let username = request.params.username;
    try{
        const query = `SELECT * FROM users WHERE username='${username}'`;
        console.log(query);
        const result = await pool.query(query);
        response.status(200).json(result.rows);
    } catch(error){
        response.status(500).json({"message": error.message});
    }
});

In the next section we'll see why this last endpoint is insecure and vulnerable to SQL Injection attacks.

SQL Injection Attack

As we mentioned before, in an SQL Injection Attack the attacker enters malicious SQL code through the input fields. In the endpoint implemented before, the input comes in the request variable, when accessing the field username, and then this input it's used in the query.

When using the endpoint normally (i.e, without malicious intentions), we would use, for example, the following URL if we are looking for a user named "john".

and the SQL query that we are performing in Node.js would look like this

SELECT * FROM users WHERE username='john'

However, instead of "john", we could use another input with certain format, to perform other operations than just searching for a user. Consider the following URL:

http://127.0.0.1:3000/users/john'; DROP TABLE users; --

What we are doing here is first searching for john, we close the name we are trying to match with a simple quote ('), then close the query with the ";" operator, and then perform another query. This second query is essentially deleting the whole table! After this, if you remember the original query, we have a simple quote left that isn't closed. In order for PostgreSQL to not throw an error for this single simple quote being left, we use the "--" so that everything that comes after the last ";" is considered a comment and gets ignored. So, the final query would look like this:

SELECT * FROM users WHERE username='john'; DROP TABLE users; --';

As we said, when the second query gets executed, the users table will be erased. Lets see what happens when we try to see the table in postgreSQL

We lost our table! In a real life application, a vulnerability like this could cause the loss of a lot of valuable information, which translates in the loss of tons of money.

The problem here is that the input data was not sanitized at all. In the following section, we will see how we can protect our database.

Preventing SQL Injection Attack

There are many ways to prevent a SQL Injection Attack. In this post, we will see how to do this using the pg-escape library. First of all, let's recover our table accessing the URL http://127.0.0.1:3000/initialize_database.

Now, consider the following end-point

app.get('/protected_users/:username', async (request, response) => {
    let username = request.params.username;
    try{
        const query = `SELECT * FROM users WHERE username=${escape.literal(username)}`;
        console.log(query);
        const result = await pool.query(query);
        response.status(200).json(result.rows);
    } catch(error){
        response.status(500).json({"message": error.message});
    }
});

As you can see, it's very similar to the previous end-point, but with one fundamental difference. When we are using the content of username in the query, we are escaping the input (a sequence of characters) with escape.literal(username). To escape a sequence of characters means that we are translating that sequence into another sequence.

So what this function from pg-escape is doing is transforming a malicious input into one that can't cause any harm, for example not allowing the execution of two queries in one single http request. Let's consider the previous malicious URL but with our protected route:

http://127.0.0.1:3000/protected_users/john'; DROP TABLE users; --

When accessing that URL, we will get an empty JSON.

And when we check our table in PostgreSQL with SELECT * FROM users, we can see that it is still there!

If we print the query that we just made in the end-point with console.log(), we can see that the following command was executed

SELECT * FROM users WHERE username='john''; DROP TABLE users; --'

If you pay attention, you can see that there is an extra single quote in the query after "john". In SQL, this means that we want to include the character " ' " (single quote) in the username we want to match. So instead of executing two SQL commands like before, what we are doing now is executing only one query that tries to search for a user with the name john'; DROP TABLE users; --. Since there is no user with that name in the table users, the end-point returns and empty JSON.

In this way, we could easily sanitize the input using pg-escape by simply applying the function literal to it.

Conclusion

SQL Injection is one of the many attacks that a malicious user can perform to a web application. What is dangerous about this type of attack is that it is easily exploitable and can cause a lot of damage to the system.

In this post we saw an easy way to protect against this types of attacks when using a SQL like database in Node.js.

References

Source code in Github

DEV Community: Federico Iarlori