Here’s how Bell was Hacked: SQL Injection Blow-by-Blow

OWASP’s number one risk in the Top 10 has featured prominently in a high-profile attack this time resulting in the leak of over 40,000 records from Bell in Canada. It was pretty self-evident from the original info leaked by the attackers that SQL injection had played a prominent role in the breach, but now we have some pretty conclusive evidence of it as well:

The usual fanfare quickly followed – announcements by the attackers, silence by the impacted company (at least for the first day), outrage by affected customers and the new normal for public breaches: I got the data loaded into Have I been pwned? and searchable as soon as I’d verified it.

Now you would think – quite reasonably – that SQLi would be becoming a thing of the past what with all the awareness and Top 10 stuff let alone the emergence of tools like object relational mappers that make it almost impossible to screw this up, but here we are. Clearly we need a bit of a refresher on the risk and what better way to do it than to reconstruct the Bell system that was breached then, well, breach it again. Let’s get to it.

A long time ago in a language far, far away

The first thing that should hit savvy readers in the image above is that this is an ASP web site. No, not ASP.NET, go back further – classic ASP. This is not classic like, say, a Ferrari 250 GTO which has grown increasingly desirable with age, rather it’s “classic” like Citroën 2CV; it was kinda cool at the time but you’d be damned if you want a mate seeing you in one today. But I digress.

Classic ASP was replaced almost 12 years ago to the day with the platform that remains Microsoft’s framework of choice for building web sites today – ASP.NET. You could forgive someone for persevering with classic ASP a decade ago, perhaps even 5 years ago, but today? I don’t think so. If you’re running this platform today to host anything of any value whatsoever on the web, you’ve got rocks in your head. (Yes, I know it’s still supported but seriously folks, it was built for another era and just isn’t resilient to today’s web risks)

Anyway, to reproduce this risk I’m going to create a very simple classic ASP site that looks just like the one above. That’s one thing that was great about classic ASP – it was dead easy to create a simple site! For added realism I’ll create a local host entry for protectionmanagement.bell.ca and even add a self-signed cert so we can hit it via HTTPS.

The affected site has been well and truly pulled by now, but of course nothing is ever really gone on the internet, it just goes to Google cache heaven:

That shouldn’t be too hard to reproduce, how’s this look?

Forgive me if I don’t go so far as to recreate the broken images! Let’s move on to the other thing we know about the attack, and that’s what the back end database looks like.

Implementing the back end

What we need to make this whole thing resemble a real attack is a little bit of classic ASP wiring and a database. The latter is quite easy to reconstruct because the entire schema was dumped along with the breach. Yes, yes, the breach got pulled very early by the powers that be, but per the earlier point, cache is your friend. Here’s what we’ve been told about the credentials table:

Columns:

tblCredentials

tblCredentials.CredentialID,
tblCredentials.OrderID,
tblCredentials.CustomerID,
tblCredentials.ServiceType,
tblCredentials.UserName,
tblCredentials.Password,
tblCredentials.Level,
tblCredentials.CustomerName,
tblCredentials.PersonName,
tblCredentials.GroupID,
tblCredentials.SecretQuestion,
tblCredentials.SecretAnswer,
tblCredentials.UserEmail
tblCredentials.UserLanguage

By prefixing the table with the letters “tbl” you know that it’s a table and not a magic unicorn or a Chinese dissident (let us not digress into the insanity that is the tbl prefix). Anyway, I’ve recreated that table and another one called tblTransaction2010 in my local SQL instance that looks just like this. I’ve then whipped up a little VB Script in the .asp file (“Tonight we’re gonna code like it’s 1999”) which connects to the database and runs a SQL statement constructed like this:

SQL = "SELECT * FROM tblCredentials WHERE UserName='" + Request.Form("UserName") + "'"

Yeah, that looks about right! Let’s see what happens now…

Employing HackBar

The extension you see in the first image of this post is HackBar, a simple little add-on for testing things like SQLi and XSS. The premise is that it can monitor requests the browser makes and then make it dead easy to reconstruct them with manipulated parameters, you know, the kind of stuff that can exploit SQLi risks. It looks like this:

What I’ve done is tried to perform a reset for the username “troy” (which performs a post request to the server) then I’ve just hit the “Load URL” button and checked “Enable Post data”. That then gives us the resource that was hit in the top text box and the form data with name value pairs in the bottom. Dead simple, now let’s break some stuff.

Mounting the attack

What we see in the first image above is what’s known as an error-based SQLi attack or in other words, the attacks are using exceptions thrown by the server and sent back in the response to discover the internal implementation of the system. I talk about this and other SQLi attacks patterns in my post on Everything you wanted to know about SQL injection (but were afraid to ask).

Let’s reproduce what the attackers have in that first image – disclosure of the internal database version. This is a useful first step as it helps attackers understand what they’re playing with. Different database environments and even versions are exploited in different ways so discovering this early is important, question is, how do you get the database to cough this information up?

In the post I mention above, I show how attempting to cast non-integer values to an integer will throw an internal exception which discloses the data. The first thing we need to establish is how to generate the data which in this case is the DB version. That’s dead simple, we just ask for @@VERSION then if we try to convert that to an int and the exception bubbles up to the browser, we’ve got ourselves some useful info.

Does this look about right?

And there we have it – the DB version data. I’ve all done with the post data is sent it over like this:

UserName=' or 1=convert(int, @@version)--

