Escaping ' from mySQL array

Question

There is some text stored in mysql table as long text. It contains ' characters.

Trying to escape these so I can fetch the results. Anything having ' gives error.

review_text with ' fails to bring back anything. The answer is supposed to be with mysql_real_escape_string but that's not working.

while ($row = mysql_fetch_array($result)) {
    $review = array();
    $review["name"] = $row["reviewer_name"];
    $review["title"] = $row["review_title"];
    $review["content"] = $row["review_text"];
    $review["rating"] = $row["review_rating"];
    $review["time"] = $row["date_time"];
    // push single product into final response array
    array_push($response["reviews"], $review);
}

Answer 1

There are several ways of handling this, however first of you need to stop using deprecated mysql_ extensions. "Deprecated" means that they are no longer included within php, and soon will stop working, as it states on the official php site here: http://php.net/manual/en/function.mysql-fetch-array.php

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_fetch_array()
PDOStatement::fetch()

You can use mysqli_real_escape_string to "escape" your variables, however it's much better to use prepared statements. Either mysqli_ or PDO will work.

In this case where you are getting the variables using a select, you can do as follows (it's not clear where you are getting your variables but let's use this as an assumption), you would connect to your database like so:

$conn = new mysqli($host, $username, $password, $dbname);

if  ($conn->connect_error) {
    die("Connection failed: " . $dbc->connect_error);

$stmt = $conn->prepare(select name, title, content, rating, time from tablename where variable = ?);
// this is assuming that this is a string, so "s" is used, otherwise use "i" for string
$stmt->bind_param("s",$variable);
$stmt->execute();
//then you can bind your results into tidy variables all of which have been "escaped"
$stmt->bind_result($name, $title, $content, $rating, $time);
$stmt->close();

To get your variables, insteady of using mysql_fetch_array() you just use $stmt->fetch();

or like this:

while (stmt->fetch()) {
    [stuff here]
}

voila, your variables are pretty clean. Note: while these will handle quotations, apostrophes, etc, they will also be much more secure for protection against SQL injecction. You can also use PDO.