从 VB.NET 构建动态 sql 查询

Building a dynamic sql query from VB.NET

提问by David Pegram

I have searched and searched for an answer for this and I cannot find one. I have a checked list box where the user will select issues they are experiencing with their computer. In the checked list box you will see things like... Slow, Viruses, Bad Hard Drive... and based on what they select I will tell them an estimated cost for repair. Currently this is how I build my query:

我已经搜索并搜索了这个答案，但我找不到。我有一个选中的列表框，用户将在其中选择他们在使用计算机时遇到的问题。在选中的列表框中，您将看到诸如...缓慢、病毒、坏硬盘...根据他们的选择，我会告诉他们估计的维修费用。目前这是我构建查询的方式：

Dim mIssues As String = ""

For i = 0 To lstIssues.CheckedItemsCount - 1
 If mIssues = "" Then
  mIssues = String.Format("IssueName = '{0}'", lstIssues.CheckedItems(i))
 Else
  mIssues = String.Format("{0} OR IssueName = '{1}'", mIssues, lstIssues.CheckedItems(i))               
 End If
Next

The above code will look to see how many issues they have selected. If they only select one issue then it will return a string like this: IssueName = 'Whatever they selected'. If they have selected more than one issue it will return a string like this: IssueName = 'Whatever they selected' OR IssueName = 'The second selection'. So basically I will append an OR between all the selections if they select more than one issue. I do this to dynamically build my where clause in my query.

上面的代码将查看他们选择了多少问题。如果他们只选择一个问题，那么它会返回一个这样的字符串：IssueName = 'Whatever they selected'。如果他们选择了多个问题，它将返回这样的字符串：IssueName = 'Whatever they selected' OR IssueName = 'The second selection'。所以基本上，如果他们选择了多个问题，我会在所有选择之间附加一个 OR。我这样做是为了在我的查询中动态构建我的 where 子句。

Here is my query:

这是我的查询：

Dim mySQL As String = "SELECT IssueID, IssueTypeID, IssueName, IssueDescription, " _
  & "CustomerID, IndividualCost, GroupCost, Active, ChargeType " _
  & "FROM (SELECT IssueID, IssueTypeID, IssueName, IssueDescription, " _
  & "CustomerID, IndividualCost, GroupCost, Active, ChargeType " _
  & "FROM(cfg_Issues) " _
  & "WHERE " & mIssues & " " _
  & "GROUP BY IssueID, IssueTypeID, IssueName, IssueDescription, CustomerID, " _
  & "IndividualCost, GroupCost, Active) " _
  & "ORDER BY IndividualCost DESC, GroupCost ASC;"

As you can see my where clause comes from the first section of code. My question is this, is there a better way to do this??? I know there has to be a better way to build a dynamic where clause query and I would like to see how. Thank you for any guidance you can help me with.

如您所见，我的 where 子句来自代码的第一部分。我的问题是，有没有更好的方法来做到这一点？？？我知道必须有更好的方法来构建动态 where 子句查询，我想看看如何。感谢您的任何指导，您可以帮助我。

回答by Steve

The first problem here is the open door to SQL Injection. I hope you have a complete control on what is inserted in your lstIssuesbecause the string concatenation is Always a Dangerous thing when you create commands for a database engine.

这里的第一个问题是SQL 注入的大门。我希望您可以完全控制插入的内容，lstIssues因为在为数据库引擎创建命令时，字符串连接始终是一件危险的事情。

Your code could be reduced using a StringBuilder class instance that helps when you have many elements to concatenate in strings

可以使用 StringBuilder 类实例减少您的代码，当您有许多元素要连接到字符串中时，该类实例会有所帮助

Dim mIssues As StringBuilder  = new StringBuilder()

For i = 0 To lstIssues.CheckedItemsCount - 1
  mIssues.AppendFormat("IssueName = '{0}' OR ", lstIssues.CheckedItems(i))
Next

' I suppose that you have a check in place to not allow to run this query if you don't have at 
' least one element checked in the list (if not the WHERE condition will fail)'
mIssues.Length -= 4

This will remove the IF inside the loop and, to remove the extra OR, it is enough reduce the length of the StringBuilder instance when you exit the loop.
In your query text the StringBuilder could return its internal string using

这将删除循环内的 IF，并且为了删除额外的 OR，在退出循环时减少 StringBuilder 实例的长度就足够了。
在您的查询文本中，StringBuilder 可以使用返回其内部字符串

mIssues.ToString

You could also try to use the IN sql clause with code like this

您也可以尝试将 IN sql 子句与这样的代码一起使用

Dim mIssues As StringBuilder  = new StringBuilder()

For i = 0 To lstIssues.CheckedItemsCount - 1
  mIssues.AppendFormat("'{0}', ", lstIssues.CheckedItems(i))
Next

' I suppose that you have a check in place to not allow this query if you don't have at 
' least one element checked in the list (if not the WHERE condition will fail)'  
mIssues.Length -= 2
mIssues.Insert(0, "IssueName IN(")
mIssues.Append(")")

回答by logixologist

I have always preferred to have SQL do all the dynamic work for me. The advantages, as many people are showing is all your SQL is done behind the scenes and is more secure and cant be injected. There are ways that evil people can actually (I would rather not explain the details of the hack itself), inject their own SQL into your form and potentially do lots of damage.

我一直更喜欢让 SQL 为我完成所有动态工作。优点，正如许多人所展示的那样，您的所有 SQL 都是在幕后完成的，并且更安全且无法注入。邪恶的人实际上可以通过多种方式（我宁愿不解释黑客本身的细节）将他们自己的 SQL 注入您的表单并可能造成大量损害。

Here is the basic premise behind Dynamic SQL (code may not be exact): You create a stored procedure that essentially writes the SQL code and then executes it

这是动态 SQL 背后的基本前提（代码可能不准确）：您创建一个存储过程，该过程基本上编写 SQL 代码，然后执行它

CREATE PROCEDURE p_getIssues
 as 

/*This is where you will place all your input parameters like this
 @paramname1  datatype,
 @paramname1  datatype,

 @mIssues varchar(max)   example: virus = 1 and slowcomp = 1


*/
BEGIN

 DECLARE @SQL as varchar(max)
 SET @SQL = 'SELECT * FROM tablename where ' + @mIssues 

 EXEC (@SQL)

END

Note: If you ever have to debug the sproc... comment out the EXEC and ADD A PRINT(@SQL) and you can see what SQL the sproc will run.

注意：如果您必须调试 sproc... 注释掉 EXEC 和 ADD A PRINT(@SQL)，您就可以看到 sproc 将运行什么 SQL。

If you have to go dynamic this is always a preferred method to go. Also on a side note sql inside your VB.NET code is not recommended IMHO because then from a scalability and updateability standpoint, if you were to add more services, you have to push out new VB.NET code. Instead, you log into SQL and you can make the changes and you are done!

如果您必须动态，这始终是首选方法。顺便提一下，不建议在 VB.NET 代码中使用 sql 恕我直言，因为从可扩展性和可更新性的角度来看，如果要添加更多服务，则必须推出新的 VB.NET 代码。相反，您登录 SQL 并进行更改即可完成！