Geek Answers Handbook

(node-postgres, pg) Insert table name correctly

How to correctly specify a table name if the name can be dynamically determined and still prevents SQL injection attacks?

For instance:

The following works, but I find it unsafe:

dbclient.query("INSERT INTO " + table_name + " VALUES ($1, $2, $3)", [value_a, value_b, value_c])

What I would like equivalently (but not working):

dbclient.query("INSERT INTO $1 VALUES ($2, $3, $4)", [table_name, value_a, value_b, value_c])

Edit: I am using node-postgres : https://github.com/brianc/node-postgres .

+5
source share
3 answers

Any good library should provide proper escaping of SQL names, which include:

  • schema name
  • table name
  • column name

For example, in pg-promise you should use it like this:

 db.query("INSERT INTO $1~ VALUES ($2, $3, $4)", [table_name, value_a, value_b, value_c])

i.e. you will get the proper escaping of your table name by adding a variable with ~ , which in turn makes it safe from SQL injection.

From here , a simple escaping for table names executed by the library:

 return '"' + name.replace(/"/g, '""') + '"';

See also: SQL Names

+1
source

You can manually check if the regular expression table name has different validation logic. I would probably use a dictionary containing valid table names.

 var tables = {users:'users', boats:'boats'}; table_name = tables[table_name]; if (! table_name) throw new Error(); dbclient.query("INSERT INTO " + table_name + " VALUES ($1, $2, $3)", [value_a, value_b, value_c])

If you plan to generate a lot of dynamic sql, use a query builder like http://knexjs.org/

0
source

How about having a hash let tables = {tableName1: 'table_name1', tableName2: 'table_name2'...} and then

 //assuming you receive t as table name input if(tables[t]) //build SQL query with tables[t] as the table name else //throw error about non-existing table

This way you manage the actual table names in the database.

Also, be sure to clear all input - the values ​​may contain injections.

0
source

More articles:


All Articles