Do Htmlspecialchars and MySQL_Real_Escape_String Keep My PHP Code Safe from Injection

Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?

When it comes to database queries, always try and use prepared parameterised queries. The mysqli and PDO libraries support this. This is infinitely safer than using escaping functions such as mysql_real_escape_string.

Yes, mysql_real_escape_string is effectively just a string escaping function. It is not a magic bullet. All it will do is escape dangerous characters in order that they can be safe to use in a single query string. However, if you do not sanitise your inputs beforehand, then you will be vulnerable to certain attack vectors.

Imagine the following SQL:

$result = "SELECT fields FROM table WHERE id = ".mysql_real_escape_string($_POST['id']);

You should be able to see that this is vulnerable to exploit.

Imagine the id parameter contained the common attack vector:

1 OR 1=1

There's no risky chars in there to encode, so it will pass straight through the escaping filter. Leaving us:

SELECT fields FROM table WHERE id= 1 OR 1=1

Which is a lovely SQL injection vector and would allow the attacker to return all the rows.
Or

1 or is_admin=1 order by id limit 1

which produces

SELECT fields FROM table WHERE id=1 or is_admin=1 order by id limit 1

Which allows the attacker to return the first administrator's details in this completely fictional example.

Whilst these functions are useful, they must be used with care. You need to ensure that all web inputs are validated to some degree. In this case, we see that we can be exploited because we didn't check that a variable we were using as a number, was actually numeric. In PHP you should widely use a set of functions to check that inputs are integers, floats, alphanumeric etc. But when it comes to SQL, heed most the value of the prepared statement. The above code would have been secure if it was a prepared statement as the database functions would have known that 1 OR 1=1 is not a valid literal.

As for htmlspecialchars(). That's a minefield of its own.

There's a real problem in PHP in that it has a whole selection of different html-related escaping functions, and no clear guidance on exactly which functions do what.

Firstly, if you are inside an HTML tag, you are in real trouble. Look at

echo '<img src= "' . htmlspecialchars($_GET['imagesrc']) . '" />';

We're already inside an HTML tag, so we don't need < or > to do anything dangerous. Our attack vector could just be javascript:alert(document.cookie)

Now resultant HTML looks like

<img src= "javascript:alert(document.cookie)" />

The attack gets straight through.

It gets worse. Why? because htmlspecialchars (when called this way) only encodes double quotes and not single. So if we had

echo "<img src= '" . htmlspecialchars($_GET['imagesrc']) . ". />";

Our evil attacker can now inject whole new parameters

pic.png' onclick='location.href=xxx' onmouseover='...

gives us

<img src='pic.png' onclick='location.href=xxx' onmouseover='...' />

In these cases, there is no magic bullet, you just have to santise the input yourself. If you try and filter out bad characters you will surely fail. Take a whitelist approach and only let through the chars which are good. Look at the XSS cheat sheet for examples on how diverse vectors can be

Even if you use htmlspecialchars($string) outside of HTML tags, you are still vulnerable to multi-byte charset attack vectors.

The most effective you can be is to use the a combination of mb_convert_encoding and htmlentities as follows.

$str = mb_convert_encoding($str, 'UTF-8', 'UTF-8');
$str = htmlentities($str, ENT_QUOTES, 'UTF-8');

Even this leaves IE6 vulnerable, because of the way it handles UTF. However, you could fall back to a more limited encoding, such as ISO-8859-1, until IE6 usage drops off.

For a more in-depth study to the multibyte problems, see https://stackoverflow.com/a/12118602/1820

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

mysql_real_escape_string and html_special_chars enough?

Sam, if you are storing the input in a database, to avoid SQL injection and XSS then those two functions are enough. If you are storing passwords, you must encrypt the passwords with one-way encryption (that is they can not be decrypted).

Let me expand my answer:
First of all, SQL Injection is a method where a malicious user will attempt to modify your SQL statement to make it do their will. For example, let's say you have a login form. By inserting one of the following values into an un-protected form, I will be able to log into the first account without knowing the username or password:

' or 1=1 -- 

There are many versions of the above injection. Let's examine what it does to the SQL executed on the database:

The PHP:
mysql_query("SELECT * FROM users WHERE username='" . $username."' AND password='" . $password . "';");

When the above is executed, the following SQL is sent to the database:

SELECT * FROM users WHERE username='' or 1=1-- ' AND password='' or 1=1--';

The effective part of this SQL is this:
SELECT * FROM users WHERE username='' or 1=1

as the double dash (with the space afterwards) is a comment, removing the rest of the statement.

Now that gives the malicious user access. With use of an escaping function such as mysql_real_escape_string, you can escape the content so the following is sent to the database:

SELECT * FROM users WHERE username='\' or 1=1-- ' AND password='\' or 1=1--';

That now escapes the quotes, making the intended strings, just that - strings.

Now let's view some XSS.
Another malicious user would like to change the layout of a page. A well known XSS attack was the Facespace attack on Facebook back in 2005. This involves inserting raw HTML into forms. The database will save the raw HTML and then it will be displayed to users. A malicious user could insert some javascript with use of the script tag, which could do anything javascript can do!

This is escaped by converting < and > to <l; and > respectively. You use the html_special_chars function for this.

This should be enough to secure normal content on a site. However passwords are a different story.

For passwords, you must also encrypt the password. It is advisable to use PHP's crypt function for this.

However, once the password is encrypted and saved in the database as an encypted password, how can you decrypt it to check that it is correct? Easy answer - you don't decrypt it. HINT: A password always encrypts to the same value.

Were you thinking 'We can encrypt the password when the user logs in and check it against the one in the database', you are correct...

