ef core context.Database.ExecuteSqlRaw(sql, param1, param2) throws SqlException

Question

ef core version I use is :3.0 The code I write is somehow like below:

SqlParameter table = new SqlParameter("@tableName", tableName);
SqlParameter entryId = new SqlParameter("@entryId", id);
string sql = "delete from @tableName where id = @entryId";
context.Database.ExecuteSqlRaw(sql, table , entryId);

my sql statement have two parameters, which I have already defined and passed, but each time I execute it, i just got the following exception, please help to take a look. thanks

Microsoft.Data.SqlClient.SqlException (0x80131904): Must declare the table variable "@tableName". at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, Object[] parameters) at

With just a stacktrace and message we won't be able to help you. Besides, the message Must declare the table variable "@tableName". seems clear to me. What kind of help are you looking for? — Peter Bons
@PeterBons, the sql should work from the document, but i get an error, i want to know how to make it works. Thanks — Ray
Table names, column names and other sql identifiers cannot be parameterized — Caius Jard
@CaiusJard, really? I did not found this in the Microsoft document, if so I know the reason. Thanks a lot — Ray

Caius Jard Caius Jard · Accepted Answer · 2019-12-05T12:17:25

Table names and column names cannot be parameterized. In C# terms this would be like putting your class name in a string and trying to instantiate it:

string className = "Person";
className p = new className; //it doesn't work

You code will have to be more like:

SqlParameter entryId = new SqlParameter("@entryId", id);
string sql = "delete from "+tableName+" where id = @entryId";
context.Database.ExecuteSqlRaw(sql, entryId);

Do not give the end user the ability to alter the contents of the tableName variable

Extra info courtesy of Lutti Coelho:

To avoid SQL Injection on your query it's better to use ExecuteSqlInterpolated method. This method allow using string interpolation syntax in a way that protects against SQL injection attacks.

context.Database.ExecuteSqlInterpolated("delete from {tableName} where id = {id}");

Always use parameterization for raw SQL queries

When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

You can see more about raw SQL queries at this link: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

ef core context.Database.ExecuteSqlRaw(sql, param1, param2) throws SqlException

1 Answers