Introduction

The vulnerable environment (OWASP Juice Shop) used in this lab can be set up locally or accessed via demo instances online.

SQL injection remains one of the most critical and prevalent web application vulnerabilities, consistently ranking in the OWASP Top 10. Despite being well-understood for over two decades, it continues to appear in modern applications due to improper input validation and insecure coding practices.

In this comprehensive lab report, I'll walk you through my hands-on experience exploiting SQL injection vulnerabilities in OWASP Juice Shop, a deliberately insecure web application. From basic authentication bypass to advanced union-based attacks and hash cracking, this journey covers the essential techniques every security professional should know.

1. Objectives

The objective of this lab is to provide participants with practical insights into SQL injection vulnerabilities and exploitation techniques. Through a structured, task-based progression, participants will gain hands-on experience in identifying and exploiting SQL injection weaknesses in a controlled environment.

By the end of the lab, participants will:

• Understand how unvalidated user inputs can be manipulated to execute unauthorized SQL queries.
• Learn to extract sensitive data such as table schema, user credentials, and other details.
• Develop a deeper understanding of advanced SQL injection techniques such as union-based injections and their role in bypassing application restrictions.

2. Background

SQL injection occurs when an application fails to validate and sanitize user inputs embedded in SQL queries, allowing an attacker to inject malicious SQL code. This vulnerability is commonly exploited to manipulate or retrieve sensitive database information.

In this lab, participants will work through a series of SQL injection tasks targeting an intentionally vulnerable application (OWASP Juice Shop). Each task focuses on different aspects of SQL injection, including:

• Database Schema Enumeration: Exploiting union-based injections to extract the names of all tables in the database.
• Data Extraction: Retrieving sensitive information, such as unavailable product details, user credentials, and other information.
• Understanding SQLite Features: Leveraging SQLite-specific system tables (e.g., "sqlite_master" or "sqlite_schema") for information discovery.

Key Concepts and Techniques

• Union-Based SQL Injection: Combining query results from multiple tables to extract unauthorized data.
• Column Enumeration: Matching column counts and types to craft valid SQL queries.
• Hash Cracking: Using tools like CrackStation to convert hashed passwords into plaintext.

Important Note: This lab focuses on exploiting SQL injection vulnerabilities in a web application using an SQLite database, not MySQL. SQLite stores schema information in a special table called "sqlite_master" or "sqlite_schema" which is queried to enumerate tables and extract data. Techniques are adapted to SQLite-specific functionalities and syntax.

Understanding Union-Based SQL Injection Attacks

Before diving into our four tasks, we must understand how Union-Based SQL Injection Attacks work.

Original Query (Legitimate)

SELECT col1, col2, col3 FROM products WHERE name = 'xyz';

This retrieves products matching the name "xyz".

Injected Query

SELECT sql, 2, 3 FROM sqlite_schema WHERE type = 'table';

This retrieves table definitions from the "sqlite_schema" system table.

Combined Query

Using the "UNION" operator, the malicious query merges with the original:

SELECT col1, col2, col3 FROM products WHERE name = 'xyz' 
UNION 
SELECT sql, 2, 3 FROM sqlite_schema WHERE type = 'table';

Result:

• Left-hand side: Results from the "products" table (e.g., product details).
• Right-hand side: Table definitions (e.g., "CREATE TABLE users (...)"), helping the attacker discover the database structure.

Why It Works: The "UNION" combines two queries with the same number of columns. The attacker uses placeholders (2, 3) to align column counts and retrieves sensitive database information due to missing input validation.

Basic SQL Injection Attack: Authentication Bypass

Conclusion of the Basic SQL Injection Attack

