Exercise 2: SQL Injection (Advanced: Union-Based)

Objective

Learn to exploit a union-based SQL Injection vulnerability to extract sensitive data from multiple database tables and understand mitigation techniques.

Scenario

You are tasked with performing a security assessment on a blogging website. The website has a search feature that allows users to search blog posts by keywords. However, the search functionality is improperly handling user inputs, making it vulnerable to Union-Based SQL Injection. Your objective is to exploit this vulnerability to extract sensitive data, such as usernames and passwords.

Lab Setup

Prerequisites:

• Basic knowledge of HTML, PHP (or any backend language), and SQL.
• XAMPP/LAMP/WAMP stack installed on your machine (or any web server with PHP and MySQL support).
• A code editor (e.g., VSCode, Sublime Text).

Step 1: Create the Vulnerable Web Application

Database Setup

Open phpMyAdmin and create a new database:

CREATE DATABASE blog_db;

Use the database:

USE blog_db;

Create tables:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL
);

Insert sample data:

INSERT INTO posts (title, content) VALUES ('Welcome', 'This is the first blog post.');
INSERT INTO posts (title, content) VALUES ('Security Tips', 'Always sanitize user inputs.');

INSERT INTO users (username, password) VALUES ('admin', 'admin123');
INSERT INTO users (username, password) VALUES ('user', 'user123');

PHP Script for Search Functionality

Create a file search.php:

<?php
$conn = mysqli_connect("localhost", "root", "", "blog_db");

if (isset($_GET['search'])) {
    $search = $_GET['search'];
    $query = "SELECT * FROM posts WHERE title LIKE '%$search%'";
    $result = mysqli_query($conn, $query);

    while ($row = mysqli_fetch_assoc($result)) {
        echo "<h2>" . $row['title'] . "</h2>";
        echo "<p>" . $row['content'] . "</p>";
    }
}
?>
<form method="GET" action="">
    Search: <input type="text" name="search" required>
    <button type="submit">Search</button>
</form>

Running the Application

Start your Apache and MySQL servers.

Place search.php in the web server’s root directory (htdocs for XAMPP).

Open http://localhost/search.php in your browser.

Exploitation Steps

Step 1: Identifying the Number of Columns

In the search box, enter:

' ORDER BY 1--

Increase the number (1, 2, 3, …) until an error occurs to identify the number of columns.

Expected Result:

• No error for existing columns, but an error for non-existent ones. Assume 3 columns exist.

Step 2: Testing for Union-Based SQL Injection

Input the payload:

' UNION SELECT NULL, NULL, NULL--

If no error, replace NULLs with data to extract information:

' UNION SELECT 1, username, password FROM users--

Expected Result:

• Usernames and passwords from the users table are displayed on the page.

Explanation

• The payload merges the users table with the original query, exposing sensitive data.

Solution and Prevention

Problem Analysis

• The application directly concatenates user input into SQL queries.

Fixing the Vulnerability

Use Prepared Statements and Input Validation:

<?php
$conn = new mysqli("localhost", "root", "", "blog_db");

if (isset($_GET['search'])) {
    $stmt = $conn->prepare("SELECT * FROM posts WHERE title LIKE ?");
    $search = "%" . $_GET['search'] . "%";
    $stmt->bind_param("s", $search);
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        echo "<h2>" . htmlspecialchars($row['title']) . "</h2>";
        echo "<p>" . htmlspecialchars($row['content']) . "</p>";
    }
}
?>

Additional Protections:

• Input validation and sanitization.
• Least privilege principle for database users.

Testing After Fix

1. Attempt the same SQL Injection payloads after implementing prepared statements.
2. Observe that the input is properly handled, and no data is leaked.

Conclusion

In this lab, you exploited a Union-Based SQL Injection vulnerability to extract sensitive data. You also learned how to secure applications using prepared statements and input validation to prevent such attacks. Mastering these concepts is crucial for identifying and mitigating complex SQL Injection vulnerabilities.