CODEWITHSUNDEEP
sqlsql-server-2005
Nazmul
Nazmul

Reputation: 7218

Way to insert text having ' (apostrophe) into a SQL table

While I was trying the following SQL command , I got sql error.

INSERT INTO exampleTbl VALUES('he doesn't work for me')

where doesn't contain the apostrophe.

What is the way to insert text having ' (apostrophe) into a SQL table.

Upvotes: 40

Views: 205778

Answers (8)

Laurence Gonsalves
Laurence Gonsalves

Reputation: 143154

In SQL, the way to do this is to double the apostrophe:

'he doesn''t work for me'

However, if you are doing this programmatically, you should use an API that accepts parameters and escapes them for you automatically. Programmatically escaping and using string concatenation to assemble a query yourself is a sure way to end up with SQL injection vulnerabilities.

Her daughter is named Help I'm trapped in a driver's license factory.

Upvotes: 96

Kirk
Kirk

Reputation: 11

I know the question is aimed at the direct escaping of the apostrophe character but I assume that usually this is going to be triggered by some sort of program providing the input.

What I have done universally in the scripts and programs I have worked with is to substitute it with a ` character when processing the formatting of the text being input.

Now I know that in some cases, the backtick character may in fact be part of what you might be trying to save (such as on a forum like this) but if you're simply saving text input from users it's a possible solution.

Going into the SQL database

$newval=~s/\'/`/g;

Then, when coming back out for display, filtered again like this:

$showval=~s/`/\'/g;

This example was when PERL/CGI is being used but it can apply to PHP and other bases as well. I have found it works well because I think it helps prevent possible injection attempts, because all ' are removed prior to attempting an insertion of a record.

Upvotes: 0

sunil guragol
sunil guragol

Reputation: 201

insert into table1 values("sunil''s book",123,99382932938);

use double apostrophe inside of single apostrophe, it will work

Upvotes: 2

Daniel Draganov
Daniel Draganov

Reputation: 101

$value = "he doesn't work for me";

$new_value = str_replace("'", "''", "$value"); // it looks like  " ' "  , " ' ' " 

INSERT INTO exampleTbl (`column`) VALUES('$new_value')

Upvotes: 9

tika
tika

Reputation: 34

yes, sql server doesn't allow to insert single quote in table field due to the sql injection attack. so we must replace single appostrophe by double while saving.

(he doesn't work for me) must be => (he doesn''t work for me)

Upvotes: -1

Muhammad Asif Mahmood
Muhammad Asif Mahmood

Reputation: 1710

you can use backslash '\' if you want to display a single quote in your text.

INSERT INTO exampleTbl VALUES('He doesn(\')t') ;

Upvotes: -6

Ed B
Ed B

Reputation: 6054

INSERT INTO exampleTbl VALUES('he doesn''t work for me')

If you're adding a record through ASP.NET, you can use the SqlParameter object to pass in values so you don't have to worry about the apostrophe's that users enter in.

Upvotes: 13

FosterZ
FosterZ

Reputation: 3911

try this

INSERT INTO exampleTbl VALUES('he doesn''t work for me')

Upvotes: 6

Related Questions