最节俭的sql statement写法

一只优雅的猪

我知道这段sql statement不够完善... 因为这段sql statement可以存在很多可能性，想知道最节俭的写法....

请大家赐教

Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")


      Str = "SELECT name FROM customer WHERE "

      If NOT name = "" then
         Str = Str + "postcode = '"+ postcode +"'"
      End If         
      
      If NOT age = "" then
         Str = Str + "AND age = '"+ age +"'"           
      End If
      
      If NOT tel1 = "" then
         Str = Str + "AND tel1 = '"+ tel1 +"'"           
      End If

goatstudio

如果你的 name 是空的... 那么你的 code 就会出现问题了.
你的方法已经很好, 再想想看怎么安排 where 和 and 吧...

笨陈

Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")

Str = "SELECT name FROM customer WHERE 1 "

If name <> "" then
   Str = Str & "AND postcode = '" & postcode & "'"
End If         
      
If age <> "" then
   Str = Str &  "AND age = '" & age & "'"           
End If
      
If tel1 <> "" then
   Str = Str + "AND tel1 = '" & tel1 & "'"           
End If

kennynjc

Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")

Str = "SELECT name FROM customer WHERE 1<>2 "

If name <> "" then
   Str = Str & "AND postcode = '" & postcode & "'"
End If         
      
If age <> "" then
   Str = Str &  "AND age = '" & age & "'"           
End If
      
If tel1 <> "" then
   Str = Str + "AND tel1 = '" & tel1 & "'"           
End If

一只优雅的猪

原帖由 笨陈 于 14-6-2006 02:13 PM 发表
Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")

Str = "SELEC ...

WHERE 1??  可以这样的吗？

goatstudio

应该用 kennynjc 的方法, 因为 WHERE 1 的方法会导致 sql injection.
另外, 最好把 where 和 and 转成 variable.

charleschin

where (1=1)

kennynjc

原帖由 goatstudio 于 23-6-2006 02:19 AM 发表
应该用 kennynjc 的方法, 因为 WHERE 1 的方法会导致 sql injection.
另外, 最好把 where 和 and 转成 variable.

要小心类似的sql, 尤其是牵涉到system user 的query,会有安全漏洞哦。If I input "myvalue OR 1=1 OR ", then will somehow cause your query to return all records or able to login with any username. If can parameterize then will be better.