SQL Injection

Learn about SQL Injection vulnerabilities and how to exploit and prevent them across different security levels

Overview

SQL Injection (SQLi) is a critical web security vulnerability that occurs when an attacker can insert or “inject” malicious SQL code into queries executed by the application’s database. A successful SQL injection attack can have severe consequences, including:

• Reading sensitive data from the database
• Modifying database data (INSERT/UPDATE/DELETE operations)
• Executing administrative operations (such as shutting down the DBMS)
• Recovering files from the database server filesystem using LOAD_FILE()
• In some cases, issuing commands to the operating system

SQL injection attacks are a type of injection attack, where SQL commands are injected into data-plane input to affect the execution of predefined SQL commands.

Objective

There are 5 users in the database with IDs from 1 to 5. Your mission is to steal their passwords via SQL injection.

Security Levels

Low

Vulnerability Analysis

At the low security level, the application uses raw user input directly in SQL queries without any sanitization or validation. This is the most dangerous form of SQL injection vulnerability.

Vulnerable Code

$id = $_REQUEST[ 'id' ];

$query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";
$result = mysqli_query($GLOBALS["___mysqli_ston"],  $query );
```sql

**Key Vulnerability**: The `$id` parameter is inserted directly into the SQL query string using single quotes. An attacker can escape these quotes and inject arbitrary SQL.

#### Why It's Vulnerable

- No input validation or sanitization
- User input is directly concatenated into the SQL query
- Single quotes around the parameter allow easy escape using `'`
- Error messages are displayed to users, revealing database structure

#### Testing Approach

1. **Basic injection test**: Try entering `1' OR '1'='1` to see if you can bypass the query logic
2. **UNION-based injection**: Use UNION SELECT to extract data from other columns
3. **Comment syntax**: Use `-- -` or `#` to comment out the rest of the query

<Accordion title="Show Hint">
  The query uses single quotes around the user_id parameter. You can close the quote, add your own SQL, and comment out the rest.

  Try using the UNION operator to select additional data. Remember that UNION requires the same number of columns in both SELECT statements.
</Accordion>

<Accordion title="Show Spoiler">
  **Example payload**: `?id=a' UNION SELECT "text1","text2";-- -&Submit=Submit`

  This payload:
  1. Closes the original query with `'`
  2. Uses UNION to add another SELECT statement
  3. Comments out the rest with `-- -`

  For extracting passwords, you could use:

1’ UNION SELECT user, password FROM users— -

    </Accordion>

  </Tab>

  <Tab title="Medium">
    ### Vulnerability Analysis

    The medium level attempts to implement protection using `mysql_real_escape_string()`, but the implementation is **fundamentally flawed** because the query parameter is not wrapped in quotes.

    #### Vulnerable Code

    ```php
    $id = $_POST[ 'id' ];
    $id = mysqli_real_escape_string($GLOBALS["___mysqli_ston"], $id);

    $query  = "SELECT first_name, last_name FROM users WHERE user_id = $id;";
    $result = mysqli_query($GLOBALS["___mysqli_ston"], $query);

Key Vulnerability: While mysqli_real_escape_string() escapes special characters, the parameter has no quotes around it in the query. This means numeric injection is still possible.

Why It’s Still Vulnerable

• mysqli_real_escape_string() escapes quotes, backslashes, and NULL bytes
• However, since there are no quotes around $id in the query, attackers don’t need to escape anything
• Numeric-based injection still works perfectly
• The form was changed from GET to POST (dropdown instead of text box), but this provides no security

Changes from Low Level

• Uses POST instead of GET
• Implements mysqli_real_escape_string() for escaping
• Dropdown UI instead of text input (easily bypassed with proxy tools)

Show Hint

Notice that the query doesn’t have quotes around the user_id parameter. This means you can inject numeric SQL without needing to escape quotes.

Try injecting without using single quotes. You can still use UNION SELECT and comments.

Show Spoiler

Example payload: ?id=1 UNION SELECT 1,2;-- -&Submit=Submit

This works because:

1. No quotes to escape
2. mysqli_real_escape_string() doesn’t prevent numeric injection
3. The UNION statement is valid SQL syntax

For extracting data:

      1 UNION SELECT user, password FROM users-- -

High

Vulnerability Analysis

The high level is similar to the low level in terms of the SQL vulnerability itself, but it uses a different attack vector: the input is transferred via session variables instead of direct GET parameters.

Vulnerable Code

$id = $_SESSION[ 'id' ];

$query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id' LIMIT 1;";
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);
```sql

**Key Vulnerability**: The query still uses raw, unescaped input with single quotes. The only difference is that the input comes from a session variable set on another page.

