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 Code the 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 )&"""