Dreaded Single Quote Problem

One of the biggest problems new ASP developers have is with single quotes (I know I did). When a user enters a name like "O'Neal" and then you try to write it to the database you'll get an error. It's not as hard as you might think it is. When 2 single quotes are entered SQL accepts them as 1 quote. There are two easy ways to go about it...

USE A FUNCTION
Function SQLQuote(var)
If InStr(var, "'") <> 0 Then
var = Replace(var, "'", "''")
End If
SQLQuote = var
End Function

Then use is like this: SqlQuote(txtPubTitle)

or

REPLACE IT

txtPubTitle = replace(Request.Form("txtPubTitle"),"'","''")

note: If you're using Stored Procedures you won't have to worry about this.

Source: Chuck Dearbeck
Viewed 10675 times