CODEWITHSUNDEEP
javascriptsqlnode.jspostgresqlsql-injection
Brian H.
Brian H.

Reputation: 2235

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

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

Upvotes: 1

Views: 1806

Answers (3)

vitaly-t
vitaly-t

Reputation: 25920

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.

Upvotes: 1

Manjeet Singh
Manjeet Singh

Reputation: 2398

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')

Upvotes: 0

LJ01
LJ01

Reputation: 589

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.

Upvotes: 0

Related Questions