pqxx - prevent sql injection on table name

Question

Is there any possible method to securely execute queries, where the table name is a parameter, like: TRUNCATE TABLE $1, table_string? Because my problem is that a user can create tables during runtime and can truncate their contents. And if I just concetenate "TRUNCATE TABLE" + table_name then there is a possibility to attack my database. Is there any functionality for my truncate example? I use libpqxx for a C++ application.

Answer 1

There are two options to do this safely - ideally you should use both:

• When your users create the names used for tables (or any DB objects) you may should limit the names to use a "safe" set of characters (alphanumeric & dashes, underscores...)
• You should enquote the table name - adding double quotes around the object. If you do this, you need to be careful to validate there are no quotes in the name, and error out or escape the quotes. You also need to be aware that adding quotes will make the name case sensitive.