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, Action
1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 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(TaskCompletionSource
1 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
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
User contributions licensed under CC BY-SA 3.0