问题描述
我将Entity Framework与PostgreSQL一起使用，这意味着我正在使用Npgsql作为数据提供程序。我想调用 context.Database.ExecuteSqlCommandAsync 进行查询，并且我试图使用参数来避免注入攻击。我要查询的一个示例是alter table add column。这是我的代码设置参数和sql查询的样子：
I am using Entity Framework with PostgreSQL, which means I'm using Npgsql as the data provider. I would like to call context.Database.ExecuteSqlCommandAsync to make a query, and I'm trying to use parameters to avoid injection attacks. One example of a query I would like to make is alter table add column. Here is what my code looks like to set up the parameters and the sql query:
NpgsqlParameter tableNameParam = new NpgsqlParameter("tableName", NpgsqlDbType.Text);
tableNameParam.Value = entityTableName;
NpgsqlParameter fieldNameParam = new NpgsqlParameter("fieldName", NpgsqlDbType.Text);
fieldNameParam.Value = sqlFieldName;
NpgsqlParameter fieldTypeParam = new NpgsqlParameter("fieldType", NpgsqlDbType.Text);
fieldTypeParam.Value = sqlType;
object[] sqlParams = {tableNameParam, fieldNameParam, fieldTypeParam};
string addColumnQuery = "ALTER TABLE @tableName ADD @fieldName @fieldType";
await _context.Database.ExecuteSqlCommandAsync(addColumnQuery, sqlParams);
运行此命令时，我捕获到NpgsqlException
When I run this I catch an NpgsqlException with
我查看了堆栈跟踪，发现ErrorSQL显示：
I looked into the stack trace and found that the ErrorSQL displays:
您可以看到查询非常错误。在我的测试中，我使用人员实体并添加类型为 varchar的列 name。我只希望查询如下：
As you can see the query went horribly wrong. In my test I am using a person entity and adding a column "name" with type "varchar". I just want the query to be like this:
ALTER TABLE dbo.person_entity ADD name varchar
但是由于某些原因，使用NpgsqlParameters会造成混乱我怀疑这与 NpgsqlDbType.Text 有关，但似乎NpgsqlParameter构造函数需要类型作为第二个参数。
But for some reason using NpgsqlParameters is messing that up. I suspect it has to do with the NpgsqlDbType.Text but it seems like the NpgsqlParameter constructor requires a type as the second argument.
推荐答案
首先，Postg reSQL不支持列/表名的参数。造成这种现象的原因有很多：通常，不允许用户在数据库中创建具有任意类型的任意字段，即使您对它们进行了清理以防止SQL注入也无济于事。尝试考虑重新设计应用程序。
First, PostgreSQL doesn't support parameters for column/table names. There are some good reasons for this behavior: it doesn't normally make sense to allow users to create arbitrary fields with arbitrary types in your databases - even if you sanitize them to prevent SQL injection. Try to think of redesigning your application.
有关情况的更详细的技术说明，Npgsql Entity Framework提供程序将参数占位符（例如@fieldName）替换为文本文字-和（（'dbo.person_entity'）:: text）是一个完全有效的文字。问题在于列名和类型不是文本文字：您不会在引号中将它们括起来。
For a more technical explanation of what's happening in your case, the Npgsql Entity Framework provider replaces your parameter placeholder (e.g. @fieldName) with a text literal - and (('dbo.person_entity')::text) is a perfectly valid literal. The problem is that column names and types aren't text literals: you don't surround them with quotes.
这篇关于如何将NpgsqlParameter与ExecuteSqlCommand一起使用以避免注入攻击？获取NpgsqlException错误42601：语法在at''（\）处或附近错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！