mysql_real_escape_string during retrieval

Question

I've used mysql_real_escape_string() to sanitise data when inserting it into the database, is it okay to use it again when retrieving data from the database?

Answer 1

It's not ok to use it at any time. The MySQL extension has been deprecated. Use PDO or MySQLi instead.

When using data in a query, use parameter binding instead of string manipulation for sanitising purposes.

You do not need to sanitise data on retrieval. If you're displaying the data in an HTML page, use the htmlspecialchars() or htmlentities() functions instead.

Update

To explain, you should not be storing data with escape characters in it. The best approach is to store data as received without modification (this is external to any validation you use to filter inputs prior to storing).

PDO and MySQLi both support parameter binding which is the safest way to store volatile data, eg (PDO)

$stmt = $pdo->prepare('INSERT INTO tableName VALUES (:param1, :param2)');
$stmt->bindParam('param1', $var1);
$stmt->bindParam('param2', $var2);
$stmt->execute();

Upon retrieval, you will receive the data as it went in so you do not need to modify it again.

To safely display this data in an HTML page, use one of the encoding functions listed above, eg

$stmt = $pdo->prepare('SELECT name FROM tableName');
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);
?>

<p>Hello, <?= htmlspecialchars($row['name']) ?></p>

Answer 2

No, mysql_real_escape_string() (and it's more modern version mysqli::real_escape_string()) should not be used on data retrieved from the database.

The only reason for escaping data for insertion into the database is because you are assembling it as string-data in another language: SQL. That is the purpose of escaping. If there was an API call where you provided all the elements as discrete parameters, you would not need to escape the data. But that's not how SQL works.

(Similarly, stripslashes() is only needed if you can't turn off magic quotes in your PHP instance.)

Answer 3

Yes always.

    $username = stripslashes($_POST['username']);
    $username = mysql_real_escape_string($_POST['username']); 
    $password = mysql_real_escape_string(md5($_POST['password']));//with md5
    And retrieve----
    $req = mysql_query('select password,id from users where username="'.$username.'"');
    $dn = mysql_fetch_array($req);
    if($dn['password']==md5($password) and mysql_num_rows($req)>0)
{ // ...

The reason you want to sanitize on retrieval is that most injections happen here. Case in point Facebook's hack from a user a year ago--he submitted a mysql_query retrieval into the browser to fetch another users data. And wound up inside the admin's program.