Knowledge Base Articles » KB100206: SQL Queries and the Single Quotation Symbol Problem.

Consider the following SQL query used in an ASP program:

Dim strInsertQuery
strInsertQuery = "INSERT INTO Users " & _
"(EmailAddress, Date, Subject, Message) " & _
"VALUES('"& _
strEmailAddress & "', '" & _
dtmDate & "', '"& _
strEmailSubject & "', '" & _
strMessage & "')"

While the INSERT may seem straightforward, there is a lurking time bomb. Here is the text inside strMessage:

"Mr. Barker’s 1.1 gig hard drive has died. Can you install a 20 gig to replace it?"

In SQL queries, strings are enclosed within a pair of single quotes. Look carefully at the INSERT code and you can see that the string, strMessage, is appropriately preceded and followed by single quotes (although they may be hard to see because they appear next to the double quotes surrounding the strings). However, note that a single quote also appears inside the text of strMessage. Unfortunately, when you run the query, SQL will interpret the single quote inside the string as signifying the end of that string.

Depending on how SQL interprets the remaining portion of the string that occurs after the single quote, there are two possible outcomes.

1. You could end up with a truncated version of strMessage ("Mr. Barker") in the database.

2. You could get an error message. For example, here is an actual SQL server error message generated by the misuse of a single quote:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'. /test/billtest.asp, line 73

And here is an actual error message generated by Access:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((InStr(Book.Title,'Programmer's') > 0) OR (InStr(Book.Notes,'Programmer's') > 0)) OR ((InStr(Book.Title,'') > 0) OR (InStr(Book.Notes,'') > 0))'. /library/Books.asp, line 185

The challenge, therefore, is to have SQL recognize a single quote when it is used literally within a string text, in light of the fact that single quotes are used as delimiters for SQL strings.

Fortunately, there is a simple solution. Two single quotes in a row signify an escape sequence from the normal interpretation of the single quote character. When two single quotes appear together, they are interpreted by SQL as one literal single quote. All we need do, then, is replace any single quote with two single quotes in strings that we want interpreted literally by SQL. The following VBScript code snippet (provided by Mark Harr) performs this task. The final result is a string that can be recognized correctly by SQL Server, Oracle, and Microsoft Access.

Function StrQuoteReplace(strValue)
  // Replace any single quote in strValue with two single quotes.
  // The second argument to Replace consists of
  // one single quote enclosed in a pair of double quotes.
  // The third argument to Replace consists of
  // two single quotes enclosed in a pair of double quotes.
  StrQuoteReplace = Replace(strValue, "'", "''")
End Function

Of course, you do this as you create the SQL query and hence, before you call it, as illustrated below:

Dim strInsertQuery
strInsertQuery = "INSERT INTO Users " & _
"(EmailAddress, Date, Subject, Message) " & _
"VALUES('" & _
StrQuoteReplace(strEmailAddress) & "', '" & _
StrQuoteReplace(dtmDate) & "', '" & _
StrQuoteReplace(strEmailSubject) & "', '" & _
StrQuoteReplace(strMessage) & "')"