What is the way to protect this function from SQL injection?

Question

public static bool TruncateTable(string dbAlias, string tableName)
{
    string sqlStatement = string.Format("TRUNCATE TABLE {0}", tableName);
    return ExecuteNonQuery(dbAlias, sqlStatement) > 0;
}

Answer 1

As far as I know, you cannot use parameterized queries to execute DDL statements/specify table names, at least not in Oracle or Sql Server. If I had to have a crazy TruncateTable function, and had to be able to avoid SQL injection, then what I would do is create a stored procedure that checks if the input is a table that can be safely truncated.

-- Sql Server specific!
CREATE TABLE TruncableTables (TableName varchar(50))
Insert into TruncableTables values ('MyTable')

go

CREATE PROCEDURE MyTrunc @tableName varchar(50)
AS
BEGIN

declare @IsValidTable int
declare @SqlString nvarchar(50)
select @IsValidTable = Count(*) from TruncableTables where TableName = @tableName

if @IsValidTable > 0
begin
 select @SqlString = 'truncate table ' + @tableName
 EXECUTE sp_executesql @SqlString
end
END

Answer 2

The most common advice to combat SQL injection is to use SQL query parameters (several people on this thread have suggested doing this).

This is the wrong answer in this case. You cannot use SQL query parameters on table names in DDL statements.

SQL query parameters can only be used in place of literal values ​​in SQL expressions. This is standard for every SQL implementation.

When you have table names, my recommendation to prevent SQL injection is to validate the input string against a list of known table names.

You can get a list of valid table names from the INFORMATION_SCHEMA:

SELECT table_name 
FROM INFORMATION_SCHEMA.Tables 
WHERE table_type = 'BASE TABLE'
  AND table_name = @tableName

Now you can pass input variables as SQL parameters to this query. If the query returns no rows, you know the input is invalid and cannot be used as a table. If the query returns a row, it matches, so you can use it with more confidence.

You can also validate table names against a specific list of tables that you define as available for the application to truncate, as @John Buchanan suggested .

Even after validating that tableName exists as a table name in your RDBMS, I would also suggest delimiting the table name, just in case you use table names with spaces or special characters. In Microsoft SQL Server, the default identifier delimiters are square brackets:

string sqlStatement = string.Format("TRUNCATE TABLE [{0}]", tableName);

Now you're only at risk for SQL injection if tableName matches a real table, and you actually use square brackets in the names of your tables!