How do I write an SQL query that will safely insert a record into a variable table name?

Question

I have a function that inserts a record into a table. The name of the table needs to be a parameter of the function, and the column names are obtained dynamically. To guard against SQL Injection, I would like to use PostgreSQL's parameterized queries. Something like this:

function insert(tableName, val1, val2) {
    let qry =   `INSERT INTO $1 ($2, $3)
                VALUES ($4, $5)
                RETURNING id;`

    let values = [tableName, 'col1', 'col2', val1, val2]

    return db.query(qry, values);
}

While the $n substitution works great for values, it cannot be used for table or column identifiers.

From the PostgreSQL documention

Arguments to the SQL function are referenced in the function body using the syntax $n: $1 refers to the first argument, $2 to the second, and so on. If an argument is of a composite type, then the dot notation, e.g., $1.name, can be used to access attributes of the argument. The arguments can only be used as data values, not as identifiers

---

Compare this to the following code which works but seems to offer little protection against SQL injection.

(note the use of ECMA6 ${} string substitution in place of parameter substitution)

function insert(tableName, val1, val2) {

    let values = ['col1', 'col2', val1, val2]
    let qry =   `INSERT INTO ${tableName} (${values[0]}, ${values[1]})
                VALUES ($3, $4)
                RETURNING id;`


    return db.query(qry, values);
}

Is there a way to allow parameterized queries that mitigates this? I'm hoping for something built into PostgreSQL or the Postgres library for Node, but I will accept any solid answer.

I'm running Node 9.4 and PostgreSQL 10

Answer 1

If you have the following parameters:

• table - table name
• columns - array of column names or an object with properties
• values - array of corresponding column values

then the simplest approach within pg-promise syntax is as follows:

function insert(table, columns, values) {
    const query = 'INSERT INTO ${table:name} (${columns:name}) VALUES(${values:csv})'; 
    return db.query(query, {table, columns, values});
}

or a shorter syntax:

function insert(table, columns, values) {
    const query = 'INSERT INTO ${table~} (${columns~}) VALUES(${values:csv})'; 
    return db.query(query, {table, columns, values});
}

See SQL Names, CSV Filter.

And from version 7.5.0 it gets even simpler for dynamic objects:

function insert(table, obj) {
    const query = 'INSERT INTO ${table:name} (${obj:name}) VALUES(${obj:csv})'; 
    return db.query(query, {table, obj});
}

Under SQL Names, the first example shows how a column name can be inserted dynamically. Is this insertion something that is done by your library, or does the replacement happen on the Postgres side?

PostgreSQL server does not allow dynamic SQL names, pg-promise implements it internally, providing safe escaping to protect against SQL injection.