Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Anatomy of a SQL Injection Attack

timothy posted more than 4 years ago | from the the-alarm-you-trip-could-be-your-own dept.

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

cancel ×

267 comments

Sorry! There are no comments related to the filter you selected.

Use a persistence library (2, Informative)

ls671 (1122017) | more than 4 years ago | (#31283036)

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 (5, Informative)

Splab (574204) | more than 4 years ago | (#31283082)

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 (0)

Anonymous Coward | more than 4 years ago | (#31283214)

"Use procedures/views where possible and never allow dynamically created queries." How true. Still upsets me how many developers are anti Stored Procedures

Re:Use a persistence library (2, Insightful)

Anonymous Coward | more than 4 years ago | (#31283378)

> 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 (3, Interesting)

Splab (574204) | more than 4 years ago | (#31283488)

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 (1, Informative)

Anonymous Coward | more than 4 years ago | (#31283812)

first proper interface for databases

AKA "the lowest common denominator". Thanks, but I'll keep using pg_prepare/pg_execute where I can, and pg_query with pg_escape_string where I can't.

Let me know when someone comes up with one of these "generic" interfaces that can actually manage to use the database's prepared query API (rather than the usual fakeout [purdue.edu] of escaping and inserting the text into a standalone query for you, removing the query planner benefit of the database knowing what you're doing, and hoping to God that your API knows what it's doing [wikipedia.org] when it escapes the text).

Re:Use a persistence library (1)

mgkimsal2 (200677) | more than 4 years ago | (#31283892)

PDO has been around for years, and offers standardized escaping and binding for all the major db platforms. If you're stuck with an "old PHP ways" host, they probably are still using PHP4 and have register_globals set to on - IOW, time to move to a modern host. Just like you wouldn't stick with a Java host only offering 1.3 or 1.4, it's time to vote with your wallet and move to modern hosting operations.

Re:Use a persistence library (3, Interesting)

Archon-X (264195) | more than 4 years ago | (#31283466)

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 (3, Interesting)

Splab (574204) | more than 4 years ago | (#31283510)

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 (4, Insightful)

lorenzo.boccaccia (1263310) | more than 4 years ago | (#31283374)

how true. it baffles me how may developers use hibernate to protect themselves from injections and then proceeds to concatenate client generated string to HQL queries confident that the library will "magically" protect them.

Re:Use a persistence library (1)

will_die (586523) | more than 4 years ago | (#31283490)

And use of procedures do nothing to prevent SQL injections besides they generally show down your queries. Views also don't help much until you disable thier abaility to be used in insert and updates.
Use parameterized SQL be in with dynamic SQL, procedures or views.

Re:Use a persistence library (2, Informative)

Splab (574204) | more than 4 years ago | (#31283552)

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 (1)

will_die (586523) | more than 4 years ago | (#31283664)

Don't know SolidDB when doing this type of stuff it is in MS-SQL Server and Oracle.
The statement to use store procedures usually comes from MS-SQL server people who read and continue to repeat information from over a decade ago when MS-SQL server treated procedures and dynamic SQL differently; unfortunatly this has spread into Oracle space. With the query optimizer under MS-SQL Server and Oracle you don't have to worry about using procedures since dynamic SQL is treated the same way, especially if you use paramterized queries.
Where you really get the slowdown in the MS-SQL Server and Oracle is that with the stored procedures being used for everything they contain alot of ISNULL and COALESCE checks which are slow in addition to causing a new query to be generated.
This is for standard CRUD statements if you have something really complex and makes multiple calls back to the database parses something then makes some more queries then yes procedures are going to be faster.

Re:Use a persistence library (2, Informative)

weicco (645927) | more than 4 years ago | (#31283728)

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 [sommarskog.se]

Re:Use a persistence library (0)

Anonymous Coward | more than 4 years ago | (#31283116)

Or use directly an ODBMS like Versant with the JDO persistence API. That is nearly unbreakable.

Re:Use a persistence library (4, Insightful)

AuMatar (183847) | more than 4 years ago | (#31283120)

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 (2, Informative)

Anonymous Coward | more than 4 years ago | (#31283212)

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 (1)

Hurricane78 (562437) | more than 4 years ago | (#31283300)

Yeah, until someone comes at it with a cross-site scripting attack. ^^

Re:Use a persistence library (0)

Anonymous Coward | more than 4 years ago | (#31284114)

What has Cross-site scripting to do with SQL Injections?

Re:Use a persistence library (0)

Anonymous Coward | more than 4 years ago | (#31283652)

Fun

Re:Use a persistence library (2, Insightful)

edumacator (910819) | more than 4 years ago | (#31283670)

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 (1)

DrXym (126579) | more than 4 years ago | (#31283768)

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.

That assumes performance is somebody's number 1 priority. An app might use something like OpenJPA or Hibernate because code correctness, scalability, time to market or portability are more important than performance. Besides, I bet for typical database queries, the performance boost from handwriting SQL vs Hibernate (hql) / OpenJPA (jpql) generating it would be neglible. If you absolutely had to hand tune some SQL you could even slap it in a stored proc or function which is probably a good idea anyway for some actions.

If performance or a legacy database was a concern all over the place then iBatis or its ilk might be a better fit. Then you can handwrite every SQL call but at least it sits in an XML file so it doesn't pollute the application code. It's still harder to code than using a persistence API though.

Re:Use a persistence library (0)

Anonymous Coward | more than 4 years ago | (#31283822)

How is persistence a bad idea? Persistance libraries make it easy to manipulate the data in an object oriented way and with sensible caching most performance issues become non-issues.

Having said that, Hibernate (for example) uses parameterized statements under the hood, so sql injection is also a non-issue. Lots of problems solved :)

Re:Use a persistence library (5, Insightful)

mk_is_here (912747) | more than 4 years ago | (#31283152)

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

Re:Use a persistence library (0)

Anonymous Coward | more than 4 years ago | (#31283350)

I second this. Preventing SQL Injection is ridiculously easy, with either parametrized statements, stored procedures or a persistence library and of course input sanitation. There is simply no valid excuse for failing to use either of these methods to prevent it.

Re:Use a persistence library (2, Insightful)

andi75 (84413) | more than 4 years ago | (#31283806)

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 (1)

Michael Kristopeit (1751814) | more than 4 years ago | (#31283870)

There is simply no valid excuse for failing to use these methods to prevent SQL Injection.

you're not accepting "job creation to fix the mess"? think of the unemployed!

Re:Use a persistence library (1)

moreati (119629) | more than 4 years ago | (#31284204)

Sure you're aware of this, but to make to clear for everyone. Python, Perl and other languages don't require extra libraries to do parameterized queries either. In Python the pattern is

import db_module
conn = db_module.connect('user/pass@host')
curs = conn.cursor()
curs.execute('select field1, field2 from table1 where field3 = ? and field4 = ?', ('foo', 7.6))
curs.fetchall()

Exactly the same number of lines as doing it with string munging, but type safe and zero chance of sql injection.

Re:Use a persistence library (1)

ArsenneLupin (766289) | more than 4 years ago | (#31283718)

No need to use a persistency library, but there is no excuse to set up queries by concatenating string. Use wildcards instead! All modern databases support them. executeQuery("update users set score=? where id=?", 95, 113); There is no way anybody could abuse that. The only place where concatenating may be accepatble is for variable ordering: executeQuery("Select * from users order by "+column+(desc ?" desc":"")) And here you better make sure you compare column against the list of valid columns first.

A cautionary tale' OR 1=1 (4, Funny)

kyz (225372) | more than 4 years ago | (#31283086)

...for these modern times.

Aarghhhh (5, Insightful)

boner (27505) | more than 4 years ago | (#31283134)

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 (2, Insightful)

dltaylor (7510) | more than 4 years ago | (#31283180)

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 (5, Insightful)

xtracto (837672) | more than 4 years ago | (#31283270)

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 (1)

zefciu (1654897) | more than 4 years ago | (#31283356)

But forcing programmer to do things 'right way' can limit his abilities to hack interesting solutions. Yes for sanitization as *default*. No for limiting my possibilities of writing queries the framework's author didn't think about.

Re:Aarghhhh (1, Insightful)

Anonymous Coward | more than 4 years ago | (#31283430)

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.

Re:Aarghhhh (0)

Anonymous Coward | more than 4 years ago | (#31283496)

In that case there should be a switch to turn security off, which every day sends an email to the CEO letting him know. If he's OK with that, then sure.

Or strike that - how about some giant message on login? That must be disabled using an _additional_ procedure? The way programming chimps work is, they will try to cobble something together until it works, but if there is an explicit "security on-off" switch that they MUST turn off to succeed, they might actually take the time to read what is in the attached documentation.

Re:Aarghhhh (0)

Anonymous Coward | more than 4 years ago | (#31284006)

no, 99% of developers will just default to always turning off the "safe mode" and then keep making the same stupid mistakes they make now. The other 1% already do correct input checking and wouldn't be impacted by the enforced rules anyway.

Re:Aarghhhh (1)

vadim_t (324782) | more than 4 years ago | (#31283404)

Please provide an example of how would it work.

For instance, in Perl I can do a query safely like this:

my $sth = $dhb->prepare("SELECT * FROM users WHERE user_id = ?");
$sth->execute($user_id);

But, I also have a bit like this:

if ( $filter_owner ) {
    $cmd .= " WHERE owner_id = ?"
    push @args, $filter_owner;
}
 
...
 
my $sth = $dbh->prepare($cmd);
$sth->execute(@args);

The second bit is also safe, but it creates a query by concatenation which could be used unsafely.

Re:Aarghhhh (1)

WiFiBro (784621) | more than 4 years ago | (#31283800)

It's not such a big deal to filter all user input inorder to prevent SQL injection. It's simply a habit you need to learn and stick to.

It is more difficult to make a site that allows some people to provide content including html and script, and still prevent evil content to enter your database / pages.

And it is difficult to enforce a strict password regime because many a client have asked to remove the safety measures for convenience sake. I guess we all know examples of dumb passwords. Like 'coconuts' for the admin section of a coconut group.
 

Re:Aarghhhh (1)

dkf (304284) | more than 4 years ago | (#31284058)

It is more difficult to make a site that allows some people to provide content including html and script, and still prevent evil content to enter your database / pages.

The issue there is that you're allowing that at all (see CWE-79 [mitre.org] ). The solution is to not allow general HTML/script input from non-trusted sources (i.e., they can upload new HTML with sftp, but not through a web form) and instead support some greatly restricted syntax (e.g., bbcode or wikisyntax) that is easy to convert to guaranteed fang-free content. And use a proper templating library for output of content from the database instead of hacking things.

Re:Aarghhhh (2, Interesting)

vadim_t (324782) | more than 4 years ago | (#31284128)

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 (0)

Anonymous Coward | more than 4 years ago | (#31283860)

When people are talking about unsafe query concatenation, they're talking about "WHERE owner_id=".$filter_owner;

Your query's only risk is that someone will make a typo and then none of the ? marks line up with the arguments anymore.

Re:Aarghhhh (1)

Kjella (173770) | more than 4 years ago | (#31283620)

Then what needs to be done is make the libraries have this security implemented *by design*.

Libraries do, but they're powerless against string concatenation unless it's impossible to run raw SQL. I think the only thing you could do is deny non-paramter values at all, but it'd make everything a lot more annoying and probably have a performance impact. Like you couldn't say "WHERE is_active = 1" but had to use "WHERE is_active = ?" and bind the value.

Re:Aarghhhh (0)

Anonymous Coward | more than 4 years ago | (#31283318)

Jesus Christ, you can't even talk straight. Can you think straight? Code straight?

Re:Aarghhhh (2, Insightful)

The Mighty Buzzard (878441) | more than 4 years ago | (#31283448)

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 (1, Insightful)

gmack (197796) | more than 4 years ago | (#31283232)

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 (0)

Anonymous Coward | more than 4 years ago | (#31283282)

Leave them to it.

It'll keep us that actually know what we're doing in a job :).

Re:Aarghhhh (5, Insightful)

ZeroExistenZ (721849) | more than 4 years ago | (#31283792)

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 a sock in it. You sound like management I've walked out on numerous times. They all come back begging to help them out, but they can find some other tool to be looked down to as long he's doing good work and keeps all running and evolving to meet deadlines. It's when things are burning and melting down that you "realize what these guys were doing to keep it from burning down".

Re:Aarghhhh (4, Insightful)

gmack (197796) | more than 4 years ago | (#31283884)

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 (4, Interesting)

ZeroExistenZ (721849) | more than 4 years ago | (#31283942)

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

SQL is the problem, really. (1)

MichaelSmith (789609) | more than 4 years ago | (#31283304)

I have been doing a bit of work with sqlite lately and I am surprised to find that the C api is basically a way to pass in strings containing SQL commands. Now even in C I could imagine an API which allows you to build up queries to do everything SQL does without using commands in text strings.

With an OO language it should be dead easy.

Re:SQL is the problem, really. (1)

GvG (776789) | more than 4 years ago | (#31283396)

Sounds like a problem with sqlite, not SQL in general.

Re:SQL is the problem, really. (1)

MichaelSmith (789609) | more than 4 years ago | (#31283434)

Sounds like a problem with sqlite, not SQL in general.

So why can sql code ever be injected on other platforms?

Instead of execute_command("create table X")

I want to see create_table("X")

Re:SQL is the problem, really. (2, Informative)

GvG (776789) | more than 4 years ago | (#31283516)

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

Anonymous Coward | more than 4 years ago | (#31283584)

SQL is the "Structured Query Language", which defines a textual interface to a relational database. If you want an interface which doesn't involve parsing on the database side, it is not going to be SQL. There are workarounds in APIs to help the programmer construct safe SQL queries, but in the end it's the SQL which creates the pitfalls that must be worked around.

Re:SQL is the problem, really. (1)

Michael Kristopeit (1751814) | more than 4 years ago | (#31283924)

the mother and father of the programmer created the pitfall that must be worked around...

Re:Aarghhhh (4, Insightful)

ZeroExistenZ (721849) | more than 4 years ago | (#31283462)

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.

didn't you ever watch startrek? (4, Insightful)

Colin Smith (2679) | more than 4 years ago | (#31283920)

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 (1)

BBadhedgehog (955308) | more than 4 years ago | (#31283520)

*> Ever heard of input sanity checking? It was very popular in the say, 60's, 70's and 80's.

Exactly! Guard the borders of your application, regardless of where those borders are: file, UI, webservice, whatever. Do it right at the border and only when you're sure it's not going to blow a hole in the system, then let it through.

And always use whitelists.

Re:Aarghhhh (4, Insightful)

l0b0 (803611) | more than 4 years ago | (#31283618)

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 (0)

daveime (1253762) | more than 4 years ago | (#31283772)

If it looks wrong to you, suggest that they change it, but for f's sake put it in your DB ?

Wha ??? No, no, a thousand times no !!!

First Name ? "Mr javascript:redirect_to_malicious_website"

Sure, let's put that in the DB, and hope to fucking christ whoever takes it OUT of the db and uses it on a webpage has the sense to escape it ???

If data is suspect, you never insert it into the DB. Passing on the responsibility to the output stage is no substitute for stopping the crap going in on the input stage.

Re:Aarghhhh (2, Insightful)

l0b0 (803611) | more than 4 years ago | (#31283968)

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 (0)

Anonymous Coward | more than 4 years ago | (#31283858)

but for f's sake put it in your DB. And don't tell me about quotes or backspaces - RTFM or Google it.

You should treat userdata as an ejaculate; it's like saying "ok, I want your DNA" and someone starts facialling you, because it's not what you actually ment (you wanted an oral swabbing.) that's why you wear a mask and gloves doing such operations...

So, you rather wrap your semendata in a condom or tissue when you handle the data-ejaculate, unless it's your own or someone (woman) who wants it or you're involved in a trusting and loving relationship where ejaculates have become part of a regular interation which is known to be trusted and ok.

The database doesn't want AIDS and the business logic shouldn't hocus pocus when it comes out of the database, the database must be trustable while pulling things out of it; in many cases alot of people are pulling things out of the database and trust the database to be AIDS-free. Input should be checked and validated as proper, if you cannot predict what "proper" is, invest time in r&d and datamining/statistical data-validation routines.

tl;dr: Your database is your lover who doesn't want AIDS. Because if something comes out of it, you don't want it to be spewing AIDS all over the place, you secure input, not output (otherwise you'd have women wearing vagfilters during menstruation and giving birth instead of using condoms).

Re:Aarghhhh (1)

grumbel (592662) | more than 4 years ago | (#31283964)

Whoever, in their right mind thought it was a good idea to expose SQL query inputs on the Web?

Most people are not doing it because they want to, but because the software they use allows such things to silently happen behind their back. It is a classic case of in-band signaling, you are pumping data through the same pipe as code and when the data isn't properly escaped, things break in bad and unexpected ways. To get rid of this once and for all you need to seperate the pipes, seperate the data and the code and don't allow them to be mixed. LINQ for example does that by moving the query language into your programming language, instead of having it as string-magic somewhere outside the languages syntax.

Obligatory xkcd (4, Funny)

tangent3 (449222) | more than 4 years ago | (#31283236)

Re:Obligatory xkcd (4, Funny)

Inda (580031) | more than 4 years ago | (#31283292)

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

Re:Obligatory xkcd (1, Funny)

Anonymous Coward | more than 4 years ago | (#31283436)

Select TOP 1 as bestever from t_XKCD where id = thisone

Lemme be the first to say (1)

bytesex (112972) | more than 4 years ago | (#31283338)

Use perl. Because the support both in java and php for applying regexes and preparing SQL statements has been late, convoluted and lacking.

Re:Lemme be the first to say (1)

Max_W (812974) | more than 4 years ago | (#31283544)

I used Perl in 90's. Then switched over to PHP.

I remember that Perl was not too good for web programming. It was unstable in a sense that variables sometimes got strange values inexplicably.

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.

PHP also has got its minuses (why I cannot create RAR or ZIP archive locked by a password on a website?), but in general it is OK, if one pays attention to what he gets from users.

I found out that casting: page.php?id=123 , i.e. $id=(int)$id; is not enough. One has also check that $id is not greater than, say, 1000, and is not less or equal zero. Because I saw that some visitors try to insert huge numbers into $id for some reason. So basically value of incoming variables should be carefully checked before processing it further. If $id cannot be more than 1000 or negative, than we just enforce it.

Re:Lemme be the first to say (2, Informative)

pedestrian crossing (802349) | more than 4 years ago | (#31283722)

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

ArsenneLupin (766289) | more than 4 years ago | (#31283836)

Although there might be lots of reasons to use Perl rather than Java (and vice-versa), security against SQL injections is not one of them. Java JDBC has been supporting wildcards (parameters) [sun.com] (using statement.setObject(pos,value);) since day one.

Re:Lemme be the first to say (1)

pooh666 (624584) | more than 4 years ago | (#31284180)

Yeah totally agree on that. DBI and JDBC have a great deal in common, but I still think JDBC is beautiful. With either you have to work fairly hard to be an idiot, or else just not bother to learn the whole of their specs. I don't think that should take more than a couple of weeks they just make *sense*

Re:Lemme be the first to say (1)

pooh666 (624584) | more than 4 years ago | (#31284028)

Support for binding params in PHP has been there a LONG LONG time(5 years maybe more?), it is the culture that tends not to use it. I discovered it as a kind of odd hack sort of thing, not commonly documented when it first came out. One reason is it had to be adopted, it wasn't a part of PHP to begin with. WHY PHP didn't have it to BEGIN with, that is my issue with PHP. To hold true to its credo, I would think that binding params would be seemless and transparent with no need for a developer to make a choice. That didn't happen for some reason. I found the same to be true not too long ago with Ruby, it blew my mind when I read about its MySQL interface and oh that is coming soon. This was some time ago, but I dropped it right there. Rails could go stuff itself if basics like that were not in place. So I like Perl too, but you are not accurate in your statement or focus of blame.

SQL Injections SHOULD NEVER WORK (5, Insightful)

mcalwell (669361) | more than 4 years ago | (#31283432)

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 (3, Interesting)

will_die (586523) | more than 4 years ago | (#31283580)

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 (2, Informative)

mcalwell (669361) | more than 4 years ago | (#31283704)

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 (1)

Random Person 1372 (1529155) | more than 4 years ago | (#31283854)

Is it more work than a simple users table and single sign on? Yes. Is it a more sound methodology than SSO? Yes.

Single sign-on [wikipedia.org] is not the same as using the same user account for everybody...

Re:SQL Injections SHOULD NEVER WORK (1)

Bengie (1121981) | more than 4 years ago | (#31284036)

how does your design method apply to blog/twitter style web sites where everyone about inserts to tables and about everyone reads everyone else inserts?

Your view method may work well in your Postgres DB setup, but in the MS world, a view causes a full table scan and cannot be indexed unless your fork out $25k per socket for enterprise ed. using views sounds bad to me.

Re:SQL Injections SHOULD NEVER WORK (1)

mcalwell (669361) | more than 4 years ago | (#31283716)

of database credentials or having different area, including duplicate pages, that handle the different database credentials.

Why would you have duplicate pages? You store the database connection credentials in a session, or even the database connection object. When they login, they change credentials and run with those.

Re:SQL Injections SHOULD NEVER WORK (1)

Eivind (15695) | more than 4 years ago | (#31283698)

Uhm. No.

Well, yes, but it don't help much. True, the web-sql-user should only have access to information it needs to see. But that doesn't help you at all against the fact that a single web-user shouldn't nessecarily be able to see everything and do everything the web-server as such can see and do.

To make a concrete example, if you're making a internet-bank, then the web-frontend need to be able to see the account-balance and movements of everyone who has internet-banking, it also needs to be able to put in new transactions.

But it doesn't follow that Joe should be able to see Janes balance, or to transfer Janes balance to himself.

No web-frontend I know of create and use one sql-connection, with the apropriate rights, for every user of the web-application. I suspect that'd be very unwieldy to do anyway.

SQL is not always the answer ... (1)

oneiros27 (46144) | more than 4 years ago | (#31283898)

You're right -- because it's SQL, which has assumptions about how it's used.

LDAP, on the other hand, you can set up to bind as the individual user, and you adjust which attributes a user is allowed to see or modify in their own entry, and which entries they can see in other entries.

So, part of the solution is using the correct data store for the situation, and SQL isn't always it. (I haven't played with any of the "NoSQL" stuff yet, but much of the behaviour with replication and and flexibility of storage seem rather similar to the LDAP implementations I've worked with.

Re:SQL Injections SHOULD NEVER WORK (1)

Qzukk (229616) | more than 4 years ago | (#31284118)

The idea is that instead of creating a "users" table and filling it with your users, the user is created as a database user, and their username and password is handed straight to the database during the connection process. If it connects, the user had a valid username/password. If it doesn't connect, the user didn't. If you have a million users, then your database server would need to be able to handle having a million different users each with different levels of access on different tables/rows/columns/etc.

Aside from the problem of having the database wade through a million users to decide if you have permission to perform every last query, the process of finding out whether a user has permission to perform a query in advance is usually a hairy system-level (possibly db-version-specific, new system catalog next version) query, but if you don't do that, then you get users raging at you because they spent their time filling out a form and it threw an error at them when they hit save and now it's all gone and they want that 30 minutes of their life back.

The other problem is that for most database servers, db accounts are server/cluster-wide, meaning that on any kind of shared hosting, everyone's usernames have to be distinct across people you don't even know about.

Re:SQL Injections SHOULD NEVER WORK (3, Interesting)

ArsenneLupin (766289) | more than 4 years ago | (#31283750)

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.

Independent Programmers' No-Win Scenario (0)

Anonymous Coward | more than 4 years ago | (#31283440)

Yesterday we read about the problems of individual developers who can't get a foothold in the industry due to a shift towards accountability for software bugs. Today we read about SQL injection vulnerabilities affecting many web sites. Can anybody else see how one is the cause of the other? It is very obvious that a lot of people who should not come anywhere close to a text editor are writing public-facing code. Is there a way to remove these people from the pool other than by making programmers responsible for failing to prevent at least well known attacks? How are serious programmers, who take the time to get it right, supposed to compete when any hack can get away with abysmal code quality?

PHP security object (1)

DeanLearner (1639959) | more than 4 years ago | (#31283534)

I create a security object that stores $_GET and $_POST as arrays and escapes all the contained details, once this is done i blitz both $_GET and $_POST so they cant accidentally be called within the programme.

From this point to call a get variable you need to call $security->get('name');

This object also checks for dodgy content like scripts and the like and further down the line, each input is checked for proper formatting.

Im wondering though, what else should I be doing?

Re:PHP security object (0)

Anonymous Coward | more than 4 years ago | (#31283978)

So you made your own magic quotes object?!

What you should do is escape the data according to how you use it.
For SQL, use prepared statements.
For html, use htmlentities.
For javascript, make your own escape function.
For shell arguments, use escapeshellarg.

Re:PHP security object (2, Insightful)

ArwynH (883499) | more than 4 years ago | (#31283982)

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.

limit the length and content of what you accept (0)

bl8n8r (649187) | more than 4 years ago | (#31283572)

This isn't a new concept, just one that people have been removed from.

If ($QUERY_STRING > $MAX_QUERY)
{
    print "*Boom* Check server for smoke!"
    exit;
}

# only allow characters 0 through 9 and upper/lowercase a-z
$Input = $QUERY_STRING;
$Input =~ s/[^0-9a-zA-Z]//g;

Re:limit the length and content of what you accept (4, Insightful)

pedestrian crossing (802349) | more than 4 years ago | (#31283746)

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

Re:limit the length and content of what you accept (2, Interesting)

Dunbal (464142) | more than 4 years ago | (#31283832)

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

Anonymous Coward | more than 4 years ago | (#31283862)

So, no names with accent ? No emails with "-" or "." or "+" or whatever freaking character is legal ? In many *Western* countries " ' " is a vaild character in names/surnames (O'Neill -> http://en.wikipedia.org/wiki/O%27Neill_%28surname%29). And let's not start to mention countries with non-Latin alphabets, which are where the majority of people in the world live.

USDA likes to put SQL strings in their URLS (2, Informative)

RaigetheFury (1000827) | more than 4 years ago | (#31283754)

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 (4, Funny)

h00manist (800926) | more than 4 years ago | (#31283760)

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.

Re:Slash Dot Virus Sequel Injected in You (0)

Anonymous Coward | more than 4 years ago | (#31283882)

There's no cleaning utility, you will need to reformat your brain.

S.M.A.R.T. status: Critical

It is a sad world we live in. (5, Informative)

TaggartAleslayer (840739) | more than 4 years ago | (#31283828)

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. (2, Interesting)

asdf7890 (1518587) | more than 4 years ago | (#31283900)

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.

Just bind! (1)

Angvaw (992553) | more than 4 years ago | (#31284022)

Just bind! [oracle.com]

I produced a video on SQL injections - (2, Informative)

JRHelgeson (576325) | more than 4 years ago | (#31284060)

I wanted it to be short, easy for management to understand (even non-technical). Definitely worth watching, IMHO.

http://www.youtube.com/watch?v=jMQ2wdOmMIA [youtube.com]

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>