The problem of executing special characters in database queries in ASP

Data | database | problem | When you perform a query for a database, you often encounter situations like this:
　　 For exampleYou want to query his user name and his password in a user database, but it happens that the user has a special character in the name and password, such as single quotes, "|" Resolution

Double quotes or hyphen "&".

　　 For exampleHis name is 1 ' test, and 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:

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) & "'"