Why are double quotes disappearing in mysql query?

Question

I have this code:

<?php
   $conn = mysqli_connect("localhost", $db['username'], $db['password'], "the_table");

   // Check connection
   if (!$conn) {
       die("Connection failed: " . mysqli_connect_error());
   }

   $sql = "SELECT * FROM xlsws_images";

   if(!$result = $conn->query($sql)){
       die('There was an error running the query [' . $db->error . ']');
   }

   while($row = $result->fetch_assoc()){
       $path = $row['image_path'];
       echo $path;
   }
?>

It works as expected until it reaches a row with an image_path that has double quotes in it such as product/1/1”-wood-wheel-casters-set-of-4.png. In this case it returns the path without the double quotes in it like so product/1/1-wood-wheel-casters-set-of-4.png.

What makes these quotes disappear and how can I avoid that?

Answer 1

"...that has double quotes in it such as product/1/1”"

That's a curly (smart) quote ” and should be changed to ". (That's if what you shown us in your question, is the actual type of quotes in your database).

That's why it's failing you and was introduced into your DB from the start.

You could use REPLACE

• http://dev.mysql.com/doc/en/replace.html
• http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace

to change them in your table. (or even delete them).

Ideally, you should be taking care of getting rid of that quote before it gets to db.

• str_replace() could (also) help you with this.
• http://php.net/manual/en/function.str-replace.php

---

Edit:

The following during testing, echo'd product/1/1”-wood-wheel-casters-set-of-4.png

$result = $connection->query("SELECT * FROM table");

while($row = $result->fetch_object()){

   echo $row->path_column;

}

So, it's hard to say what is causing your code to fail.

A few things you can try are:

$path = mysqli_real_escape_string($conn, $row['image_path']);

and

$path = htmlentities(stripslashes($row['image_path']));

---

Example of using str_replace():

$str = "product/1/1”-wood-wheel-casters-set-of-4.png";

echo $newstring = str_replace("”", "", $str);

Which produced:

product/1/1-wood-wheel-casters-set-of-4.png

• If you wish to go that route in regards to file uploads.

Consult the following Q&A on Stack to replace characters (rename) during uploading files:

Sidenote: It was meant to replace spaces with underscores, but you can base yourself on that same logic.

• Replace Spaces with Underscores in Uploaded File

---

A few more examples that could be useful.

This gets rid of the quote:

$result = $conn->query("SELECT * FROM table");

while($row = $result->fetch_object()){

    $new = $row->path_column;
    $newstring = str_replace("”", "", $new);

}

echo $newstring;

and escaping the newly changed curly quote to a regular quote:

$result = $conn->query("SELECT * FROM table");

while($row = $result->fetch_object()){

    $new = $row->path_column;
    $newstring = str_replace("”", "\"", $new);

}

echo $newstring;

which that one produced product/1/1"-wood-wheel-casters-set-of-4.png

• Notice the " instead of the smart quote ”.

---

Edit:

As it turns out, it was a matter of passing UTF-8 before the connection was set.

OP: "Great! $conn->set_charset('utf8mb4'); worked. That was all I needed to added to my code above. Works seamlessly now. Thank you! – Joshua Goossen".

As per my comment to the OP:

"You can also try setting as UTF-8 as your connection before querying. Have a look at this Q&A on Stack UTF-8 all the way through there are a few answers in there. The connection thing is $connection->set_charset('utf8mb4'); as an example and used just before you open the db connection."

• As per suggested in UTF-8 all the way through