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

@AbderrahmaneTAHRIJOUTI I'm using node-postgres (node-postgres.com/features/queries). I haven't come across any alternatives. — Brian H.
– Brian H., Commented Feb 10, 2018 at 10:18
You can use pg-promise, and its SQL Names for dynamically generating schema/table/column names ;) If it is of interest, I can publish it as an answer ;) — vitaly-t
– vitaly-t, Commented Feb 11, 2018 at 0:46
@vitaly-t Thank you. I took a look at the library and I think it will work better than just using pg. But I'm a little confused on how I would use it for the above problem. 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? Either way, if you want to write this up as an answer, I will accept it. Thanks. — Brian H.
– Brian H., Commented Feb 11, 2018 at 20:11

vitaly-t · Accepted Answer · 2018-02-12 01:06:03Z

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.

LJ01 · Accepted Answer · 2018-02-10 09:56:10Z

0

One option is to use the parameter to check the table name from a list of available tables before proceeding.

I'm not sure if there is an easier way to transfer the variables, but if you put the values into a temporary table, and call about procedure that takes the values from temp table, inserts those into dynamically selected table you should be fairly safe.

answered Feb 10, 2018 at 9:56

LJ01

5894 silver badges11 bronze badges

2 Comments

Brian H. Over a year ago

I'm not quite sure if I follow (new to SQL). Is the idea that I somehow generate a brand new table with a query, then if that table is successfully generated, copy the inserted row into the actual table? Would this work for the dynamically generated column names as well?

LJ01 Over a year ago

No, the first procedure inserts the val1 and val2 into a temp table, and checks that the table name is in the list of and tables. The second procedure takes those values from the temp table and inserts then into the variable table then deletes them from the temp table.

Manjeet Singh · Accepted Answer · 2018-02-10 10:39:50Z

0

Well I would like to suggest to Object-relational mapping (ORM) for this case scenario, and this is my personal recommendation and maybe this will help you

here is a library link objection which you can use.

Objection.js is built on an SQL query builder called knex.

so then you can write a clean syntax

here is very simple example

// person is an instance of Person model.

const movie = await person
  .$relatedQuery('movies')
  .insert({name: 'The room', awesomeness: 9001});

console.log('best movie ever was added');

insert into "Movie" ("name") values ('The room')

answered Feb 10, 2018 at 10:39

Manjeet Singh

2,4061 gold badge19 silver badges39 bronze badges

2 Comments

Brian H. Over a year ago

I like the idea of this, as I think it would work very well for what I wanted to do. However, the link you provided is dead. There is no GitHub page for the account referenced.

Manjeet Singh Over a year ago

@BrianHVB here is link https://www.npmjs.com/package/objection

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related