Escaping user input from database necessary?

Question

So I know about MySQL injection and always escape all my user input before putting it in my database. However I was wondering, imagine a user tries to submit a query to inject, and I escape it. What if I then at a later moment take this value from the database, and use it in a query. Do I have to escape it again?

So: (sql::escape() contains my escape function)

$userinput = "'); DROP `table` --";
mysql_query("INSERT INTO `table` 
             (`foo`,`bar`) 
             VALUES 
             ('foobar','".sql::escape($userinput)."')");

// insert php/mysql to fetch `table`.`bar` into $output here

mysql_query("INSERT INTO `table2` 
            (`foo`,`bar`) 
            VALUES
            ('foobar','".$output."')");

Does MySQL automatically escape their output or something like that, or should I escape in the second query as well?

This is a testcase but this occurs in some other ways within my program and I'm wondering how tight the security has to be for cases like this.

EDIT

My escape function

static function escape($string){

    if(get_magic_quotes_gpc()) 
        $string = stripslashes($string); 

    return mysql_real_escape_string($string);

}

Answer 1

Does MySQL automatically escape their output or something like that, or should I escape in the second query as well?

You need to escape in the second query as well. MySQL does not do any escaping on its output.

Long answer: MySQL string escaping does not modify the string that is being inserted, it just makes sure it doesn't do any harm in the current query. Any SQL injection attempt still remains in the data.

Answer 2

Yes, you have to escape the string in the second query too.

Escaping the string sounds magical to many people, something like shield against some mysterious danger, but in fact it is nothing magical. It is just the way to enable special characters being processed by the query.

The best would be just to have a look what escaping really does. Say the input string is:

'); DROP `table` --

after escaping:

\'); DROP `table` --

in fact it escaped only the single slash. That's the only thing you need to assure - that when you insert the string in the query, the syntax will be OK!

insert into table set column = '\'); DROP `table` --'

It's nothing magical like danger shield or something, it is just to ensure that the resultant query has the right syntax! (of course if it doesn't, it can be exploited)

The query parser then looks at the \' sequence and knows that it is still the variable, not ending of its value. It will remove the backslash and the following will be stored in the database:

'); DROP `table` --

which is exactly the same value as user entered. And which is exactly what you wanted to have in the database!!

So this means that the if you fetch that string from the database and want to use it in the query again, you need to escape it again to be sure that the resultant query has the right syntax.

But, in your example, very important thing to mention is the magic_quotes_gpc directive!

This feature escapes all the user input automatically (gpc - _GET, _POST and _COOKIE). This is an evil feature made for people not aware of sql injection. It is evil for two reasons. First reason is that then you have to distinguish the case of your first and second query - in the first you don't escape and in the second you do. What most people do is to either switch the "feature" off (I prefer this solution) or unescape the user input at first and then escape it again when needed. The unescape code could look like:

function stripslashes_deep($value)
{
        return is_array($value) ?
               array_map('stripslashes_deep', $value) :
               stripslashes($value);
}

if (get_magic_quotes_gpc()) {
        $_POST = stripslashes_deep($_POST);
        $_GET = stripslashes_deep($_GET);
        $_COOKIE = stripslashes_deep($_COOKIE);
}

The second reason why this is evil is because there is nothing like "universal quoting". When quoting, you always quote text for some particular output, like:

1. string value for mysql query
2. like expression for mysql query
3. html code
4. json
5. mysql regular expression
6. php regular expression

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (never forget to handle it, or better, assure it is switched off!!!).

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

1. mysql_real_escape_string($str)
2. mysql_real_escape_string(addcslashes($str, "%_"))
3. htmlspecialchars($str)
4. json_encode() - only for utf8! I use my function for iso-8859-2
5. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
6. preg_quote()