What is an SQL injection and how can they be prevented?


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?

Most queries are not typed by humans. Most of the time, computers generate queries automatically. Here is an example in JavaScript:

var query = "UPDATE users SET password = '" + password + "' WHERE user_id = " + uid

This example creates queries of the sort seen above. A password and user id is inserted into the query, allowing various users to choose any password. Typically, this will allow a user to change her own password. Notice how a single quote will appear on either side of the password. (The double quotes are not added; they tell JavaScript where the string starts and ends.)

The weakness

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.

Coder Academy is the most loved Australian Coding Bootcamp provider according to Course Report. Our accredited coding Bootcamps will set you up for a new career in tech via our Fast Track Bootcamp or our Flex Track Bootcamp . Our immersive courses help students acquire in-demand skills through hands on, project-based training by industry educators and experts.

Now enrolling | domestic & international students in Sydney, Melbourne & Brisbane! Study now, pay later with FEE-HELP!