HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 5.0
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions for Windows, version 4.0
• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
• Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0

SUMMARY

Building concatenated SQL statements based on user-typed text values can result in invalid SQL statements. This article provides a solution to the problem of building concatenated SQL.

MORE INFORMATION

When building concatenated SQL statements, you can run into the following problems based on incorporating user-typed text into the SQL statement:

User Types the Delimiter Character

If the user types the same character you use to delimit the text field, such as:

   LName contains: O'Brien

   SQL = "SELECT * FROM Employees WHERE LastName='" & LName & "'"

   SQL now contains:
   SELECT * FROM Employees WHERE LastName='O'Brien'

   Run-time error 3075
   Syntax error in query expression '...'

   SQL = "SELECT * FROM Employees WHERE LastName=""" & LName & """"

The solution is to replace the apostrophe in the variable with two apostrophes so that SQL contains:

   SELECT * FROM Employees WHERE LastName='O''Brien'

User Types the Pipe Symbol

   Password contains: A2|45

   SQL = "SELECT * FROM SecurityLevel WHERE UID='" & UserID & "'"
   SQL = SQL & " AND PWD='" & Password & "'"

   SQL now contains:
   SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
   AND PWD='A2|45'

   Run-time error 3061
   Too few parameters. Expected n.

   SELECT "|LastName|, |FirstName|" FROM Employees

The solution is to replace the pipe symbol with a concatenated expression so that SQL contains:

   SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
   AND PWD='A2' & chr(124) & '45'

Implementing the Solution

The solution to both these problems can be addressed via substring replacement. The sample functions, ReplaceStr, SQLFixup and JetSQLFixup, are provided below to illustrate the technique.

WARNING: Microsoft provides code examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This code is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. For product versions that don't not support the line-continuation character, remove the underscore and combine that line with the next lines as a single statement when re- creating this code.

   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)
     SQLFixup = ReplaceStr(Temp, "|", "' & chr(124) & '", 0)
   End Function

   LName contains: O'Brien

   SQL = "SELECT * FROM Employees WHERE LastName='" & _
         SQLFixup(LName) & "'"

   SQL now contains:
   SELECT * FROM Employees WHERE LastName='O''Brien'

   UserID cntains: JohnDoe
   Password contains: A2|4'5

   SQL = "SELECT * FROM SecurityLevel WHERE UID='" & _
         JetSQLFixup(UserID) & "' AND PWD='" & JetSQLFixup(Password) & "'"

   SQL now contains:
   SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
   AND PWD='A2' & chr(124) & '4''5'

REFERENCES

Microsoft Jet Database Engine Programmer's Guide

Visual Basic Help topic: InStr

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147687
   TITLE     : HOWTO: Query for Literal Special Characters in a Where
               Clause

Keywords          : VB4WIN vb5all vb5howto
Version           : WINDOWS:1.0,1.1,2.0,3.0,4.0,5.0,7.0,97
Platform          : WINDOWS
Issue type        : kbhowto