The solution of encountering Special characters in Database query

This article introduces the relevant knowledge of "the solution to encounter special characters in database query". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Special Character Problems in Database Query

When querying a database, you will often encounter such a situation:

For example, you want to query a user's username and password in a user database, but it happens that the user uses a special name and password.

Characters such as single quotes,"|"Sign, double quotes or hyphen"&."

For example, his name is 1"test, and his password is A.|&900

When you execute the following query statement, you will definitely report an error:

SQL = "SELECT * FROM SecurityLevel WHERE UID="" & UserID & """

SQL = SQL & " AND PWD="" & Password & """

Because your SQL will look like this:

SELECT * FROM SecurityLevel WHERE UID="1"test" AND PWD="A|&900"

In SQL,"|"For splitting fields, obviously something's wrong. The following functions are provided specifically to handle these headaches database escape characters:

The copy 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 a sql, use

SQL = "SELECT * FROM SecurityLevel WHERE UID="" & SQLFixup(UserID) & """

SQL = SQL & " AND PWD="" & SQLFixup(Password) & """

"Database query encounter special character solution" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!