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.
Re:Use a persistence library (Score:5, Insightful)
Persistence is just a bad idea, it hides the real performance issues of how databases work, and limits how you can easily manipulate the data. A better idea is just to always use bind variables. Problem solved.
Aarghhhh (Score:5, Insightful)
I for one am sick and tired of these types of attack. Whoever, in their right mind thought it was a good idea to expose SQL query inputs on the Web?
Ever heard of input sanity checking? It was very popular in the say, 60's, 70's and 80's. It means you reject fields you don't expect to be there, instead of arbitrarily passing them onto the backend database. These types of attacks illustrate what is wrong with web security: developer convenience trumps common sense everytime...
Next time we see Ballmer hopping along shouting developers, maybe could he please add the words 'SECURITY BY DESIGN', please, pretty please?
SQL injection attacks are asinine because they are so prevalent, easy for the hackers AND easy to fix. We should name and shame every site, and every web-application stack that allows these attacks to take place.
nuf said.
Re:Use a persistence library (Score:5, Insightful)
A more simple way is to use a parametrized statement [wikipedia.org]. No extra libraries if you are using Java, .NET, or PHP5.
Re:Aarghhhh (Score:3, Insightful)
Sanity input checking was EASY when it was programmed into the 3270s.
To make a "Web Programmer", whatever kinda tool (operator) that is do some real work and provide a sane interface is like having just the one chimp pound away at the keyboard and produce Shakespeare immediately.
Re:Aarghhhh (Score:2, Insightful)
The problem is that what a programmer does is largely behind the scenes and no one really know what they do anyways. The current crop of "programmers" are web designers who learned a bit of web programming to add to their skill set. They don't understand any of the implications of what they are doing and only know how to take results from a database and display it in a nice looking web page.
Re:Aarghhhh (Score:5, Insightful)
Then what needs to be done is make the libraries have this security implemented *by design*.
That is, the only possible way to get or insert data from a database should be the correct one. Security should be an enforced feature of the library (PHP, Java, etc).
It is kind of like "accessibility", it is available there (at least say, in Java and Flash) but *because* it is not compulsory, very few programmers implement it.
Comment removed (Score:5, Insightful)
Re:Use a persistence library (Score:2, Insightful)
Using stored procedures is harder than just creating the SQL query.
You need to know even more about the DB.
Even it's harder if you have to get the DB guy to do stuff for you.
Yes you can run your own DB in development, but in the production environment, you may depend someone else (DBA) to set up all those stored procedures so that stuff works.
In contrast on sane programming languages using explicit SQL queries with "bind variables/parameters" can actually be EASIER than using SQL and doing the quoting for each variable yourself.
Such stuff used to be hard to do properly on PHP+MySQL (example - there's boneheaded stuff like register_globals, magic_quotes and also mysql_escape_string() vs mysql_real_escape_string(), good thing there's no mysql_the_actual_real_escape_string_this_time_no_kidding()
And many hosting sites still are stuck with the "old PHP" ways.
Re:Aarghhhh (Score:1, Insightful)
The invention of things such as PHP abstracted security to yet another level. Instead of dealing with buffer overflows, you're now dealing with SQL injection and XSS.
There is only one way to get security and that's by taking it seriously and not letting libraries, etc, do it for you.
The more that security is simply assumed, the more that it is ignored and the more that it becomes an issue.
SQL Injections SHOULD NEVER WORK (Score:5, Insightful)
If your code is running at the correct privilege level, SQL injections should be completely irrelevant.
If your user is connecting with the correct credentials, they should only be able to see public data and their own records, nothing else.
This is implemented by using views in the database, and only allowing users rights to views, not tables.
If your website user is connecting with credentials that allow a crafted SQL query to see priveleged data, you have set everything up wrong
If you have set everything up correctly, even a successful SQL injection will only return data the user can see
Re:Aarghhhh (Score:3, Insightful)
Re:Aarghhhh (Score:5, Insightful)
You're clearly not writing software for a living...
There are a few things more important than security: time to delivery and budget.
Colour this with unrealistic expecations and you get situations like these:
"What's your estimate?" *honest assessment* "Ok, so if you work harder, you can do it in less time right? (all programmers are soo lazy.. I read that somewhere)"
"Well, it depends on what I encounter while bringing this analysis into reality..."
"Just make it work so we have something to show for by date xx-xx-xxxx"
^
Even in large coorperations with large budgets, the smaller one's usually are more idealistic but are on a tight budget.
Because alot of developers are struggling with getting the "damn thing to work", and there are so many shifts in deadlines, "security", as a seperate item, often is neglected because people are relieved they're having something "up and running".
I do agree though, that initial design and architecture should be welldefined and requires extra attention with security measures and considerations built-in, whereas many developers are running around with such a sense of urgency and pressure they just want to get to "coding thing" instead of thinking first what and how they'll code it, yet it doesn't change or improve the environment and pressure which results in these things.
Re:Aarghhhh (Score:5, Insightful)
Ever heard of input sanity checking?
Yep, it's that enormously annoying thing that almost no developers get right. They filter out emails that contain + or -, names with accents, and zip codes / phone numbers for other countries. You should never reject a value from a user: If it looks wrong to you, suggest that they change it, but for f's sake put it in your DB. And don't tell me about quotes or backspaces - RTFM or Google it.
Re:Use a persistence library (Score:3, Insightful)
Note: I even admit in my profile I'm a bad web developer.
I have JFGI, but most of the stuff I've found leads me to articles I don't fully understand how to implement. I mostly code simple websites for my school and friends that have little db interaction, but I'd rather learn to do it right from the beginning, so if anyone has some links to good articles for beginners to understand how to properly secure their SQL code, I'd be happy for the help.
Re:limit the length and content of what you accept (Score:5, Insightful)
Re:Aarghhhh (Score:5, Insightful)
Well, there are many like that, and in essence that's webdevelopment, right?
Consider an application where you can control the logical flow, you need to know your basic language and your GUI's behave the way you expect. Done.
Now, for being a webdeveloper you need to know HTML, XHTML, CSS, JavaScript, PHP, MySQL, MS SQL, .NET (preferably working knowledge of 3.5 and playing around with WCF/WPF), AJAX-concepts and implementation, various toolkits and libraries in place, XML, XSLT, JSON, WebServices, COM+ interaction, and need strong afinity around security concepts and be aware of injection methods, sniffing, current state of hashing algorithms, make sense of server technology and scaling (if your server is in fumes, you need to kickstart it) so that extends to IIS, Apache. If you're going more the el cheapo/opensource approach, it's mostly a box running Apache, MySQL and PHP (for which you need to subtle differences through different releases) often Mediawiki too, so you'll need to find your way around a Linux station and often are deploying and setting up such a box ad hoc as well... It adds up quite fast if you've consulted a bit and in each environment encounter different setups, architectures and approaches.
"Web development" has gotten pretty involving to get the pretty display, for which there isn't really a good methodology anymore as the web has evolved in such a way the "hypertext markup" combined with "style sheets" sortof feels dated. (that's why you have XAML, Flash, Flex, .. trying to solve the problem adding to complexity).
I do agree; webdev is pulling data and storing data while showing it in a pretty way, modify the page based on that and have a fluid user experience. However, those lasts are pretty difficult if there's a clear idea about the result and you need to depend on external parties (IE bugs, FF bugs, toolkits bugs, API frameworks with bugs, ...) to get your thing done.
I think webdevs are the gluers between all these frameworks and layers, there's maybe not much writing logic, but trying to make sense of the mess and compiling and stringing very specific technologies (legacy or hyped new) together in order to have a functional and pleasing result.
It's odd to me that there's a general looking down on webdevelopers, not just from non-techies, but also from techies whoe feel their work is "so much more significant" because "they have to think more and aren't a code monkey", yet wouldn't survive in an unstructured choatic environment where you have to think on your feet and act quick when things fall out and can't have flow in a straight line (say "I'll write function x and y today, and nobody will bother me all day while I do so") but are constantly interrupted or required to take some action, asap and efficient, while you're juggling a dosen projects, maintaining another handful and are trying to please clients. Plus ofcourse, get new projects worked out, writing analyses and following up/leading communication of 3rd parties in order "to hook up that webservice the client wanted to implement" and god knows what.
But yes, it's just displaying stuff on a page, right? I can show you complex systems (webbased stockmarket software fe.) which makes your head spin and cry in desperation (I've seen some break up and give up on the legacy mess), yet it's all "just showing data in pretty boxes" and "pulling it from a datasource" (stock market floor) and "saving it" (processing orders with business rules and automating processing of orders all within legal limitations) all to meticious specification of the clients, all with their own perculior wishes?
"But they are the lazy programmers and we don't know what the hell they are doing, but they have no concept of the implications of their work, sir.". Put
Re:Use a persistence library (Score:3, Insightful)
What about "The code was written and deployed when PHP3 was new and noone will give me any money for fixing it and probably blame me if anything breaks."?
I have written a lot of web stuff from 1995-2000 (first in C using Tom Boutell's cgic, then in perl, then in PHP) and yes, quite a bit is still in use today. What should I do about all those old vulnerabilities lying around (keep in mind that I mostly quit web development in 2001)?
Re:Aarghhhh (Score:5, Insightful)
The problem is not the web devs it's the managers who didn't realize they need both a programmer and a webdev.
They are very different functions. If you have only webdevs you tend towards the sort of security mess we are seeing here. If you have only programmers you end up with a site that is butt ugly and useless from a user interface perspective.
Your stock market display software is a good example of a case where the entire project will fall apart unless you have programmers who can move the data efficiently and securely and then some skilled webdevs to handle the user interface work.
didn't you ever watch startrek? (Score:5, Insightful)
learn from Scotty. always double your estimates... Especially when they ask for an honest estimate.
I'm up to a multiple of 16 now.
Re:Aarghhhh (Score:3, Insightful)
The counterpart to accepting any input is sanitizing any output. It's really very easy if you have centralized DB fetching (and you should).
Re:PHP security object (Score:3, Insightful)
Quick answer: A lot.
Long answer:
You are mistaking escaping with sanitising. These are two very different things.
Sanitising should occur as soon as possible, before the values are used. It involves validating and optionally filtering _each_ field, so that you know the data you are getting is exactly what you are expecting it to be. This is a lot of work, which is why a lot of people skip it, hence the large number of vulnerabilities in the wild. I suggest looking into libraries like Zend_Form to help with this.
Escaping on the other hand, is done just before the variable is used. This is because different output formats have different escape sequences. E.G for SQL you would use named variables and let the engine handle the escaping for you, but for HTML you would use something like htmlspecialchars().
Both sanitising and escaping are required for a secure application.
Re:Use a persistence library (Score:1, Insightful)
Use addslashes on all variables and make sure you set the character set and you are done. Go to sleep.
Re:Use a persistence library (Score:4, Insightful)
You define it up front in the project and stick to it.
Look at the skill set of the team. If you don't have a strong database guy, your logic will probably be in the app layer.
If the database is shared between apps/services/etc., then more logic needs to be enforced there. Data integrity triggers to prevent bad data from getting into the database from any side. Access to tables going through stored procs.
If you have to debug, you work through it regardless of where it is. Just like testing anything with multiple layers (gui, app layer, remote web services, database code, etc.), you test each layer individually but using the same call as a collective test. Eventually, you will isolate the layer with the issue. Dig in deep and root out the problem.
Re:SQL Injections SHOULD NEVER WORK (Score:2, Insightful)
That is assuming that each web user has their own database account, and more importantly, their own set of views; this introduces a couple of problems.
1. No SQL database engine I'm aware of supports "generic views" taking the user as a parameter in a reasonable way. If they did, you might have a case, but the
2. One db-user per web-user? If your web-application has more than a few hundred users, your DBA will kill you for this.
3. Most web app servers use connection pooling; some DB engines support "switching user" on an open connection, but there are security implications there too. Without user-switching, you screw up performance as establishing a connection is very slow.
Since you DO have to prevent SQL injections anyway, the price for this strategy is generally too high for the bonuses it brings.
Re:It is a sad world we live in. (Score:1, Insightful)
Your post is quite good. In fact, I'm sitting in the corner, thinking that (1) enhances the user experience, but does not by any means decrease any chance of SQL injection. Anyone with Firebug can overcome client side validation. (4) Is the essential point where the data is actually persisted. You can perform as many contortions as you want with your data, but only what is stored there will be retrieved. So it makes sense to start with making sure (4) is correct, because any manipulation on the data has the ultimate objective of being persisted in the database.
Let's hear from a grown-up then what is the role of (2) and (3), please, please. I've not yet made mysellf a strong opinion on protecting these layers, but seems to me that they are much less important than (4). And from a security perspective, (1) is completely irrelevant.
Re:Use a persistence library (Score:4, Insightful)
The author should be more careful... (Score:3, Insightful)
Re:Aarghhhh (Score:3, Insightful)
Heh, no. Finished MSc in CS in 2004, worked in large companies + CERN since then, and doing a PhD now. And yes, I believe it's easy and desirable to accept any input. Learn to escape and unescape (or parametrization when possible), along with the basics of Unicode, and your users will love your software even more.
Re:Use a persistence library (Score:5, Insightful)
Putting the logic in stored procedures allows ME, the DBA, the guy with the SQL know-how, to tune the gawd-awful query that you, the pointy-clicky .NET monkey, is using to bring my server to its knees. NINE left joins again subqueries, each with a GROUP BY, then another GROUP BY applied over the query as a whole? WTF are you thinking? Fixing your code requires a new build & deployment cycle. Fixing a stored proc, I can do that with a simple DROP/CREATE script.
Yes, I'm bitter. I'm surrounded by pointy-clicky types who insist on procedural thinking when writing queries. Set theory? What's that?
This lovely programmer has sold his code around (Score:5, Insightful)
Took me 2 minutes with Google to find other sites that are apparently using the same crappy code with the same vulnerabilities. "inurl:" does wonders.
Re:The author should be more careful... (Score:3, Insightful)
You're right.
On the other hand, Google hides nothing. Just google for 'client login' or 'customer login' plus maybe some random word such as 'enterprise', or 'sales' or what have you.
I can guarantee you that in the first fifty results, you are in. Just fill in as the username:
' or 'a'!='
Re:Aarghhhh (Score:3, Insightful)
Ok but seriously what we are talking about here is really not that hard. It should be standard procedure to escape user input before it hits the dbms. I mean all we're talking about is casting strings to floats or integers where numbers are expected and escaping string input. In PHP you'd run the input through intval()/floatval() or mysql_real_escape_string() before you shunt it to the db - it isn't rocket science...
Re:Use a persistence library (Score:1, Insightful)
At this rate, I'd rather roll my own. Individual calls to bindValue when we've got HASHES in our language? Inability to reuse parameter names (where x=:foo and y!=:foo)? Fuck no!
bindValues($_POST) and letting the prepared query decide what parameters it's actually going to be using, rather than dicking around with every possible combination of ways to search for something in my table? Hell yes!
Re:Use a persistence library (Score:2, Insightful)
Tell your people to hire real programmers (because real programmers know TSQL and set theory just as well as you do) and stay the hell away from my business logic, thanks. Worry about replication, administration, and index tuning like you're supposed to. (The "A" in DBA, ya know...)
In the rare case that I can't figure out how to make a specific query fly - I'll ask for your help - but rest assured that the code stays in my control.
Re:Use a persistence library (Score:3, Insightful)
This includes coders who insist Hibernate is the be all and end all of database interfaces. 'I don't need to know SQL well because I have just abstracted that whole icky database thingy.' Meanwhile you get both shit code (because they are all silver bullet use the latest technology and agile technobabble to write factory and strategy patterns everywhere when a simple 'if' statement would have worked well) and shit database performance. No I am not frustrated nor bitter at blind lemming-like stupidity. OK, well a little... (and yes, hibernate and other ORMs are good, when used in moderation).
Re:Use a persistence library (Score:2, Insightful)
Actually that's a real problem when you have to maintain versioned systems.
You can't track versions of stored procedures as easy as you can do it within the code. At least I'm not aware of a fair method to do this.
Re:Use a persistence library (Score:2, Insightful)
Another difficulty is to database schema changes. Want to change to a partitoned table? Can't really do that when the DBA has no idea how all of the client apps are interfacing with the tables using dynamic SQL. I have seen too many situations where the end result was that the DBA made the change, and waited until applications started breaking to figure out how people were (incorrectly) using the schema.
Re:Use a persistence library (Score:3, Insightful)
No, the logic belongs to the app. The database is there to store data, how we are going to use it is app-dependent.
Be careful with generalizations. It's a matter of requirements not some universal law.
If it makes sense to put it in the app and you don't particularly care about performance or security (because you trust your users) then put it in the app. I would have a problem with letting an untrusted user execute direct queries against my database though - the app has to log in somehow to the database with a username and password, and it's never hard to extract the username and password, and so suddenly a malicious user has access to do pretty much anything they want. If the app can delete a single order then the user can delete all orders etc.
Some advantages of putting business logic in the database are:
1. code reuse - your windows app, linux app, web app, and mobile app can all re-use the same business logic and just have to focus on the presentation layer.
2. control - you get to control exactly what the app can do, audit it etc, because all access goes through your stored procedures
3. performance - as fast as your network is, you aren't going to beat having the code running in the database process itself
4. performance again - left to their own devices, users will construct horrible queries that will sap the very life out of your database. If they have to go through your stored procedures then they can't wreak as much havoc on your server.
But at the end of the day the requirements might be that only a few trusted users are going to use this app, all access is via the web, and they want it finished yesterday, and so suddenly none of the above advantages really matter so much...
Re:limit the length and content of what you accept (Score:3, Insightful)
I agree. Just like any regular program, input must be reduced to an EXPECTED set of values.
This is a good advice, but not when applied to this example. You do not want to restrict what users type for their name in any way (as a bunch of replies already point out, GP has missed a few obvious things... and then, what if I'm Russian or Japanese, and want to use my original alphabet?). All that needs to be done is properly escaping any unsafe chars (and for those, the set is known and well-defined); leave everything else be.