#### Why It's Vulnerable

- Same fundamental vulnerability as the low level
- Session variables are still controllable by the attacker (just indirectly)
- The LIMIT clause doesn't prevent injection
- Generic error message ("Something went wrong") but doesn't fix the underlying issue

#### Changes from Previous Levels

- Input is set via a separate page and stored in `$_SESSION`
- Added `LIMIT 1` to the query (doesn't prevent injection)
- Error messages are suppressed ("Something went wrong")

#### Testing Approach

You'll need to:
1. Identify where the session variable is set
2. Inject your payload through that mechanism
3. The injection technique is identical to the low level

<Accordion title="Show Hint">
  The vulnerability is the same as the low level - the query uses single quotes and doesn't sanitize input. You just need to inject through the session setting mechanism.

  Look for the page where the ID is set in the session.
</Accordion>

<Accordion title="Show Spoiler">
  **Example payload**: `ID: 1' UNION SELECT "text1","text2";-- -&Submit=Submit`

  The injection is the same as low level, just delivered through a different page that sets the session variable.

  You can also try to bypass the intermediate page and set the cookie directly if you can identify the session mechanism.
</Accordion>

</Tab>

<Tab title="Impossible">
### Secure Implementation

The impossible level demonstrates the **correct way** to prevent SQL injection: using **parameterized queries** (prepared statements).

#### Secure Code

```php
$id = $_GET[ 'id' ];

// Was a number entered?
if(is_numeric( $id )) {
    $id = intval ($id);
    
    // Parameterized query using PDO
    $data = $db->prepare( 'SELECT first_name, last_name FROM users WHERE user_id = (:id) LIMIT 1;' );
    $data->bindParam( ':id', $id, PDO::PARAM_INT );
    $data->execute();
    $row = $data->fetch();
}

Why It’s Secure

1. Input validation: Checks if the input is numeric using is_numeric()
2. Type casting: Converts to integer with intval()
3. Parameterized queries: Uses PDO prepared statements with parameter binding
4. Anti-CSRF token: Validates the request came from a legitimate form
5. Clear separation: The query structure is defined separately from the data

Defense Mechanisms

• Prepared statements: The database knows what is code vs. data before execution
• Parameter binding: bindParam() with PDO::PARAM_INT ensures type safety
• Input validation: Multiple layers of validation before query execution
• CSRF protection: checkToken() prevents cross-site request forgery

This implementation makes SQL injection impossible because:

• The query structure is predefined
• User input is treated purely as data, never as code
• The database driver handles all escaping automatically
• Type checking ensures only valid integers are used

Testing Methodology

Manual Testing

1. Input validation bypass: Try special characters: ', ", ;, --, #
2. UNION-based injection: Determine the number of columns, then extract data
3. Boolean-based blind injection: Test with AND 1=1 vs AND 1=2
4. Error-based injection: Force SQL errors to reveal database structure
5. Time-based blind injection: Use SLEEP() or BENCHMARK() functions

Automated Testing

Tools like sqlmap can automate SQL injection testing:

# Basic scan
sqlmap -u "http://target/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="PHPSESSID=..."

# Dump database
sqlmap -u "http://target/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="PHPSESSID=..." --dump
```sql

## Defense Strategies

### Primary Defenses

1. **Use Prepared Statements (Parameterized Queries)**
   ```php
   $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
   $stmt->execute(['id' => $id]);

2. Use Stored Procedures (if properly implemented)
   $stmt = $pdo->prepare('CALL getUserById(:id)');
   $stmt->execute(['id' => $id]);
   

### Additional Defenses

3. **Input Validation (Whitelist)**
   - Validate data type (numeric, alphanumeric, etc.)
   - Validate format (regex patterns)
   - Validate length and range

4. **Least Privilege**
   - Database users should have minimal permissions
   - Use separate accounts for different operations
   - Never use root/admin accounts for web applications

5. **Escaping (Last Resort)**
   - Only if parameterized queries aren't possible
   - Use database-specific escaping functions properly
   - **Must** combine with quotes in queries

### What Doesn't Work

- **Blacklisting special characters**: Easily bypassed
- **Escaping without quotes**: As shown in medium level
- **Suppressing error messages**: Security through obscurity
- **Using POST instead of GET**: Doesn't prevent injection
- **Client-side validation**: Can be bypassed entirely

## Resources

- [OWASP SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)
- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [PortSwigger Web Security Academy - SQL Injection](https://portswigger.net/web-security/sql-injection)
- [MySQL Real Escape String](https://secure.php.net/manual/en/function.mysql-real-escape-string.php)
- [PDO Prepared Statements](https://www.php.net/manual/en/pdo.prepared-statements.php)