How Prevalent Are SQL Injection Vulnerabilities?

Krishna Dagli writes to tell us of an investigation, by Michael Sutton, attempting to get an estimate of how widespread SQL-injection vulnerabilities are among Web sites. Sutton made clever use of the Google API to turn up candidate vulnerable sites. You might quibble with his methodology (some posters on the blog site do), but he found that around 11% of sites are potentially vulnerable to SQL injection attacks. He believes the causes for this somewhat alarming situation include development texts that teach programmers insecure SQL syntax, and point-and-click tools that allow the untrained to put up database-backed sites.

The abuse of SQL injection

destroys thousands of lives a year. Sure, it starts small - a SELECT there, a few INSERTS on the weekend. Eventually, though, you're using stored procedures and trying to score triggers in the middle of the night.

Just say no, kids.

Re:The abuse of SQL injection

Yeah, but most people are still stupid and humorless. So, in the end, I come out ahead.

Unfortunately: Not Surpirsing

This is a possibility that was obvious back when I was developing web applications as far back as 1996 using CGI. The approach in TFA was a similar approach we used "back when" to demonstrate the need for (a) not using GET, (b) turning off verbose error reporting, (c) controlling *how* queries were made (e.g. architecture of the app and DB I/O), and (d) storing sensitive data encrypted. The sad part is that it is *still* a problem. I guess it underscores the need for a decent architect as opposed to letting whiz-bang do-it-yourselfers start coding without design, and the need for security analysis, et. Al. Just my 2 cents.

Re:Unfortunately: Not Surpirsing

How does not using GET stop anything, you can POST anything you want to a webserver just like you can GET anything you want from a webserver. Only using POST will make things a little harder, but it doesn't stop anything.

Re:Unfortunately: Not Surpirsing

IMHO, any web application should be written to accept both GET and POST.

No. Web applications should use GET and POST where appropriate - GET for idempotent requests (showing database records, search results, those kind of recurring, non-database-changing things) while POST should be used for things which actually change data, user state, and so on.

Using GET in the wrong places [slashdot.org] can lead to all kinds of irritations and miniature security problems. Imagine sending an email to your web application administrator containing something like the following: <img src="http://example.com/webapp/user_admin?action=e dituser&username=me&accesslevel=administrator" width="0" height="0">...

Many web applications do get the two horrendously mixed up (I've seen search results done via POST, which is subtly, incredibly annoying) but they're definitely not interchangeable.

For simply displaying a non-password-protected package shipment page, GET would probably be the best solution. But blindly accepting both isn't a good alternative.

Simple solution

The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.

Point and click tools

The fact that tools can be used to put up insecure sites is not exactly a failing of the tool. The tool will have had a spec (even an informal spec) - which may have been "put sites up fast and let users sort out the security".

It's only a failure of the tool, or the developer of the tool, if the tool is marked as being a one step solution. Of course a lot are, there is no shortage of snake oil salesmen, and in that case they take 100% of the blame. However most rapid deployment tools contain a clear disclaimer of what it does or doesnt do and a guide to helping you with the rest of the steps.

When those things exist the fault is with the user and not with the tool - elitism aside. It's like using a hammer to kill a man (despite the do not kill people with this hammer label and 100 page guide to not killing people with hammers - now with pictures!) and blaming people killed with hammers on the hammer designer.

Some kind of software checklist

There should be some kind of government run website somewhere.

You would answer questions and it would give you license keys to software that you were qualified to use. For example, I might tick:

Engineer (check)
Artist ( )
Manager (check)
Linux (Check)
Mac ( )
Windows ( )

And it would issue keys for website point and click installation software, Vi, apache and Latex - but deny me keys to powerpoint thereby saving the lives of people who might otherwise have to gnaw off their own leg to survive my 8 hour presentation on optimising synergisyms in a web 3.0 environment by sub molecular interactions.

Criminalize?

Hello? Has the Net gotten so crime-ridden that instead of blaming criminals for doing illegal things, that we're now blaming developers? I mean, sure developers need to do all they can, but at what point do we, as a society say, "Hey, let's prosecute some of these assholes that are making life online a royal pain in the ass?" It's pretty absurd that today, you can still do whatever you'd like online (hack sites, networks, steal information), and there are really no repurcussions. We see what, one person getting jail time every 6 months? That's fucking insane. It's time for our government's law enforcement to step up to the plate, learn what the fuck a mouse is, and start prosecuting some of these people. Email is already useless. The web is getting there pretty quickly. SQL injection, firewalls, blah, blah blah.

If I have somebody who is constantly trying to break into my house, to the extent that I have to pay a security professional just to keep them bastards out, I'd be down at the police station talking to the police chief, angry as hell. As is, people have come to accept that there's almost more crime than legitimate traffic on the Net these days. That's insane. Something has to be done about this mess before the entire Net is just useless.

testing methods

This 11% was determine by a weak testing mechanism. For every site that baltently spews sql errors to the user there are two that silently return a generic sanitized error, and another two that return no error at all. It would produce more results if you take it a step further and ask yes no questions, such as:

?id=99999' OR '10

and see if the page returns the results of id=10 as expected. It's also common for people to use weak regexp (regexp should NEVER be used to protect against sql injection, see mysql_real_escape_string) and miss some characters:

?id=99999)

