Problems with special characters in 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 the name and password that the user is using has a special
characters, such as single quotes, "|" Number, double quotes or hyphen "&".
For example, his name is 1 "test, the 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 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 specifically for dealing with these headaches database escape characters:
Copy Code code 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 (+) &" ", 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) & ""
