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