Parametise 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.

I Know how to do this for values, i.e 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 Field and Tablesnames.

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 can pass the table name as a parameter like any other parameter. the key is you have to build a dynamic sql statement, which then you should consider if it's easier to build it in your app tier or in the procs.

create procedure myProc

@tableName nvarchar(50)

as

sp_executesql N'select * from ' + @tablename

fyi this code sample is from memory have a look at BOL for the proper syntax of sp_executesql.

Also this is highly sucesptible to SQL injection as you indicated is not an issue for you but anyone reading this should be very wary of accepting input from a user to generate their queries like this.

Answer 2

SQL query parameters can only take the place of a literal value. You cannot use a parameter for a table name, column name, list of values, or other SQL syntax. That's standard SQL behavior across all brands of database.

The only way to make the table name dynamic is to interpolate a variable into your SQL query before you prepare that string as a statement.

BTW, you're fooling yourself if you think this isn't a risk for SQL injection. If you interpolate the table name into the query dynamically, you need to use delimited identifiers around the table name, just as you would use quotes around a string literal that is interpolated from a variable.

Answer 3

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

Answer 4

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 sent 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 5

The idea that it is not prone to SQL injection is misguided. It may be less prone to SQL injection from front end users, but it is still very much prone to SQL injection. Most attacks on databases come from inside the company being attacked, not from end users.

Employees may have grudges, they may be dishonest, they may be disgruntled, or they may just be not so bright and think that it's ok to bypass security to do whatever it is that THEY think should be done to the database.