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

Answer 3

I'd say that whole idea of this question is wrong.

You're taking this problem absolutely wrong way.
One doesn't have to count his queries, if it's first or second or 100th.
Same goes for the the user input: it doesn't matter, where the data come from!

Data destination, not source should be your concern. Is this string going to database? Escape it! With no questions. This rule is plain and simple and require no query counting or anything.

But that's not only fault in your question.
One:

Does MySQL automatically escape their output or something like that?

That's a very bad idea. Funny part, you're fighting with a consequence of the same idea in your code, by applying get_magic_quotes_gpc(). What are these magic quotes if not such automatic escaping?

Two:
moreover, using get_magic_quotes_gpc() in your escaping function is a very bad idea again :)

imagine you have magic quotes on and using your function to protect your "second query". And there is some blob that contain \' sequence in the data. Your function will strip the slash and spoil the data. In fact, stripslashes has absolutely nothing to do with any escaping function. do it separately, on the data where it belongs - on the user input.

Three:
mysql_real_escape_string() is not some magic function that "makes everything safe". In fact, to create dynamic mysql query, one have to escape four kinds of data:

• strings
• numbers
• identifiers
• operators

while mysql_real_escape_string() escaping only one of them. And your query stand absolutely naked in all three other cases. Funny, eh?

Most disappointing part: I know that all this ultimate knowledge is in vain and would be read scarcely by few noobs and never change either overall knowledge level of PHP community in general, nor answers quality on SO in particular. :(

Answer 4

Try to use PHP's PDO for database access if you can. There are two important reasons for this:

1. You can use PDO's prepare function to compile your query. This is efficient if you need to issue the same query with different input (as is often the case). So, compile once and execute multiple times.
2. Compiling the query with prepare has other nice effects. Once the query is compiled, the database engine knows the exact syntactic structure of the query, and does not allow any input that changes this syntactic structure. This is good because in SQL injection, the injected input changes the syntax of the query.

Warning: This doesn't prevent all kinds of SQL injection, but it prevents the most common kind.

References:

1. Are PDO prepared statements sufficient to prevent SQL injection?
2. http://php.net/manual/en/pdo.prepare.php