It’s time for your SQL Injection, now hold still you’re going to feel a sharp scratch…
If your business has a website, and I suspect it does, then the chances are that, behind-the-scenes, it uses a programming language called Structured Query Language (SQL) to manipulate and retrieve data stored in databases.
Now when I say “behind-the-scenes”, what I mean is that it operates entirely on the “server side” – though your site visitor (termed the client) may submit, for example, text via a web form that will then be used at the server to query the database, the client never interacts with the database directly.
Thus any code written in SQL is totally invisible to the client, and it won’t be found in any source that’s accessible from the “client side”. The data returned from the constructed SQL query is used on the server side to produce a pretty front end response, and only that is sent to the client.
So it’s secure, right?
Actually just because they can’t see it, doesn’t change the fact that an attacker knows it’s there. And without proper controls they could post anything they like in place of those web form inputs.
SQL injection (SQLi) is the simple process of submitting (or “injecting”) some SQL code in place of an expected input. The injected code is then read by the database as though it is part of the SQL construction, rather than input data, and alters the behaviour of your query.
At this point your hacker just has to decide what commands to send through to reap the greatest benefit.
For example…
Suppose you have a form on your website for submitting a username and password. On the server side an SQL query is constructed to search a table of your database for an entry with matching credentials. If an entry is found the user is authenticated and the details are used to construct the response page, otherwise the user receives a message that their credentials could not be authenticated.
You can imagine the SQL query is intended to be interpreted by our database something like this (note that we have chosen not to post actual SQL code here, rather I will describe the interpretation using natural language for a broader audience; but there’s plenty of real code SQLi examples out there if you care to look for them)
- Look in the table of users, find any entries that have a username that matches “<the contents of the submitted username field>” and also a password that matches “<the contents of the submitted password field>”
And, used correctly, replacing the two sections enclosed in <> would have precisely the desired effect. But what if instead of submitting my username, I chose to submit a closing speech mark and add some further instructions instead? For example, I could make the query read
- Look in the table of users, find any entries that have a username that matches “” or if the username doesn’t match then just return them all anyway. Now ignore anything else after this sentence.” and also a password that matches “”
The database has no reason not to follow these instructions absolutely, and hence return every single entry in your table. At the very least your authentication has been bypassed, and worst case it breaks the site and perhaps even the whole response, i.e. all of your user data, could simply be dumped onto the attackers screen.
Alternatively, as long as the original query remains valid, an attacker could tell the database to do anything else they like
- Look in the table of users, find any entries that have a username that matches “” and also a password that matches “”After that, show me the entire table of unfulfilled order data, then delete it from the database. Now ignore anything else after this sentence.”
A whole table of data has been stolen and deleted, and a ransom demand will likely arrive shortly.
There is very little that cannot be achieved by injecting commands in this manner into a vulnerable application, in some cases commands can even be issued to the OS.
New dogs, old tricks
It’s been referred to as the “oldest hackers’ trick in the book”, “the Hack That Will Never Go Away” and, “It’s the most easy way to hack”. SQLi is certainly no zero-day monster vulnerability – In fact it’s believed that the first time the dangers of SQL injection were published was way back in 1998 by a man called Jeff Forristal, or rain.forrest.puppy as he was known at the time. And the story goes that, trying to do the right thing, his fellow researcher approached Microsoft prior to publishing and they were simply told it was “not a problem”.
Yet here we are almost two decades later, and statistics suggest that 32% of all web applications remain unsecured against it, while 27% of all web attacks are SQL injections. These can often be very significant breaches too, with the average cost of a minor SQLi attack estimated at $196,000. [1]
Indeed, SQLi has been revealed to be the vulnerability behind a huge number of high profile attacks over the years, including TalkTalk, Sony, Infraguard, Worldview, Yahoo!, Bell Canada, Kaspersky Lab, The Pirate Bay, the MySQL website, Barracuda Networks and many smaller government services and universities.
You barely even need any knowledge to do it
It’s actually got quite a following, with several tools in existence to automate the whole process. Not only will these tools crawl through a website looking for forms and then trying various combinations of inputs to quickly identify any weakness, but they will even automate the task of finding sites to target too!
“They would use Google to search for URLs,” says Mustafa Al-Bassam, a security researcher and former LulzSec hacker, “They would typically have a script that goes through all the URLs and tests them automatically to see if they're vulnerable.” He adds, “You could teach a 4-year-old to do it.” [2]
Meanwhile Troy Hunt, founder of breach site haveibeenpwned.com, has proven that point by uploading a video of him teaching his 3-year-old son how to carry out an SQLi attack with one such tool. [3]
Yet it’s really not that hard to prevent
Often people think that the most obvious approach is to painstakingly filter the input, effectively blacklisting all sorts of potentially dangerous characters and strings. Well, actually, and this should really apply to all forms, not just those with inputs that will be used for SQL queries, all input received by the application should be filtered (but not for possible SQL commands), and all data that will be stored or presented back must be escaped (that’s when another character is added before a character with ambiguous meaning to clarify its purpose, e.g. in the examples above a speech mark “ could be interpreted as signifying the end of the input data, or it could just mean that the input data includes some speech marks, by escaping it, something like “, we make it clear that the system should continue to treat it, and the text that follows, as input data, not as code).
But in terms of protecting against SQLi? You’re wasting your time; this should definitely be somewhat secondary.
SQL already has this covered, all you’ve got to do is use it!
What if I wanted to set up a form to legitimately input SQL code to add to a list of SQL commands stored in an SQL database? Surely I should be able to set up my queries in such a way that I can safely do that?
Yes, I can. And it’s easy.
By using parameterized SQL statements all of my SQL code is defined first and my input is only added to the SQL query at execution time as the contents of a parameter, not parsed as part of the query code. This way it doesn’t matter what is entered as an input, since our database knows that anything stored inside parameters is just input, and simply cannot be tricked into reading it as code. Again, there are tons of resources out there describing how exactly to go about doing this, you just have to look for them.
Another simple safety measure that there’s no excuse not to take is applying the Principle of Least Privilege, that is ensuring that your web application only has the minimum permissions possible in order to still be able to perform the required tasks. Does your web app really need permission to create backups? Or to destroy the entire database? Well probably best if it doesn’t then…
Of course there’s more than one way to skin a cat (is that true?) and it’s not up to me to tell you which method(s) to employ. Apart from perhaps to say that when it comes to security, it should be as many as possible. So one more approach to consider would be to convert your input into another form, such as its hexadecimal representation, for use in the SQL query. Most scripting languages have a built-in function to do this, and used correctly it is simply another way of ensuring that your input is used as data and not interpreted as code.
So why are so many web applications still vulnerable to SQLi?
There is no excuse. Get it done.
[1] – “Infographic: How SQL Injection Attacks Work”, http://news.softpedia.com/news/infographic-how-sql-injection-attacks-work-489340.shtml, softpedia.com, 15 August 2015
[2] – “The History of SQL Injection, the Hack That Will Never Go Away”, http://motherboard.vice.com/read/the-history-of-sql-injection-the-hack-that-will-never-go-away, MOTHERBOARD, 20 November 2015
[3] – “Hacking is child’s play – SQL injection with Havij by 3 year old”, http://www.troyhunt.com/2012/10/hacking-is-childs-play-sql-injection.html, troyhunt.com, 15 October 2012