Parameterise Table Name in .Net/Sql

Parameterise table name in .NET/SQL?

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

How to use Select query for table names with parameters?

You can't pass an object name as a parameter directly to the statement like that. You can still use a parameter but the command will have to be something like this:

(@"declare @sql nvarchar(max) = N'create table dbo.'
   + QUOTENAME(@1) + N'([BACK_LANG] varchar(50));';
  EXEC sys.sp_executesql @sql;")

Or just build the string in C#, using SqlCommandBuilder's QuoteIdentifier(this.getName()) which provides similar protection as QUOTENAME().

For more on SQL injection: Dynamic SQL

Related Topics