Troubleshoot special characters in the execution of database queries

This is often the case when querying a database:
For example, to query a user's database for his username and his password,
But there are special characters in the name and password that the user uses, such as single quotes, "|" Resolution
Double quotes or hyphen "&"
For example, his name is 1 ' test, and the password is a|&900.
When you execute the following query statement, you will certainly get the wrong one:
SQL = "SELECT * from SecurityLevel WHERE uid= '" & UserID & "'"
sql = SQL & "and pwd= '" & Password & "'"
Because your SQL is going to be like this:
SELECT * from SecurityLevel WHERE uid= ' 1 ' test '
and pwd= ' a|&900 '
In sql, the "|" For the Split field, it's obviously going to go wrong. Now provide a few of the following functions
Something designed to deal with 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 () & '", 0)
Findfirstfixup = Replacestr (Temp, "|", "' & Chr (124) & '", 0)
End Function
With the above several functions, before you execute a SQL, please use the
SQL = "SELECT * from SecurityLevel WHERE uid= '" & Sqlfixup (UserID) & "'"
sql = SQL & "and pwd= '" & Sqlfixup (Password) & "'"