为什么 PostgreSQL 将我的查询中的值视为列名？

发布于 2024-12-20 19:00:18 字数 925 浏览 1 评论 0原文

我使用 Npgsql 和 C# 与我的 PostgreSQL 数据库进行通信。我的数据库中使用的所有名称都是大小写混合的，因此在查询中我确保在每个名称周围使用双引号。以下是我发送查询的方式：

// construct an insert query
string insertQuery = "insert into \"Update\" (\"Vehicle\",\"Property\",\"Value\") " + 
                     "values (" + vehicleNum.ToString() + ",\"" + propertyName + 
                     "\",\"" + propertyValue + "\")";

// execute the query
NpgsqlCommand insertCommand = new NpgsqlCommand(insertQuery, conn);
insertCommand.ExecuteScalar();

通过插入断点并检查，我验证了字符串 insertQuery 在发送之前看起来是这样的：

insert into "Update" ("Vehicle","Property","Value") values (12345,"EngineSpeed","50")

当我发送此查询时，PostgreSQL 给出了一个错误，即包含在 Npgsql 异常中，该异常指出：错误：42703：列“EngineSpeed”不存在

从我的查询中，应该很明显EngineSpeed不是一列，它的值是Property 列，因此自然不可能存在具有该名称的列。那么为什么 PostgreSQL 以这种方式处理我的查询，我该如何解决这个问题呢？我的查询构造方式是否错误？

原文

I'm using Npgsql with C# to communicate with my PostgreSQL database. All names used in my database are mixed case, so in the query I make sure I use double quotes around each name. Here is how I am sending the query:

// construct an insert query
string insertQuery = "insert into \"Update\" (\"Vehicle\",\"Property\",\"Value\") " + 
                     "values (" + vehicleNum.ToString() + ",\"" + propertyName + 
                     "\",\"" + propertyValue + "\")";

// execute the query
NpgsqlCommand insertCommand = new NpgsqlCommand(insertQuery, conn);
insertCommand.ExecuteScalar();

By inserting a breakpoint and checking, I verified that the string insertQuery looks this before it is sent:

insert into "Update" ("Vehicle","Property","Value") values (12345,"EngineSpeed","50")

When I send this query, PostgreSQL gives me an error, which is wrapped up in an Npgsql exception that states: ERROR: 42703: column "EngineSpeed" does not exist

From my query, it should be evident that EngineSpeed is not a column, it is the value of the Property column, so naturally a column with that name is unlikely to exist. So why does PostgreSQL treat my query this way, and how can I solve this issue? Has my query been constructed the wrong way?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苍风燃霜 2024-12-27 19:00:18

使用单引号来引用字符串。双引号用于表示列名称。

回复收藏 0 原文

我ぃ本無心為│何有愛 2024-12-27 19:00:18

不，从您显示的查询中可以明显看出，EngineSpeed 是一列，因为它是这样转义的。

您也没有注意确保传递的值被转义，这可能是一个严重的安全问题。

您想要插入“Update”（“Vehicle”、“Property”、“Value”）值（12345、'EngineSpeed'、'50'）

您可以安全地提供这些值：

string insertQuery = "insert into \"Update\" (\"Vehicle\",\"Property\",\"Value\") " + 
                     "values (" + vehicleNum.ToString() + ",'" + propertyName.Replace("'", "''") + 
                     "','" + propertyValue.Replace("'", "''") + "')";

虽然您更好不要使用 NPGSQL 的参数，它会为你处理这个问题，包括我们的单元测试中充满的所有令人讨厌的边缘情况:)

No, from the query you show it's evident that EngineSpeed is a column because it's escaped as such.

You also weren't taking care to make sure the values passed were escaped, which can be a serious security issue.

You want insert into "Update" ("Vehicle","Property","Value") values (12345,'EngineSpeed','50')

Which you could safely provide with:

string insertQuery = "insert into \"Update\" (\"Vehicle\",\"Property\",\"Value\") " + 
                     "values (" + vehicleNum.ToString() + ",'" + propertyName.Replace("'", "''") + 
                     "','" + propertyValue.Replace("'", "''") + "')";

Though you are better off using parameters with NPGSQL, which will handle this for you, including all of those nasty edge cases our unit tests are full of :)

回复收藏 0 原文

~没有更多了~