How to make PostgreSQL queries secure on Heroku

Viewed 43

I'm wondering if the following Postgres queries are secure. I seem to have some gaps in a user id table, but I'm not sure what the cause is, so I'd like to make sure these queries are safe from injection or any kind of deletion in my tables. I haven't seen anything in my logs but you never know. I've deployed this on Heroku:

var format = require('pg-format');
const { Pool, Client } = require('pg');
var helmet = require('helmet');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

let cmd1 = 'SELECT * FROM table';
let cmd2 = `INSERT INTO table (col1, col2, col3, col4, col5) VALUES($1, $2, $3, $4, $5)`
let cmd3 = 'DELETE FROM table WHERE col1 < ($1) RETURNING *'
let cmd4 = 'DELETE FROM table WHERE col2 = ($1) AND col3 = ($2) RETURNING *'
let cmd5 = 'SELECT * FROM table WHERE col1 > ($1)'
let cmd6 = 'UPDATE table SET col1 = ($1) WHERE col1 < ($2) AND col2 = ($3) AND col3 = ($4)'
let cmd7 = 'SELECT * FROM table WHERE col1 = ($1)'
let cmd8 = 'INSERT INTO table (col1) VALUES($1)'
let cmd9 = 'UPDATE table SET col1 = col1 + 1 WHERE col2 = ($1)'
let cmd10 = 'UPDATE table SET col1 = array_remove(col1, ($1)) WHERE col2 = ($2) AND col3 = ($3)'
let cmd11 = 'UPDATE table SET col1 = array_cat(col1, ($1)) WHERE col2 = ($2) AND col3 = ($3)'
let cmd12 = 'UPDATE table SET col1 = col1 + ($1) WHERE col2 = ($2) AND col3 = ($3) RETURNING *'
let cmd13 = 'UPDATE table SET col1 = col1 - ($1) WHERE col2 = ($2) AND col3 = ($3) RETURNING *'
let cmd14 = `INSERT INTO table (col1, col2, col3) VALUES($1, $2, $3) ON CONFLICT (col1, col2) DO UPDATE SET col3 = EXCLUDED.col3`
let cmd15 = 'DELETE FROM table WHERE col1 = ($1) AND col2 = ($2)'
let cmd16 = 'DELETE FROM table WHERE col2 = ($1)'
let cmd17 = 'SELECT * FROM table WHERE col1 = ($1) AND col2 = ($2)'
let cmd18 = 'INSERT INTO table (col1) VALUES($1) ON CONFLICT DO NOTHING'
let cmd19 = 'SELECT * FROM table WHERE col1 = ($1)'

A lot of my scheme for calls looks like:

const client = await pool.connect();
try {
    await client.query('BEGIN')
    await client.query(sql_statement_from_above_goes_here, [value])
    await client.query('COMMIT')
} catch (err) {
    await client.query('ROLLBACK')
} finally {
    client.release()
}

The other way I make queries is like this (using pg-format npm module):

const client = await pool.connect();
try {
    await client.query('BEGIN')
    let modifiedSQLStatement = format('UPDATE table SET %I = %I + %s WHERE col1 = %s AND %I <= 100 AND %I >= -100', col2, col2, value, col1, col2, col2)
    await client.query(modifiedSQLStatement)
    await client.query('COMMIT')
} catch (err) {
    await client.query('ROLLBACK')
    console.log(err)
} finally {
    client.release()
}

Are all of these queries safe from injection and other security threats?

0 Answers
Related