Clearly the version won’t convert to an int so we get the error above. The %2x values you’re seeing in the HackBar window are simply URL encoded characters which can be achieved by selecting the string and then then choosing the correct encoding context from the menu (I’ll leave the unencoded values there in future grabs for the sake of legibility):

So this is a start, but where’s the good stuff? How about we move onto discovering the schema because until we know what tables and columns are in there, it’s going to be a tough job pulling the data. Let’s start with table names:

tblTransaction2010 is it? We know it’s a table because of the prefix… ok, I’ll let it go, point is we now know a table name and all it took was to select out of sysobjects. I go into detail about how this works in the aforementioned everything you want to know post so I won’t dwell on it here, let’s get another table name:

Ah, so there’s our tblCredentials table and all it took was to adjust one number in the query so that the inner select statement took the top 2 records instead of the top 1 thus allowing the outer select to grab the next table in sysobjects.

Let’s get some columns and there’s no one “right way” of doing this as there are multiple ways of pulling columns names from SQL Server (and for pulling table names too, for that matter). Let’s try this one:

The exception discloses the presence of a column called UserName on the table tblCredentials. That’s handy, let’s move on and I’ll just keep incrementing the integer in the inner select statement:

Ah, so there’s a password column as well, that’s handy, let’s see about pulling some data out of there:

I’ve deliberately simplified this statement so it just pulls the first record in the default order but by now these nested, sorted selects should give you an idea of how easy it is to enumerate through the data. So there’s the username – “troy” – let’s grab the password too:

This is unfortunate because clearly I’ve taken my personal security seriously and substituted not only the “a” for an “@”, but also the “o” for a “0”. But when you don’t have any cryptographic storage on the credentials which was the case with Bell, even my real passwords that are all randomly generated by 1Password have nowhere to hide when an SQLi attack hits pay dirt.

In practice, you’re not going to go through and manually enumerate every single table, column and then row (column by column, I might add), instead you’re going to automate the process using a tool like Havij once you’ve discovered an at-risk target. If Havij is new to you, it’s child’s play – here’s my 3 year old learning how to use it, it really is that simple.

There will be nuances between how I’ve replicated the attack here and how the guys behind it actually went about it. There might be other vectors through other pages or depending on how the original password recovery page responded, more streamlined ways of pulling the data. There may have even been SQL credential exposure at some point which would make the whole thing dead easy. Either way Bell (or whoever is copping the blame) will have more than enough data in their logs to reconstruct the attack and know exactly where it all went wrong.

Hardening Bell’s environment

Firstly, yes, I know that Bell has laid the blame on a partner providing services to some of their customers but it’s Bell in the headlines, it’s Bell sending out the apology emails and it’s Bell who now has to clean up this mess. I say this not to berate Bell but to draw attention to the responsibility that organisations have to ensure that their partners are employing appropriate security measures. The risk above could have been discovered in minutes by and automated tool and almost as quickly by even the most junior penetration tester. Nobody tested this system for security vulnerabilities – including Bell – and now they have a very unfortunate blight on their record that will be referenced for years to come.

Anyway, let’s focus on the mitigations of this risk because as I said from the outset, this needs to be taken as an opportunity for others to learn some fundamentals that could save them from a similar fate. Let me summarise in point form:

1. Using out-dated frameworks: Classic ASP guys – get rid of it. It has nowhere near the defences that modern web platforms have in place not just for SQLi, but for a whole range of attacks. You cannot afford to keep running VB script on the server.
2. No white-listing of untrusted data: In the example above (and inevitably in the real system), SQLi attacks were thrown at the website and it… welcomed them with open arms. “Validate all untrusted data against a whitelist of allowable values” is the mantra I’ve repeated so many times and the username should only be allowing characters that it actually accepted when people signed up so that means no brackets, quotes, spaces, etc (none of these are in the breached data).
3. Non-parameterised SQL: My example earlier on about how the SQL statement was likely constructed shows just a concatenated string with the potential to mix the query with untrusted data. This is what got them and I talk extensively about the right way to do this in part one of my series on the Top 10.
4. Internal implementation leakage: This attack was made dead easy by the fact that internal exceptions bubbled up to the UI. Someone had to actually enable this – newer versions of IIS won’t allow to happen by default. The extent of this risk goes well beyond SQLi as well as there are some very, very juicy things that web sites sharing their internals can disclose.
5. Plain text password storage: Shit happens. Sites get breached. We now all understand that, but what makes it a whole lot worse is when the data is usable by attackers, not just the ones who pulled it, but anyone in the general public who now has access to it. Passwords should always be stored with a strong cryptographic hashing algorithm designed for protecting credentials. Anything short of this leaves you naked in an attack.

They’re just a few easy ones – SQLi 101 – and they should be painfully obvious.

In conclusion…

SQLi attacks remain rampant. They’re still in the number one spot in OWASP’s Top 10 (even the latest 2013 version) and it’s still rated as easy to exploit and as having a severe impact. They’re favoured by attackers because they’re just so easy to crack open which was the point of showing my 3 year old doing it earlier on. In this case the attackers actually showed a decent understanding of the mechanics behind SQLi, but the point is that the barrier to entry for this attack can be very, very low.

Lastly, if you’re a dev or managing devs then get them into some in-depth security training whether that be via my Pluralsight courses or though any of the other excellent resources out there. You can’t wait until after things go wrong to do this.