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.

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.

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.

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.