PHP: Is mysql_real_escape_string sufficient for cleaning user input?

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

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.

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

There are two ways, one is to use prepared statements (as mentioned in other answers), but that will slow down your app, because you now have to send two requests to the Database, instead of one. If you can live with the reduced performance, then go for it; Prepared Statements makes your code prettier and easier to deal with.

If you chose to use mysql_real_escape_string, then make sure that you escape all the strings that are untrusted. An (mysql_real_escape_string) escaped string is SQL Injection secure. If you don't escape all the strings, then you are not secure. You should really combine mysql_real_escape_string with input validation; checking that a variable you expect to hold a number really is a number and within the expected range. Remember, never trust the user.

Answer 5

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)

Answer 6

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 7

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 8

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.

Answer 9

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.