or fail to sanitize non us language encoding. Also, get variables are often the most protected. It is much more common to find sql injection in <input type=hidden variables, or in cookie data. The number 11% is extremely low. I'd guess more like 80%.

The "Oh-Sh*t" face...

From the article:

* Many development texts actually teach programmers insecure SQL syntax. ...
* Many sites are exposed to SQL injection attacks but don't know it.

I agree completely! I've seen the texts, I've seen the hordes of VB+SQL programmers that learned from said texts moving to the web porting the same "vices" to the new platform.
And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities exposed the customers to the posibility of serious financial damage.

So far, the stupidest work arounds i've seen have been:
Developer: It's ok, I'll switch to post instead of get so the user can't forge the request.
Developer: It's ok, I'll write a method that removes sinlge quotes for every string i get from the user.
Developer: It's ok, I'll write some java script that will validate user input.

Writing secure software is never easy.

Re:The "Oh-Sh*t" face...

Microsoft VB.NET and ASP.NET texts are AWFUL in this regard. Nearly all examples use in-line SQL queries rather than paramaterized stored procedures. Why? Probably because they are trying to fit in with Microsoft's strategy that devoping applications should require absolutely no knowledge of code (or anything else for that matter). The big selling point for their VS 2005 suite is "no code required". That speaks volume.

Re:The "Oh-Sh*t" face...

> Writing secure software is never easy.

It's easy if you use good tools. PHP is not a good tool. Rather than hacks like mysql_replace_the_string_with_things_that_wont_com primise_my_database(), you should be using tools that make it impossible to inject SQL.

Some ideas:

Perl's DBI, whose docs tell you to ALWAYS write SQL like:

