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