Page 2 of 2

Posted: Mon Jan 23, 2006 2:54 am
by matthijs
Chris, thanks for your reply. Some things are cleared up a bit. Others aren't.

I'll try to formulate this as good as I can.

The whole discussion started with the notion:
addslashes() and mysql_real_escape_string() are an equal solution to cleansing data before it's inserted in a database
(1)
(note: assumed escaping is meant, not cleansing)
and(2)
The mysql_real_escape_string function does not protect against SQL injections anymore than addslashes()
But then Chris replies:
Wrong. .... The important issue is that mysql_real_escape_string() is more appropriate for escaping data used in a MySQL query
, which he explaines on his site. Chris points out that despite the use of addslashes() one can exploit the SQL injection vulnerability.
However, this exploit is only possible when you set the character set to: default-character-set=GBK

Then, Ilia follows in a post with a similar exploit of mysql_real_escape_string(), also setting the default-character-set=GBK. (done by changing it on a per-connection basis).

Ok. At this point, I am confused. Something which I pointed out in another post in the current thread, to which Chris replies:
In fact, even the situation I brought up only applies to multi-byte character sets. If you're not developing an application for an international audience, you won't notice a difference between addslashes() and mysql_real_escape_string(). However, there is a difference, and it does matter to some people
So let's get this straight. As far as I understand it now:
1) When I am not changing the character set there is no difference with respect to mysql injection between addslashes and mysql_real_escape_string().
2) When I do change the character set to GBK, addslashes can be bypassed.
3) as can mysql_real_escape_string
2) Mysql_real_escape_string() does escape a couple of characters which addslashes doesn't. This has consequences for the way data is written to log files.

To summerize, although Chris strongly suggests that "The important issue is that mysql_real_escape_string() is more appropriate for escaping data used in a MySQL query", I still cannot see why or when. (assuming I'm not changing the character set)


p.s. 1 - please note that I am just trying really hard to understand this well. I'm not trying to attack anyone. If I misunderstood someone and quoted out of context, please correct me.
p.s. 2 - the whole character encoding thing is something that I will read more about. Untill yesterday I had never heard about GBK before. Let alone change the character encoding in my code.

Posted: Mon Jan 23, 2006 3:35 am
by Jenk
I'm going to add that the best reason, in my opinion, for using mysql_real_escape_string() over addslashes() is that mysql_real_escape_string uses the function from your MySQL installation, and not a default PHP function. Not only this, but it actually looks at which char-set you are currently using, which gives me more confidence as I know it is more in-depth than just looking for ", ' and NULL.

And also to add:

If sometime in the future someone finds an exploit with a char that is not recognised as threatening by either function, which do you think will be updated first, if at all? :)

Posted: Wed Jan 25, 2006 12:48 am
by AGISB
As I stated before I want to make my opinion clear once more.

I think those functions are good for making sure the data isn't corrupted while saved into the database but NOT! for security against any attacks. Noone can possibly imagine all the way someone can abuse any input. So I think it is a mood point which is better in which situation. The fact that someone has proven that 1 is inssecure in at least one instance does not make the other more secure. Tomorrow someone might find a hole in the other.

You need to use a whitelist solution to protect against security holes like SQL injection.

Posted: Wed Jan 25, 2006 2:24 am
by matthijs
@AGISB: Although I (think I) can understand the point you're trying to make, my guess is the discussion is not about which of the two functions makes your application "more secure". As far as I know - anyone is free to correct me if I'm wrong - the question is: is there a difference between the functions? which function does it's job better then the other, and that's: escaping output/ problematic characters to prevent sql injection. Or, which function does it's job better or worse in which situations?

Of course if you build a script/application and you're dealing with input, you must try to use a whitelist approach to filter the input. But at the same time, you want to escape your output. And that's what these functions are (meant) for.

For example, what if I want to allow usernames with an ' in them? Say, "Monsieur D'alfonso" is a valid name. How am I supposed to add that data to a database? By escaping the '. So, although using a whitelist approach to filter your input is a good point, how to escape output is another important question.

