nTuply nTuply - 12 days ago 5
PHP Question

Double quotes in PHP PDO Update query

I've tried everything, and I still can't figure it out.

addslahes()
,
str_replace()
,
htmlentities()
, I just can't understand why double quotes is not saving in my table in the UPDATE query.

Code

//Update Data
$sql = "UPDATE `user_settings` SET `advertising_1`='$advertising_1', `advertising_2`='$advertising_2', `statistics`='$statistics', `website_name`='$website_name', `website_url`='$website_url' WHERE `user_session` = '$user_session'";
//trace error
//echo $sql; die();
//Prepare Statement
$stmt = $con->prepare($sql);
$stmt->execute();


Can someone please explain where I'm going wrong here? Problem arises with Double quotes in my string. Single quotes was fixed with mysql_escape but it appears to be deprecated.

$sql = "UPDATE `user_settings` SET `advertising_1`=:advertising_1, `advertising_2`=:advertising_2, `statistics`=:statistics, `website_name`=:website_name, `website_url`=:website_url WHERE `user_session` = '$user_session'";

//Prepare Statement
$stmt = $con->prepare($sql);

$stmt->bindParam(':advertising_1', $advertising_1);
$stmt->bindParam(':advertising_2', $advertising_2);
$stmt->bindParam(':statistics', $statistics);
$stmt->bindParam(':website_name', $website_name);
$stmt->bindParam(':website_url', $website_url);

$stmt->execute();


SELECT QUERY

$sql = $con->prepare("SELECT * FROM `user_settings` WHERE `user_session` = '$user_session'");
$sql -> execute();

$result = $sql->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
$advertising_1 = $row['advertising_1'];
$advertising_2 = $row['advertising_2'];
$website_name = $row['website_name'];
$website_url = $row['website_url'];
$statistics = $row['statistics'];
}

Answer

You need to escape the data you are outputting to the browser use htmlspecialchars and use the quotes constant (ENT_QUOTES) so all quotes are converted to entities. Note this also is how XSS injections are prevented/performed. Elements/attributes are closed when they aren't suppose to be and then malicious code is written.

echo htmlspecialchars('Encode all of these "test" test \'test \'', ENT_QUOTES);

Output:

Encode all of these "test" test 'test '

and in a browser:

Encode all of these "test" test 'test '

Also from the code you displayed you are misusing prepared statements. Values need to be bound, not concatenated to your query. This way the PDO driver will handle the quoting/escaping. This could result in similar issues for you in the future, if you continue to use it as you have it. Also opens you to SQL injections.

For more information on prepared statements see: http://php.net/manual/en/pdo.prepared-statements.php