Second order SQL injections

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Second order SQL injections

Post by alex.barylski »

I just read several articles on Mordred's blog, interesting perspective you have their. :)

One thing that confuses me though about second order SQL injection...

I only escape GPC data and Ill tell you why I don't escape config variables or data from a database, INI file, etc...

Anytime changes are made to those sources they come from GPC which are sanitized...so how is it that second order SQL injection attacks occur, especially if my models are the ones that escape all data before querying?

Why would I sanitize config variables from INI or database or XML or whatever before using that in a SQL query. While I agree with sanitizing data I'm not sure I see the point behind escaping data that has already been escaped???

Can someone offer some examples so I may reconsider my techniques and best practices.

Cheers :)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Second order SQL injections

Post by Mordred »

I am a bit confused by the description of your escaping practice, so I'll just drop you pointers on what I think is your problem, and you will meanwhile elaborate on your concerns.
I only escape GPC data and Ill tell you why I don't escape config variables or data from a database, INI file, etc...
Escaping is not a security measure. It ensures data integrity. (Only a subset of all possibly mishandled data is data that causes SQL injection).
Anytime changes are made to those sources they come from GPC which are sanitized...so how is it that second order SQL injection attacks occur, especially if my models are the ones that escape all data before querying?

Why would I sanitize config variables from INI or database or XML or whatever before using that in a SQL query. While I agree with sanitizing data I'm not sure I see the point behind escaping data that has already been escaped???
Whoo, walking on thin ice here.
It seems to me that besides missing the purpose of escaping, you also miss how it works on the database side of the deal.

I'll give you a counter-question. Why is that data which gets escaped before being put in the database, is not being unescaped after being retrieved from the database? Surely someone would mind all those \-s in the data?

The answer is that data escaping is needed only on the input interface of the database. It ensures data integrity on its entrance, because it comes in a bunch of SQL syntax. Data egress, on the other hand, doesn't happen in a special context, it's just formatted data. The characters in the data don't have meaning, so we don't need to treat some of them in a special way (unlike when the data enters the database). Note that it doesn't mean that "the database unescapes it for us" (a misconception I've heard once or twice) - it has never been "escaped" in the database! It is escaped only at the entrance, no further than that.

How does it relate to your case and second order injection:
1. SQL injection vector arrives on $_GET.
2. Your code correctly escapes it, and it safely enters the database without doing anything.
3. Parts of your model class pull data from the database, one of them happens to be the SQL injection vector.
4. The Update() method of your model gets called.

Here, I'm not sure what happens in your code, becase you're telling two different stories:

If "my models are the ones that escape all data before querying" is true, you're doing the right thing. The vector will get escaped once again, and will be safely handed to the database.

If "I only escape GPC data" is true, you're doing it wrong [insert lolcat joke here]. This is how second order SQL injection happens! If at Update() your model wants to discriminate between GPC and other data, and escape only the GPC data, it will leave the vector unescaped, and it will cause SQL injection.

In that case, the model failed to ensure the data integrity, and it led not just to a broken query (if the mishandled piece of data was benign - i.e. "O'Henry") but to full-blown malicious SQL injection.

------------

I will repeat my main thesis again - escaping is not a security measure! Remember that your model will want to pull the name "O'Henry" from the database and then put it back there. That's your true concern, but if you handle it correctly, SQL injection will not happen as well.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Second order SQL injections

Post by alex.barylski »

I think I got you...i'm pretty sure my code is safe from second order attacks.

Code: Select all

 
class MyModel{
 
  function Update($fname, $lname, $age)
  {
    $fname = mysql_escape_real_string($fname);
    $lname = mysql_escape_real_string($fname);
    $age = (int)$age;
 
    mysql_query("INSERT INTO table VALUES (0, '$fname', '$lname', $age)");
 
    return true;
  }
 
}
This isn't an accurate reflection as to how I escape my data it only demonstrates *where* I escape my data so pardon invalid functions or quoting...I actually used prepared statements and PDO so I don't explicity escape using the RDBMS required functions.

The model itself does not care where the data comes from...could be INI, XML and DB for all it knows...it secures the data at the interface to the data store, which is where I also validate data, to ensure integrity.

What confused me after reading your article was this statement:
What is “user supplied”? $_GET, $_POST, $_COOKIE, $_REQUEST are clearly user supplied. $_SERVER? Partly so. Database? It depends. (If you answered “no” to this one, go read about Second order SQL injection). Config vars? Nah, they’re fully in our control (hmmm)
What I was asking is how could config data be dangerous if it's escaped, secured or filtered prior to being serialized? I was under the impression that you were suggesting one escape data as they pull it from a data source AND escaped data before submitting -- which I interpreted as redundacy (DRY).

When data is pulled from a data source (INI, XML, DB, etc) it's already been checked, validated and presumably secured unless someone has access to the system and modified a INI file via text editor but then you have bigger fish to fry.

However I see what your saying now, in that you want to escape data at the incoming interface of the model not nessecarily at the outgoing.

If Model_A pulls on the database which is returned from Model_B so long as both models escape data upon incoming they don't need to escape data on the out going as well -- at least this is my current through process and is what I was curious to know if it could still be exploited via second order SQL injection???

The escaping I perform on outgoing data is in the template layer with htmlspecialchars().
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Second order SQL injections

Post by Mordred »

The model itself does not care where the data comes from...could be INI, XML and DB for all it knows...it secures the data at the interface to the data store, which is where I also validate data, to ensure integrity.
As I already said, if you're doing so, it's correct. It's just that you keep saying two things and not showing real code, so I can't help you any further besides pointing up at my previous post where I explained what might be wrong.
What confused me after reading your article was this statement:
What is “user supplied”? $_GET, $_POST, $_COOKIE, $_REQUEST are clearly user supplied. $_SERVER? Partly so. Database? It depends. (If you answered “no” to this one, go read about Second order SQL injection). Config vars? Nah, they’re fully in our control (hmmm)
What I was asking is how could config data be dangerous if it's escaped, secured or filtered prior to being serialized? I was under the impression that you were suggesting one escape data as they pull it from a data source AND escaped data before submitting -- which I interpreted as redundacy (DRY).
...
If Model_A pulls on the database which is returned from Model_B so long as both models escape data upon incoming they don't need to escape data on the out going as well -- at least this is my current through process and is what I was curious to know if it could still be exploited via second order SQL injection???
Escaping is done only before, never after. It ensures safe entry.

Why don't you post the code snippets that puzzle you so we can talk about a concrete example?


Here's a paper that discusses 2nd order injection attacks in general:
http://www.ngssoftware.com/papers/Secon ... ection.pdf
And one that mentions 2nd order SQL injection among other things:
http://www.ngssoftware.com/papers/advan ... ection.pdf

The advise they give is such a piece of cowdung though:
This is a dangerous problem, present in most large applications that attempt to 'escape' data. The best solution is to reject bad input, rather than simply attempting to modify it. This can occasionally lead to problems, however, where 'known bad' characters are necessary, as (for example) in the case of names with apostrophes; for example O'Brien

From a security perspective, the best way to solve this is to simply live with the fact that single-quotes are not permitted. If this is unacceptable, they will have to be 'escaped'; in this case, it is best to ensure that all data that goes into a SQL query string (including data obtained from the database) is correctly handled.
Wow. Just wow.

The correct solution is to escape everything, everytime before putting it in a database query, period.
Post Reply