(node-postgres, pg) Proper insertion of table name

问题:

How does one correctly provide the table name if the name can be dynamically determined and still prevent SQL injection attacks?

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

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

回答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

回答2:

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.

回答3:

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/