How can I provide the table name for the query as a command parameter in Npgsql?

Question

I want to specify the table name for the query as command parameters, for example:

public class Foo { private const String myTableName = "mytable"; public void Bar() { NpgsqlCommand command = new NpgsqlCommand("SELECT * from :tableName", connection); command.Parameters.Add(new NpgsqlParameter("tableName", DbType.String)); command.Parameters[0].Value = myTableName; } } 

As a result of this query, the following query appears: "SELECT * from E'mytable'" , which results in an error (consider single quotes).

Do I need to perform string concatenation for this? This does not matter from a security point of view, since the table name cannot be changed by the user, but concatenating strings to create SQL queries always gives me creeps ...

Thanks Eric

Answer 1

Table names cannot be sent as parameters. Table names are resolved during parsing, as they are necessary for planning and such things. Parameters are replaced only when the executor (or, if necessary, the optimizer) takes time.

So, you will need to use a string replacement. Of course, this is not a security problem (or even the risk of becoming one) if the table name comes from a constant in your class.

But if you create a table name from user input, you need to be very careful. But usually, if you need to build a table name from user input, something is poorly designed in the database in the first place and should be fixed (yes, of course, there are exceptions).