Protecting Your Database from SQL Injections

SQL Injections happen when malicious queries allow an attacker to gain unauthorized access to a database, or even to execute commands on the database directly. According to the Open Web Application Security Project (OWASP), code injections top the list of the most pressing web application security risks. This makes sense, given that a poorly designed database can allow a malicious agent to bypass authorization mechanisms, permitting them to retrieve or edit an entire database, which is clearly a major privacy and security concern. In some cases, agents may even gain a “backdoor” into a database which allows them to access an organization’s systems over the long-term, if it goes unnoticed.

How can SQL Injections happen?

SQL Injections can happen when an application takes in data entered by a user, such as on a login form, and does not properly sanitize this untrusted data before adding it to a SQL query.

There are many examples of how this can impact a business:

Accessing private user data

Let’s assume we have an application where users login using a username and password. Typically, if the user supplied the username "harrypotter" and the password "hogwarts," the SQL query would check the credentials and return the information for the user as so:

SELECT * 
FROM users 
WHERE username = ‘harrypotter’ 
AND password = ‘hogwarts’;

A malicious user could exploit an insecure database by adapting the query to always return true, regardless of the input, and access all of the data:

SELECT * 
FROM users 
WHERE username = ‘’ OR 1=1--’;

If the attacker inputs ’ OR 1=1--’ as the username, the password requirement is bypassed and all user data is returned. This is because 1 = 1 always returns true, and the -- symbol is commonly used to “comment out” the rest of a query, meaning that it won’t be reached.

Displaying hidden data

Imagine a scenario where an e-commerce website displays only the items that have already been released on a web page. Perhaps they have a highly-anticipated new shoe collection, which is ready to go on the website soon, but is not yet available to the public. The URL for such a page may look something like:

https://webstore.com/products?category=shoes

When the user accesses this URL the application creates a SQL query to retrieve all of the shoes that are currently available from the database:

SELECT * 
FROM products 
WHERE category = ‘shoes’ 
AND released = 1;

This query will retrieve all the items from the products table that are in the shoes category and have been released (released = 1: the items from the new collection would be labelled with released = 0).

Without proper controls, an attacker could game this system by altering the URL:

https://webstore.com/products?category=shoes’--

The new query would look like this:

SELECT * 
FROM products 
WHERE category = ‘shoes’--’ 
AND released = 1;

In the query above, the final part (AND released = 1) is commented out, and therefore never included, so all products are returned. The attacker now has access to all the information about the new collection and can leak this information to the public.

Retrieving data from other tables

Beyond just accessing data from one table, an attacker can use the UNION keyword to SELECT items from another table entirely. Building on the example above, say an attacker included the following URL:

https://webstore.com/products?category=shoes' UNION SELECT name, address FROM users--

This would create the following final query:

SELECT * 
FROM products 
WHERE category = ‘shoes’ 
UNION SELECT name, address 
FROM users--

...allowing the attacker to access private customer details, just from a URL.

Deleting information

Some database servers allow multiple SQL statements separated by semicolons to be executed at once. In our ongoing example, the URL for item 100 could be modified as follows:

https://webstore.com/items/itemid=100; DROP TABLE users

Creating the following query:

SELECT * 
FROM items 
WHERE itemid = 100;
DROP TABLE users;

Thereby deleting all user records.

Source: https://xkcd.com/327/

How can SQL Injections be prevented?

It’s clear that SQL injections present a major privacy and security risk. So, how can they be prevented?

As a first step, the incoming data should be “sanitized.” This involves checking for things like special symbols, null/empty input, or common SQL injection payloads, and removing them as appropriate. We’d also want to check that the data are in the correct format before allowing them (for example, credit card numbers should be of an expected data type and length).

Another key preventative measure is to incorporate “parameterized queries” instead of string concatenation when creating queries. In this case, rather than simply inserting the user’s input, the query contains a placeholder. If an incorrect input is submitted an error will still be generated, but the attacker will not be able to access the database.

A simple example would adapt our original code that uses string concatenation:

“SELECT * 
FROM users 
WHERE username = 
‘“ + username + “’ 
AND password = 
‘“ + password * “‘

To look something like the following:

sql = “SELECT * 
FROM users 
WHERE username = '%s’ 
AND password = '%s’”

args = (my_username, my_password)

cursor.execute(sql, args)

In this case, the query cannot dynamically construct a new query if it is passed malicious text. If we were to pass the username ’ OR 1=1--’ to the second query, it would simply look for a user whose name matched ’ OR 1=1--’, presumably would not find one, and would return an error.

There are many more specifics regarding how best to implement data sanitization and parameterized queries for complicated databases. For this reason, it is critical to ensure that your database administrator has the tools and knowledge to properly secure your database infrastructure from SQL injection attacks.