如何用ASP过滤从数据库解析出来的数据

Question

我最近得到了一个ASP项目，这个项目相当混乱。我不熟悉这门语言，但在网上搜索了一段时间后，我设法学会了它，但我仍然不熟悉数据库查询等。

这就是问题所在，我的代码是从MS Access数据库文件中获取数据。我想要的是根据传递的post/get参数过滤这些数据。

以下是我到目前为止拥有的代码：

sql = "SELECT * FROM tyres"

if len(brand1) > 2 then 
     sql = sql & " WHERE brand = '" & brand1 & "' AND application = '" & season1 & "'"

if len(brand2) > 2 then 
     sql = sql & " or brand = '" & brand2 & "' AND application = '" & season2 & "'"

if len(brand3) > 2 then 
    sql = sql & " or brand = '" & brand3 & "' AND application = '" & season3 & "'"

if len(brand4) > 2 then 
    sql = sql & " or brand = '" & brand4 & "' AND application = '" & season4 & "'"

if len(brand5) > 2 then 
    sql = sql & " or brand = '" & brand5 & "' AND application = '" & season5 & "'"

set Dataconn = Server.CreateObject("ADODB.Connection") 
Dataconn.Open "database-in"
set DataTable = Server.CreateObject("ADODB.recordset")
DataTable.Open sql, Dataconn

而且它似乎不起作用。请注意，用户能够插入多达5个不同的参数(如您所见)，以便在数据库中搜索产品。因此，如果你有任何关于如何让这项工作感觉自由建议的进一步信息。

Answer 1

从字符串连接创建SQL是非常不明智的，如果这些字符串部分来自用户输入，甚至是直接的dangerous。

针对这种情况，数据库库(如ADODB )提供了commands和parameters。它们使用带有占位符的固定SQL字符串，无论用户提供的值是什么，库都会确保不会发生任何不好的事情。

这也意味着我们可以预先准备一条SQL语句，并在页面的整个生命周期中多次重用它。

Dim Conn ' As ADODB.Connection
Dim Cmd  ' As ADODB.Command

Set Conn = Server.CreateObject("ADODB.Connection")
Set Cmd = Server.CreateObject("ADODB.Command")

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\path\to\your\database.accdb;"

' prepare a reusable command with parameters (i.e. placeholders)
With Cmd
  Set .ActiveConnection = Conn
  .CommandType = adCmdText
  .CommandText = "SELECT Field1, Field2, Field3 WHERE brand = @brand AND application = @season"

  ' set up the parameters for each placeholder
  '  - use proper datatypes here, as per your DB
  '  - varchar types need a defined length
  .Parameters.Append .CreateParameter("@brand", adVarChar, , 50)
  .Parameters.Append .CreateParameter("@season", adVarChar, , 100)
End With

' helper function that operates the Command object and returns a RecordSet
Function SearchTyres(brand, season)
  Cmd.Parameters("@brand", brand)
  Cmd.Parameters("@season", season)
  Set SearchTyres = Cmd.Execute
End With

能够在代码中使用adCmdText或adVarChar等特定于ADODB的常量是很方便的。要让它们在任何地方都可用而不会造成麻烦，您需要在global.asa文件中声明ADODB类型库(如果没有，则创建一个)，并将以下代码添加到文件的顶部：

<!--metadata 
    type="TypeLib" 
    name="Microsoft ActiveX Data Objects 6.1 Library" 
    uuid="B691E011-1797-432E-907A-4D8C69339129"
    version="6.1"
-->

现在，您可以在页面中使用此代码，例如：

If Len(brand1) > 2 Then
  With SearchTyres(brand1, season1)
    ' ...let's do something with the RecordSet
    While Not .EOF
      Response.Write Server.HTMLEncode(!Field1) & "<br>"
      .MoveNext
    Wend
  End With
End If

备注

类型库不要做设置--总是写出你想要设置类型库的字段并不是严格必要的，但是如果你不这样做，那么你必须自己定义所有的常量，比如，这比它的

• is SELECT *要麻烦得多，简写为adVarChar forDim Rs = SearchTyres(...)有了R‘..。End With
• Rs!Field1是Rs.Fields("Field1")的便捷简写形式。在With Rs块中，Rs本身是可选的，因此一个普通的!Field1实际上是!Field1，它可以帮助在MS Office产品(如Word)的VBA IDE中创建代码。使用工具/引用引用相同的ADODB类型库。VBA和VBS不是100代码兼容的，但是VBA集成开发环境有智能感知，一个合适的调试器，使用相同的对象，代码可以以最小的改动传输到ASP。

Answer 2

因为其他人都在忙于抨击你，而不是真正回答你如何构建sql字符串的问题：

<%

    SqlStr = "SELECT * FROM Tyres WHERE 1 = 1 " 
    If Len(brand1) > 2 OR  Len(brand2) > 2 OR  Len(brand3) > 2 OR  Len(brand4) > 2 OR  Len(brand5) > 2 Then
        BrandInStr = " AND brand IN("
            If Len(brand1) > 2 Then
                BrandInStr = BrandInStr & "'" & brand1 & "',"    
            End If          
            If Len(brand2) > 2 Then
                BrandInStr = BrandInStr & "'" & brand2 & "',"    
            End If              
            If Len(brand3) > 2 Then
                BrandInStr = BrandInStr & "'" & brand3 & "',"    
            End If              
            If Len(brand4) > 2 Then
                BrandInStr = BrandInStr & "'" & brand4 & "',"    
            End If                  
            If Len(brand5) > 2 Then
                BrandInStr = BrandInStr & "'" & brand5 & "',"    
            End If  
        BrandInStr = Left(BrandInStr,Len(BrandInStr)-1)             
        BrandInStr = BrandInStr & ") "
    End If
    If Len(season1) > 2 OR  Len(season2) > 2 OR  Len(season3) > 2 OR  Len(season4) > 2 OR  Len(season5) > 2 Then
        SeasonInStr = " AND Season IN("
            If Len(Season1) > 2 Then
                SeasonInStr = SeasonInStr & "'" & Season1 & "',"    
            End If          
            If Len(Season2) > 2 Then
                SeasonInStr = SeasonInStr & "'" & Season2 & "',"    
            End If              
            If Len(Season3) > 2 Then
                SeasonInStr = SeasonInStr & "'" & Season3 & "',"    
            End If              
            If Len(Season4) > 2 Then
                SeasonInStr = SeasonInStr & "'" & Season4 & "',"    
            End If      
            If Len(Season5) > 2 Then
                SeasonInStr = SeasonInStr & "'" & Season5 & "',"  
            End If  
        SeasonInStr = Left(SeasonInStr,Len(SeasonInStr)-1)              
        SeasonInStr = SeasonInStr & ") "
    End If

    SqlStr = SqlStr & BrandInStr & " " & SeasonInStr

%>

但是，如果您的变量作为单个逗号分隔的字符串(而不是编号)传递，则会容易得多

<%

    SqlStr = "SELECT * FROM Tyres WHERE 1 = 1 "

    If Len(Trim(Replace(Replace(Brand," ",""),",","") > 0 Then
       SqlStr = SqlStr & "AND Brand In('" & Replace(Brand,",","','") & "') "
    End If


    If Len(Trim(Replace(Replace(Season," ",""),",","") > 0 Then
       SqlStr = SqlStr & "AND Season In('" & Replace(Season,",","','") & "') "
    End If

%>