Exercise 41: Improper Input Validation and SQL Injection

Objective

Learn how to exploit Improper Input Validation leading to SQL Injection (SQLi) vulnerabilities to execute unauthorized SQL commands on a database. Understand how to prevent SQL injection using prepared statements, parameterized queries, and secure coding practices.

---

Scenario

You are testing a web application that uses user input directly in SQL queries without proper validation. Due to this flaw, an attacker can manipulate SQL queries to access sensitive data or alter the database.

---

Lab Setup

Prerequisites:

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

Step 1: Create the Vulnerable Application

1. Database Setup
   • Create a database and users table: CREATE DATABASE sql_injection_lab; USE sql_injection_lab; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL ); INSERT INTO users (username, password) VALUES ('alice', 'alice123'), ('bob', 'bob123');
2. PHP Script with SQL Injection Vulnerability
   • Create a file login.php: <?php $conn = mysqli_connect("localhost", "root", "", "sql_injection_lab"); if (isset($_POST['login'])) { $username = $_POST['username']; $password = $_POST['password']; $query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'"; $result = mysqli_query($conn, $query); if (mysqli_num_rows($result) > 0) { echo "<h2>Login Successful!</h2>"; } else { echo "<h2>Invalid Credentials!</h2>"; } } ?> <h2>Login</h2> <form method="POST" action=""> Username: <input type="text" name="username" required><br> Password: <input type="password" name="password" required><br> <button type="submit" name="login">Login</button> </form>
3. Running the Application
   • Start the Apache server.
   • Place login.php in the web server’s root directory (htdocs for XAMPP).
   • Open http://localhost/login.php in your browser.

---

Exploitation Steps

Step 1: Bypassing Authentication

1. Enter the following payload in the Username field: ' OR '1'='1
2. Enter any value in the Password field and click Login.

Expected Result:

• The attacker successfully bypasses authentication due to the SQL injection.

Step 2: Extracting Data

1. Enter this payload in the Username field: ' UNION SELECT null, username, password FROM users --

Expected Result:

• The application displays all usernames and passwords from the users table.

Step 3: Deleting the Users Table

1. Enter this destructive payload in the Username field: '; DROP TABLE users; --

Expected Result:

• The users table is deleted, making the login system unusable.

---

Solution and Prevention

Problem Analysis

• User input is embedded directly into SQL queries without sanitization or validation.

Fixing the Vulnerability

1. Use Prepared Statements (Parameterized Queries)
   • Replace the vulnerable query with a secure one: $stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); $stmt->bind_param("ss", $username, $password); $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { echo "<h2>Login Successful!</h2>"; } else { echo "<h2>Invalid Credentials!</h2>"; }
2. Validate and Sanitize User Input
   • Ensure all inputs are strictly validated: $username = filter_var($_POST['username'], FILTER_SANITIZE_STRING); $password = filter_var($_POST['password'], FILTER_SANITIZE_STRING);
3. Limit Database Permissions
   • Restrict the database user to only necessary permissions (e.g., no DROP privileges).
4. Use Stored Procedures
   • Encapsulate SQL logic within stored procedures to reduce risk.
5. Error Handling
   • Disable detailed SQL error messages in production to prevent leakage.

---

Testing After Fix

1. Attempt to inject ' OR '1'='1.
2. Expected Result:
   • The login fails because the query is now properly handled.
3. Attempt to run destructive commands like DROP TABLE.
4. Expected Result:
   • The input is rejected, and the database remains intact.

---

Conclusion

In this lab, you exploited SQL Injection due to improper input validation and unsanitized SQL queries. You also learned how to prevent SQLi attacks by using prepared statements, validating input, and following secure database practices.