c# - ef 核心 context.Database.ExecuteSqlRaw(sql, param1, param2) 抛出 SqlException

Question

我使用的ef核心版本是:3.0我写的代码有点像下面这样:

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);

我的sql语句有两个参数，我已经定义并传递了，但是每次执行时，都出现如下异常，请帮忙看看。谢谢

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

Answer 1

表名和列名不能参数化。在 C# 术语中，这就像将您的类名放入字符串中并尝试实例化它:

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

您的代码必须更像:

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

不要让最终用户改变 tableName 变量的内容

---

额外信息由 Lutti Coelho 提供:

为了避免查询中的 SQL 注入(inject)，最好使用 ExecuteSqlInterpolated 方法。此方法允许以防止 SQL 注入(inject)攻击的方式使用字符串插值语法。

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.

您可以在此链接中查看有关原始 SQL 查询的更多信息: https://learn.microsoft.com/en-us/ef/core/querying/raw-sql