Proper insertion of table name

Question

How does one correctly provide the table name if the name can be dynamically determined and still prevent SQL injection attacks? I am using node-postgres.

For example:

The following works but I believe is insecure:

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

What I would like equivalently (but does not work) is:

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

Answer 1

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

• schema name
• table name
• column name

For example, within pg-promise you would use it like this:

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

i.e. you get your table name properly escaped by appending the 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

Answer 2

You might manually check the validity if table name with a regex some other validation logic. I would probably use a dictionary containing permissible 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 on generating a lot of dynamic sql, use a query builder like http://knexjs.org/

Answer 3

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 control the actual table names in the DB.

Also, do not forget to clean all input - the values may contain injections.

Answer 4

You can use escape function from pg-escape npm module to quote identifiers such as table names:

Only using escape function:

escape("INSERT INTO %I VALUES (%L, %L, %L)", table_name, value_a, value_b, value_c);

Combine it with node postgres:

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