Do values coming directly from the database need to be escaped?

Question

Do I need to escape/filter data that is coming from the database? Even if said data has already been "escaped" once (at the point in time where it was inserted into the database).

For example, say I allow users to submit blog posts via a form that has a title input and a textarea input.

A malicious user submits the blog post

title: Attackposttitle');DROP TABLE posts;--

textarea: Hahaha nuked ur site noobzors!

Now as this is being inserted into my database, I am going to escape it with mysql_real_escape_string, but once it is in the database I will later reference this data in my php blog application with something like this:

sql="SELECT posttitle FROM posts WHERE id=50";
$posttitlearray = mysql_fetch_array(mysql_query($sql));

This is where my concern is, what if I, for example, run the following query to get the post content:

sql="SELECT postcontent FROM posts WHERE posttitle=$posttitlearray[posttitle]";

In theory am I not sql injecting myself? IE, am I not effectively running the query:

sql="SELECT postcontent FROM posts WHERE posttitle=Attackposttitle');DROP TABLE posts;--";

Or does the "Attackposttitle');DROP TABLE posts;--" data continue to be escaped once it is in the database?

Do I need to continually escape it like so:

sql="SELECT postcontent FROM posts WHERE posttitle=msql_real_escape_string($posttitlearray[posttitle])";

Or is the data safe once it has been escaped initially upon first being inserted into the database?

Thanks Stack!

Answer 1

It does not continue to be escaped once it's put in the database. You'll have to escape it again.

$sql="SELECT postcontent FROM posts WHERE posttitle='".mysql_real_escape_string($posttitlearray[posttitle])."'";

Answer 2

The value should be escaped every time just before insertion to SQL query. Not for magical security reasons, but just to be sure that the syntax of the resultant query is OK.

Escaping the string sound 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:

Attackposttitle');DROP TABLE posts;--

after escaping:

Attackposttitle\');DROP TABLE posts;--

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 posts set title = 'Attackposttitle\');DROP TABLE posts;--'

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:

Attackposttitle');DROP TABLE posts;--

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

Try using bind variables. which will remove the need to escape your data completely.

http://php.net/manual/en/function.mssql-bind.php

only down side is your restricted to using them with stored procedures in SQL server, other database you can use them for everything.