asp.net - Variable table name using dynamic SQL in C#

Question

I've been looking around for a way to enter a variable table name and it seems the best way is to use dynamic sql, although it can lead to SQL injection. Can anyone demonstrate how this is done in C#? For example, I want something to implement something like this:

 SqlCommand command= new SqlCommand("SELECT x FROM @table WHERE @column = @y", conn);

As you can see from this, the table name and column name would be variables. I was using string concatenation before but I want to avoid that for security purposes. Not sure if it matters, but the table and column are not determined by user input but actually determined by the links the user selects, so maybe SQL injection isn't a problem here?

Answer 1

The Best way I can think of is two part.

1. You have a stored procedure that takes the name as a parameter. This this proc first looks up the table name in the catalog (Information_Schema.Table) and get's the proper name to verify it's a valid.

2. Then using this look up the stored procedure constructs the required SQL and not from the parameter.

Using this look up you are essentially protecting against non valid table names being passed in.

CREATE PROCEDURE RunSQL
    (@table NVARCHAR(50))
AS
BEGIN 
    DECLARE @validTableName NVARCHAR(50)

    SELECT 
        @validTableName = TABLE_NAME    
    FROM 
        INFORMATION_SCHEMA.TABLES
    WHERE 
        TABLE_NAME = @table
        AND SCHEMA_NAME = 'dbo' -- here you can limit or customise the schema

    IF @validTableName IS NULL
        RAISE ... raise an exception

    DECLARE @dynamicSql NVARCHAR(100) = REPLACE('SELECT * FROM dbo.[#TABLE#]', '#TABLE' @validTableName) 

    EXECUTE sp_executesql .... @dynamicSql ....
END

You can use extend this to validate, schema, columns etc...

You ultimately need to construct your own SQL and do the protection against injection. There is no getting around that.