Slashdot Mirror
Anatomy of a SQL Injection Attack
Trailrunner7 writes "SQL injection has become perhaps the most widely used technique for compromising Web applications, thanks to both its relative simplicity and high success rate. It's not often that outsiders get a look at the way these attacks work, but a well-known researcher is providing just that. Rafal Los showed a skeptical group of executives just how quickly he could compromise one of their sites using SQL injection, and in the process found that the site had already been hacked and was serving the Zeus Trojan to visitors."
Los's original blog post has more and better illustrations, too.
One should definitely use a persistence library instead of concatenating strings to help mitigate the possibilities of being victim of SQL injections. They are pretty good at it. Hibernate is a widely used one.
Everything I write is lies, read between the lines.
"CREATE TABLE" is probably a bad example, if your web code needs to create a table you're doing something wrong. However, for e.g. an INSERT statement you'd typically use bind variables, something like this:
// Binds SomeNumericValue to first "?" in statement // Binds SomeStringValue to second "?" in statement // Set values you want to insert // Insert new row, setting Col1 to 42 and Col2 to "Hello, world"
long SomeNumericValue;
char SomeStringValue[SOME_SIZE];
StatementHandle Statement = Parse("INSERT INTO TableName (Col1, Col2) VALUES (?, ?)");
BindNumericVar(Statement, 0, &SomeNumericValue);
BindStringVar(Statement, 1, SomeStringValue, SOME_SIZE);
SomeNumericValue = 42;
strcpy(SomeStringValue, "Hello, world";
Execute(Statement);
The user can see the table structure, perhaps the view definition, but not the data they have no rights to.
You deny select on the table, and grant access to the view. The view contains a constraint that forces the view only to return the data the connecting user is allowed to see.
I have implemented this in Postgres/PHP.
You have a group role that has read access to the public tables (eg products). The webserver runs, by default, at this user level.
When a user logs in, they reconnect to the database. They are in two groups now, the same one the webserver runs in by default, and another, which gives them access to their view
To CREATE users, you have an insert trigger in a users table to which the webserver user has INSERT rights, which then creates a new role with the required credentials.
Is it more work than a simple users table and single sign on? Yes. Is it a more sound methodology than SSO? Yes.
You wouldn't have SSO in a corporate environment, why should you have it in a web environment?
I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.
Funny, the thing I -like- about Perl is that it is very stable in the sense that variables never get strange values inexplicably. It is a very deterministic environment, set it up and it just works as promised.
And also the architecture of the language was not suited for web pages. When I saw PHP3, I switched to it immediately and never looked back.
There are packages that make it very well suited for web pages. OK, you can't really just sprinkle code into your html like you can with php (or maybe you can, but really, why the hell would you want to do that?) but it generates web pages just fine.
I totally agree with you about sanity checking in addition to using bound parameters. Never trust input.
A house divided against itself cannot stand.
If you look for a while you'll find them. The developers replied to me with "It's perfectly fine". While it seems they do parse this information isn't that screaming "Exploit me!"
I go through this all of the time. Though I call it laziness, it is actually a combination of ignorance, indignation, and laziness.
Here is a very, very, very simple and very, very, very standard way of keeping SQL injections out. Validate everything at every level. There you go. Done.
1) Client side matters. Check input, validate it and pass it through to the application layer.
2) Application layer matters. Check variable, strictly type it, validate it and pass it through to your data layer.
3) Data layer matters. Check argument against strict type, validate it, paramaterize it, and pass it off to the database.
4) Database matters. Check paramater against strict type, validate it, and run it.
You run into problems when someone only follows any one of the steps above. You could handle it with a medium level of confidence in areas 2 and 3 (and if you're asking why not 1 and 4, go sit in the corner while the grown-ups talk), but good practice for keeping it clean is validate it at every layer. That doesn't mean every time you touch the information you have to recheck the input, but every time it moves from one core area of the platform to another or hits an area it could be compromised, you do.
As I said above, the only reason for not following 1-4 is laziness, ignorance, or indignation. SQL injections aren't hard to keep out.
We're in an age where web development IS enterprise level programming and developers need to treat it as such.
There, I just saved your organization millions of dollars. Go get a raise on my behalf or something.
I wanted it to be short, easy for management to understand (even non-technical). Definitely worth watching, IMHO.
http://www.youtube.com/watch?v=jMQ2wdOmMIA
Good security is based upon reality and common sense. Common sense is a function of having common knowledge.
In regards to your experience with inexplicable values in Perl, it sounds like at the time you had issues with some combo of not using the strict pragma and not understanding how Perl works. If you don't fully understand what is going on, it can be confusing. If you're not using strict, it can be an extra confusing clusterfuck. I think there were a lot of tutorials and such in the mid 90s not using strict.
It was unstable in a sense that variables sometimes got strange values inexplicably.
Perl doesn't stop you from programming like a rodeo clown (for those who don't even qualify as cowboys...).
If you're going to make zealous use of globals and then use mod_perl you will get hurt.
Universities teach about something called "coupling". Every professional programmer will talk about something called "use strict". If either of these concepts are too difficult you're better off with a language that does its best to help you from yourself (but be aware Java threads are not going to stop any determined doofus from causing real pain).