Solve the Special Character Problem in asp db query execution

When you query a database, you will often encounter this situation:

For example, you want to query the user name and password in a user database, but the user name and password exactly contain 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 | & 123'
In SQL, "|" is used to separate fields, and an error occurs obviously. The following functions are provided to handle these headaches:

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 executing an SQL statement, use:
SQL = "SELECT * FROM SecurityLevel WHERE UID = '" & SQLFixup (UserID )&"'"
SQL = SQL & "AND PWD = '" & SQLFixup (Password )&"'"