PG Parameterized Database Update Command

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.

Leave a Reply Cancel reply