How does a SQL injection attack work?

If you're at all familiar with web development, you've probably heard of SQL injection attacks. An SQL injection is number one on the OWASP Top 10, a list of the most critical web application security risks, and can have a devastating effect.


Security best practices for PostgreSQL

Securing data with PostgreSQL


They’re simple to learn and execute, and so they’re one of the most commonly used forms of cyber attack. They’re also fairly simple to defend against, yet they’re a commonplace attack method on social media sites, online retailers, universities, and against SMBs who don’t have the know-how or resources to prevent these attacks.

The reason SQL injection attacks can prove so fearsome is that a large proportion of the web is built on SQL databases, including the ones provided by the likes of Microsoft, Oracle, and SAP. This makes SQL injection one of the most versatile attack methods in a hacker's arsenal, and it's usually among the first tools used as part of a breach attempt.

What is SQL injection?

To analyse how SQL injection works we first have to establish what SQL is. In short, SQL (or structured query language) is a programming language designed to manage large databases, such as the kind used by web applications. SQL is used to modify, retrieve and reorganise the data within a database with text-based commands.

A SQL injection attack is when a third party is able to use SQL commands to interfere with back-end databases in ways that they shouldn't be allowed to. This is generally the result of websites directly incorporating user-inputted text into a SQL query and then running that query against a database. How this works in a non-malicious context is that the user-inputted text is used to search the database - for example, logging in to a specific account by matching it based on the username and password entered by the user.

In a SQL injection, however, this process is hijacked to perform unauthorised functions. To use a simple example, the attacker could make use of the query process outlined above by using another SQL command to override the query's logic. The standard SQL query is designed to log into an account once it finds one in the database that matches a specific set of inputs; therefore, if the attacker is able to amend the query so that it adds the condition 'OR 1=1', it means that every entry in the table will return a positive result. Under those conditions, the query will log into the first account it finds, which in most databases is a user with admin privileges.

This functions in a very similar way to cross-site scripting, another style of injection attack that involves hackers inputting malicious scripts into web forms to target user browsers.

One of the most common outcomes of a SQL injection attack is the theft of user data. Login credentials, email addresses or personal information can all be sold on the black market or exploited for further cyber attacks. The attack can also be used to knock applications offline by deleting tables from the database, or to add new information to the database.

How to defend against SQL injection

Because these types of attacks leverage the user input fields in websites, whatever characters are used in these inputs can be ‘sanitised’ or screened for malicious code before it gets executed on the website.

There are telltale signs of an SQL injection attack, and the syntax they use to cause a digital nuisance. There are specific characters that can be found in the code of an SQL injection attempt, such as a colon, that should not - or will not as a result of the sanitisation rules applied to the input field - be allowed to be entered by the user. A colon, for example, would not be used in a name, therefore a website administrator could set a rule that forbids a first name or surname entry field from accepting a string with a colon in it. The same goes for an email address field - email addresses do not accept such characters and therefore can be safely sanitised using this rule.

This method of sanitisation works well, but a more efficient way of doing things is to use parameterised queries - the method recommended by OWASP as the first port of call. The method allows an SQL database that’s fed a malicious script to differentiate what is code and what is data - what might be malicious and what is a legitimate entry. By pre-defining all the SQL code and passing the user input fields into it as separate parameters, the website administrator, or developer, can ensure only valid data is passed into the entry field.

How have SQL injections been used and what impact do they have?


Security best practices for PostgreSQL

Securing data with PostgreSQL


SQL injections have been used in multiple cyber attacks over the last 20 years, often as an initial probe before other, more sophisticated tools and techniques are deployed.

They’re nothing to scoff at, though. SQL injection can result in stolen, deleted, or altered sensitive data. Attackers can create fake identities, change transactions, make themselves database administrators, or even go so far as to completely take over the webserver.

SQL injection attacks were used in the 2020 Freepick data breach to access 8.3 million users’ records, and in 2015's mammoth TalkTalk breach, resulting in the theft of over 150,000 customers’ personal data and a £400,000 pound fine against the firm.

Back in 2012, a group also used SQL injection attacks to steal 450,000 Yahoo users' login information, in one of a number of breaches that have hit the embattled web company over the past decade.

According to a report from web security firm Akamai, SQL injection attacks have accounted for more than 65% of web-based attacks between November 2017 and March 2019, with the US and the UK topping the charts as the most frequently targeted countries.

Adam Shepherd

Adam Shepherd has been a technology journalist since 2015, covering everything from cloud storage and security, to smartphones and servers. Over the course of his career, he’s seen the spread of 5G, the growing ubiquity of wireless devices, and the start of the connected revolution. He’s also been to more trade shows and technology conferences than he cares to count.

Adam is an avid follower of the latest hardware innovations, and he is never happier than when tinkering with complex network configurations, or exploring a new Linux distro. He was also previously a co-host on the ITPro Podcast, where he was often found ranting about his love of strange gadgets, his disdain for Windows Mobile, and everything in between.

You can find Adam tweeting about enterprise technology (or more often bad jokes) @AdamShepherUK.