August 2021

After my last post I realized that not everyone knows what SQL injection is. In short, it involves providing SQL commands via user-input fields which results in the database running those commands as part of what is supposed to just be an insert or update. This injection of SQL into fields that aren’t expecting SQL can cause serious problems in your database (like dropping entire tables).

Here’s a brief overview: https://www.w3schools.com/sql/sql_injection.asp

Postgres (and the PG npm package) will allow you to prevent SQL injection through a process called parameterization. (https://www.postgresql.org/docs/9.2/sql-prepare.html) in short, via a parameterized query, you are telling your database which pieces of your query are meant to be plain text (which won’t run SQL commands regardless of what the user might try to inject), and which pieces are meant to include actual database-changing commands.

In my last post, I talked about using the update command and offered up this simple example:

UPDATE users
SET instances = instances - 1
WHERE id = 102
RETURNING *

Switching this to Typescript code that would work with PG (and having it update the first_name rather than the instances) would look like this:

import { Pool } from 'pg';

const pool = new Pool({
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

const updateFirstName = async (
  firstName: string,
  id: number,
): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    const query = `
      UPDATE users
      SET first_name = '${firstName}'
      WHERE id = ${id}
      RETURNING *
    `;

    await client.query(query); // use the connection
  } catch (error) {
    // Deal with your catch error
  } finally {
    client.release(); // release the connection
  }
};

The problem with this, obviously, is that there isn’t any parameterization, which means that it’s vulnerable to SQL injection attacks.

I looked around trying to find an example of parameterization with the PG npm package as it related to an UPDATE query/command. I found plenty of examples for the INSERT INTO query/command, but nothing with the UPDATE.

So, I did some experimenting and determined that this works:

import { Pool } from 'pg';

const pool = new Pool({ // Customize all of this...
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

const updateFirstName = async (
  firstName: string,
  lastName: string,
  id: number,
): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    if(typeof id !== 'number) {
      throw new Error('non-number id passed into updateFirstName');
    }
    const query = `
      UPDATE users
      SET first_name = $1, last_name = $2
      WHERE id = ${id}
      RETURNING *
    `;

    const values = [firstName, lastName];

    await client.query(query, values); // use the connection
  } catch (error) {
    // Deal with your catch error
  } finally {
    client.release(); // release the connection
  }
}

You’ll obviously have to customize that some to match your use case, but that should give you the correct approach.

Month: August 2021

SQL Injection & Parameterization

PG Parameterized Database Update Command