Parameterized Query for dynamic SQL

Question

We are using snowflake client nuget package and creating dynamic queries as below:

   using Snowflake.Client;

   using (var conn = new SnowflakeDbConnection())
   {
       conn.ConnectionString = ConnectionString;
       conn.Open();

       var cmd = new SnowflakeDbCommand(conn);
       cmd.CommandText = $"select count(*) from {tableName}";
       //other code goes here
   }

We are using the above code but our sonar keeps flagging the dynamic SQL query as below:

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

We have tried using parameter as mentioned here:

Parametrized query with Snowflake and pass the values from Snowflake .NET Connector

But with this the command fails as null.

We have also tried using string.format as:

string.Format("select count(*) from \"{0}\"", tableName);

But this one still is flagged by sonar.

If anyone can provide any other suggestions.

Thanks

Answer 1

I have done something similar in SQL and Python, but not .NET. So your mileage may vary.

I typically, create a variable outside the initial SQL call, when I am unable to do a string replacement.

   using Snowflake.Client;
   
   var sqlCall = "select count(*) from \"" + tableName + "\"";

   using (var conn = new SnowflakeDbConnection())
   {
       conn.ConnectionString = ConnectionString;
       conn.Open();

       var cmd = new SnowflakeDbCommand(conn);
       cmd.CommandText = sqlCall;
       //other code goes here
   }

This has typically worked well for me. What you should watch out for is the number of double quotes that appear in your query string before being submitted to cmd.CommandText.