Wednesday, October 24, 2007

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!

2 comments:

Aaron on December 9, 2011 at 2:31 AM said...

Hi,

Just wanted to know how you implement this code into a SQL string, here is my current code:
("SELECT ProjectId FROM osaka_Projects WHERE ProjectName = '" & TextBox1.Text & "'")

Want to replace all apostrophes entered into "textbox1".

Thanks

xiaoyu on December 9, 2011 at 9:14 AM said...

use the REPLACE method e.g.:
inputSQL.Replace("'", "''");

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template