Where to Use MySQL_Real_Escape_String to Prevent SQL Injection

Can mysql_real_escape_string ALONE prevent all kinds of sql injection ?

mysql_real_escape_string ALONE can prevent nothing.

Moreover, this function has nothing to do with injections at all.

Whenever you need escaping, you need it despite of "security", but just because it is required by SQL syntax. And where you don't need it, escaping won't help you even a bit.

The usage of this function is simple: when you have to use a quoted string in the query, you have to escape it's contents. Not because of some imaginary "malicious users", but merely to escape these quotes that were used to delimit a string. This is extremely simple rule, yet extremely mistaken by PHP folks.

This is just syntax related function, not security related.

Depending on this function in security matters, believing that it will "secure your database against malicious users" WILL lead you to injection.

A conclusion that you can make yourself:

No, this function is not enough.

Prepared statements is not a silver bullet too. It covers your back for only half of possible cases. See the important addition I made to the famous question for the details

Where to use mysql_real_escape_string to prevent SQL Injection?

Basically, each time you use some unsafe data (user input, value from a database, a file or an external website, i.e. any data that you are not 100% sure that it is safe) in a SQL query, you should escape it using mysql_real_escape_string. Note that according to OWASP, this function is not secure for escaping dynamic table names (but this is far less common than "basic" user input insertion).

I suggest you to have a look at the whole OWASP article on SQL injection, and also to browse the rest of the website. It's a great source of information about security in web applications.

IMO, the preferred way of preventing SQL injection is to use prepared statements.

Please remember that if you do choose to use mysql_real_escape_string() it only works when
used inside a string that is delimited by quotes. Never use it on
any unquoted values. This includes numeric values; instead, validate that the user-input is actually numeric.

Is mysql_real_escape_string enough to Anti SQL Injection?

mysql_real_escape_string is usually enough to avoid SQL injection. This does depend on it being bug free though, i.e. there's some small unknown chance it is vulnerable (but this hasn't manifested in the real world yet). A better alternative which completely rules out SQL injections on a conceptual level is prepared statements. Both methods entirely depend on your applying them correctly; i.e. neither will protect you if you simply mess it up anyway.

SQL injection that gets around mysql_real_escape_string()

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1");    
$sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect you against this.
The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:

$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";

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);

When to use mysql_real_escape_string?

You should use mysql_real_escape_string() whenever you're building a query that will be run against the database. Any user input that is being used to build a database query should be run through this function. This will prevent sql injection attacks.

User inputs are your big area of concern when it comes to this.

Is it possible to hack mysql_real_escape_string() Htmlspecialchars()?

If your PHP is updated try to use mysqli or PDO and prepared statements

But to answer your question, YES mysql_real_escape_string() can be injected, but it's very complicated to do so. Here's a example

Does mysql_real_escape_string() FULLY protect against SQL injection?

According to Stefan Esser, "mysql_real_escape_string() [is] not safe when SET NAMES is used."

His explanation, from his blog:

SET NAMES is usually used to switch the encoding from what is default to what the application needs.
This is done in a way that mysql_real_escape_string doesn’t know about this. This means if you switch to some multi byte encoding that allows backslash as 2nd 3rd 4th… byte you run into trouble, because mysql_real_escape_string doesn’t escape correctly. UTF-8 is safe…

Safe way to change encoding is mysql_set_charset, but that is only available in new PHP versions

He does mention that UTF-8 is safe, though.

---

Related Topics