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 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.
...for these modern times.
Does my bum look big in this?
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.
I still have more fans than freaks. WTF is wrong with you people?
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.
A more simple way is to use a parametrized statement. No extra libraries if you are using Java, .NET, or PHP5.
http://xkcd.com/327/
You still need bind variables mentioned by the gp if using HQL.
http://www.owasp.org/index.php/Interpreter_Injection#ORM_Injection
Comment removed based on user account deletion
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
My only issue w/ stored procedures comes from an abstraction quarrel:
Where should the logic be? The code? The DB?
What if I need to debug, what if someone else needs to debug?
I've seen way too many nasty examples of shit going awry in databases, because someone has crazy triggers or stored procedures in place without documentation..
For PHP + *SQL, use DBO, first proper interface for databases in PHP IMO.
Where I work there is no interface to the database other than stored procedures, yes writing programs takes longer and requires one of the DBAs to make the procedure, however, we have never had a single incident of some cowboy programmer forgetting to add a where clause to an update/delete, nor some insane environment where random pageviews clobbers the databases.
The logic for the dataset should be in the database where it belongs.
Crazy trigger/Crazy procedure problems are the same as every where else, if it's undocumented the code is hard to maintain.
Not sure what your problem with debugging a procedure is, most databases has interfaces for tracing procedures, I actually find SolidDb procedure trace to be preferred over normal print statements in .
Unless you are trying to put Chris O'Connor into your database, and his name must be spelled correctly...
A house divided against itself cannot stand.
You can't stop reading slashdot. Full of nonsensensical arguments, but you read on, your brain oozes, your eyes are red, dry and hurt. Still, you read on, and participate in the debate. You don't recognize your odd behavior. There's a sequel reply injected into your brain. It's a slash dot sequel brain virus injection. There's no cleaning utility, you will need to reformat your brain.
Build your own energy sources from scratch. http://otherpower.com/
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.
learn from Scotty. always double your estimates... Especially when they ask for an honest estimate.
I'm up to a multiple of 16 now.
Deleted
I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.
Perhaps less(or more) drinking would help?
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.
Stored procedures are not the cure-all for everything. They are good if you have only a few ways of doing things, but it's ridiculous to write a different stored proc for every single column that you want to sort by. Its stupid to write a new stored proc for every possible way of varying the query. Yes they do guaruntee some kind of type checking and parsing compliance, but you can do that with a prepared statement as well. Dynamic SQL is a lot more flexible, especially when the number of stored procs would be combinatorial in number. You just have to be smart, and know what to do. Try converting your values to the types you want. Make your own parser if there is no other way, but for example, in the .NET world you can use ADO.NET with the typed parameters on text queries and it's every bit as safe and efficient as a stored proc. I'm not sure how well or not this translates to PHP and MySQL but I think the db module has most of the same stuff, if I recall correctly.
Speak for yourself.
Simply searching on google fo the tail end of the URL shows exactly which sites are vulnerable and the provider of the sites... Now the entire database of restaurants is open to attack. If the author was trying to teach their client a lesson or two (or 50)--well, good job...
Monitor bandwidth usage on IIS6 in real-time: http://www.waetech.com/services/iisbm/
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?
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.
Do you have ESP?
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).
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.
Anthropic principle: We see the universe the way it is because if it were different we would not be here to see it.
I was going to MOD this up as super informative but I had to pipe in myself ;)
Having worked in a small startup, a major Fortune 500, and in between companies this kind of thing is by far the best approach over the long run. The places where the DB/Code guys are separate always end up with a better product. Simply because it allows people who excel at something to really apply that benefit to what their doing. I love writing code but hate writing SQL and maintaining databases. So I tend to focus on the code and the DB stuff gets done but pretty half assed. Now people could say you should do it all equally well but in real life that never happens. Let the database go do his thing and the programmer guy do his thing. Get them talking together and your product will benefit greatly.
Also when logic is in procs, views, whatever you don't need to redeploy anything to achieve results. Simply change the database and it's done.
Hold up, wait a minute, let me put some pimpin in it
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.
If I have been able to see further than others, it is because I bought a pair of binoculars.