[sql] 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.

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

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

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

try this

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

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

use double apostrophe inside of single apostrophe, it will work

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.

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

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

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)

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.