Solutions to problems caused by special characters in database queries

Special characters in Database Query
When you query a database, you will often encounter the following situations:
For example, if you want to query the user name and password in a user database, but the user's name and password have a special
Characters, such as single quotes, "|", double quotation marks, or hyphen "&".
For example, his name is 1 "test, and his password is A | & 900
When you execute the following query statement, an error is returned:

SQL = "SELECT * FROM SecurityLevel WHERE UID =" "& UserID &"""
SQL = SQL & "AND PWD =" "& Password &"""
Because your SQL statement will be like this:

SELECT * FROM SecurityLevel where uid = "1" test "and pwd =" A | & 900"

In SQL, "|" is used to separate fields, and an error occurs obviously. The following functions are provided to deal with these headaches:
Copy codeThe Code is as follows:
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 (1, WorkText, SearchStr, CompMode)
Do While Pointer> 0
WorkText = Left (WorkText, Pointer-1) & 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, ",", 0)
End Function
Function JetSQLFixup (TextIn)
Dim Temp
Temp = ReplaceStr (TextIn, "", ", 0)
JetSQLFixup = ReplaceStr (Temp, "|", "" & chr (124) & "", 0)
End Function

Function FindFirstFixup (TextIn)
Dim Temp
Temp = ReplaceStr (TextIn, "", "" & chr (39) & "", 0)
FindFirstFixup = ReplaceStr (Temp, "|", "" & chr (124) & "", 0)
End Function

With the above functions, before you execute an SQL statement, use

SQL = "SELECT * FROM SecurityLevel WHERE UID =" "& SQLFixup (UserID )&"""
SQL = SQL & "AND PWD =" "& SQLFixup (Password )&"""