how to do a SQL insert with a value with an apostrophe in it

Question

what is the correct SQL syntax to insert a value with an apostrophe in it.

Insert into Person  
  (First, Last)  
Values  
  'Joe',
  'O'Brien'

i keep getting an error as i thinks the apostrophe after the O is the ending tag for the value

Answer 1

You need to escape the apostrophe,

In t-sql this is with a double apostrophe, so your insert statement becomes:

Insert into Person
(First, Last)
Values
'Joe', 'O''Brien'

Answer 2

Use a backtick (on the ~ key) instead;

`O'Brien`

Answer 3

Double up your apostrophe:

Insert into Person
(First, Last)
Values
('Joe', 'O''Brien')

Answer 4

You just have to double up on the single quotes...

insert into Person (First, Last)
values ('Joe', 'O''Brien')

Answer 5

Because a single quote is used for indicating the start and end of a string, you need to escape it. The short answer is to use two single quotes - '' - in order for a SQL database to store the value as '.

Look at using REPLACE to sanitize incoming values:

• Oracle REPLACE
• SQL Server REPLACE
• MySQL REPLACE
• PostgreSQL REPLACE

You want to check for '''', and replace them if they exist in the string with '''''' in order to escape the lone single quote.