Thursday, November 29, 2007

Replace apostrophe in PHP

When creating SQL statements, string values are delimited using apostrophes. So what happens when there is an apostrophe in the data you are trying to insert? A SQL error will occur if, for example, the value of a variable included an apostrophe. Because you do not know what the user will type in, you must assume they are entering all sorts of bad data.

To insert an apostrophe into the database using SQL you need to put two apostrophes in the text where you want just one. For example, to insert the phrase "what's it?" into a database, the SQL code looks like:
INSERT INTO mytable (phrases) VALUES ('what''s it?')

In PHP there is a string function which allows you to replace on the variable easily: str_replace This function replaces one value with another in a string. So before you insert data in the database you should replace all single apostrophes with double-apostrophes. For the example variable, the PHP code is:
$comment = str_replace("'","''",$comment);

9 comments:

Anonymous said...

This is a great tip. This is the most effective method i have found. Simple.

Anonymous said...

Glad your site came up first in the search, it was going nuts trying to figure this out. Very quick and simple solution.

Adela on February 6, 2010 at 4:37 PM said...

Thanks a lot. Great solution. Ales Loziak (CZ).

Anonymous said...

THANKS !!!

Will Jones on November 4, 2010 at 4:39 AM said...

Absolutely works perfect!

Anonymous said...

Thanks a lot!

Anonymous said...

Many thanks for that info. It was just what I needed, straight to the point and it works. Regards - John

Anonymous said...

You are awesome.....

Ram on May 24, 2012 at 5:50 AM said...

addslashes is another php function which does the same job. Pretty neat too. http://php.net/manual/en/function.addslashes.php

 

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.
Sign up for PayPal and start accepting credit card payments instantly. http://www.emailcashpro.com
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template