Welcome to WebmasterWorld Guest from 34.228.143.13

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Apostrophe problem in updating MYSQL

     
12:44 am on Mar 17, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 14, 2004
posts:80
votes: 0


I have a form that updates a record in my MYSQL database. It works great for all the records except the records which have an apostrophe in one of the fields (it's a database containing records of books and some titles have apaostophes). I tried using a? as a placeholder in the INSERT query and then made an array like this:

INSERT into db (title, author, year) Values (?,?,?),
array($_POST['title'],($_POST['author'],($_POST['year']);

It works if I just have one place holder but as soon as I add more it doesn't. Also is there another way of correcting the apostrophe problem?
Any help appreciated.

1:42 am on Mar 17, 2005 (gmt 0)

Full Member

10+ Year Member

joined:Feb 23, 2005
posts:282
votes: 0


apostrophe's are reserved characters in sql queries, but if you filter your information before you insert it then it will process ok:

$title = addslashes($_POST['title']);
$author = addslashes($_POST['author']);
$year = addslashes($_POST['year']);

INSERT into db (title, author, year) Values ($title,$author,$year)

You can also use the function mysql_escape_string() to filter your data before inserting it.

(Note: beware magic quotes, if they are turned on then your ' chars are already escaped and addslashes will just add extra slashes where you won't want them).

6:04 am on Mar 17, 2005 (gmt 0)

New User

10+ Year Member

joined:Oct 6, 2004
posts:5
votes: 0


just to add a little bit to previous answer. check for magic quotes before using addslashes()

if (!get_magic_quotes_gpc()) {
$title = addslashes($_POST['title']);
$author = addslashes($_POST['author']);
$year = addslashes($_POST['year']);
}

INSERT into db (title, author, year) Values ($title,$author,$year)

9:37 am on Mar 17, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
posts:3719
votes: 0


Also, don`t forget to use stripslashes when retrieving your data.

dc

5:12 pm on Mar 17, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 14, 2004
posts:80
votes: 0


Thanks very much to all of you.

Adding this to my INSERT query solved the problem

if (!get_magic_quotes_gpc()) {
$title = addslashes($_POST['title']);
$author = addslashes($_POST['author']);
$year = addslashes($_POST['year']);
}

I didn't need the stripslashes when retrieving. I don't know why.

I continue to be in awe of all the help I get in this forum. It's such a relief to know you can get help when you are stuck. I hope there will come a day when I can be of help to someone.

5:22 pm on Mar 17, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:June 24, 2004
posts:147
votes: 1


Also, don`t forget to use stripslashes when retrieving your data.

Don't use stripslashes on data read from a database unless you somehow accidently stored the information wrong (perhaps by using addslashes on text which already had the slashes escaped).

-- Roger

5:35 pm on Mar 17, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 29, 2004
posts:194
votes: 0


If you used PEAR's DB module, you wouldn't have to worry about this problem...example using autoExecute (a rad function):

$Results = $db->autoExecute("db",$_POST,DB_AUTOQUERY_INSERT);

This function takes a name => value array (like in $_POST) and makes your SQL statement for you, then does an insert. It also takes care of slashes and all that stuff that irritates a lot of developers.

Another way to do it:

$SqlStmt = $db->prepare("INSERT into db (title, author, year) Values (?,?,?)");
$Values = array($_POST['title'],$_POST['author'],$_POST['year']);
$Results = $db->execute($SqlStmt,$Values);

[pear.php.net...]

PEAR rocks!

8:12 pm on Mar 17, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
posts:3719
votes: 0


Glad you got it working ok.

dc

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members