15

There is a lot of talk about how addslashes and mysql_real_escape function are not safe to prevent injections. The truth is even the big frameworks or CMSs like Wordpress are using this functions and they do a god job so far.

I know there are some particular scenarios when using GBK charset, or utf8_decode can be used to inject some sql code, or some simple examples like 1' OR 1 -- that can be used when there is a simple where involved.

However, after a bit of research it seems very hard to inject something into a simple query with addslashes or mysql_real_escape used if the charset is UTF-8 and let's admit it, this is the most common scenario.

So, given this newbie script, pls provide a sql injection POC ( remember UTF-8 charset )

$mysql['username'] = addslashes($_POST['username']);
$mysql['password'] = addslashes($_POST['password']);

$sql = "SELECT *
FROM users
WHERE username = '{$mysql['username']}'
AND password = '{$mysql['password']}'";

Update - I just need a simple example not a full disclosure of the process. Even a link from google might work.

Improve this question
6
  • 2 bookmarks and 2 close votes ? Any reasons ? Commented Feb 28, 2011 at 7:24
  • Good question, but what I would like to know is: Why not simply use mysql_real_escape_string if there's even a remote theoretical possibility that addslashes is not good enough (not to mention prepared statements et al)? Commented Feb 28, 2011 at 7:32
  • 2
    I told you this is completely wrong site for such questions. No kidding. That's because most people never think of the question (not to say of the problem behind the question) but rather tend to issue some sort of piece of knowledge which they think is connected to the question. Look, this guy asked similar question and got no good answer: stackoverflow.com/questions/3448441 however, it contains some sort of answer to your question too Commented Feb 28, 2011 at 8:07
  • I understand you opinion Col. Shrapnel but last time you gave me the right answer :) Commented Feb 28, 2011 at 8:36
  • Lol, like I said! The guy got the bounty for his useless rant :) Commented Mar 25, 2011 at 15:20

1 Answer 1

Reset to default
11
+150

Update 2:

After further research, MySQL versions prior to 5.0.77 may be vulnerable to the GBK issue when combined with SET NAMES alone. It was earlier believed that only 5.0.22 and earlier were vulnerable.

This means that if you are using PHP versions prior to 5.2, in which mysql_set_charset / mysqli_set_charset were introduced, your code may be vulnerable under specific, well-crafted conditions.

If you're stuck on PHP 5.1, please ensure that you are using MySQL 5.0.77 or later. 5.0.77 is "only" two years old, but has been pushed into the repositories for RHEL/CentOS 5.x, the more popular distribution stuck with the 5.0.x series of MySQL and 5.1.x series of PHP.

Get upgrading, people!

Update 1: Another recent question has uncovered the source of the GBK thing: A bugfix in MySQL 5.0.22. Versions earlier than this are severely vulnerable when using anything other than mysql_real_escape_string combined with mysql_set_charset instead of just SET NAMES. The mysqli equivilent is named mysqli_set_charset.

There does not appear to be an equivilent of mysql_set_charset in PDO. This may be either because it can use MySQL native prepared statements, which may be immune from the problem, or whether SET NAMES is enough for their underlying escaping mechanism to work as expected.

Regardless, if you're using any MySQL version prior to 5.0.22 5.0.77 and are not taking extreme care to ensure that you're only passing in strings in a known character set, you may find yourself open to attack.

I'm leaving the rest of my original post unmodified, but I have updated the tldr.

There is a lot of talk about how addslashes and mysql_real_escape function are not safe to prevent injections

This is half correct. addslashes is entirely the wrong thing to use to protect against SQL injection because it is not guaranteed to provide the right escaping method for all databases, mainly because it adds backslashes and sometimes the escaping mechanism is entirely different.

If you're stuck in the ghetto of the prehistoric lump of crap known as the "mysql" extension (instead of using PDO or mysqli), mysql_real_escape_string is some of the best protection you've got when you need to concatenate together some SQL.

I know there are some particular scenarios when using GBK charset, or utf8_decode can be used to inject some sql code

You're probably thinking of creating malformed UTF-8 sequences, however I've only ever seen this as an XSS mechanism, never an SQL injection mechanism. Running strings through iconv with //IGNORE//TRANSLIT should be good enough protection (usually by truncating the string at the point of the bad sequence, which is an acceptable failure mode when you're being attacked -- malformed sequences should never happen in legitimate requests).

Further, while there are plenty of "quote" characters in non-Latin languages, MySQL is pretty decent at only actually obeying the backtick and double quote for identifiers and the single quote for string values.

Thinking about it more, perhaps there's some sequence of characters in another character set that might include a single quote in the middle, if taken as a different character set. However, it's very, very likely that addslashes is entirely ignorant of character set, and just works on the raw bytes. It'd stick a backslash in the middle of a sequence, and blow it up. However, that should just result in a whine somewhere along the lines about bad character set information.

mysql_real_escape_string, on the other hand, is designed with knowledge of the connection's character set built in, so it wouldn't escape the sequence if it sees the sequence instead of a quote. However, because it would recognize it as a sequence instead of as a quote, there's no danger at all.

Ultimately if you think this is a problem, it's your responsibility to ensure that you accept input in only the expected character sets, and transform all input to your desired character set if there's a mismatch. This will rarely if ever trip up a legitimate request.

tl;dr: Not a concern unless you're using a really old MySQL version and/or aren't making sure your data is in a known-good character set. Always use database-specific escape mechanisms for maximum safetey, and always assume the user is out to get you.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

What do you mean by "assume the user is out to get you". He is! ;)
mres will only secure the input 100% if you also call mysql_set_charset. Otherwise it's susceptible to the same character set based attacks as addslashes(). But if you set the charset to the connection charset, you're fine...
Hmm, in the documentation there, they suggest using that function instead of SET NAMES .... I wonder why.
I have updated my post with more information about the source of the GBK thing -- a bug in MySQL 5.0.22 and earlier.
Can you give an example to this vulnerability? I tried encoding " many different ways with UTF's variable length encoding but MySQL never decoded it as " to be able to use it for SQL injection.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.