CODEWITHSUNDEEP
c#sqlado.net
Alfredo Torre
Alfredo Torre

Reputation: 728

Query with dynamic table name

I would like to change a dynamic SQL statement in a parametrized statement, but in the query the table name is dynamic. So I tried to paramrtrize the whole statement in this way, as I saw in the newCRMsupport's answer in this link:

command.CommandType = CommandType.Text;

string sqlStr = " DECLARE @sqlSubstr nvarchar(max) SET @sqlSubstr = N'UPDATE quotename(@tempTable) = SET @flag = 1 WHERE @tempCol = @tempColVal' EXECUTE sp_executesql @sqlSubstr";
command.CommandText = sqlStr;
command.Parameters.AddWithValue("@tempTable", TemporaryTableName);
command.Parameters.AddWithValue("@flag", flagToUpdate);
command.Parameters.AddWithValue("@tempCol", ImportRegister.TemporaryTableKeyColumn);
command.Parameters.AddWithValue("@tempColVal", sourceRow[ImportRegister.TemporaryTableKeyColumn]);
command.ExecuteNonQuery();

But when I run it, I have an Exception. "Must declare calar variable @tempTable", and I can't figure out what I am missing. Thanks

Upvotes: 0

Views: 347

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Using dynamic SQL like this is a sign of a poor design, either on the database design or on the application design.

However, assuming the design can't be changed, the problem with your current SQL statement is that you are not actually using dynamic SQL.
A dynamic SQL for this would look like this:

DECLARE @sqlSubstr nvarchar(max) = N'UPDATE '+ quotename(@tempTable) +N' 
    SET '+ quotename(@flag) +N' = 1 
    WHERE '+ quotename(@tempCol) +' = @tempColVal;

EXECUTE sp_executesql @sqlSubstr, N'@tempColVal varchar(2)', @tempColVal;

Note the parameters for the table name and column names are concatenated into the string representing the SQL you are executing.
Also, please note I've added quotename to the column names as well.

However, I'm not sure that this provides a full-proof protection against SQL injection attacks. The use of quotename does provide some protection but I'm pretty sure that it is possible to overcome this.
To truly protect yourself you must white-list all the identifiers - so you first need to query information_schema.columns to make sure everything is safe.

Only once you do that, you can be sure the code is SQL Injection safe. While we're at it, you really should stop using AddWithValue already.

Here's a revised version of your code:

string sqlStr = @"DECLARE @sqlSubstr nvarchar(max) = 
    N'UPDATE '+ quotename(@tempTable) +
    N' SET '+ quotename(@flag) +
    N' = 1 WHERE '+ quotename(@tempCol) +' = @tempColVal' 
    IF EXISTS(
        -- make sure both columns exists in the table
        SELECT 1
        FROM Information_schema.Columns
        WHERE Table_Name = @tempTable
        AND Column_Name IN(@flag, @tempCol)
        HAVING COUNT(DISTINCT Column_Name) = 2 
    )
    EXECUTE sp_executesql @sqlSubstr N'@tempColVal nvarchar' @tempColVal"; -- I had to guess the data type

command.CommandText = sqlStr;
command.Parameters.Add("@tempTable", SqlDbType.NVarChar).Value = TemporaryTableName;
command.Parameters.Add("@flag", SqlDbType.NVarChar).Value = flagToUpdate;
command.Parameters.Add("@tempCol", SqlDbType.NVarChar).Value = ImportRegister.TemporaryTableKeyColumn;
command.Parameters.Add("@tempColVal", SqlDbType.NVarChar).Value = sourceRow[ImportRegister.TemporaryTableKeyColumn]);
command.ExecuteNonQuery();

Upvotes: 1

Related Questions