Posted: Wed Jan 25, 2006 3:44 pm
by shiflett
AGISB wrote:I think those functions are good for making sure the data isn't corrupted while saved into the database but NOT! for security against any attacks.
There are attacks that specifically target that context. For example, SQL injection. A very similar attack is XSS, and it attacks the same type of vulnerability in a different context.

It's a common misconception that these are input filtering problems:

http://www.oreillynet.com/pub/wlg/8235

Posted: Wed Jan 25, 2006 5:54 pm
by Maugrim_The_Reaper
I think those functions are good for making sure the data isn't corrupted while saved into the database but NOT! for security against any attacks.
Myself, I would filter, and regardless escape the same input value (whatever context). Point is to assume at least one block of protection will fail and have a backup (or three). It can definitely be used for security - not just an anti-corruption measure - I thought that was the point of evangilising the Filter/Escape approach. Its just common sense defense in depth...

Posted: Thu Jan 26, 2006 1:50 am
by Christopher
I agree with Maugrim, both filtering and escaping should be done, plus dealing with things like html entities. I don't know if I would consider this defense in depth though, because they are not really layers but are separate and necessary actions that need to be taken.

One way to think about this (which may be a helpful rule) is that a security measure needs to happen whenever we switch from one languge/domain to another. In PHP we have three critical places where this occurs:

1. HTTP to PHP - example: $myvar = $_GET['myvar'];

2. PHP to SQL - example: "UPDATE mytable SET myvar='$myvar'"

3. PHP to HTML - example: echo $myvar

Note that different measures need to taken in each instance -- filtering, escaping, and html entities.

Posted: Thu Jan 26, 2006 2:56 am
by Maugrim_The_Reaper
I agree with Maugrim, both filtering and escaping should be done, plus dealing with things like html entities. I don't know if I would consider this defense in depth though, because they are not really layers but are separate and necessary actions that need to be taken.
Quite true in many cases, however I've come across arguments on this forum and elsewhere that both are not required for a subset of input. Classic example is an integer value passed by form to be written to database. Many people would see the intuitive filter check - check is_numeric(), use intval() or casting. Many do not however see any sense in afterwards escaping that value - filter logic/validation has ensured its an integer.

In that sense defense in depth can be used to offer some good reason for the extra and arguably redundant step of escaping. Even then its an uphill battle...

Posted: Thu Jan 26, 2006 7:20 pm
by Christopher
Maugrim_The_Reaper wrote:In that sense defense in depth can be used to offer some good reason for the extra and arguably redundant step of escaping. Even then its an uphill battle...
I think you are right and that always doing all steps is the best practice. That would certainly be defense in depth. I stand corrected (and hopefully a little bit smarter ;)).

Posted: Sun Jan 29, 2006 1:21 am
by AGISB
shiflett wrote:
AGISB wrote:I think those functions are good for making sure the data isn't corrupted while saved into the database but NOT! for security against any attacks.
There are attacks that specifically target that context. For example, SQL injection. A very similar attack is XSS, and it attacks the same type of vulnerability in a different context.

It's a common misconception that these are input filtering problems:

http://www.oreillynet.com/pub/wlg/8235
I was not suggesting filtering out but doing a whitelist approach by allowing only what makes sense. There is a very big difference between both solutions.

Posted: Sun Jan 29, 2006 8:57 am
by shiflett
AGISB wrote:I was not suggesting filtering out but doing a whitelist approach by allowing only what makes sense.
Yeah, a whitelist approach is the best type of input filtering:

http://phpsecurity.org/code/ch01-3

My point wasn't that this is bad, but that it isn't a substitute for escaping output. These are two entirely separate tasks that are often confused to be the same, because input filtering can, coincidentally, stand on its own. (For example, an alphanumeric username can be safely used in many different contexts.)

Hope that's a bit clearer. :-)