PHP: Is mysql_real_escape_string sufficient for cl

Question

Is mysql_real_escape_string sufficient for cleaning user input in most situations?

::EDIT::

I'm thinking mostly in terms of preventing SQL injection but I ultimately want to know if I can trust user data after I apply mysql_real_escape_string or if I should take extra measures to clean the data before I pass it around the application and databases.

I see where cleaning for HTML chars is important but I wouldn't consider it necessary for trusting user input.

T

Answer 1

The best way to go would be to use Prepared Statements

Answer 2

There are different types of "cleaning".

mysql_real_escape_string is sufficient for database data, but will still be evaluated by the browser upon display if it is HTML.

To remove HTML from user input, you can use strip_tags.

I would suggest you look into using PDO instead of regular MySQL stuff, as it supports prepared statements right out of the box, and those handle the escaping of invalid data for you.

Answer 3

What situations?

For SQL queries, it's great. (Prepared statements are better - I vote PDO for this - but the function escapes just fine.) For HTML and the like, it is not the tool for the job - try a generic htmlspecialchars or a more precise tool like HTML Purifier.

To address the edit: The only other layer you could add is data valdation, e.g. confirm that if you are putting an integer into the database, and you are expecting a positive integer, you return an error to the user on attempting to put in a negative integer. As far as data integrity is concerned, mysql_real_escape_string is the best you have for escaping (though, again, prepared statements are a cleaner system that avoids escaping entirely).

Answer 4

I thought I'd add that PHP 5.2+ has input filter functions that can sanitize user input in a variety of ways.

Here's the manual entry as well as a blog post [by Matt Butcher] about why they're great.

Answer 5

mysql_real_escape_string() is useful for preventing SQL injection attacks only. It won't help you with preventing cross site scripting attacks. For that, you should use htmlspecialchars() just before outputting data that was originally collected from user input.

Answer 6

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)