Drop it Like it’s Hot: SQLi 101

Estimated difficulty: 💜💜💜🤍🤍

Continuing our journey into the land of web hax, this week (as requested by the world of Twitter) we are covering SQL injection basics.

Little Bobby Tables – Digital, Data & Policy — *One of my favourite cartoons*

What is SQL?

SQL stands for Structured Query Language and is commonly used by various applications to interact with a database, usually submitting queries to retrieve specific information. SQL can also be used to modify databases, such as adding or deleting data. SQL is used to manage relational database systems, such as MySQL, PostgreSQL and Microsoft SQL Server. Relational databases are essentially a type of database that use a structure to facilitate direct access of data in relation to other pieces of data in the database.

Relational databases are usually organised into tables, tables often store rows (and rows, and rows..) of data. These rows are called records. Every record should have a unique identifier known as a primary key, this helps identify each individual record. Each column in a table is called a field and is used to describe the different aspects of the record data (with individual components of records described as field values). Each table is usually specific to a particular subject e.g. employee names, credentials, products etc.

When a web application uses SQL, it often queries the database to retrieve specific information from a table. This could be a whole record, or just individual field values. For example, let’s say you have an online shop, you want to search the online shop for a new laptop (treat yo’ self!)

When you try to find products matching the laptop category, the application will execute a query similar to the following…

SELECT * FROM products WHERE category = 'laptops'

This SQL query asks the database to select all items (SELECT *) from the table products (FROM products) where the category is laptops (WHERE category = ‘laptops’)

Similarly, let’s say Bob wants to login to an application. When Bob attempts to login to an application that utilises SQL it may query the following…

SELECT * FROM users WHERE username = '$username' AND password = '$password'

This SQL queries asks the database the user input into the form by selecting all items (SELECT *) from the table users (FROM users) where the username of the user attempting to login matches the password on record (WHERE username = ‘$username’ AND password = ‘$password’ e.g. WHERE username = ‘bob’ AND password = ‘supersecurepassword’)

If the password input into the login form does not match the password on record (in this case, ‘supersecurepassword’) then it will deny the login.

Make sense? Sort of? If not, have no fear – I’ll add some additional resources at the end of this blog 🙂

SQL Query Components

To help out with SQLi, it may also be handy to know some common SQL query components. I recently helped host an SQLi workshop with the Ladies of Cheltenham Hacking Society – so the following section is due credit to them (shoutout to Nat & Jenny), and similarly a lot of the content of the blog has been supported by the materials from that workshop!

A single backtick or closing quote (` or ‘) are used by SQL to close query fields. These are usually automatically added and are a great way to attempt to trigger an error in an application

Comments are specified by using a double hyphen (—), adding this to the end of a query will comment the rest of the query out

Logic statements are use to validate queries, such as 1=1 (true) and 1=2 (false)

SELECT is used to select data, and is commonly the start of a query (e.g. SELECT * FROM users)

UNION combines the results of two SELECT statements, and can help retrieve duplicate fields from multiple tables (e.g. SELECT firstName FROM customers UNION SELECT firstName FROM suppliers)

WHERE adds a condition within the query to retrieve specific results (e.g. WHERE id=1)

AND/OR are used to define criteria that needs to be checked before returning the query (e.g. SELECT * from users WHERE firstName = ‘bob’ AND lastname = ‘smith’)

DELETE removes records from a table, it’s easy to specify which records you would like deleted and these queries are logged in the case of a rollback

TRUNCATE removes all records from a table (this is faster then doing a whole bunch of “DELETE”‘s)

DROP deletes whole tables and databases, this is super-hard to reverse and just causes absolute chaos tbh

Knowing the above, we should now be all set to go through some basic SQLi attacks!

What is SQLi?

SQL injection is a vulnerability that is exploited by attackers so that they can submit their own SQL queries to an application database. It usually occurs when applications do not sanitise user input, and therefore user-supplied data is unintentionally processed by the web application. Usually this vulnerability allows attackers to view sensitive data, or modify the contents of the database. Sometimes SQLi attacks can even be utilised to compromise backend infrastructure. SQLi falls under the “Injection” category of the OWASP Top 10, however “Injection” covers a wide variety of injection techniques such as OS, LDAP and NoSQL.

Consider we are trying to bypass a login page which uses an SQL database to retrieve and check credentials…

When we use the login page, the application submits the following query to check if the supplied credentials match the database…

SELECT * FROM users WHERE username = '$username' AND password = '$password'

But what if we can breakout the query and submit our own one…? Using a logic statement, we can trick the application into logging us in – without having to submit the correct password!

SELECT * FROM users WHERE username = 'admin' OR 1=1-- AND password = '$password'

By submitting the string “admin’ OR 1=1–” the application runs the following query…

SELECT * FROM users WHERE username = 'admin' OR 1=1

Using a single quote closes the first query field, and as 1=1 is always true – the application therefore logs us in without the need for a matching password! By commenting out the rest of the query (–) the latter half of the original query is not run.

Now let’s consider a web application that uses SQL to manage the search function of an online shop.

When we search for headphones, the application submits the following query to check the product list for any matching items…

SELECT * FROM products WHERE category = 'headphones'

Once again, we can breakout this query by submitting our own…

SELECT * FROM products WHERE category = 'headphones' UNION SELECT username, password FROM users--

Similarly we can use a single quote to close the first query field, and then submit our own UNION SELECT query to retrieve data from another table in the database (in this case, credentials of other users!)

There are plenty of tricks that you can use to submit your own SQL queries, Netsparker have a really comprehensive SQLi cheatsheet here and Portswigger have another fab cheatsheet for SQLi here.

SQLMap

There are several tools that can help you detect and automate SQLi, one of my favourites is SQLMap. SQLMap is pre-built into Kali Linux, or you can grab the git repo here and install it to the OS of your choice.

SQLMap has tonnes of features, from grabbing database version to automating the SQLi testing process.

Below I’ve recorded a very quick demo of using SQLMap against a DVWA instance to retrieve the user information from the backend MySQL database. I’ve specified a cookie to execute the scan as an authenticated user, and then use several SQLMap options to map the database and extract the usernames and hashes.

Mitigation

So, now that we know the basics of SQLi – hopefully you now understand how much damage an SQLi attack can cause. There are several ways you can mitigate SQLi, the most common being sanitising all user input fed to the application. Another popular mitigation technique is to use parameterised queries within prepared statements to allow the database to defferentiate between code and data.

OWASP have written an SQLi prevention cheatsheet here if you want to find out more about mitigating SQLi.

And that’s a wrap! Hopefully by this point in the blog, you’ll now understand this meme…