Problems with special characters in database queries

Problems with special characters in database queries

This is often the case when querying a database:
For example, you want to query his user name and his password in a user database, but there are special characters in the name and password that the user uses, such as single quotes, "|" Number, double quotation marks, or hyphens "&".
For example his name is 1 "test, password is a|&900
When you execute the following query statement, you will definitely get an error:
SQL = "SELECT * from SecurityLevel WHERE uid=" "& UserID &" ""
sql = SQL & "and pwd=" "& Password &" "
Because your SQL will 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 wrong. Here are a few of the functions that are designed 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 &" ", 0)
Findfirstfixup = Replacestr (Temp, "|", "" "& Chr (124) &" ", 0)
End Function
With the above functions, before you execute a SQL, use the
SQL = "SELECT * from SecurityLevel WHERE uid=" "& Sqlfixup (UserID) &" "
sql = SQL & "and pwd=" "& Sqlfixup (Password) &" ".

Finish this article!