• Identified Vulnerability: The login functionality is vulnerable to SQL injection due to improper input validation and sanitization. Entering a single apostrophe (') caused an SQL error, confirming the vulnerability.
• Exploited Information Leakage: Examining the error in the browser's developer tools revealed the underlying SQL query used to authenticate users:

SELECT * FROM Users WHERE email = "" AND password = "<hash>" AND deletedAt IS NULL

This query exposed sensitive database details, including column names and structure.

• Crafted a Tautology Injection: By injecting ' OR 1=1-- as the username, we bypassed authentication:

SELECT * FROM Users WHERE email = "" OR 1=1-- AND password = "<hash>" AND deletedAt IS NULL

The "1=1" condition always evaluates to true, logging us in as the first user in the database (the administrator).

• Achieved Administrator Access: Using the SQL injection, we successfully gained access to an administrator account without valid credentials.
• Key Takeaway: SQL injection attacks exploit insufficient input validation. This attack demonstrated how attackers can bypass authentication and gain unauthorized access, highlighting the importance of secure coding practices such as parameterized queries and input sanitization.

Advanced SQL Injection Attack

Conclusion of the Advanced Injection Attack

• Confirmed SQL Injection Vulnerability in the Search Endpoint: By testing with "q='))--", it was confirmed that the search functionality was vulnerable to SQL injection. The error response indicated the application was executing our input as part of its SQL query.
• Matched Column Count for Union Injection: Incrementally increased the number of columns in the injection payload until finding a match with the original query's structure:

q=')) UNION SELECT '1', '2', '3', '4', '5', '6', '7', '8', '9' FROM users

• Extracted Sensitive Data: Retrieved user credentials, including email and password hashes, using:

q=xyz')) UNION SELECT email, password, '3', '4', '5', '6', '7', '8', '9' FROM users

• Cracked Password Hashes: Converted the extracted hashes into plaintext passwords using tools like CrackStation, revealing login credentials for multiple users, including administrators.
• Accessed Detailed User Information: Extracted sensitive information from other tables (e.g., Addresses), demonstrating the ability to compromise the application and its user base.

Task 1: Determining the Entire Database Schema Using SQL Injection

Objective: To uncover the database schema by listing all table names and retrieving their definitions.

Key Learnings from Research

After reviewing the SQLite FAQ, two essential findings were identified:

• sqlite_schema Table: This special table contains metadata about the database, including information about tables, indices, and their definitions.
• sql Field in sqlite_schema: This field contains the exact SQL commands ("CREATE TABLE" or "CREATE INDEX") used to create the respective tables or indices. This is critical for understanding the database structure.

Methodology

Injection Payload: The following payload was crafted to list table schema information:

http://localhost:3000/rest/products/search?q=xyz') UNION SELECT sql, 2, 3, 4, 5, 6, 7, 8, 9 FROM sqlite_schema WHERE type='table'--

Explanation of the Payload

• UNION: Combines the original query results with results from the injected query.
• sqlite_schema: Targets the special table holding metadata.
• WHERE type='table': Filters to retrieve only table-related metadata.
• sql: Selects the column containing the "CREATE TABLE" statements, revealing table structure.

Purpose of Column Alignment: The placeholders (2, 3, 4, …) align the number of columns in the injected query with the original query, avoiding query syntax errors.

Executed Query

The injection effectively mimicked this SQLite command:

SELECT sql FROM sqlite_schema WHERE type='table';

Results

The payload successfully extracted JSON data showing:

• All table names in the database
• The original SQL statements ("CREATE TABLE") defining each table's schema

Summary of Findings

Using SQL injection and insights from the "sqlite_schema" table, the database schema was enumerated, providing an understanding of its structure and aiding further testing or exploitation.

Table Names Extracted

The screenshot is placed below the table names text for better transparency.

Addresses
BasketItems
Baskets
Captchas
Cards
Challenges
Complaints
Deliveries
Feedback
ImageCaptchas
Memories

Task 2: Determining Which Products Are No Longer Available for Sale

To identify products that are currently unavailable for sale in the Juice Shop, we can execute the following SQL injection payload to query the "Quantities" table. This payload retrieves product details where the quantity is either 0 or NULL, indicating that the product is out of stock or unavailable.

Injection Payload

http://localhost:3000/rest/products/search?q=xyz')) union select id, ProductId, quantity, limitPerUser, createdAt, updatedAt, 7, 8, 9 from Quantities WHERE quantity = 0 OR quantity IS NULL--

Results: Unavailable Products

The payload successfully retrieves details of products that are unavailable for sale. Each result includes:

• ID: A unique identifier for each product.
• ProductId: The associated product's ID from the "Quantities" table.
• Quantity: Indicates the current stock level, with values of 0 or NULL indicating unavailability.
• LimitPerUser: The maximum quantity allowed per user, if set.
• CreatedAt & UpdatedAt: Timestamps for when the record was created and last updated.

List of Products No Longer Available

The screenshot is placed below the table names text for better transparency.

ID
27
34
36
37
38

Task 3: Extracting Fields from the Addresses Table

To access specific fields from the "Addresses" table found in Task 1, the following SQL injection payload was used:

Injection Payload

http://localhost:3000/rest/products/search?q=xyz')) UNION SELECT fullName, zipCode, mobileNum, 4, 5, 6, 7, 8, 9 FROM Addresses

Results: Address Data Extracted

The payload successfully retrieves the "fullName", "zipCode", and "mobileNum" fields, along with placeholder data for the other fields, represented as follows:

Understanding the Response Mapping

The reason why fields such as "fullName", "zipCode", and "mobileNum" don't appear directly in the response with their original column names is due to how the SQL "UNION SELECT" payload manipulates the database query and maps the extracted data into the expected structure of the original query's output.

Explanation

Original Query Structure: The application expects to query the "Products" table, which has fields like "id", "name", "description", etc. When data is returned from the database, it is mapped into this predefined structure (e.g., "id" → "Administrator", "name" → "4711", "description" → "1234567890").

SQL UNION SELECT: The "UNION SELECT" statement forces the database to append data from the "Addresses" table (i.e., "fullName", "zipCode", "mobileNum") into the query's results. However, the column names from the "Addresses" table are not preserved in the output. Instead, their data is assigned to the placeholders of the original query's column names:

Why This Happens: The application doesn't know about the additional fields from the "Addresses" table because it was not designed to handle this extra data. Instead, it just uses its preconfigured column names ("id", "name", "description", etc.) to display whatever data is returned.

Task 4: Cracking/Breaking Hashes

We successfully executed a SQL injection attack to retrieve sensitive user information, including email addresses and password hashes.

Injection Payload

By injecting the following payload into the search query parameter:

http://localhost:3000/rest/products/search?q=xyz')) UNION SELECT email, password, '3', '4', '5', '6', '7', '8', '9' FROM users--

Results

This malicious query allowed us to extract credentials from the "users" table, revealing emails and hashed passwords for multiple users.

Hash Cracking with CrackStation

After successfully retrieving the hashed passwords from the SQL injection, we proceeded to break the hashes using CrackStation, a free online password hash cracker.

By submitting the hashed values to the tool, we were able to break the password hashes and obtain the original plaintext values, further demonstrating the vulnerability of the system to hash cracking.

Key Takeaways and Mitigation Strategies

What We Learned

1. Input Validation is Critical: Never trust user input. All input should be validated, sanitized, and parameterized.
2. Error Messages Leak Information: Verbose error messages can reveal database structure and help attackers refine their exploits.
3. Union-Based Attacks are Powerful: Once column counts are matched, attackers can extract data from any table.
4. Hash Cracking Complements SQL Injection: Weak or unsalted hashes can be cracked, turning extracted hashes into usable credentials.
5. SQLite-Specific Features: Understanding the target database system (SQLite vs. MySQL vs. PostgreSQL) enables more effective exploitation.

Mitigation Techniques

• Use Parameterized Queries (Prepared Statements): This is the most effective defense against SQL injection.
• Implement Input Validation: Whitelist allowed characters and validate input types.
• Minimize Error Information: Return generic error messages to users while logging details internally.
• Use Strong Hashing Algorithms: Implement bcrypt, Argon2, or PBKDF2 with appropriate work factors and salts.
• Apply the Principle of Least Privilege: Database accounts should have only the permissions they absolutely need.

Conclusion

This hands-on lab provided invaluable experience in understanding SQL injection vulnerabilities from an attacker's perspective. Through progressive tasks, we learned:

1. Basic exploitation: Authentication bypass using tautology injections
2. Schema enumeration: Extracting database structure using "sqlite_schema"
3. Data extraction: Retrieving sensitive information from multiple tables
4. Hash cracking: Converting password hashes to plaintext credentials

The key takeaway is that SQL injection remains a critical vulnerability that can lead to complete database compromise. However, understanding these attack vectors is crucial for building more secure applications and implementing effective defenses such as parameterized queries, input validation, and proper error handling.

This lab was completed as part of the Software Security course using OWASP Juice Shop. All attacks were performed in a controlled educational environment.