Microsoft KB Archive/178070

= How To Handle Quotes and Pipes in Concatenated SQL Literals =

Article ID: 178070

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q178070





For a Microsoft Visual Basic .NET version of this article, see 311023.



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.

NOTE: Microsoft Visual Basic 6.0 and later products and Microsoft Office 98 and later products come with a Replace function built-in



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' this can result in the following error message when you execute the SQL statement:

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

One solution is to replace the apostrophe delimiter with quotes ("), such as:  SQL = "SELECT * FROM Employees WHERE LastName=""" & LName & """" However, the user could easily type O"Brien by mistake (forgetting to release the SHIFT key when typing the apostrophe) and the problem reappears. In addition, SQL Server uses " to delimit table and field names. If the user-supplied value exceeds the maximum length of an identifier name, SQL Server will return a syntax error.

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

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

FindFirst Method and Delimiters
The previous section shows the technique of doubling the delimiter when building SQL strings. However, this technique does not work when passing search criteria to the FindFirst method. You have to replace the embedded quote with an expression concatenating chr(39) into the literal:

Original criteria that doesn't work:

LastName = 'O'Brien' Criteria that doesn't work with FindFirst:

LastName = 'O''Brien' Criteria that does work with FindFirst:

LastName = 'O' & chr(39) & 'Brien'

User Types the Pipe Symbol
If the user types the pipe symbol (|), such as:

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' and you are querying a Jet database, it can cause either the "Syntax Error" given above or the following error:

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

The pipe symbol causes problems because Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as:

SELECT "|LastName|, |FirstName|" FROM Employees This was considered easier for beginner users to learn than concatenation when building ad hoc queries through the Access Query designer. However, when used inadvertently in building a SQL statement, it can result in an error.

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 these problems can be addressed through substring replacement. The sample functions, ReplaceStr, SQLFixup, JetSQLFixup, and FindFirstFixup 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) 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 SQLFixup should be used if your SQL statement is going to be used with Jet SQL pass-through queries or with ODBCDirect, RDO, or ADO to a non-Jet back- end database:

LName contains: O'Brien SQL = "SELECT * FROM Employees WHERE LastName='" & _ SQLFixup(LName)& "'" SQL now contains: SELECT * FROM Employees WHERE LastName='O''Brien' JetSQLFixup should be used if Jet is your database back-end, or if doing a non-Pass-through query to an ODBC datasource:

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'

FindFirstFixup should be used if using the FindFirst method of a DAO Recordset. It can also be used wherever the JetSQLFixup function is used:

LName contains: "O'Brien" Criteria = "LastName = '" & FindFirstFixup(LName) & "'" Criteria now contains: LastName = 'O' & chr(39) & 'Brien'

