Learning from major cyber security incidents
Learning from major cyber security incidents

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

Free course

Learning from major cyber security incidents

3.2 How did it work?

Judging by the large fine and the harsh comment from the ICO, you may have guessed that the TalkTalk attack was a relatively simple one. It was a type of attack known as a Structured Query Language injection (SQLi) – which, at the time of writing, has been well known and understood within the security field for over a decade.

The Structured Query Language (SQL) is a programming language that is used for managing relational databases and their data. As the contents of most modern commercial websites are database-driven, many web pages are dynamically created based on templates, user inputs, the data in the database and other information. This method enables web pages to be more easily personalised. However, if the designer of the template – which is usually a script or program that can access the databases – does not consider SQLi prevention, an attacker can append SQL codes to their input fields in the web page to manipulate data in the database, even if they are not authorised to do so.

To give you an insight into how the SQLi attack works, Box 1 gives a simplified example.

Box 1 How the SQLi attack works

Suppose there is a landing web page that asks you to enter your username and password, as shown in Figure 4.

Described image
Figure 4  A landing web page asking for username and password

This web page is controlled by a script, which will create a personalised web page if the user logs in successfully. The script takes the entered username and password from the input fields of the web page and uses them to construct a SQL query statement. For this example, the query statement is to ask the database to return the user’s stored information, including the password, so that it can compare it with the entered password. As ‘John’ and ‘myPass’ were entered, as shown in Figure 4, the script will create a query statement like the one below:

SELECT * FROM Users WHERE Name = "John" AND Password = "myPass"

Don’t worry if you don’t understand what the above SQL query statement means, as this will be explained now. The first part:

SELECT * FROM Users

asks the database to select all the fields (as the * symbol means all the fields) in the data table named Users. Table 1 shows the contents of the Users data table.

Table 1  An example table from a relational database, showing records of all the users

IDNameAddressEmailPhonePassword
1Faisal10 ABC Street, Some Townfaisal@abc.com01234 567890hisPass
2John20 ABC Street, Some Townjohn@abc.com01234 123456myPass
3Mei-ling30 ABC Street, Some Townmeiling@abc.com01234 098765herPass
156959Bert1 DEF Drive, Another Town Bert99@abc.com01567 987654BertHasAtleastTriedtoUseA securePassword^3

The second part:

WHERE Name = "John" AND Password = "myPass"

is a condition statement, which determines which row(s) in the data table are affected. In this example in Table 1, it is the second row as the contents in the Name and Password field match with those in the condition statement. In other words, the SQL statement asks the database to return all the data of the user whose name is ‘John’ and password is ‘myPass’.The following row of data will hence be returned:

IDNameAddressEmailPhonePassword
2John20 ABC Street, Some Townjohn@abc.com01234 123456myPass

Now, if the attacker can find a way to make the database bypass the checking of the username and password, it can potentially obtain all the information in the database. One way to achieve this is to construct the SQL statement as follows:

SELECT * FROM Users WHERE TRUE

As the condition statement is now always TRUE regardless of what the entered username and password are, the database will return everything in the Users table.

An attacker cannot change the SQL query statement directly as they have no control of the script. Nevertheless, they may be able to influence what the constructed SQL query statement will be by carefully crafting and appending SQL codes to the ‘Username’ and ‘Password’ fields in the landing web page. Figure 5 shows an example.

Described image
Figure 5  Example SQLi code

These inputs look quite odd – for instance, they are missing the beginning and ending quotation marks. However, they are specially crafted such that when the script combines the entered username and password to construct the SQL query statement, it will become:

SELECT * FROM Users WHERE Name = "whatever" OR "a"="a" AND Password = "whatever" OR "a"="a"

The condition statement now contains two OR clauses and one AND clause. The OR operator will output TRUE if either of the conditions on the left and right sides of the OR operator is TRUE. As "a"="a" (two identical letters) will always be evaluated as TRUE, the query statement is in effect equivalent to:

SELECT * FROM Users WHERE TRUE AND TRUE

The AND operator will output TRUE if both of the conditions on the left and right sides of the AND operator are TRUE. This means the query statement is equivalent to:

SELECT * FROM Users WHERE TRUE

This query statement will make the database bypass the checking of the username and password and show all the information in the Users data table.

The TalkTalk attackers used a similar SQLi principle to steal TalkTalk’s customer information. SQLi can also be used to add or delete data or even to delete the whole database. The web page designer must therefore ensure that any user inputs obtained through fields in a web page are free of SQL codes. There are a number of ways of doing this validation, but they will not be described here as they are out of the scope of this course.

Activity 9

Timing: Allow about 5 minutes

To reinforce your understanding of the SQLi attack watch Video 1, which explains the attack using an animation.

Download this video clip.Video player: Video 1
Skip transcript: Video 1  Animated explanation of the SQLi attack

Transcript: Video 1  Animated explanation of the SQLi attack

NARRATOR:
SQL injection is one of the top three most common web app risks. And it’s probably in part because of how simple it is to perform. So who’s most vulnerable? Any website that’s SQL database driven with poor code.
So how does it work? Well, this attack is very commonly done on login screens. So let’s first see what’s happening to your username and password after they’re submitted.
In a poorly designed site, the name and password strings are directly inserted into a SQL statement, which probably looks like this. With this statement, the application is asking the server, do we have a user with the name chris and the password mypass1. And if so, it grants the user access to his or her account.
So the first thing an attacker will do is find out if the site is using a SQL database by entering a single quote into the username field and submitting it. Single quotes are special characters in the SQL language, and using them as part of a username will cause an error if the website doesn’t check for them. Once the error message is displayed, the attacker can confirm that the website’s using SQL, as well as other useful information.
And now the real injection begins. The attacker can enter the following command logic, which will render the following command. The command will force the selection of a valid username because the evaluation of one equals one is always true.
Now most of the time, the server will log in the attacker with the credentials of the first user in the table. The range of command manipulations is vast, from record retrieval to complete table deletion, so you can imagine how damaging this exploit can be to a website.
So how can we prevent this form of attack? Well, make sure that you do the necessary string checking for special SQL characters. It’s really not that many more lines of code, and it’s most definitely worth the time.
There’s also automated software available that can check your entire web application for injection, as well as other vulnerabilities, so you should check it out. It’s called Rational AppScan, and it’s really awesome software. That’s it for now. I hope you learned something.

[MUSIC PLAYING]

End transcript: Video 1  Animated explanation of the SQLi attack
Video 1  Animated explanation of the SQLi attack
Interactive feature not available in single page view (see it in standard view).
TM255_1

Take your learning further

Making the decision to study can be a big step, which is why you'll want a trusted University. The Open University has 50 years’ experience delivering flexible learning and 170,000 students are studying with us right now. Take a look at all Open University courses.

If you are new to University-level study, we offer two introductory routes to our qualifications. You could either choose to start with an Access module, or a module which allows you to count your previous learning towards an Open University qualification. Read our guide on Where to take your learning next for more information.

Not ready for formal University study? Then browse over 1000 free courses on OpenLearn and sign up to our newsletter to hear about new free courses as they are released.

Every year, thousands of students decide to study with The Open University. With over 120 qualifications, we’ve got the right course for you.

Request an Open University prospectus371