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.

Comments

Use a persistence library

[ls671]

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

[Splab]

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

[AuMatar]

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.

Re:Use a persistence library

[mk_is_here]

A more simple way is to use a parametrized statement. No extra libraries if you are using Java, .NET, or PHP5.

Re:Use a persistence library

[Anonymous Coward]

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

[SpazmodeusG]

You still need bind variables mentioned by the gp if using HQL.
http://www.owasp.org/index.php/Interpreter_Injection#ORM_Injection

Re:Use a persistence library

[Anonymous Coward]

> Still upsets me how many developers are anti Stored Procedures

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:Use a persistence library

[Archon-X]

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

[Splab]

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

[Splab]

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:Use a persistence library

[Splab]

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:Use a persistence library

[edumacator]

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:Use a persistence library

[weicco]

Use procedures/views where possible and never allow dynamically created queries.

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

Re:Use a persistence library

[andi75]

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:Use a persistence library

[SQLGuru]

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:Use a persistence library

[TheSunborn]

IT's very simple. Don't use any of the mysql_* functions.

Use the PDO prepare function (http://dk2.php.net/manual/en/pdo.prepare.php) and remember newer to pass any input you got from the user directly into the string you give to prepare.

In most cases(99%) the string you give to the prepare function should really be constant and not depend on user input at all.

Re:Use a persistence library

[aldousd666]

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.

Re:Use a persistence library

[QuoteMstr]

Except that Python's DB-API is a horrible mess. Depending on what db_module is, you might need to spell your query as:

1. 
   curs.execute('select field1, field2 from table1 where field3 = ? and field4 = ?', ('foo', 7.6))
2. 
   curs.execute('select field1, field2 from table1 where field3 = :1 and field4 = :2', ('foo', 7.6))
3. curs.execute('select field1, field2 from table1 where field3 = :field3 and field4 = :field4', {field3:'foo', field4:7.6})
4. curs.execute('select field1, field2 from table1 where field3 = %s and field4 = %s', ('foo', 7.6))
5. curs.execute('select field1, field2 from table1 where field3 = %(field3)s and field4 = %(field4)s', {field3:'foo', field4:7.6})

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

[Simon Brooke]

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:Use a persistence library

[ShannaraFan]

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?

Re:Use a persistence library

[cayenne8]

"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. "

??

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:Use a persistence library

[CastrTroy]

It's ok to create dynamic queries as long as you aren't generating those based on user content. Doing the following (VB/Pseudocode) is perfectly fine.

sql = "SELECT item FROM table WHERE keyword IN ("
FirstValue = True
ParamNo = 1

For each Value in MyValueList
If Not FirstValue Then
sql &= ","
Else
FirstValue = False
End If

sql &= "@Param_" & i
cmd.Parameters.AddWithValue("@Param_" & i,Value)
ParamNo += 1
Next

sql &= ")"

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

[jlechem]

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

[CastrTroy]

Well this is the entire problem. People doing stuff that they have no business doing. There's a lot of coders out there who could write a lot better SQL than many DBA's I've seen, and a lot of DBAs who can write SQL better than coders. Now it all comes down to how you want to run your company. Do you want to hire a coder, who can't write SQL, and and DBA who can write sql, and get them to communicate together to get the jobs done, or do you want to hire 1 person, who can do both coding and SQL, and they can get the job done by themselves? Oh, the other problem. If they can't code SQL, what kind of job will they do in the other programming language they are working in. Most of the coders I know who work in an environment where SQL is used, and who don't know SQL aren't very good at writing the other half of the code either.

Re:Use a persistence library

[Qzukk]

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

[DavidTC]

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.

Re:Use a persistence library

[Anonymous Coward]

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

[he-sk]

I cringe at this particular example of boilerplate code, b/c I have seen it so often lately and it's such an obvious choice for refactoring. If your language of choice has string join operator, the above code can be expressed in two lines:

items = list.join(", ")
sql = "SELECT item FROM table WHERE keyword IN (" + items + ")"

Much more readable with a better chance to spot the bug in your code: What happens if list is empty?

IHMO, better yet is to put the values in a temporary table using a batch insert and then perform a normal table join. This will allow the database to at least cache the query plan. Depending on the smarts of the SQL execution engine it could apply various other optimizations: Like sort the inner table (items) and then reorder the access to the outer table. (Of course, these kind of optimizations are theoretically possible without using two tables, but the last time I checked, PostgreSQL didn't bother.)

Re:Use a persistence library

[theshowmecanuck]

Most of the coders I know who work in an environment where SQL is used, and who don't know SQL aren't very good at writing the other half of the code either.

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

[alien9]

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

[TimothyDavis]

Another way to phrase what parent is saying is that the database is a shared resource. It only takes *one* poorly written ad-hoc query to screw performance for everyone interfacing with the database.

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

[jamesh]

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...

A cautionary tale' OR 1=1

[kyz]

...for these modern times.

Aarghhhh

[boner]

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:Aarghhhh

[dltaylor]

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

[xtracto]

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.

Re:Aarghhhh

[The Mighty Buzzard]

Yeah and a bungee cord is easier to hold your front door closed with than a deadbolt or even a standard doorknob but you'd still have to be a fucking moron to use one.

Re:Aarghhhh

[ZeroExistenZ]

developer convenience trumps common sense everytime

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

[l0b0]

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:Aarghhhh

[ZeroExistenZ]

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.

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:Aarghhhh

[gmack]

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.

Re:Aarghhhh

[ZeroExistenZ]

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.

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

[l0b0]

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:Aarghhhh

[vadim_t]

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.

Re:Aarghhhh

[l0b0]

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:Aarghhhh

[gpuk]

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...

Obligatory xkcd

[tangent3]

http://xkcd.com/327/

Re:Obligatory xkcd

[Inda]

Oh, oh, oh, please let it be Bobby DropTables, please, please.

Comment removed

[account_deleted]

Comment removed based on user account deletion

SQL Injections SHOULD NEVER WORK

[mcalwell]

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:SQL Injections SHOULD NEVER WORK

[will_die]

Couple of problems with this.
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

[mcalwell]

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:SQL Injections SHOULD NEVER WORK

[ArsenneLupin]

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:SQL Injections SHOULD NEVER WORK

[Anonymous Coward]

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:SQL is the problem, really.

[GvG]

"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:

long SomeNumericValue;
char SomeStringValue[SOME_SIZE];

StatementHandle Statement = Parse("INSERT INTO TableName (Col1, Col2) VALUES (?, ?)");
BindNumericVar(Statement, 0, &SomeNumericValue); // Binds SomeNumericValue to first "?" in statement
BindStringVar(Statement, 1, SomeStringValue, SOME_SIZE); // Binds SomeStringValue to second "?" in statement
SomeNumericValue = 42; // Set values you want to insert
strcpy(SomeStringValue, "Hello, world";
Execute(Statement); // Insert new row, setting Col1 to 42 and Col2 to "Hello, world"

Re:Lemme be the first to say

[pedestrian crossing]

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:limit the length and content of what you accept

[pedestrian crossing]

Unless you are trying to put Chris O'Connor into your database, and his name must be spelled correctly...

USDA likes to put SQL strings in their URLS

[RaigetheFury]

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!"

Slash Dot Virus Sequel Injected in You

[h00manist]

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.

It is a sad world we live in.

[TaggartAleslayer]

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.

Re:It is a sad world we live in.

[asdf7890]

One to add to you list if we stray beyond just SQL injection and consider other attack vectors too:

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:It is a sad world we live in.

[DarkOx]

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:limit the length and content of what you accept

[Dunbal]

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.

didn't you ever watch startrek?

[Colin Smith]

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:PHP security object

[ArwynH]

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.

I produced a video on SQL injections -

[JRHelgeson]

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

Re:Lemme be the first to say

[pooh666]

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?

Use access control

[helixcode123]

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.

The author should be more careful...

[joshuao3]

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...

Re:The author should be more careful...

[cerberusss]

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'!='

This lovely programmer has sold his code around

[Trailer Trash]

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:Lemme be the first to say

[Jimmy King]

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.

Re:Lemme be the first to say

[ztransform]

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:limit the length and content of what you accept

[shutdown -p now]

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.