Incorrect syntax near @indexName. Dapper

Question

This seems like a strange behaviour, but the following code is throwing an error:

public async Task RebuildIndex(string tableName, string indexName)
{
    await _dbConnection.ExecuteAsync($@"
        alter index @indexName on @tableName rebuild;", 
    new
    {
        indexName = indexName,
        tableName = tableName
    });
}

Error message:

System.Data.SqlClient.SqlException (0x80131904): Неправильный синтаксис около конструкции "@indexName".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 678
   at Railways.DbManager.Repositories.DbmIndexDefragmentationRepository.RebuildIndex(String tableName, String indexName) in C:\Railways\asu-itk-core\Railways\Railways.DbManager.Repositories\DbmIndexDefragmentationRepository.cs:line 48
   at Railways.DBManager.Services.IndexDefragmentationService.DefragIndexes() in C:\Railways\asu-itk-core\Railways\Railways.DBManager.Services\IndexDefragmentationService.cs:line 41
ClientConnectionId:7db4e7f6-e01d-4671-9fe3-e22dcb388cd4
Error Number:102,State:1,Class:15

What can be wrong here?

I am just passing the parameter and this should work.

Maybe I am missing something? Could anyone please suggest a solution?

Answer 1

You won't be able to parameterize target object names used in SQL statements, i.e. table names and index names can't be parameterized. This isn't just a Dapper restriction - e.g. ADO.Net and sp_executesql similarly can't parameterize tables. (speculatively, parameterization protects not just against SQL injection attacks, but also improves execution plan caching based on exact data types - plan caching is obviously not possible if the target table is dynamic).

In your case, you will need to do your own SQL injection checking (if needed - it would be highly unusual for index names and table names to come from an untrusted user), and then just substitute the name into the SQL statement, e.g. via string interpolation or String.Format.

If you really accept table or index names from untrusted users and need to guard against SQL injection attacks, you can use Aaron Betrand's advice and first check for the existence of the table in sys.tables and the index in sys.indexes before executing the SQL statement.

Edit

The reason why you can parameterize the table name in queries against system tables like sys.tables and DMVs like sys.dm_db_index_physical_stats is because here, the name of the table or other object is a value in a column. But similarly, you won't be able to parameterize the name of the system table or DMV itself.