ASP.net简单删除查询，传入字符串值

发布于 2024-10-21 16:23:49 字数 392 浏览 1 评论 0原文

DELETE FROM tblArtworkApprovalUsers
WHERE     (userID NOT IN (@UserIDList)) AND (approvalID =
                          (SELECT     ID
                            FROM          tblArtworkApprovals
                            WHERE      (templateID = @TemplateID)))

这是在我的表适配器中。 @UserIDList 需要接受类似以下内容：

2,44,12,70

如何使此查询接受该字符串？

原文

DELETE FROM tblArtworkApprovalUsers
WHERE     (userID NOT IN (@UserIDList)) AND (approvalID =
                          (SELECT     ID
                            FROM          tblArtworkApprovals
                            WHERE      (templateID = @TemplateID)))

This is in my table adapter. @UserIDList needs to accept something like:

2,44,12,70

How can I make this query accept that string?

分享到QQ

分享到微博

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

发布评论

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

西瓜 2024-10-28 16:23:49

NOT IN 需要表达式而不是字符串。因此，如果您传递参数而不是动态构造 SQL，则无法完成此操作。

另一种方法是动态创建 SQL（同时了解 SQL 注入）：

string commad = @"DELETE FROM tblArtworkApprovalUsers " +
             "WHERE (userID NOT IN ({0})) AND (approvalID ="+
                          "(SELECT     ID " +
                            "FROM          tblArtworkApprovals " +
                            "WHERE      (templateID = {1})))";

command = string.Format(command, userIDList, templateID);

更新

Craig 在此指出了一个更好的解决方案，该解决方案并没有提供更好的性能（因为参数是可变的，而查询计划确实如此）除非完全相同，否则不会被缓存），但有助于 SQL 注入攻击：
参数化 SQL IN 子句

NOT IN <expr> requires an expression and not a string. So if you are passing the parameter and not constructing the SQL dynamically this cannot be done.

Alternative is to create the SQL dynamically (while being aware of SQL Injection):

string commad = @"DELETE FROM tblArtworkApprovalUsers " +
             "WHERE (userID NOT IN ({0})) AND (approvalID ="+
                          "(SELECT     ID " +
                            "FROM          tblArtworkApprovals " +
                            "WHERE      (templateID = {1})))";

command = string.Format(command, userIDList, templateID);

UPDATE

Craig pointed to a better solution here which does not provide much better performance (since parameters are variable and query plan does not get cached unless it is exactly the same) but help with SQL injection attack:
Parameterize an SQL IN clause

回复收藏 0 原文