Apostrophes in MySql update

[franwpd]

I am trying to update a MySql table with text that includes apostrophes. When I insert using the code below, it works perfectly.

mysql_query("INSERT INTO prayer VALUES ('$id', '$timestamp', '".mysql_real_escape_string($name)."', '$prayer', '$private', '$active')") or die (mysql_error());

Then I try to update the database and I get the results below:

//$name = mysql_real_escape_string($name); empties name field
//$name = addslashes($POST['name']); leaves capital letter only
//$name = stripslashes($POST['name']); Blank screen
//$name = str_replace"'", "''", $name); Blank screen
//$name = (str_replace"'", "''", $_POST['name'])); Blank screen

$sql = "UPDATE prayer SET id = '$id[$i]', timestamp = '$timestamp[$i]', '$name[i]',  prayer = '$prayer[$i]', private = '$private[$i]', active = '$active[$i]' WHERE id = '$id[$i]'";

What is my problem???

[franwpd]

I found something that works!

<?

$name = str_replace("'", "&#039;", $name);

$sql = "UPDATE prayer1 SET id = '$id[$i]', timestamp = '$timestamp[$i]', name = '$name[$i]', prayer = '$prayer[$i]', private = '$private[$i]', active = '$active[$i]' WHERE id = '$id[$i]'";

etc...
?>

[djjjozsi]

Create a function first:

<?php

function mres($string)
{
    if(get_magic_quotes_gpc()) {
        return mysql_real_escape_string (stripslashes($string));
    } else {
        return mysql_real_escape_string ($string);
    }
}
?>

and call on evry inputs which goes into your SQL query.

<?php
if(!empty($_POST))
$_POST=array_map("mres" , $_POST);

?>

Its not suggested to select field names from this list:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

array_map()
empty()
get_magic_quotes_gpc()
stripslashes()

[bradgrafelman]

Also note that if you apply the DB escaping mechanism to the $_POST array directly as djjjozsi suggests then you're destroying data integrity (there won't be any copies of the original data).

Whenever building SQL queries, I either a) use PDO's prepared statements so that no escaping needs to be done on my part, or b) use [man]sprintf/man like so:

$query = sprintf("INSERT INTO foo VALUES ('%s', '%s', '%s', %d)",
    mysql_real_escape_string($_POST['name']),
    mysql_real_escape_string($_POST['location']),
    mysql_real_escape_string($_POST['nickname']),
    $_POST['age']
);

It just seems cleaner to me, plus it won't modify the original data.

[djjjozsi]

bradgrafelman, yes, you're right. sprintf has an advantage to limit the inserted strings, depending on the field's lenght.

Little :offtopic:
Most of my paid hosts don't give PDO (only SQL lite).