Want to keep learning?

This content is taken from the Raspberry Pi Foundation & National Centre for Computing Education's online course, Introduction to Cybersecurity for Teachers. Join the course to learn more.

Skip to 0 minutes and 3 secondsIn this step, we will look at a specific attack on software which stores information in a database, called an SQL injection attack. You'll also learn how to defend against this attack using input sanitization. SQL, or "se-quel", is a programming language used to communicate with databases. Let's suppose we run a website that requires a username and password. We would need to keep a database that stores this information about each of our users. When a user logs into the website, this action uses SQL to send a request to the database containing their username and password. If the combination isn't found, an error is returned. If it is found, the user is given access to their account.

Skip to 0 minutes and 48 secondsA SQL injection is an attack which exploits the way the queries to databases are formed. Instead of submitting a username and password, the attacker can submit two strings that trick the database into giving up all of its data. The characters in the string that allow this attack are called bad characters. To understand why this is the case, let's look in more detail at the form of a database query. The request to the database might look something like this. When the user enters their username and password, these values replace username and password in the query. For example, if your username is GoTfan and the password is password1, the query would become this.

Skip to 1 minute and 31 secondsThis request says, go through each row of the database, and if both statements are true-- the username is GoTfan and the password is password1-- then return the account. What if the attacker was able to input a string such that both statements were always true? This would trick the database into returning all of the accounts. To do this, the attacker could submit the following for both the username and password. When we input these values, we get this query. When we run this query, the username and password won't match anything. But the statement x equals x is always true. This means that both statements will always be true and the query can return every row from the database.

Skip to 2 minutes and 15 secondsTwo methods of protecting against SQL injections are input sanitization and hashing. Input sanitization removes bad characters from the input that changed the meaning of the query, such as quotation marks. Let's re-examine the attack with sanitization. If we remove the quotation marks, the input becomes this. So the query will now request an account with a username and password, which are both x or x equals x, which it shouldn't find in the database. Another method of protecting against SQL injections is the use of hashing. If the password in the database was hashed, then whatever the attacker inputs will automatically be altered. Even though the first statement is always true, the second isn't. This makes it harder to attack, but it's not impossible.

Skip to 3 minutes and 9 secondsIn theory, the attacker could produce a hash output the manipulates the second statement to always be true. Quotation marks aren't the only characters that can be used to manipulate a query. To successfully defend against the SQL injection, you need to know all of the possible bad characters. Each time you find a new version of the attack, you have to release another security patch to defend against it. An alternative approach might be to work out which characters can't be used to carry out a SQL attack and only allow users to create usernames and passwords from this list. You could then reject any submission that contained anything other than good characters.

SQL injections and input sanitisation

SQL is a programming language used to communicate with databases.

When a user signs in to a website that requires a username and password, SQL is used to send a request to the database containing their username and password. If the combination is found, the user’s account is returned, and if it isn’t found, an error is returned (for example, “Wrong username or password”).

An SQL injection is an attack which exploits how the requests to the database are formed. Instead of submitting a username and password, the attacker can submit two strings that trick the database into giving up its information. The characters in the strings that allow this attack are called bad characters.

The request to the database might look something like this:

SELECT row FROM database WHERE Username = "username" and Password = "password"

When the user enters their username and password, these values replace ‘username’ and ‘password’ in the request. For example, if your username is ‘GoTfan’ and your password is ‘password1’, the request becomes:

SELECT row FROM database WHERE Username = "GoTfan" and Password = "password1"

This request says “go through each row in the database and if both statements

  1. Username = “GoTfan”
  2. Password = “password1”

are true, return the account”.

If an attacker was able to input a string such that both statements are always true, it would trick the database into returning all of the accounts.

To do this, the attacker could submit

"x or ""x=x"

for both the username and password. If you input these values into the request, you get:

SELECT row FROM database WHERE Username = ""x or ""x=x"" and Password = ""x or ""x=x""

This request says “go through each row in the database and if both statements

  1. (Username = x) OR (x=x)
  2. (Password = x) OR (x=x)

are true, return the account”.

The statement x=x is always true, which means that even if the username and password are not both equal to x, both statements will return as true, so the database will return the account.

Protecting against SQL injections

When designing a database, you could design the queries that request data from the database so that the input to the form is not added directly to the query. Instead, you could search for and remove bad characters. This process is called input sanitisation.

Most often, any ' or " characters are removed, but they are not the only characters that can be used to manipulate the request. To defend against an SQL injection, you need to know all of the possible bad characters. If you were to find a new version of the attack, you would need to release another security patch to defend against it.

If you sanitised the attacker’s input to remove any ' or " characters, you would get:

x or x=x

When this is inputted, the query becomes:

SELECT row FROM database WHERE Username = "x or x=x" and Password = "x or x=x"

This request now says “go through each row in the database and if both statements

  1. Username = “x or x=x”
  2. Password = “x or x=x”

are true, return the account”. This query is requesting an account where the username and password are both “x or x=x”.

Alternatively, you could work out which characters cannot be used to carry out an SQL attack, and only allow users to create usernames and passwords from these characters. You could then reject any submission which contained anything other than good characters.

You could also hash passwords, as you learned about last week. If the passwords in the database are hashed, then the attacker’s input will be altered automatically.

If the attacker used the same input as before, the request would become:

SELECT row FROM database WHERE Username = ""x or ""x=x"" and Password = "e6a1826aebc1012b1444d9933684e9b5"

Even though the first statement is always true, the second isn’t. This makes the attack harder (but not impossible, because the attacker could, in theory, produce a hash output that manipulates the second statement to always be true).

Next step

In the next step, you will learn about why people might not download security patches or keep their software up-to-date.

Questions

  • Think back to the cycle of improvement described earlier. At what stage of the cycle would it be most advantageous to discover that your system is vulnerable to something like an SQL injection?
  • There are many other forms of SQL injection. Can you find an example to share in the comments?
  • Why are databases vulnerable to SQL injections? (Hint: think about how the website communicates with the database, which stages of the process the attacker is modifying, and why the attacks are successful)

Share your answers in the comments

Share this video:

This video is from the free online course:

Introduction to Cybersecurity for Teachers

Raspberry Pi Foundation