Parameterise table name in .NET/SQL?

Question

As the topic suggests I wish to be able to pass table names as parameters using .NET (doesn't matter which language really) and SQL Server.

I know how to do this for values, e.g. command.Parameters.AddWithValue("whatever", whatever) using @whatever in the query to denote the parameter. The thing is I am in a situation where I wish to be able to do this with other parts of the query such as column and table names.

This is not an ideal situation but it's one I have to use, it's not really prone to SQL injection as only someone using the code can set these table names and not the end-user. It is messy however.

So, is what I am asking possible?

EDIT: To make the point about SQL injection clear, the table names are only passed in by source code, depending on the situation. It is the developer who specifies this. The developer will have access to the database layer anyway, so the reason I am asking is not so much for security but just to make the code cleaner.

Answer 1

You cannot directly parameterize the table name. You can do it indirectly via sp_ExecuteSQL, but you might just as well build the (parameterized) TSQL in C# (concatenating the table-name but not the other values) and send it down as a command. You get the same security model (i.e. you need explicit SELECT etc, and assuming it isn't signed etc).

Also - be sure to white-list the table name.

Answer 2

I don't think I've ever seen this capability in any SQL dialect I've seen, but it's not an area of expertise.

I would suggest restricting the characters to A-Z, a-z, 0-9, '.', '_' and ' ' - and then use whatever the appropriate bracketing is for the database (e.g. [] for SQL Server, I believe) to wrap round the whole thing. Then just place it directly in the SQL.

It's not entirely clear what you meant about it not being a SQL injection risk - do you mean the names will be in source code and only in source code? If so, I agree that makes things better. You may not even need to do the bracketing automatically, if you trust your developers not to be cretins (deliberately or not).