C#: How to pass parmeterize values to System.Data.Entity.SqlSquery

I’m going to execute SQL query directly on database. I’ve define connection to my database using:

System.Data.Entity.DbContext rawDbContext = new DbContext(connectionString);

I don’t want to insert parameters directly to query string to avoid SQL Injection so I want to set parametrized values for my SQL query this way:

string sqlCommandString =
        "IF EXISTS(select* from @MappingTableName where " + Environment.NewLine +
        "BranchID= @PrimaryKeyID and " + Environment.NewLine +
        "BranchNo = @BranchNo and " + Environment.NewLine +
        "TableName = @TableName and " + Environment.NewLine +
        "BranchSchema = @SchemaNameInBranch and " + Environment.NewLine +
        "TableID = @TableID) " + Environment.NewLine +
        "    select 1" + Environment.NewLine +
        "ELSE " + Environment.NewLine +
        "select 0 " + Environment.NewLine;
SqlParameter parameterMappingTableName = new SqlParameter("@MappingTableName", vipMappingTableName);
SqlParameter parameterSchemaNameInBranch = new SqlParameter("@SchemaNameInBranch", schemaName);
SqlParameter parameterPrimaryKeyInBranch = new SqlParameter("@PrimaryKeyID", primaryNodeId);
SqlParameter parameterBranchNo = new SqlParameter("@BranchNo", branchNo);
SqlParameter parameterTableId = new SqlParameter("@TableID", tableId);
SqlParameter parameterTableName = new SqlParameter("@TableName", tableName);


DbRawSqlQuery<int> result = rawDbContext.Database.SqlQuery<int>(sqlCommandString, 
new[] {
    parameterMappingTableName,
    parameterSchemaNameInBranch,
    parameterPrimaryKeyInBranch,
    parameterBranchNo,
    parameterTableId,
    parameterTableName
});
int finalResult = result.Single();

Running this query leads to excpetion "Must declare the table variable "@MappingTableName"."

How can I fix this?

Answer

Check this from Microsoft forums

Database objects (tables, stored procedures or any other objects) cannot be passed as parameters. Only actual values for columns or variables can be parameters. You need to build your SQL statement dynamically in this case

Which basically means that you have to provide and/or build the table name, under the danger that this can be compromised.

How to mitigate the risk. Declare a set of probable table name and do an exact match.

Then build your query with text concatenation. This is something that can’t be done with parameters cause you can’t expect the possible values, but can be done with tables because they are only so many. Be carefull to use Equals instead of Contains in your list of names.