没有在mysqli中更新撇号的字符串

Question

I am using prepared statements to preform an update. But for some reason a string that contains an apostrophe does not get updated. Is there a way where I can update a string ans preserve the apostrophe??

Below is an example of my query

$query = "UPDATE items SET item_name = ? , item_price = ? WHERE id = ? ";
    $stmt = $db->prepare($query);

    foreach($items_and_prices as $id => $item_and_price){
        $item = $item_and_price[0];
        $price = $item_and_price[1];
        $stmt->bind_param("sdi",$item, $price,$id);
        $stmt->execute();

    }

Answer 1

The reason that your strings aren't inserting correctly is because apostrophes are special characters. The best way to go about inserting special characters into mysql databases, is to escape them in some way first. With PHP, you have quite a few different options.

I will explain some of them here:

• htmlentities(); Converts special characters to html entities.
  Usage: http://php.net/manual/en/function.htmlentities.php

• htmlspecialchars(); Converts special characters to html special character codes (ie:  ) Usage: http://php.net/manual/en/function.htmlspecialchars.php

• addslashes(); Escapes special characters with the backslash \
  Usage: http://php.net/manual/en/function.addslashes.php

Example: $safestring = addslashes($orginalstring); would cause all special characters to be escaped with a \, causing ' to become \', making it possible to insert special characters into the database.

You can also use stripslashes(); to get rid of the \ escaping when outputting code that has been escaped with addslashes();