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:4, Interesting)
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..
Re:Use a persistence library (Score:4, Interesting)
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.
Re:Use a persistence library (Score:4, Interesting)
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 .
Re:SQL Injections SHOULD NEVER WORK (Score:4, Interesting)
If the attacker can still input SQL commands they can display the views,tables, procedures,etc that the account accessing the database can access. Besides most current databases allow you to use views for update and insert.
That means you need to implement a solution using multiple database credentials that way they attempt to access something the account used to access the database has the least permissions needed for the specific page and the rights of that current user. There are very few tools that understand using multiple database credentials and those that do are expensive and a pain, been a few years so maybe they are better.
So that leaves you having to write your own code and adding alot of code to handle the switching of database credentials or having different area, including duplicate pages, that handle the different database credentials.
Re:SQL Injections SHOULD NEVER WORK (Score:4, Interesting)
If your code is running at the correct privilege level, SQL injections should be completely irrelevant.
True, if you run your web app at the correct privilige level, there is no way an SQL injection can be used to root the machine.
But it can still be used to corrupt the application itself, which is often more valuable that the system.
Example: a gaming application that wants to store a score per user. Even if the app uses a separate DB user per game user, and even if the DB only allows the user himself to update his score, this would not be good enough, because SQL injection might allow a player to assign himself an arbitrary score of his chosing.
Re:limit the length and content of what you accept (Score:3, Interesting)
I agree. Just like any regular program, input must be reduced to an EXPECTED set of values. Bounds checking must be performed. Anything outside that strict set of values must be rejected offhand and an error message provided. This is programming 101.
Unfortunately when HTML, PHP and SQL went "mainstream", these core programming concepts didn't get passed along. Frankly I say let "evolution" take careof/teach sloppy web developers - the smarter ones will have backups and be able to fix their problems. What really gets me is when you see large, allegedly professional sites taken down by something as silly as this.
Re:It is a sad world we live in. (Score:3, Interesting)
5. Output matters. Check data from the layer below, ensuring any characters that might carry unintended meaning but need to be in the data are escaped as required.
Always check the data on the way out as well as on the way in, in case something malicious got in by any means (due to a failure in steps 1 through 4, or direct database access by other means). This is implied by your supplementary text, but I think it is worth explicitly adding to the list itself.
Re:Aarghhhh (Score:5, Interesting)
This is a very valid point, yet "programmer" and "webdev" is often seen as very closely related with a blurry line; in my experience a "webdev" is a programmer who's proficient with webtechnologies, but usually has a blind spot for design. (or the inability to be visually creative and create pretty interfaces, but might be brilliant with logical creativy and finding solutions). The agencies I've worked for had the design part done by "designers" who drew a few designs, shook hands on one and had a "webdev" implement it. They never touched the websites, just sliced up images when they were done.
Maybe my strong reaction was rather based on the difference of concept we have from "webdev" and "programmer", for me they're very closely related wheras you seem to see the "webdev" as a designer with a course of HTML or something alike :)
Re:Aarghhhh (Score:3, Interesting)
I think you didn't understand my question. The grandparent said: "That is, the only possible way to get or insert data from a database should be the correct one". That excludes any kind of "habit you need to learn and stick to", it must simply be impossible to do otherwise.
My question is, how do you actually implement a system like that? I'd like an example code of a hypothetical system that would allow me to compose an arbitrary SQL query with variable amounts of selected columns, JOIN and WHERE clauses, etc, while guaranteeing that it won't be vulnerable to SQL injection.
To make the above more challenging, my code also constructs queries that sometimes include calls to stored procedures inside them.
Use access control (Score:2, Interesting)
When setting up a system I always set up both a readwrite and readonly database user, granting only SELECT for the readonly user. Many web apps are "SELECT-only" that grab info out of a database and display it. By requiring these apps to use the readonly user adds another layer of protection should the web programmer code unsafely. Note that a hacker can still get info out of the database using injection, but can't put stuff in, or delete your data.
Re:Use a persistence library (Score:3, Interesting)
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.
Speaking as someone who has used both approaches, Hibernate is a lot of overhead for, in many cases, very little gain, and having used it on a number of large projects my team has decided not to use it in future. Of course you must sanitise all values passed in from untrusted clients carefully before they are spliced into any SQL string, but there are a number of frameworks which do this which are far lighter weight than Hibernate.
Re:It is a sad world we live in. (Score:3, Interesting)
I am with you on thee through 4, and you probably should or are doing 1 because you want to be able to help the user put the right information in fields, check onblur an give some useful feedback but spending allot of time on careful input validation at the client level with web is pretty pointless. Anyone doing something malicious does not have to use your interface at all.
Re:Use a persistence library (Score:4, Interesting)
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.
Re:Use a persistence library (Score:3, Interesting)