Dealing with apostrophes in SQL strings

In many applications, the developer has side-stepped the potential use of the apostrophe in some of the text fields. So when adding values to a table, be aware that problems may be caused by embedded apostrophes in a string.

Consider the SQL Insert statement below.

INSERT INTO table_name (table_field_name) VALUES (‘O’reill’, ‘92314567’)

Notice that there is an apostrophe in the text “O’reill”. In SQL, the apostrophe is an illegal character. It is interpreted as a string delimiter or the end of the string. So when it encounters to the text and detects another apostrophe, an error will occur.

A very simple solution to solve the problem: to allow apostrophes to be inserted into a database, simply double-up all occurrences of the apostrophes. So the final SQL will look like this:

INSERT INTO table_name (table_field_name) VALUES (‘O’’reill’, ‘92314567’)

In VB.NET, in order to convert the single enclosed apostrophe to two apostrophes, you can use Replace statement to accomplish the replacement.

"'" & Replace (txtName.text, "'", "''") & "'"

Good Luck!