SQL uses a single quote (') character to mark the beginning and
end of a string. As such, using a string that contains a quote
can cause errors because your database thinks the string
is finished and expects SQL commands to come immediately after
it.
In order to get around this problem, when SQL encounters two
quotes in a row, it translates them into a single quote and
continues reading the string instead of interpretting the
quote as a string delimiter.
Note: This is the exact same behavior VBScript uses to deal
with the double quote (") as illustrated in
this tip.
To easily deal with this problem you can utilize a simple
function like the one that follows to automatically double
up your quote characters before passing your string to the
SQL parser.
Function DoubleUpSingleQuotes(strInput)
DoubleUpSingleQuotes = Replace(strInput, "'", "''")
End Function
If you have a tip you would like to submit, please send it to:
webmaster@asp101.com.
