Quotes in SQL (Those Darn Things!)

Quotes in SQL (Those Darn Things!)


August 9, 1999

Watkins

corner.jpg (739 bytes) Quotes in SQL (Those Darn Things!) corner2.jpg (786 bytes)

See this script in action: click here

About two months ago, I got done writing a large application used to look up listings for different towns. The thing was, I never actually thought of the fact that some towns have " ' " (quotes) in their names.   This was not good, considering the site was live, and I had an error. For example:

I would have a town, and its name would be: John's Town (not real). What ended up happening was when the query string was created, the single quote was added to the string and it looked like this:

"SELECT * FROM table WHERE town='John's Town';" An error will occur when you execute this SQL statement, saying unexpected ending.

I needed to solve this quick, and quick I did. I used the following function to search through the string and put an extra quote in where needed.

<SCRIPT LANGUAGE="VBScript" RUNAT="SERVER">
Function convertToSQL(strStmt)
    For i = 1 to len(strStmt)
        If Mid( strStmt, i, 1 ) = "'" Then strOut = strOut & "'"
        strOut = strOut & Mid(strStmt, i,1)
    Next
    convertToSQL = strOut
End Function
</SCRIPT>

In an SQL statement you do not need to enclose the value in quotes if it is a number. However, when you are using a string, you need to enclose the value.

Example:

Dim town = "John's Town"
strQuery = "SELECT * FROM table WHERE town='" & convertToSQL(town) & "';"


The resulting string would be as followed.

"SELECT * FROM table WHERe town='John''s Town'"

Watkins
corner.jpg (739 bytes) Quotes in SQL (Those Darn Things!) corner2.jpg (786 bytes)

Back to the article: Click here

Please enter a string that contains single quotes in the text box below. Normal output will display the string without the function described on the previous page. Modified output runs the string through the function and adds single quotes as needed.



Dim strQuery
Dim strTextBox
strTextBox = request.form("strTown")
strQuery = "SELECT * FROM table WHERE town='" & strQuery & "';"


OUTPUT: <%=strOutput%>