Let’s break down what SQL injection is, how attackers use it to access data, and why it’s still a major issue, despite being one of the oldest vulnerabilities around.
First things first, what is SQL injection?
At its simplest, SQL injection is a way for attackers to manipulate SQL queries that are being made by a website.
An SQL query will be sent to a database to gather, update, etc. data held by the database.
In a typical SQL database, data is stored in tables (we can use different tables to store different information – a users table may store username, password, email while a payments table may store account numbers, sort codes, account name).
Each table is made up of columns (defining the type of data, for example, username, password) and rows (which are referred to as records) which represent an entry in the database.
By being able to manipulate the query that is being sent to these tables, SQLi can be used to gain access to data that should not be provided to typical users.
So how does it work?
While there may be lots of information on the internet about using SQL injection to bypass login prompts, this is relatively rare now. Instead let’s look at other key functionality of a website that is likely to talk to a database.
A search feature of a website is, more likely than not, going to search a database (be that for users, pages, or any other information the site holds).
A typical search function of a website may run a query to the database that looks like the below:
SELECT username, email FROM users WHERE username LIKE ‘%<userinput>%’;
What is this doing?
- SELECT – select is an SQL command that will go and get data from the database
- Username, email – this is the data that is going to be provided to the user
- FROM users – this means that the query will go and get data from the ‘users’ table
- WHERE username – where adds a filter for the results, in this case the filter will be based on the column called ‘username’
- LIKE ‘%<userinput>%; – LIKE is used for a partial match, the use of the % character before and after the userinput means that any result that includes the input will be shown, regardless of what comes before or after. Eg searching for ‘a’ would show output that includes results like dan, frank, etc. (the % character represents any number of characters before or after the user input)
Crafting an SQL injection payload
One of the best processes for successful SQL injection is to start using a legitimate search query, then test for SQLi, and if the parameter is vulnerable, steadily craft a malicious payload.
Legitimate query
Search Query: a
So if we look at a simple search for ‘a’, the query would be:
SELECT username, email FROM users WHERE name LIKE’%a%;
Identifying vulnerability
Search Query: a’
Search Query: a”
One of the best ways to identify whether an application is vulnerable to SQL injection is to input the character ‘ and then “. This is because by adding a single quote, we are going to break the database query. It breaks because there will now be an odd number of quotation marks (the user’s input is already in quotes, so we are prematurely closing the parameter). If a single quote causes an error, it is a good thing for a pentester.
The next thing to check is that double quotes should be accepted by the application. The results may not be what is expected, but there should be no database error as we now have two sets of opened and closed quotation marks.
Once an application has been identified as vulnerable to SQL injection, then the fun starts, and an attacker will try to extract information they are interested in.
Getting All Possible Search Results
Search Query: a’ OR 1=1–
One of the most famous SQL injection payloads is OR 1=1–
What this does is makes sure that the query to the database is always true (this is because 1 is equal to 1 so saying OR 1=1 is the same as saying “or if I’m giving a true statement”)
So an attacker may use this logic to try and identify all products in the database by making the query:
SELECT username, email FROM users WHERE name LIKE’%a’ OR 1=1– %’;
While this will get all the information available from the current query, there are times where we want to get access to other information (this might be usernames and passwords or any other information the database stores).
Crafting a basic injection string
Search Query: a’ union select 1,2 —
While getting all possible search results can be useful, the real value comes from extracting data from other parts of the database.
This is where a UNION SELECT statement comes in. By using UNION, we can append an additional query to the one the application is already running, potentially retrieving sensitive information.
For this to work, the number of columns in our injected query must match the number of columns in the original query. In our example, the application returns two pieces of data: a name and an email address.
This suggests that the original query is selecting two columns, meaning our injection must also select two values. To test this, we can use simple numbers like 1,2, which will be returned if the query executes successfully. If we see those numbers appear in the results, we know we’ve matched the column count and can start replacing them with more useful data from the database.
Identifying the Type of Database
Search Query: ‘ union select 1,sqlite_version() —
To do this, it is important to identify which type of database is being used.
This is important as it helps us structure the queries that we will use to get table information, and ultimately access the data we are interested in.
One of the common methods used to identify the type of database is to simply ask what version of software is being used. Each database has a specific command for this, so testing different version queries can reveal which database is in use. If a query returns an error, that means the database doesn’t recognise the command, helping us rule out certain options. If it responds with a version number, we have successfully identified the type of database we are talking to.
Common version queries:
- MSSQL – @@version
- MySQL – @@version
- Oracle – version()
- SQLite – sqlite_version()
By searching for ‘union select 1,sqlite_version() — we get the following output:
This shows that the database is an SQLite database.
Getting data
To get data out of this database, we first need to know what tables are used (this is where the data is held). Then what columns are used by the tables we are interested in. This is because in our example, we can only get two pieces of data, so our final injection will need to include the specific column names that we are interested in.
Getting table names
Search Query: ‘ union select 1,tbl_name from sqlite_master where type=’table’ —
Getting column names
Search Query: ‘ union select 1,name from pragma_table_info(‘users’) —
This shows us all the columns in the users table.
Now that we have this information, we can make a final SQL injection payload that will get us all the usernames and passwords in the database.
Getting usernames and passwords
Search Query: ‘ union select username, password from users —
Our database is particularly badly configured and stored passwords in plaintext. In a real SQL injection attack, you would expect to see password hashes that you could then try to crack.
To continue your SQL learning journey, head to our YouTube channel for a video tutorial.