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.