CODEWITHSUNDEEP
c#sqlsql-server
romeozor
romeozor

Reputation: 941

Safely query SQL table with variable table name

I'm trying to make some common code for retrieving identities from tables and that involves making an unsafe query string to inject the table name.

I read everywhere that I cannot safely inject the table name. So I want to query if the table exists, then based on the result, perform a real or dummy query.

var unsafeTableQuery = "SELECT [Id] FROM [dbo].[" + tableName + "] WHERE [BulkInsertSessionID] = @bulkInsertSessionId";
var guardQuery =
"DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );" +
"IF (@Exists = 0) SELECT TOP 0 NULL 'Id'" +
"ELSE " + unsafeTableQuery;
var cmd = new SqlCommand(guardQuery, conn, tran);
cmd.Parameters.Add(new SqlParameter("@TableName", tableName));
cmd.Parameters.Add(new SqlParameter("@bulkInsertSessionId", bulkInsertSessionId));

using (SqlDataReader reader = cmd.ExecuteReader())
{
    int index = 0;
    while (reader.Read())
    {
        int id = (int)reader[0];
        entities[index++].Id = id;
    }
}

Even though I have an unsafe concatenation, I'm first querying the table name against the sys.tables by a parameter. And if it doesn't exist, the IF..ELSE block will never step into the unsafe query.

For easier readability I'm expecting to run the following query:

DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );
IF(@Exists = 0)
SELECT TOP 0  NULL 'Id'
ELSE
SELECT [Id] from <InjectedTableName> where BulkInsertSessionID = @bulkSessionId

Am I correct in my assumption that this is safe?

Upvotes: 0

Views: 265

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Suppose your users have an access to change the variable tableName. I suppose some user types it on some form. Suppose he types this:

Users]; DROP TABLE Users;--

Then your whole command will be:

DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );
IF(@Exists = 0)
SELECT TOP 0  NULL 'Id'
ELSE
SELECT [Id] from [Users]; DROP TABLE Users;-- where BulkInsertSessionID = @bulkSessionId

This will do its IF ELSE part and then will go to next statement which is:

DROP TABLE Users;

Note that drop statement will execute in any case even if ELSE part is not executed, because you don't have BEGIN END. Note that the rest is commented out... This is most basic injection method...

Upvotes: 1

Related Questions