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!

@GH DevOps this does not replace any form of validation. How should the StringBuilder or string.format stop me from concating a bad string to inject it? — LMA
– LMA, Commented Feb 25 at 13:46
SqlCommandBuilder class is for ms sql server, not for mysql. I update the tags. — Shadow
– Shadow, Commented Feb 25 at 17:51
@AmitJoshi Yes. In my case I'm creating a generic repository. — Sumisha Sankar
– Sumisha Sankar, Commented Feb 27 at 13:12

Marc Gravell · Accepted Answer · 2025-02-25 13:58:21Z

2

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.

edited Feb 25 at 13:58

answered Feb 25 at 13:50

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Charlieface Feb 25 at 14:10

Why is builder.QuoteIdentifier not safe?

Marc Gravell Feb 25 at 14:17

@Charlieface what tables is the relevant piece of code expected to access? it is rarely "everything" - although to be fair, that problem is probably better solved via ACLs

Charlieface Feb 25 at 14:53

I don't see how that's relevant. If it's safe it's safe, it's no different to using quote_identifier() or similar in SQL. Note that MySQL doesn't use [] syntax, also SqlCommandBuilder does that anyway so you are duplicating the [], so either way it's incorrect.

Martin Smith Feb 26 at 12:00

Manually appending [ and ] does not escape ] correctly if the object name contains that character. Presumably this is something builder.QuoteIdentifier would do

Marc Gravell Feb 26 at 12:38

@MartinSmith you're right, but if you're allow-listing the tables, that becomes less of a concern in almost all cases

|

Collectives™ on Stack Overflow

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

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related