Is MySQLi_Real_Escape_String Safe

Is mysqli_real_escape_string safe?

Is this correct?

Yes. This isolated handpicked example is safe. It doesn't mean, though, that mysqli_real_escape_string should be viewed as a function that's purpose is to prevent SQL injections. Because in this example it protects you only by accident.

Is this a good example of how to use mysqli_real_escape_string?

Not at all

This function should be abandoned in favor of using parameters in the query. This function will fail you with any query part other than a string literal. And can be even simply overlooked.

A placeholder, also called a parameter, have to be used instead, to represent the data in your query:

$sql='SELECT * FROM usuarios WHERE username=?';
$stmt= $conn->prepare($sql);
$stmt->bind_param("s", $_POST['usuario']);
$stmt->execute();
$rs = $stmt->get_result();

See other examples in my article on the correct use of mysqli

If ever used, this function MUST be encapsulated into another function that does both escaping AND adding quotes, just like PDO::quote() does. Only this way it will be safe.

Mysqli_real_escape_string vulnerable

The thing is, mysqli_real_escape_string() or other proper escaping is actually technically secure, in the end, that's about what parameterized queries do too. However, there is always a however. For example it is only secure if you have quotes around variables. Without quotes it is not. When it's a 1000 line project with one developer, it's probably ok in the first 3 months. Then eventually even that one developer will forget the quotes, because they are not always needed syntactically. Imagine what will happen in a 2 million LOC project with 200 devs, after 5 years.

Similarly, you must not ever forget using your manual escape function. It may be easy first. Then there is a variable that is validated and can only hold a number, so you are sure it's ok to just put it in without escaping. Then you change your mind and change it into a string, because the query is ok anyway. Or somebody else does it after 2 years. And so on. It's not a technical issue. It's a management-ish issue, in the long run, your code will be vulnerable somehow. Hence it is bad practice.

Another point, it's much harder to automatically scan for SQL injection vulnerabilities if manual escaping is in place. Static code scanners can easily find all instances of concatenated queries, but it's very hard for them to correlate previous escaping if there is any. If you use something like parameterized queries, it is straightforward to find sql injections, because all concatenations are potential candidates.

Is mysql_real_escape_string is really safe to use?

UPDATE: The answer below was to the best of my knowledge correct at the time of writing. The fact is mysql_real_escape_string is not safe and never really was. You should always use prepared statements instead.

As mysql_* has been removed completely as of PHP 7 the situation has become moot. I've left my original answer for historical purposes below.

---

mysql_real_escape_string is safe to use if used properly (ie, everywhere you're inserting PHP variables into your queries), but as has been pointed out in the comments it's not the only thing you need to worry about. For example, HTML markup could be inserted into your DB and used for Cross Site Scripting attacks.

You might also want to consider prepared statements as an alternative to mysql_real_escape_string, as they will automatically escape input for you so there's no chance of accidentally forgetting to escape a parameter.

Is mysqli_real_escape_string enough to avoid SQL injection or other SQL attacks?

Could someone tell me if it is secure or if it is vulnerable to the SQL Injection attack or other SQL attacks ?

No. As uri2x says, see SQL injection that gets around mysql_real_escape_string().

The best way to prevent SQL injection is to use prepared statements. They separate the data (your parameters) from the instructions (the SQL query string) and doesn't leave any room for the data to contaminate the structure of your query. Prepared statements solve one of the fundamental problems of application security.

For situation where you cannot use prepared statements (e.g. LIMIT), using a very strict whitelist for each specific purpose is the only way to guarantee security.

// This is a string literal whitelist
switch ($sortby) {
    case 'column_b':
    case 'col_c':
        // If it literally matches here, it's safe to use
        break;
    default:
        $sortby = 'rowid';
}

// Only numeric characters will pass through this part of the code thanks to type casting
$start = (int) $start;
$howmany = (int) $howmany;
if ($start < 0) {
    $start = 0;
}
if ($howmany < 1) {
    $howmany = 1;
}

// The actual query execution
$stmt = $db->prepare(
    "SELECT * FROM table WHERE col = ? ORDER BY {$sortby} ASC LIMIT {$start}, {$howmany}"
);
$stmt->execute(['value']);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

I posit that the above code is immune to SQL injection, even in obscure edge cases. If you're using MySQL, make sure you turn emulated prepares off.

$db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

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)

is it safe to safe real escape string inside the same value?

Escaping depends on the context the data is going to be used in. MySQL real-escaping does nothing against HTML injection, or incorrectly formatted CSV values, or any other text format where characters needs to be escaped. Read The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

When you replace the $_GET data, that means all your following code now has to deal with specifically SQL-escaped data. What if you have any code that doesn't deal with SQL? That's why it's terrible practice.

It's even worse since you should simply use parameterised queries instead of manual escaping. See How can I prevent SQL injection in PHP?

mysqli_real_escape_string - example for 100% safety

Your isolated and simplified example is technically safe.

However, there are still two problems with it:

• the assumption: the very statement of question is made out of the assumption that mysqli_real_escape_string() is related to any security issues. Which is but a grave delusion. This is a string formatting function, that protects you from SQL injections only as a side effect. But such a protection is neither the goal nor the purpose of this function. And therefore it should never be used for the purpose. Such a fallacy will inevitably lead you to this function's misuse (such as using it to "protect" numbers) and eventually allow an SQL injection.
• the inherent separability of the code you posed. The protection consists of three parts:
  
  • setting the correct encoding
  • escaping special characters
  • wrapping the escaped value in quotes

It is not only the fact that some of these obligatory measures could be forgotten but again, the statement of question stresses only on a single part - escaping. It is only escaping which is always accented on, while two other measures get hardly mentioned at all. Just look at your question - you meant the code but asked about a function. So any literal answer to the question you asked will make a fatally wrong impression that mysqli_real_escape_string() is all right.

In short, the statement of question helps to promote the most dangerous of PHP related delusions: that this function protects from SQL injection.

Unlike this complex three-part equation, prepared statements constitute an inseparable measure. You cannot forget one part. You cannot misuse it. Try mysqli_real_escape_string() to protect an identifier, and it will silently go unnoticed, until the actual injection happen. Try a prepared statement for an identifier - and get an error.

Is using mysqli_real_escape_string enough to secure my query string?

If used everywhere correctly real_escape_string is an option. But consider the following code:

$page = $_GET['page'];
$sql = 'SELECT `name` FROM `user` WHERE `id` = ' . mysqli_real_escape_string($page);

Safe or not? real_escape_string can only be used to escape strings inside quotation marks. $page could be 1 OR id IN (2,3,4,5,6,7,8,9) → no quotation marks, no real escaping. Casting to the correct datatype (int) might help in this case. You're better off using prepared statements, they are not as easily to mis-use.

---

Related Topics