Anatomy of a SQL Injection Attack 267
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.
Use a persistence library (Score:3, Informative)
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.
Re:Use a persistence library (Score:5, Informative)
One should use positional/named bindings and let the driver handle escape sequences, make sure the Web user only has access to what is needed, rather than running everything as root. Use procedures/views where possible and never allow dynamically created queries.
Re:Use a persistence library (Score:2, Informative)
I have found, that if used correctly, hibernate can be quite powerful; you can still run native and database independent HQL queries if you like.
You can also map your native queries to objects; it is quite easy, and I believe it is same as binding to variables.
The entity manage also helped me to reduce the amount of queries that I hard code into my DAOs; you can query for objects based on their class and ID (yes, it does support composite IDs).
Also provides control for optimizations, and will automatically link objects together (depending on if they are eagerly fetch or lazily fetched)
Read a little about Hibernate before passing judgement on it.
Although, i got to admit; it does hide quite a bit. I have been on a few teams who were using it incorrectly, and the application performance degraded quite a bit because of it. (IE, retrieving a list of hundreds of Entity objects, instead of selecting only the properties they want in a HQL statement)
Re:Use a persistence library (Score:4, Informative)
http://www.owasp.org/index.php/Interpreter_Injection#ORM_Injection [owasp.org]
Re:SQL is the problem, really. (Score:3, Informative)
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);
Re:Use a persistence library (Score:3, Informative)
That really depends on your database flavour. SolidDB which I primarily work with, it is impossible to construct dynamic queries within a procedure.
Also your claim that procedures only slow down databases is just plain wrong. Databases with procedures where the SQL is immutable will genrally run much faster than your dynamically generated versions. Philippe Bonnet and Dennis Sasha claims (their book, "Database Tuning") that as much as 9/10 of your average query time spend in the database is spend on the query optimizer, SolidDB for instance will cache all cursors within a procedure (when instructed to), enabling performance gains in some cases (in our system) of up to 3000%, moving the data up into a higher language like C is unlikely to speed up your performance because the database will be unaware of what you plan on doing with the result set and is thus unable to prefetch data and optimize the general retrieval of data.
Re:SQL Injections SHOULD NEVER WORK (Score:2, Informative)
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?
Re:Lemme be the first to say (Score:3, Informative)
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.
Re:Use a persistence library (Score:3, Informative)
There's an excellent article on dynamic queries and little bit about SQL injections here but it's Sql Server specific so I don't know if it's any good for the Slashdot crowd: http://www.sommarskog.se/dynamic_sql.html [sommarskog.se]
USDA likes to put SQL strings in their URLS (Score:3, Informative)
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!"
Re:Use a persistence library (Score:1, Informative)
first proper interface for databases
AKA "the lowest common denominator". Thanks, but I'll keep using pg_prepare/pg_execute where I can, and pg_query with pg_escape_string where I can't.
Let me know when someone comes up with one of these "generic" interfaces that can actually manage to use the database's prepared query API (rather than the usual fakeout [purdue.edu] of escaping and inserting the text into a standalone query for you, removing the query planner benefit of the database knowing what you're doing, and hoping to God that your API knows what it's doing [wikipedia.org] when it escapes the text).
It is a sad world we live in. (Score:5, Informative)
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 produced a video on SQL injections - (Score:3, Informative)
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 [youtube.com]
Re:Use a persistence library (Score:3, Informative)
Except that Python's DB-API [python.org] is a horrible mess. Depending on what db_module is, you might need to spell your query as:
curs.execute('select field1, field2 from table1 where field3 = ? and field4 = ?', ('foo', 7.6))
curs.execute('select field1, field2 from table1 where field3 =
These aren't options that you have as a programmer, no. db_module.paramstyle tells you what format to use, and your application needs to use the one the back-end is expecting. It's a perfect example of why "can't decide? just make it an option!" is not a viable strategy.
Re:Use a persistence library (Score:3, Informative)
??
You can write stored procedures that take variables, for instance..one that will change the ORDER BY clause to what you want. Also, it isn't much of a problem to overload stored procedures...same name, but behaves differently by the number or types of parameters you call it with.
Re:Lemme be the first to say (Score:3, Informative)
Re:Lemme be the first to say (Score:4, Informative)
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).
Re:Use a persistence library (Score:4, Informative)
Since there is no user input used in generating the query, you can never have an SQL inection attack, and still use dynamic queries. There are ways to do dynamic queries, without opening yourself up to attacks.
Re:Use a persistence library (Score:4, Informative)
What if "item"s came from the users in the first place? Most databases don't return the strings pre-escaped for reuse in the database.
Personally, web programming is where "Hungarian Notation" style variable names shine: I have htVariableName, dbVariableName and the original inVariableName, and it's blindingly obvious when I'm using the wrongly-escaped string in the wrong place or re-escaping something I already escaped.
Re:Use a persistence library (Score:3, Informative)
Yeah, all this SQL stuff always confuses me. Partially because I often am in the Joomla framework, which doesn't let you do parameterized queries, and, while I guess you could do stored procedures, I've never seen the need.
Instead, I simply take all input and make sure it is sane. Is it supposed to be a number? Put an (int) before assigning it out of $_POST. (Now there's a JRequest::getInt that I'm learning to use instead.) Am I putting a string from a user into a database? I use $db->getEscaped(). When I get it, unless I can think of some justifiable reason otherwise.
I understand the point of all this, as people often leave themselves open to SQL injections, but I suspect the people who do that don't even consider the possibility anyway, and this entire discussion is lost on them. I guess, in a large company, it might make sense to require all queries are parameterized(1) or stored, so morons are required to do things sanely, but smart programmers check things at the start, and don't run around with obvious malicious input to start with.
I mean, what if, instead of an SQL injection, it was some other vulnerability? Sanity checking on just the DB query risks the rest of your program having insane data. Your program presumably does do more than write input straight to a DB, even if it's just checking permissions to write to the DB.
What if they passed in '0+523' as their userid, and your program ended up checking that, yup, they were logged in as their passed userid, '523', and then later, yup, their passed userid was '0' and hence they're an admin? Sounds crazy, but not impossible in typeless languages.
Sanity check at the start, as you assign to other (typed, as far as you can) variables, then permission check those variables if needed, and then you're good to just use raw SQL for 99% of the stuff, and all your calculations will be good. Escape and possibly even pre-quote all strings that are going to a database. (Which is especially handy, as if the strings have quotes to start with, you can instead assign NULL without quotes to that variable, and have both magically work using WHERE `blah`=$variable.)
The only time you'll have to worry about escaping at SQL query time is input where you can legitimately have quotes and semicolons and stuff in it and you used that input elsewhere. Which a lot of people do, if only to display what the person entered...but that's silly. It's much better to write a 'display' function that pulls from the data, in one place, and on an input or update, you use that to queries the data you just saved so a) they can see it worked, and b) see if you truncated it or whatever. If they typed data that ended up in a database, and you want to show said data to user in confirmation, put it in the database and pull it back out, don't display their inputted data that supposedly made it to the database.
I'm sure there are circumstances where you might want the unescaped data, and fine, escape that tiny fraction at query time. But in general, just get the semicolon out of their 'userid' at the start, and stop fucking around making sure each and every query is safe if they've decided to do that.
1) Of course, idiots with automated tools can write insane queries, also, and DBAs get real pissy about that and demanded stored queries, but that's not really relevant to this discussion, which is talking about security, and not idiots who can't conceptualize that SQL servers are finite.