如何在参数化查询中传递 guid？

发布于 2024-11-27 12:46:42 字数 650 浏览 1 评论 0原文

我当前最好的代码是：

string delNonQuery = "DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid";

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter kc = new SqlParameter("keycolumn", SqlDbType.VarChar);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(kc).Value = Settings.KeyColumn;
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

它执行但影响了惊人的零行。如果我将 keyuid 上的 SqlDbType 更改为 UniqueIdentifier，最终会出现“无法将字符串转换为 uniqueidentifier”的十几个变体。我必须使用参数化查询来保持数据清洁度，我只是真的不知道如何......

原文

My current best code is:

string delNonQuery = "DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid";

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter kc = new SqlParameter("keycolumn", SqlDbType.VarChar);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(kc).Value = Settings.KeyColumn;
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

This executes but affects a whopping zero rows. If I change the SqlDbType on keyuid to UniqueIdentifier it just ends up with me getting a dozen variations on "failed to convert character string into uniqueidentifier". I have to use a parameterized query for data cleanliness, I'm just really stuck as to how...

分享到QQ

分享到微博

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

发布评论

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

感性不性感 2024-12-04 12:46:42

您无法为列名指定参数 - 您需要以与表名相同的方式连接它。

这：

"DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid"

应该更改为：

"DELETE FROM " + Settings.DataSource + " WHERE " + Settings.KeyColumn + " =@keyuid"

虽然我可能会将其写为：

string delNonQuery = string.Format("DELETE FROM {0} WHERE {1} = @keyuid", 
                                   Settings.DataSource, 
                                   Settings.KeyColumn);

为了完整性起见，我会提到这对 SQL注入。您需要确保您的设置值是干净的。

You can't specify a parameter for a column name - you need to concatenate it the same way you do for the table name.

This:

"DELETE FROM " + Settings.DataSource + " WHERE @keycolumn=@keyuid"

Should change to:

"DELETE FROM " + Settings.DataSource + " WHERE " + Settings.KeyColumn + " =@keyuid"

Though I would probably write it as:

string delNonQuery = string.Format("DELETE FROM {0} WHERE {1} = @keyuid", 
                                   Settings.DataSource, 
                                   Settings.KeyColumn);

For completeness sake, I will mention that this is open to SQL injection. You need to make sure your Settings values are clean.

回复收藏 0 原文

放赐 2024-12-04 12:46:42

我不认为你可以参数化列名（“keycolumn”）

尝试这个：

string delNonQuery = string.Format("DELETE FROM " + Settings.DataSource + " WHERE {0}=@keyuid", Settings.KeyColumn);

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

关于连接字符串来构建 SQL 的常见警告；这可能是一个安全风险。

最好的方法可能是将 SQL 封装在存储过程中，将列名和值作为参数传递，然后使用动态 SQL 执行。

I don't think you can parameterise the column name ("keycolumn")

Try this:

string delNonQuery = string.Format("DELETE FROM " + Settings.DataSource + " WHERE {0}=@keyuid", Settings.KeyColumn);

SqlCommand cmd = new SqlCommand(delNonQuery,readerConn);
SqlParameter key = new SqlParameter("keyuid", SqlDbType.VarChar);
cmd.Parameters.Add(key).Value = Page.Request["key"].ToString().Trim();

readerConn.Open();
cmd.ExecuteScalar();
readerConn.Close();

Usual warnings apply regarding concatanating strings to build SQL; this is likely a security risk.

The best method might be to encapsulate your SQL in a stored procedure, pass the column name and value as parameters and then execute using dynamic SQL.

回复收藏 0 原文