Codefixer - The Apostrophe Bug

In an SQL statement text is enclosed by an apostrophe at the start and an apostrophe at the end. Consider the SQL Insert statement below.

INSERT INTO TableName(TableFieldName) VALUES ('Hi folks, I'm a developer')

On the face of it this looks a perfectly formed SQL statement. Look more closely and you'll notice the apostrophe in I'm. SQL will interpret this as the end of the string. When it encounters the following text and another ' an error will occur.

To allow an apostrophe mark to be inserted into a database, simply double-up all occurences of the apostrophe mark. SQL will see two apostrophes together and treat them as a single apostrophe and at the same time realise that this doesn't signify the end of the string.

Below our example uses the Replace function to search the variable MyVariable for all occurrences of the apostrophe. If it finds a single apostrophe it will double it up.

<%
Dim MyVariable
MyVariable="My name is Michael, I'm a developer"
MyVariable =Replace(MyVariable,"'","''") 'its hard to read this so " ' " , " ' ' "
%>

So once you have doubled up the apostrophe you could use the value of MyVariable in an SQL statement such as the following without producing an error message.

<%
Dim MyVariable, SQL
MyVariable="My name is Michael, I'm a developer"
MyVariable =Replace(MyVariable,"'","''")
SQL = "INSERT INTO TableName(TablefieldName) VALUES ('" & MyVariable & "')"

'you could then add code here to open a connection to your database and execute
'the SQL statement.
%>

Here's a common scenario. Suppose you create a form with a textbox called Message that visitors can enter text into. When your visitor enters text you want your code that will receive the text and store in your database to be able to handle an apostrophe. The solution is to receive the textbox value and assign to a variable and then use the Replace function to double-up all apostrophes. This is demonstrated in our code below.

<%
Dim MyVariable, SQL
MyVariable=Request.Form("Message")
MyVariable =Replace(MyVariable,"'","''")
SQL = "INSERT INTO TableName(TablefieldName) VALUES ('" & MyVariable & "')"

'you could then add code here to open a connection to your database and execute
'the SQL statement
%>