熱點推薦:
您现在的位置: 電腦知識網 >> 電腦故障 >> 正文

數據庫查詢中的特殊字符的解決問題

2013-11-12 00:01:33  來源: 電腦故障 

  在進行數據庫的查詢時會經常遇到這樣的情況
  例如想在一個用戶數據庫中查詢他的用戶名和他的密碼但恰好該用戶使用的名字和密碼中有特殊的字符例如單引號|雙引號或者連字符&
  例如他的名字是test密碼是A|&
  這時當你執行以下的查詢語句時肯定會報錯
  SQL = SELECT * FROM SecurityLevel WHERE UID= & UserID &
  SQL = SQL & AND PWD= & Password &
  因為你的SQL將會是這樣
  SELECT * FROM SecurityLevel WHERE UID=test AND PWD=A|&
    在SQL中|為分割字段用的顯然會出錯了現在提供下面的幾個函數 專門用來處理這些頭疼的東西
  Function ReplaceStr (TextIn ByVal SearchStr As String _
  ByVal Replacement As String _
  ByVal CompMode As Integer)
   Dim WorkText As String Pointer As Integer
   If IsNull(TextIn) Then
    ReplaceStr = Null
   Else
    WorkText = TextIn
    Pointer = InStr( WorkText SearchStr CompMode)
    Do While Pointer >
     WorkText = Left(WorkText Pointer ) & Replacement & _
     Mid(WorkText Pointer + Len(SearchStr))
     Pointer = InStr(Pointer + Len(Replacement) WorkText SearchStr CompMode)
    Loop
    ReplaceStr = WorkText
   End If
  End Function
  Function SQLFixup(TextIn)
   SQLFixup = ReplaceStr(TextIn )
  End Function
  Function JetSQLFixup(TextIn)
   Dim Temp
   Temp = ReplaceStr(TextIn )
   JetSQLFixup = ReplaceStr(Temp | & chr() & )
  End Function
  Function FindFirstFixup(TextIn)
   Dim Temp
   Temp = ReplaceStr(TextIn & chr() & )
   FindFirstFixup = ReplaceStr(Temp | & chr() & )
  End Function
    有了上面幾個函數後當你在執行一個sql前請先使用
  SQL = SELECT * FROM SecurityLevel WHERE UID= & SQLFixup(UserID) &
  SQL = SQL & AND PWD= & SQLFixup(Password) &

From:http://tw.wingwit.com/Article/Fault/201311/10498.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.