Difference between htmlspecialchars and mysqli_real_escape_string?

htmlspecialchars: "<" to "& lt;"
(Replaces HTML-Code)

mysqli_real_escape_string: " to \"
(Replaces Code, that has a meaning in a mysql-query)

Both are used to be save against some attacks like SQL-Injection and XSS

mysql_real_escape_string , htmlspecialchars mistake?

$searchTerm = $_GET['search'];

mysql_query("INSERT INTO table1 VALUES ('', '" . mysql_real_escape_string($searchTerm) . ')"', $this->connect);

echo "<a href='http://www.example.com/" . htmlspecialchars(urlencode($searchTerm), ENT_QUOTES) . "'>$search</a>";

Only escape at the exact moment needed, once. In your case, since the value is supposed to be part of a URL, it needs to be URL encoded. Since that URL is then made part of HTML, it needs to be HTML escaped.

Are mysql_real_escape_string() and mysql_escape_string() sufficient for app security?

@Charles is extremely correct!

You put yourself at risk for multiple types of known SQL attacks, including, as you mentioned

• SQL injection: Yes! Mysql_Escape_String probably STILL keeps you susceptible to SQL injections, depending on where you use PHP variables in your queries.

Consider this:

$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);  

Can that be securely and accurately escaped that way? NO! Why? because a hacker could very well still do this:

Repeat after me:

mysql_real_escape_string() is only meant to escape variable data, NOT table names, column names, and especially not LIMIT fields.

• LIKE exploits: LIKE "$data%" where $data could be "%" which would return ALL records ... which can very well be a security exploit... just imagine a Lookup by last four digits of a credit card... OOPs! Now the hackers can potentially receive every credit card number in your system! (BTW: Storing full credit cards is hardly ever recommended!)

• Charset Exploits: No matter what the haters say, Internet Explorer is still, in 2011, vulnerable to Character Set Exploits, and that's if you have designed your HTML page correctly, with the equivalent of <meta name="charset" value="UTF-8"/>! These attacks are VERY nasty as they give the hacker as much control as straight SQL injections: e.g. full.

Here's some example code to demonstrate all of this:

// Contains class DBConfig; database information.
require_once('../.dbcreds');                       

$dblink = mysql_connect(DBConfig::$host, DBConfig::$user, DBConfig::$pass);
mysql_select_db(DBConfig::$db);
//print_r($argv);

$sql = sprintf("SELECT url FROM GrabbedURLs WHERE %s LIKE '%s%%' LIMIT %s",
               mysql_real_escape_string($argv[1]),
               mysql_real_escape_string($argv[2]),
               mysql_real_escape_string($argv[3]));
echo "SQL: $sql\n";
$qq = mysql_query($sql);
while (($data = mysql_fetch_array($qq)))
{
        print_r($data);
}

Here's the results of this code when various inputs are passed:

$ php sql_exploits.php url http://www.reddit.com id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://www.reddit.com%'
               ORDER BY id;
Returns: Just URLs beginning w/ "http://www.reddit.com"

$ php sql_exploits.php url % id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE '%%' 
               ORDER BY id;
Results: Returns every result Not what you programmed, ergo an exploit --

$ php sql_exploits.php 1=1
'http://www.reddit.com' id Results:
Returns every column and every result.

Then there are the REALLLY nasty LIMIT exploits:

$ php sql_exploits.php url 
> 'http://www.reddit.com'
> "UNION SELECT name FROM CachedDomains"
Generated SQL: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://reddit.com%' 
               LIMIT 1 
               UNION
               SELECT name FROM CachedDomains;
Returns:  An entirely unexpected, potentially (probably) unauthorized query
          from another, completely different table. 

Whether you understand the SQL in the attacks or not is irrevelant. What this has demonstrated is that mysql_real_escape_string() is easily circumvented by even the most immature of hackers. That is because it is a REACTIVE defense mechism. It only fixes very limited and KNOWN exploits in the Database.

All escaping will NEVER be sufficient to secure databases. In fact, you can explicitly REACT to every KNOWN exploit and in the future, your code will most likely become vulnerable to attacks discovered in the future.

The proper, and only (really) , defense is a PROACTIVE one: Use Prepared Statements. Prepared statements are designed with special care so that ONLY valid and PROGRAMMED SQL is executed. This means that, when done correctly, the odds of unexpected SQL being able to be executed are drammatically reduced.

Theoretically, prepared statements that are implemented perfectly would be impervious to ALL attacks, known and unknown, as they are a SERVER SIDE technique, handled by the DATABASE SERVERS THEMSELVES and the libraries that interface with the programming language. Therefore, you're ALWAYS guaranteed to be protected against EVERY KNOWN HACK, at the bare minimum.

And it's less code:

$pdo = new PDO($dsn);

$column = 'url';
$value = 'http://www.stackoverflow.com/';
$limit = 1;

$validColumns = array('url', 'last_fetched');

// Make sure to validate whether $column is a valid search parameter.
// Default to 'id' if it's an invalid column.
if (!in_array($column, $validColumns) { $column = 'id'; }


$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));
while (($data = $statement->fetch())) { }

Now that wasn't so hard was it? And it's forty-seven percent less code (195 chars (PDO) vs 375 chars (mysql_). That's what I call, "full of win".

EDIT: To address all the controversy this answer stirred up, allow me to reiterate what I have already said:

Using prepared statements allows one to harness the protective measures of
the SQL server itself, and therefore
you are protected from things that the
SQL server people know about. Because
of this extra level of protection, you
are far safer than by just using
escaping, no matter how thorough.

---

Related Topics