SQL Injection Vulnerabilities
This is a really interesting post about someone finding SQL injection vulnerabilities with Google. His result is that 11.3% of websites are vulnerable to this attack.
Comments
Bryan • October 4, 2006 2:55 PM
A minor correction – he actually said 11.3% of sites from the initial population of URL’s are vulnerable, not 11.3% of all sites at large. The initial population of URL’s were those which came out of a Google query for “id=10”.
It is still a horrific number.
Quack Lawyer • October 4, 2006 3:20 PM
This is interesting and hopefully will push some web site designers to have another look at their site security but here is a cautionary tale about unauthorised penetration testing:
http://www.theregister.co.uk/2005/10/11/tsunami_hacker_followup/
Not sure what the US position is but basically, the police in the UK seem to prosecute if they can regardless of motives.
Also, the terms and conditions for using Google API could be construed to bar unauthorised penetration testing:
Section 2: APPROPRIATE CONDUCT
“Users outside of the United States agree to comply with their own local rules regarding online conduct …”
Full terms and conditions are at http://code.google.com/tos.html.
I daresay the real risk of getting into trouble doing this is negligble but it’s best to think about the possible implications before injecting SQL code into somebody’s server.
Jeremy H • October 4, 2006 5:23 PM
When I was an undergrad (in ’03 or ’04), I took a course in applied cryptography. It focussed more on the application side and the high level, rather than the mathematics and the low level.
One of our assignments was to recover a key against some public algorithm with a chosen-ciphertext attack. Our interface to the implementation containing the key was a CGI web script.
At the outset, our prof said “this is the first time I’ve coded a CGI interface, it should be secure, but if anyone finds a vulnerability, please let me know.” The first thing that I did was pass “; ls” as my ciphertext. I was rewarded with a directory listing of where the app was running from. The ciphertext from the client was being dumped to a command line shell script.
This vulnerability is no different, and will continue for as long as data from the client is not properly escaped or encapsulated. These errors would be much more rare if developers followed the MVC design pattern and never trusted data from the client side. Skipping either of those steps may be expected from amateurs, but anyone claiming to do professional work should be doing both. Unfortunately, quick hacks are more prevalent than we’d hope.
Davi Ottenheimer • October 4, 2006 5:47 PM
Input validation is a great concept but with human behavior so prone to mistakes is 11% a large or small number? And what will make it smaller? Incentives? Penalties?
For example, people are known to put food in their mouth without input validation every day. Even when faced with overwhelming evidence of harm from certain things (e.g. trans fats) they are not easily convinced to start “whitelisting”. Perhaps the rule should be food must be properly escaped or encapsulated…
Nick Andrew • October 4, 2006 6:28 PM
The PHP language is complicit in this because its SQL functions do not support placeholders. PHP applications must embed variables as literals in their SQL statements, and so there are functions to quote strings which must be used on all the variables which will go into the statement.
Forget to call the quoting function, and if your variable isn’t sufficiently well validated, your statement may be vulnerable to an sql injection attack.
Roger • October 4, 2006 7:16 PM
The problem, of course, is that world facing web applications have very high vulnerability, yet many web application development systems are sold on the ease of use by newbies, who don’t understand the security risks.
It might be possible in principle to create a web app language that prevents newbies from shooting themselves in the feet, but it’s been tried in the past and doesn’t work (usually, the language is too restrictive, AND the newbie programmer finds a way to suicide anyway!)
So it is yet another problem of user (well, newbie programmer) education — a perennial thorn in the side for security. Incidentally a couple of months ago I read Sverre H. Huseby’s “Innocent Code: a security wake-up call for web programmers”. I highly recommend it. It is so well written that I think any new programmer who reads it will not only develop a feel for the general principles involved in securing apps (as well as many specific techniques), but is also likely to develop an interest in the subject and start consciously securing everything in sight. I especially like to quote Huseby’s Rule 1 (of 27):
“Do not underestimate the power of the dark side”.
@Jeremy H:
… the MVC design pattern and never trusted data from the client side. Skipping either of those steps may be expected from amateurs, but anyone claiming to do professional work should be doing both.
I agree, never, ever ever trust data from the client side. But some projects are so simple that an MVC design is needless complexity, which itself can be a source of problems. Anything with only 1 or 2 screens and only one (or 0) types of user input, may not need it. A lot of quite useful web apps have only one or two screens.
For example, when I found a lot of my Q&D web hacks were very similar, I made a little library that made one liners of common tasks like loading a standardised config file, connecting to a (read only) db view, doing a SQL query (a fixed string taken from the config file), displaying a 2D array as an HTML table of (filtered) data, deciding what logger is in use, and other little pieces like that. The whole library is about 40 lines, is written with Extreme Security in mind, and has been audited numerous times. With it, I create Q&D web apps that are about 6 – 12 lines long (mainly light arithmetical processing), plus 6 lines of config file, and take about half an hour to build (including coding of tests, 2nd party code audit, documentation, and deployment).
Building these as MVC — even desiging the model — would be a severe waste of effort. Yet of all my web apps these are often the ones that are most popular with and useful to users; I know of one example which saved certain program managers about 2 man hours of work every week, and helped in halving the remaining failure rate in the systems it reported on.
Schmecurity • October 5, 2006 3:29 AM
Regarding SQL injection; it is a common misconception that placeholders is a silver bullet.
Don’t get me wrong; you should definetly use placeholders. However, how secure this is will always depend on the implementation in the driver. A few years ago, I investigated the application we were developing, to see if we were safe from SQL injection.
We used several databases, and when I checked the source code for the PostgreSQL JDBC driver, one of the methods used was actually vulnerable to SQL injection (if my memory serves me right, it was the PreparedStatement.setObject method… but then again, I might be wrong).
This bug was pretty obvious, and it was fixed (I think), but everyone should remember that the drivers is software just like everything else, and don’t do anything magical.
Obviously, we can’t go around checking the source code for every driver out there (and some are closed source, of course), so in the end we probably have to trust the vendors.
Schmecurity • October 5, 2006 3:30 AM
Sverre H. Huseby’s “Innocent Code: a security wake-up call for web programmers” is well written, but it is very basic and definetly for newbies. Just something to keep in mind if you buy it, and don’t expect anything more.
Ciaran McNulty • October 5, 2006 5:34 AM
@Nick Andrew
Actually PHP’s PDO interface for DB transactions does support placeholders in SQL, although it is fairly new.
The PEAR::DB libraries have long supported this sort of functionality too, but as with most things in PHP, the security vulnerability comes from the fact that anyone can get stuff up and running without having to code ‘the right way’.
@Nick Andrew,
you can easily and securely validate your inputs in PHP, it’s just that most people don’t bother to, out of a lack of knowledge. When I started doing web design, years back, I didn’t even know what could be done, so how would I protect against it? The second I heard about it, I added a handwritten (and rather aggressive) line of scrubbing code to my scripts. However, use http://uk.php.net/manual/en/function.mysql-real-escape-string.php and you will be safe from anything clever, as it simply stops things like ‘ by adding a second ‘ so while it might still sometimes cause an error due to not being a string, it won’t actually do anything bad. And your type checking should catch that.
Further, use “@… or die;” to prevent anyone causing an SQL error from seeing what they caused. Without feedback, hacking anything gets a lot harder.
For a simple web dialogue that’s enough.
The biggest issue is “Nephew Art”. Because the barriers to entry on the web are so low, and there are no enforced standards at all, people do things like passing secure variables in URL strings in good faith, in the £50 website built by their nephew.
Toby • October 5, 2006 8:18 AM
My surname is O’Neil, and I have lost count of the number of poorly implemented websites that won’t let me accept my surname.
A personal favourite is the online insurance broker where I gain an extra ‘ after each page — by the time the quotes are gathered, my surname is O””Neil. Still, at least it retains the `Neil’, frequently I become simply O, and my purchase is declined 8-(
Usually the problems seem to be occuring because the site designers are trying to attacks, and have failed to realise that an apostrophe can be part of a legitimate response…
Corey Mutter • October 5, 2006 1:58 PM
I have one word for all of you that want to avoid SQL injection: Stored Procedures.
Have the ID the Web app uses to connect to the database have no permissions to select or change any tables, only to execute some SPs you’ve written. Those SPs run as another DB user that does have the needed access to tables.
Presto! No chance of SQL injection (without guessing the password needed to connect as the user the SPs run as, if it’s allowed to connect at all, or compromising the DB server). As a plus, you can enforce arbitrary security restrictions.
For example, in a system I did like this, when the web app wants to get a list of the user’s trouble tickets, it executes a SP with one parameter: the user’s session ID. The SP looks up the session ID in a table to get the user’s login name, looks up the login name in a profile table to get the user’s company, then returns the rows from the trouble-ticket table for that company. Therefore, you just can’t get at a company’s trouble tickets without having a valid session ID for a user from that company (or compromising the DB server). This is true even if you can throw arbitrary SQL at the database from the web app (since that user can’t do anything but execute the SPs).
Schmecurity • October 5, 2006 3:47 PM
Stored procedures? Well… Putting application logic in the database? I guess it might be secure, but it’s not really great application design.
Repton • October 5, 2006 4:57 PM
Google has just released a code search facility. Some guy on Slashdot pointed out that you can use it to search for SQL injection attacks: http://www.google.com/codesearch?hl=en&lr=&q=Where+%5C%24_POST+-addslashes+lang%3Aphp
As an example, the second result on the page has this code:
$sql = "SELECT * FROM users WHERE uname='".$_POST['username']."' AND upass='".$md5pw."'";
(obviously, you’re not finding vulnerable websites, you’re finding vulnerable packages instead. But still…)
Paeniteo • October 6, 2006 4:06 AM
I always wonder what is so difficult about putting mysql_real_escape_string around variables intended to go into the database…
Replace:
$id = $_POST[‘id’];
by:
$id = mysql_real_escape_string($_POST[‘id’]);
It does not really take much skill to just do this.
Yes, I’m aware that this very function had a security flaw not long ago, but apart from this you are about as secure as you can get when you have to transfer strings into the DB.
In the above case, you could also add something like:
if (!is_number($id)) die(“illegal ID”);
Even an activated magic_quotes_gpc setting in PHP will save you from most trouble and would work even with activated register_globals (where it is not necessary to manually move request-parameters into variables).
Maybe, register_globals is again the root of all evil, as it makes writing things like
$query = “select * from table where id=’$id'”;
without thinking about where $id comes from too easy.
Thomas Mueller • November 23, 2006 1:04 PM
I suggest to enforce that application developers use parameterized (also called prepared) statements. This is possible: depending on the rights, the database rejects embedded literals in SQL statements and only allows parameterized statements. For details of my proposal, see here: http://www.h2database.com/html/advanced.html#sql_injection
Unfortunately, this feature is only implemented in the H2 database yet. But I think it is a simple and sound solution.
Thomas Mueller • November 24, 2006 3:40 PM
I suggest to enforce using parameterized statements. This can be done in the database engine itself. Just disallow string (and number) literals in SQL statements. I have implemented this functionality in my database, H2 (www.h2database.com), see ‘Advanced Topics’ – ‘ Protection against SQL Injection’.
Subscribe to comments on this entry
Leave a comment
Sidebar photo of Bruce Schneier by Joe MacInnis.
Israel Torres • October 4, 2006 2:25 PM
I know that at least once a day while surfing I’ll find simple little glitches that could cause larger problems in the future if gone ignored. I’ll send off a friendly email from an “anonymous” account stating the problem and that it would be wise to fix it (and even how). I’ll check back once in a while surfing and sometimes it is fixed and sometimes it isn’t. Let’s say I am more surprised when it is fixed than when it isn’t.
You can thank me later. 😉
Israel Torres