$sth = $dbh->preprare('SELECT foo,bar FROM baz WHERE something=? AND another = ?')
$sth->execute(q{''Some\ things"'}, 10);

Notice that the programmer can't forget to escape the SQL -- because there's no escaping.

Even better is something like DBIx::Class, which lets you write

$resultset = $table->search({something => q{''Some\ things"'}, another => 10});

Again, no opportunity for the programmer to fuck up the SQL in any way. It's just like getting data out of the hash... DBIx::Class will generate the SQL (for any backend), run the query, stream in the results as needed, etc. It's easier and it's better!

Ruby on Rail's ActiveRecord is similar, but it's impossible to do certain types of joins. DBIx::Class is better in this regard. (And Perl is faster than Rails, and Catalyst is more complete rhan Rails :)... but both Ruby and Perl are MUCH better choices than PHP.

PHP makes it easy to write insecure code. Perl makes it hard! (With taint mode, a selection of ORMs, 10000+ well-tested modules, and nicities like Moose, Moose::Autobox, etc.)

Massively widespread problem

If anything, I'd question how FEW sites they claim are vulnerable to SQL injection. It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently (as when writing something quickly, on a deadline... not that this ever happens!). I know that at my workplace we fell victim at one point to a SQL injection attack on one of our (many) custom PHP scripts. We eventually found out how it worked through the web logs and were able to fix it, but honestly even after we did our best to clean things up... I'm dead certain that there are still probably hundreds of places that we're still vulnerable. This is due to a number things including the sheer volume of PHP code in use, the fact that the code has been written at various points in time over a period of six years or so, and the fact that this code has been written by at least twenty different people. It's like trying to plug holes in a dam.

Turn Key solutions broken?

Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box? Second of all, why is it that every website has to worry so much about security. I know about databases but I don't know the first thing about preventing an SQL injection attack and why should I have to. There is nothing sensative on my sites. Let me throw out this analogy.

Let's say I own a house and around Christmas time I put out an inflatable snow man. Then some vandals come along and pop it. Are you going to walk up to me while I'm sulking over my snow man and say "Don't you know you have to wrap your snow man in kevlar to prevent vandalism and then put up an electified fence with constantine wire on it."? I would give you the strangest look if you did. Then I'd probably say something pertaining to the fact that the police should catch these bastards and presecute them.

So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?

This is way low

I've worked in web development a while and I find a SQL injection vulnerability in about 90% of the sites I've seen.

It is extremely common to have people just cut and paste the bare-bones tutorial code they find on the web and reuse that same pattern on every page in the site rather than centralizing it in a wrapper. So not only is the string not being cleaned, but it's also a huge pain to fix.

some are untrained, but ...

Going through my bachelors, we all learned the same things, yet there were always people that had unsecure code. It didn't come down to "just not getting it". It was more that they were lazy and wanted it done fast.

Persistence Framework

Sounds like even more incentive to use a good object to relational framework like Toplink or Hibernate.

DB Wrapper?

I am pretty new at PHP development, but not new to programming. I had to learn SQL on-the-spot and I'm aware of SQL injection attacks and the risks they pose.

We have implemented a DB wrapper that "escapes" user input for things like ' that would break out of the input. I also check the length on the server side to make sure they're not trying to overflow any of the variables. By the looks of many of these posts, that isn't an "acceptable" amount of protection and that procedures are a must.. Anyone care to inform me or refer me to a good website?

You don't need to bother with SQL Injection

If you search for "mdb" you can download the entire database without too much trouble.

I recently came across a commercial site where you could substitute, for instance, "(select first_name from users where id=1)" into the page url and a nice error screen came up telling you that it couldn't convert "George" into an Integer.

It's not the SQL Injection per se that is the biggest problem, but the nice error messages you get back giving you, more or less, a SQL command line interface. Errors should be detected and redirected to a sanitized page, or if you can't be bothered, an unceremonious crash.

I notified the owners of that site by the way.

Better APIs

I've written about this before. Basically, SQL injection vulnerabilities would completely disappear if better APIs were used [inglorion.net]. The problem is that queries are composed as strings, which have no intrinsic structure. The programmer creates structure by inserting certain characters (in particular, single quotes) in the string. However, the exact same mechanism is used to add user-supplied data to the queries. Unless the programmer is very careful, this allows the user to affect the structure (and thus the effect) of the query. An example is in the essay I linked to.

Moo

Blame MySQL.

MySQL is not a database. It does not support Transactions. It doesn't use normal syntax. Why? Because it's easy. So a programmer who has better things to do than learning some arcane syntax can just get the job done and move on. Good? No!

It is because of this laziness that such attacks occur by someone who did take the time to learn the syntax. If people want a quick and dirty solution, so be it, but don't pretend to be a database and open all sorts or vulnerabilities.

The ease of SQL is made for people who take the time to learn it, and naturally code it securely. For those who don't have the time, just don't use SQL.

So, i blame MySQL (and its ilk) for this. And i can only laugh at the sites that get attacked because of their own laziness.

Only 11%?

I just can't imagine that many web developers being so careful. Popular sites are relatively secure, and open source code like online forums are secure, but the security of write-once code you find on average sites is just horrible. Just search google for things like 'allinurl: "isadmin=0"' and you'll know what I mean.

it\'s easy to fix SQL injections

Magic quotes! No more problem\'s!

Re:Return of the Flat File

This is exactly why I use NanoBlogger.

Re:Sure, blame the "untrained" developers....

If the obvious fix is to exclude special characters from password fields, then why allow them by default to begin with?

Because that won't stop a wily hacker from using a tool such as curl to use those special characters as if they'd entered them in the password field. This has to be fixed at the server end, not the client end.

Re:Sure, blame the "untrained" developers....

In short, trusting the client (i.e. the web browser) to not send bad values - either through the INPUT tag's maxlength attribute, JavaScript scrubbing or whatever - is entirely the wrong way to go. The web script must check all user input for validity along with properly escaping everything from the database that's getting sent back via HTML.

Re:Return of the Flat File

Won't work. The same 'novices' who leave gaping SQL injection holes will now be writing pages that need to access the file system. Now instead of accessing the DB, script kiddies will be traversing the filesystem. Yes, this can be mitigated through file permissions, but there are a lot of servers out there (set up by these same novices) where processes run as root and would have full access to read and write files. So, a bad script could allow them to write to /etc/passwd and have all sorts of fun.

No. That's a stupid idea.

Stupid application construction isn't a good reason to make the design of the app even stupider.

In the old days, everyone used flat files, because that's what there was. Then someone (several someones, most notably Codd and some others at IBM) realized that breaking the flat data into sets of discrete data that related to each other reduced redundancy and allowed for an overall better quality of data. And it wasn't app specific.

The answer to SQL injection is to test apps more completely (including tests for this kind of attack), to provide extra checks at the database level (for integrity issues, perhaps in the form of constraints, etc. dependent on the scale and structure of the database), and to develop tools/libraries for data access that make this kind of thing hard to do accidently.

Re:Return of the Flat File

Why convert to an entirely different structure when just implimenting proper code standards will suffice? Using parameterized stored procedure calls instead of dynamic SQL will not only protect you from the vast majority of SQL Injection attacks, but will also improve the performance of your web page.

-Rick

Re:Return of the Flat File

That, and/or bind [cpan.org], bind [sun.com], bind [php.net]. Concatenating user input into your SQL statements is bad on both security and performance.

Re:Sure, blame the "untrained" developers....

I don't think you understand SQL Injection at all.

If the obvious fix is to exclude special characters from password fields

It's not. First of all, it wouldn't work. Second, it makes to sense at all at any level. Sorry if I seem rude. :)

There are a lot of new programmers (or whatever we're calling people who make websites these days), who are not naturally paranoid and sensitive to the exploitation of their code. They shouldn't need to be.

I agree, but it's a dreamworld. I shouldn't need to fiddle with keys or whatever every time I use my car or get home. But I do.

Luckily you can create pretty safe code by making nice code. It's amazing how many side-effects "nice" has. But yes, you'd need to be a good programmer to make nice code.

Re:Return of the Flat File

There's no need to go back to the stone age. Just use prepared statements/parameterized queries (along with the usual stuff like validating user input client *AND* server-side)

Re:Return of the Flat File

Isn't that called XML?

Re:Sure, blame the "untrained" developers....

As some others have pointed out, there are tools to get around that sort of thing. You should be validating all input server side, whether it's validated client side or not. There are also very easy ways to deal with SQL injection already, such as parameterized stored procedures. Doing what you suggest would involve a hell of a lot of recoding, break a hell of a lot of existing sites, and proceed to punish good coders by making them work around the "safe-guard". Programmers who don't take steps to protect against SQL injection deserve to have their sites hacked. And guess what? If they have their sites hacked, they'll probably remember to do something about it in the future, or hire someone who already knows how.

I may sound like an elitist by saying the following, but secure programming should not be "easy" for the "masses". It should be reserved for those who know what the hell they're doing, and they should be paid accordingly. I do not believe it's wise to give the average Joe the tools to make even slightly sophisticated programs. We've seen what happens when we do this; take a look at most websites from the dotcom bubble.

Re:Sure, blame the "untrained" developers....

This is kind of like saying we can prevent buffer overflow exploits by having the windows API not allow people to enter shellcode into textboxes.

Re:Sure, blame the "untrained" developers....

All the HTML standards in the world won't stop an attacker with a copy of telnet.

bash$ telnet example.com 80
GET /vulnerable_app.php?id=%2310 HTTP/1.1

Two things to note here: (1) there's no HTML involved in the actual transaction at all (2) like another poster said: you can't trust the client to send valid data.

Stick to purposing solutions for things you know about.

Re:Sure, blame the "untrained" developers....

This certainly seems to me to be a problem that needs to be addressed at the html standard level

I'm not following you. Why would a server-side exploit like SQL injection be addressed in a client-side display standard like HTML?

The 'type="password"' attribute of the HTML input element is nothing more than a style hint for the renderer -- characters typed into such an input should not echo back to the screen. Otherwise there's nothing that distinguishes it from a value taken from a text input, a checkbox, a file upload dialog or any other HTML form control. An HTTP POST is an HTTP POST.

And without knowing what the server plans to do with the data once submitted (which the client has no reason to need to know), how does one define what "special characters" are? A string that's going to be inserted into an SQL database has a different set of special chars than one that's going to be used as part of a filepath on a Windows box, which is different than one that's going to be used as part of a filepath on a Unix box, which is different than one that's just going to be compared against another string.

Business logic does not belong on the client side.

There are a lot of new programmers [...] who are not naturally paranoid and sensitive to the exploitation of their code. They shouldn't need to be.

The hell they shouldn't.

Programming consists of more than just typing out some PHP code that doesn't cause a fatal error when executed. A programmer needs to be conscious of the expected inputs and outputs of any piece of code he/she is responsible for, and aware of the ramifications when those expectations are not met.

A person who does not exercise such diligence should not be considered a programmer.

Re:Let's find out...

What I don't understand is how an SQL error message makes you vulnerable to a SQL Injection attack? Even if you are able to find out some of the tables and fields in a web-accessible database, you don't have the password to be able to execute your own queries...Is there just an assumption here that anybody stupid enough to allow a verbose error message like that would also have a database password like '12345'?

Re:Are these still around?

For login scripts:
"' or 1=1 --" -- can force a login to succeed in some systems
"' or password='' --" -- works on some systems where that doesn't

For data retrieval scripts:
"' or 1=1 --" -- retrieve all data from the database, not just the record asked for; this is a useful one.

For data update scripts:
"' or 1=1 --" -- update all records in the database, not just the one that's identified

There are many other variations on these.

Yes, not allowing multiple statements mitigates the vulnerability a little. But not a lot.

Re:It's more than 11%, I'm sure of that!

But anyway, web developers should not be using SQL any more, except in some very rare cases. We now have things like EJB3 and Hibernate which remove 99% of the need for any SQL within an application.

There are good reasons for using SQL over and above an object persistence layer (which is what I believe both of these are, although I've never used either). SQL is easier to search, easier to analyze in arbitrary ways, and easier to edit by hand if something goes wrong and the system needs fixing.