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= "https://stackoverflow.com/questions/110575/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="https://stackoverflow.com/questions/110575/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