What is an SQL injection and how can they be prevented?
6 Nov 2018
8 minute read
SQL injections are number one on the OWASP Top 10 list of web application security risks. According to a 2015 study, large businesses lost an average of $3.79 million every time they experienced a data breach. Every developer needs to know what SQL injections are and how to prevent them. Cyber security professionals in particular need to know SQL injections inside and out.
What is SQL?
SQL (often pronounced “sequel”) is a language used to write queries to a database. Popular SQL-compatible databases are MySQL, Oracle, Microsoft SQL server, and PostgreSQL.
SQL queries can be very simple or very complex. Let’s start with a simple example:
UPDATE users SET password = 'kmocrv143' WHERE user_id = 42
This query will update the record in the database corresponding to user 42, changing the password to
kmocrv143. (We will ignore the fact that passwords should not be stored in a database to make this example easier to understand.)
How are SQL queries constructed by programs?
var query = "UPDATE users SET password = '" + password + "' WHERE user_id = " + uid
The query construction we have just seen is wide open to an SQL injection. This happens if an attacker places a single quote mark in the password value, allowing unintentional queries to be constructed.
Let’s suppose an attacker tries to change their password to
cat'dog. The generated query will look like this:
UPDATE users SET password = 'cat'dog' WHERE user_id = 42
This is not a valid SQL query since
'cat' is recognised as a string but it is followed by
dog' which is meaningless to SQL. In this case, the database will produce an error and the query will do nothing. However, the user will notice an error and might suspect that a more sophisticated SQL injection is possible.
Exploiting the weakness
Let’s suppose the attacker wants to change the admin password in the system, then log in as an admin, and that the admin has user id 1. It is tricky to construct a query that does this, but let’s give it a go.
If we try to set the password to:
cat' WHERE user_id = 1 then the query that gets constructed looks like this:
UPDATE users SET password = 'cat' WHERE user_id = 1' WHERE user_id = 42
Once again we will get an error, because there is extra junk at the end of the query that the database cannot process. There is an easy way around this. Entering
-- into a query tells the database to ignore everything after it. This is normally used to write comments explaining queries. We can simply use the same password as last time but add
-- to the end, namely
cat' WHERE user_id = 1 --
This results in the following query being sent to the database:
UPDATE users SET password = 'cat' WHERE user_id = 1 --' WHERE user_id = 42
The database ignores everything from the -- onwards. It now has a valid query. It will update the password for the user whose id is 1. The attacker can then log in with this known password. The attack really is as simple as that.
Not all SQL attacks are this easy to pull off. Some of them involve slipping something into a place that is assumed to be “clean”, i.e. to not contain anything an attacker can control. Some of them involve creative abuse of the SQL language.
Preventing SQL queries
It is reasonably easy to defend against SQL attacks. Whenever data is injected into an SQL query, it must simply be escaped appropriately. In particular, single quote marks must have a backslash inserted in front of them, and certain other characters need to be escaped also. Details may vary between databases but the idea is the same. Most languages already have functions or packages that can do this for popular databases. If this had been done in the above example, we would have had this query:
UPDATE users SET password = 'cat\' WHERE user_id = 1 --' WHERE user_id = 42
This tiny change means that the database reads
cat' WHERE user_id = 1 -- as a single string, and thus the query runs as expected (albeit setting a very strange password for user 42).
Unfortunately it is easy for developers to occasionally make a mistake, and just one mistake is all it takes for an attacker to gain access. For this reason it is preferable to use tools like ORMs to make the escaping process as automatic as possible. Nonetheless there is plenty of legacy code that uses straight SQL and needs to be hardened against SQL injections.
This article is only a coverage of the basics. If you are interested in cyberattacks and defending against them, and want to quickly gain a range of skills, have a look at Coder Academy’s new Cyber Security Bootcamp.
Want to become a junior developer? At Coder Academy we have Australia's first and only accredited fast-track coding bootcamp that will set you up for a new career in tech. Our immersive course helps students acquire in-demand skills through hands on, project-based training by industry experts over six months.
Now enrolling domestic & international students in Sydney, Melbourne & Brisbane! Study now, pay later with FEE-HELP!
Are you a woman interested in coding? Check out our Women in Tech Scholarship!