PDO crashes with apostrophe in value

Question

I have the following code and it causes an error:

if(get_magic_quotes_gpc()){
    echo 'Magic Quotes enabled... sigh!';
} else {
    echo 'Magic Quotes disabled... SWEET';
}

$temp = "Chas' Bone Breaking";
$query = $conn->prepare("UPDATE chasiv_lg_cbbfl.defaults SET name=" . $temp);
$query->execute();

I'm very new to PDO use (old school mysql & mysqli, but trying to convert).

Use addslashes it works like a charm. Example: 1) $appostrophes = "D'autre personne"; 2) $CorrectAppostrophes = addslashes($appostrophes) ; 3) Now do what ever you want with your variable: $correctAppostrohes — DoctorDroid Haiti, Mar 05 '20 at 13:45

Funk Forty Niner · Accepted Answer · 2016-03-23T22:00:57.123

We're dealing with a string here and your variable in the query needs to be quoted.

Sidenote: With or without the apostrophe, your query would still have errored out.

Consult a prepared statement example that I have prepared further below.

Also consult the following which may be of help:

addslashes() http://php.net/manual/en/function.addslashes.php
striplashes() http://php.net/manual/en/function.stripcslashes.php

So change

SET name=" . $temp)

to read as

SET name= '$temp'")

A prepared statement would be better to use here though. Consult the following

References:

and escape your data using the escaping function relative to the MySQL API used to connect with.

However, you don't have a WHERE clause and that will update your entire table.

So, and for example:

SET name= '$temp' WHERE column_to_update = 'x' ")

the x is what you need to fill in and modify the column name to suit.

Reference on UPDATE:

http://dev.mysql.com/doc/en/update.html

Example from the manual (PDO/INSERT):

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

So in your case:

try {
    $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

     $sql = "

     UPDATE chasiv_lg_cbbfl.defaults   
       SET `name` = :temp

     WHERE `column_to_update` = :xxx

     ";

    $query = $conn->prepare($sql);
    $query->bindValue(":temp", $temp);
    $query->bindValue(":xxx", $xxx);
    $query = $statement->execute();

    $conn = null; // Disconnect
}

catch(PDOException $e) {
  echo $e->getMessage();
}

Thanks for all the info. The table is a defaults table, so it only has 1 record in it, thus the reason there was no where statement. It was my understanding that preparing statements removed the need for things like addslashes and such. The answer was to change $temp to $conn->quote($temp) as noted in the 'duplicate' link. I'm unsure why they want you to bind things as you can accomplish the same thing with a direct statement. I can see a use for bind if you're adding more than 1 record at a time, but then again you can use a loop just as easily. — Charles Wilkins IV, Mar 24 '16 at 12:22
@CharlesWilkinsIV You're welcome. I'm glad you got your solution, *cheers* - P.s.: I might delete my answer at some point for a few reasons. So if you see it gone, then you could delete your question also. A few downvoted us and the question was legit as was my answer. — Funk Forty Niner, Mar 24 '16 at 12:25

PDO crashes with apostrophe in value

1 Answers1