Apostrophe problem in updating MYSQL

[Ann_G]

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.

[ironik]

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).

[daku]

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)

[dreamcatcher]

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

dc

[Ann_G]

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.

[jadebox]

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

[jusdrum]

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!

[dreamcatcher]

Glad you got it working ok.

dc