WittCode💻

Is Your Node App SQL Injection Free?

By

Learn how to protect a Node app against SQL injections. We will perform a demonstration using Postgres and the npm library pg.

Table of Contents 📖

Dropping Tables with SQL Injection

The code below is vulnerable to SQL injection. When using string interpolation, the pg library does not escape the query before sending it to the database server. Do NOT write your SQL queries this way. In the example below, the malicious query drops an entire table in the database.

import pg from 'pg';

const pool = new pg.Pool({
  host: process.env.POSTGRES_CONTAINER_NAME,
  port: process.env.POSTGRES_PORT,
  user: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DB
});

const QUERY = `' OR true; DROP TABLE app_user; --`;

async function main() {
  try {
    const response = await pool.query(`SELECT * FROM app_user where name = '${QUERY}'`);
    const {rows} = response;
    console.log('The rows are:', rows);
  } catch (err) {
    console.log(err);
  } finally {
    await pool.end();
  }
}

main()
  .then(() => console.log('Finished query!'))
  .catch(err => console.error('Error connecting to Postgres!', err));

The rows are: undefined
Finished query!

We can double check that the app_user table was dropped by running the following query. Postgres will not be able to find the table.

const response = await pool.query(`SELECT * FROM app_user`);

2024-09-18 15:29:48.321 UTC [519] ERROR:  relation "app_user" does not exist at character 15
2024-09-18 15:29:48.321 UTC [519] STATEMENT:  SELECT * FROM app_user
error: relation "app_user" does not exist

Protecting Against SQL Injection

The pg library has built in SQL injection protection, we just have to use it. The code below is known as parameterized queries.

INFO: With parameterized queries, the query text and values are sent separately as opposed to being interpolated as a single query.

import pg from 'pg';

const pool = new pg.Pool({
  host: process.env.POSTGRES_CONTAINER_NAME,
  port: process.env.POSTGRES_PORT,
  user: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DB
});

const QUERY = `' OR true; -- DROP TABLE app_user; --`;

async function main() {
  try {
    const response = await pool.query('SELECT * FROM app_user where name = $1', [QUERY]);
    const {rows} = response;
    console.log('The rows are:', rows);
  } catch (err) {
    console.log(err);
  } finally {
    await pool.end();
  }
}

main()
  .then(() => console.log('Finished query!'))
  .catch(err => console.error('Error connecting to Postgres!', err));

The rows are: []
Finished query!

In this instance, we are simply returned an empty array. This is because no user with the name ' OR true; -- DROP TABLE app_user; --' was found. We can double check that the app_user table was not dropped by running the following query.

const response = await pool.query(`SELECT * FROM app_user LIMIT 1`);

The rows are: [
  {
    subscriber_id: 1,
    name: 'WittCepter',
    email: 'the-best-chrome-extension@a.com'
  }
]
Finished query!