How to Safely Parameterize Table Names in C# to prevent SQL Injection?

Question

I'm using Dapper in my project and I want to pass table name as a dynamic parameter in the query. This is my code:

var tableName = GetTableNameDynamically<TEntity>();
using (var builder = new SqlCommandBuilder())
{
    tableName = builder.QuoteIdentifier(tableName);
}
string qry = $"select case when exists(select 1 from "+ tableName +" where Id = @Id) then 1 else 0 end";
return await sqlConnection.QuerySingleAsync<bool>(qry, new { Id = id }, sqlTransaction);

When I run sonarQube scanner I got an error like this "Make sure using a dynamically formatted SQL query is safe here." How can I fix it? I can't use stored procedures.

Thank You!

Answer 1

The only safe way to do this is to ensure that the table name is expected, valid and permitted:

if (!knownTableNames.Contains(tableName)) // ideally HashSet<string> or similar
{
    throw new ArgumentOutOfRangeException(nameof(tableName));
}

(or switch works fine too!)

This satisfies our

Make sure using a dynamically formatted SQL query is safe here.

guidance, so you can go ahead and suppress the message in this specific instance.

Optionally, you might want to use ... from [" + tableName + "] where ... syntax in case you ever have table names with whitespace etc.