PHP: Is mysql_real_escape_string sufficient for cleaning user input?

mysql_real_escape_string is not sufficient in all situations but it is definitely very good friend. The better solution is using Prepared Statements

//example from http://php.net/manual/en/pdo.prepared-statements.php

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

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

Also, not to forget HTMLPurifier that can be used to discard any invalid/suspicious characters.

...........

Edit: Based on the comments below, I need to post this link (I should have done before sorry for creating confusion)

mysql_real_escape_string() versus Prepared Statements

Quoting:

mysql_real_escape_string() prone to the same kind of issues affecting addslashes().

Chris Shiflett (Security Expert)

The answer to your question is No. mysql_real_escape_string() is not suitable for all user input and mysql_real_escape_string() does not stop all sql injection. addslashes() is another popular function to use in php, and it has the same problem.

vulnerable code:

mysql_query("select * from user where id=".mysql_real_escape_string($_GET[id]));

poc exploit:

http://localhost/sql_test.php?id=1 or sleep(500)

The patch is to use quote marks around id:

mysql_query("select * from user where id='".mysql_real_escape_string($_GET[id])."'");

Really the best approach is to use parametrized queries which a number of people ahve pointed out. Pdo works well, adodb is another popular library for php.

If you do use mysql_real_escape_string is should only be used for sql injection, and nothing else. Vulnerabilities are highly dependent on how the data is being used. One should apply security measures on a function by function basis. And yes, XSS is a VERY SERIOUS PROBLEM. Not filtering for html is a serious mistake that a hacker will use to pw3n you. Please read the xss faq.

To the database, yes. You'll want to consider adequately escaping / encoding data for output as well.

You should also consider validating the input against what you expect it to be.

Have you considered using prepared statements? PHP offers numerous ways to interact with your database. Most of which are better than the mysql_* functions.

PDO, MDB2 and the MySQL Improved should get you started.