Active Server Corner
Quick Site Search
What's Inside
Home
Activity
Books
Excerpts
In-Depth
CodeLibrary
Columns
ActiveTalk
COMSutra
Cornered!
My 3 Cents
Square ONE
Downloads
Events
FAQ's
Jobs
Search
Tools
Product Reviews
Tutorials
Site Info
About Us
Advertising Info
Contact Us
Privacy Policy
Terms of Use
Write for Us
  CodeLibrary @ Active Server Corner   The source for source code

Dealing with apostrophes in SQL strings

by Manohar Kamath
July 26th, 1999

Contains: ASP, SQL

If you are using SQL strings in your database operations, chances are you have come across problem with strings in SQL statement. One apostrophe screws up the SQL string, causing the SQL statement to fail.

Assume you have a SQL string:

lsSQL = "SELECT * FROM tUsers WHERE LastName='" & lsLastName & "' "

When lsLastName contains a name like O'Brien, the final string becomes

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

The second ' ends the SQL statement, leaving the rest of the string dangling. The solution is easy: Replace each ' in O'Brien with two 's, something like O''Brien, so the final SQL looks like:

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

The second ' "escapes" the third ', thus leaving the whole SQL statement complete.

In VBScript, use the Replace() function

lsLastName = Replace(lsLastName, "'", "''")

And in JavaScript/JScript, use the replace() method of the string class and use a regular expression to find the 's within the string.

var lsLastName = "O'Brien";
var lsRegExp = /'/g;
lsLastName = String(lsLastName).replace(lsRegExp, "''")

You can also write a function and "correct" strings anywhere on the page.

function quoteReplace(psString)
{
  var lsRegExp = /'/g;
  return String(psString).replace(lsRegExp, "''");
}

Use these scripts for every text field that will go into a SQL query.






Copyright 1997-2000 Active Server Corner. All rights reserved.

Sponsored by Coastline Web Hosting in Santa Barbara, California