The ongoing scourge that is SQL injection and Azure’s new SQL Database Threat Detection

Hey, did you hear about this new security risk? It’s called SQL injection and attackers can just suck all your datas out of your system if you screw it up badly enough. Allegedly there’s like, millions of websites at risk and even kids can easily break into them!

Wait – this isn’t a new risk?! Well how come it’s all over the news and these seriously large companies keep getting pwned by it?! How is that even possible?!

And here we are at that reality of today; SQL injection, whilst well understood for a good decade and a half, remains the number one risk on the web today. Certainly it’s there according to OWASP and their Top 10 Web Application Security Risks and there for several reasons:

  1. It’s easy to discover. Kids – yes kids – are running Googledorks, finding at-risk systems and then just grabbing all the data which brings me to the next point:
  2. It’s easy to exploit. Watch my Hacking is child’s play video where I taught my 3 year old how to undertake a SQL injection attack. It’s that easy.
  3. The prevalence is common. SQL injection is all over the place and not just in the old stuff either, there’s new risks being built every day and indeed even tutorials aimed at developers which already have SQL injection risks in them.
  4. The impact is severe. You get pwned by SQL injection and it could mean everything in your database gets extracted. Or modified. Or deleted. Or the attacker runs commands on the server itself. Or pivots to other machines in the network. Or… or… or… it goes on.

Some notable incidents in recent times include TalkTalk in the UK getting well and truly rolled by a 15 year old (a couple of 16 year olds and an elderly gent of 20 were later also arrested) and the really big one in the news this week, VTech. Thanks to good old SQL injection, they lost hold of not only 4.8 million customer details, but 6.3 million children’s details. That’s not just a massive hack, that’s simply unprecedented. SQL injection is the exploit that just keeps on giving.

Moving on, I’m often asked how this is still a thing; we know about this risk, it’s documented extensively, I’ve even written an entire Pluralsight course on it! But it remains an education problem and put bluntly, developers keep screwing it up. That’s the harsh reality of it – crap code – we can’t trust a lot of the guys and girls writing this code to do it properly and despite the emerging of things like ORMs which make it (almost) impossible to create SQL injection risks, here we are.

One approach to handling the risk is to address it out of band of the code itself, for example via a device in front of the web app that understands what a SQL injection attack looks like and blocks it there. The web application firewall (WAF) approach can be a valuable additional layer of defence but let me emphasise additional – this isn’t a license to then go and write crap code, it’s another layer of defence. This can be dead easy to implement too, particularly using a WAF-as-a-service such as CloudFlare offers (mandatory course plug for Getting Started with CloudFlare Security while I’m here).

Azure has just introduced another tool to help in the fight against SQL injection known as SQL Database Threat Detection. You can go and read all the Microsofty bits there or watch it work in a real live app here.

Firstly, this is threat detection, not prevention. In a nutshell, this feature will tell you when an attack is mounted against your database and in order to do that, the upstream app has to have a vulnerability in it that’s allowing the attack to get that far. Now before you give it a bit of “well that’s pretty useless then”, the main reason this makes sense is that you can go and enable it with a single checkbox tick and it won’t break your things. Plus, even if the code is solid and you have a device or a service like a WAF, this is just one more layer that’s good to have in place. Let’s just jump into it.

I have an Azure website at hackyourselffirst.troyhunt.com using their database as a service offering. I use it for a bunch of my courses and workshops and by design, it’s a security disaster. The good news is that makes it a perfect candidate for testing this feature!

To get started, jump over to your Azure SQL Database (remember, this is the PaaS offering, it’s not your traditional SQL Server running on a VM), and into the settings for the database. This is not the server it’s running on (PaaS databases still enable you to configure a few things at the server level), this is the individual database itself.

The settings for the database

Into Auditing and Threat Detection and turn “Threat detection” on. You’ll see I’ve given it my email address too – this is where I’m going to get sent alerts when someone is hammering away at my database:

Configuring threat detection

Now for all of this to function you’ll need auditing turned on. That’s been there in Azure for a while now and it means you get a heap of events dumped into Table Storage which you can then go back to and review or report on do whatever you’d like with. I already had that enabled so Threat Detection was just one more little step.

Now let’s break stuff! One of the SQL injection risks in the site allows you to do neat things (depending on your perspective!) like retrieving passwords from the database. Here’s the link to do that: http://hackyourselffirst.troyhunt.com/Make/1?orderby=(select%20top%201%20password*1%20from%20userprofile)

Got it? Get the first password, try and apply an arithmetic function to it, you get a type cast exception then it bubbles up to the user interface in a classic error-based SQL injection attack. Moments later, this email arrives:

An email alert about SQL injection

Now if we jump into Table Storage and have a look at the audit log, here’s what’s been recorded:

The SQL injection attack in the logs

The value in the statement column looks like this:

SELECT * FROM Supercar WHERE MakeID = 1 ORDER BY (select top 1 password*1 from userprofile)

And there you go! You’ve been notified of the attack, you can see exactly what statement was run and by looking at the web logs as well you can really start to piece together what’s going on. This is all built into Azure and all you’re doing is using the features that are freely available once you’re got a DB on there. Maximum ease factor.

One more point as I know some people will be wondering: I’m told by the guys on the team that built this that Threat Detection will identify not just error based SQL injection attacks, but union and blind ones as well. They won’t share exactly how it does this though as obviously they don’t want to disclose the signatures which could lead to an attacker attempting to circumvent the monitoring. I’m yet to play with it extensively, but it would be interesting to see where the ability to identify an actual threat begins and ends. Having said that, it may be difficult to test if there’s a dependency on establishing known good patterns based on usage because then you need a site that’s actually getting used and you’d have to add an injection risk to that and, well, things could get messy.

As I’ve already said, this won’t stop SQL injection but here’s what it should stop:

“On November 14 [Hong Kong Time] an unauthorized party accessed VTech customer data on our Learning Lodge app store customer database,” Grace Pang, a VTech spokesperson, told Motherboard in an email. “We were not aware of this unauthorized access until you alerted us.”

This was VTech’s response to a reporter after the incident was disclosed to them. They had no idea. Threat Detection wouldn’t have stopped this entirely, but they would have known about it very early and at worst been able to respond quickly after the event. At best, they may have been able to stop it as soon as the attack began and that could have made all the difference.

Azure SQL Injection
Tweet Post Share Update Email RSS

Hi, I'm Troy Hunt, I write this blog, create courses for Pluralsight and am a Microsoft Regional Director and MVP who travels the world speaking at events and training technology professionals