SQL Injection Lab
DOWNLOAD SQL INJECTION LAB FILES -----------------------------------> |
Warning: For educational purposes only.
Below is a thorough, defensible explanation of SQL Injections, the common subtypes, how specific attacks work in principle, how to detect them, and safe, legal mitigation and testing advice. I will not provide instructions, exploit code, or step-by-step attack procedures that could be used to harm systems.
What is SQL injection?
SQL injection is an input-injection vulnerability that occurs when an application builds SQL queries by combining user input and SQL code without proper separation. If an attacker can control part of a query, they may cause the database to run unintended commands, read data they should not see, or modify data. It remains one of the most common and impactful web vulnerabilities. OWASP Foundation
High level types (conceptual)
Legal & ethical rules before you test
Example 1 — PHP (vulnerable pattern vs prepared statement)
Vulnerable pattern (do not run on production)
<?php
// vulnerable.php <-- DEMONSTRATION ONLY, run on localhost lab
$mysqli = new mysqli("127.0.0.1", "testuser", "testpass", "testdb");
$username = $_GET['username']; // user-controlled input
// Unsafe: concatenating user input into SQL
$sql = "SELECT id, username FROM users WHERE username = '" . $username . "'";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row['username']);
}
?>
Why this is bad: user input is merged into SQL text. If the input contains SQL syntax, the query's meaning changes.
Secure pattern — use prepared statements
<?php
// safe.php <-- preferred approach
$mysqli = new mysqli("127.0.0.1", "testuser", "testpass", "testdb");
$username = $_GET['username']; // user-controlled input
// Safe: parameterized query, database driver handles binding
$stmt = $mysqli->prepare("SELECT id, username FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row['username']);
}
?>
Prepared statements separate code from data, preventing the DB from interpreting user input as SQL.
Example 2 — Python Flask + sqlite3
Vulnerable pattern
# app_vulnerable.py (lab only)
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def get_db():
conn = sqlite3.connect('test.db')
return conn
@app.route('/user')
def user():
username = request.args.get('username', '')
# Unsafe: string formatting of SQL
sql = f"SELECT id, username FROM users WHERE username = '{username}'"
conn = get_db()
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
return '\n'.join([r[1] for r in rows])
Secure pattern — parameterized
# app_safe.py
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def get_db():
conn = sqlite3.connect('test.db')
return conn
@app.route('/user')
def user():
username = request.args.get('username', '')
conn = get_db()
cur = conn.cursor()
# Safe: use parameter substitution provided by DB-API
cur.execute("SELECT id, username FROM users WHERE username = ?", (username,))
rows = cur.fetchall()
return '\n'.join([r[1] for r in rows])
Note: In ORMs such as SQLAlchemy, using the ORM query API also helps avoid raw string building.
Example 3 — Using an ORM (SQLAlchemy) safely
# sqlalchemy_safe.py
from sqlalchemy import create_engine, MetaData, Table, select
from sqlalchemy.orm import Session
engine = create_engine("sqlite:///test.db", echo=False)
metadata = MetaData(bind=engine)
users = Table('users', metadata, autoload_with=engine)
def get_user(username):
with Session(engine) as session:
stmt = select(users.c.id, users.c.username).where(users.c.username == username)
rows = session.execute(stmt).all()
return [r.username for r in rows]
ORM query builders avoid manual string interpolation and bind parameters automatically.
Mitigations and best practices (summary)
Below is a thorough, defensible explanation of SQL Injections, the common subtypes, how specific attacks work in principle, how to detect them, and safe, legal mitigation and testing advice. I will not provide instructions, exploit code, or step-by-step attack procedures that could be used to harm systems.
What is SQL injection?
SQL injection is an input-injection vulnerability that occurs when an application builds SQL queries by combining user input and SQL code without proper separation. If an attacker can control part of a query, they may cause the database to run unintended commands, read data they should not see, or modify data. It remains one of the most common and impactful web vulnerabilities. OWASP Foundation
High level types (conceptual)
- In-band (classic): attacker receives results through the same channel the application uses, for example via returned query results or SQL error messages.
- Blind: the application does not show database errors, but differences in responses or timings reveal whether injected queries succeeded.
- Out-of-band: the database initiates a separate interaction (rare, needs specific DB/network config).
Legal & ethical rules before you test
- Never test production systems you do not own.
- Always obtain explicit written permission describing scope, dates, and allowed techniques.
- Prefer isolated labs: local VMs, containers, or intentionally vulnerable apps like DVWA, WebGoat, or custom test pages.
- Use non-destructive tests first, and coordinate with owners if anything might affect availability.
OWASP emphasizes safe, authorized testing practices. OWASP Foundation
- Use an intentionally vulnerable lab
- Install DVWA, WebGoat, or a small test app on a local VM. Test payloads there. This keeps risk contained and is legal. OWASP and many training resources recommend labs for learning. OWASP Foundation
- Observe application behavior differences
- Tests check whether input alters server behavior: different error messages, changed output, or timing differences. In a lab you can demonstrate this safely without exposing live data. The OWASP testing guide describes techniques for discovering injection points. OWASP Foundation
- Automated scanners and proxies (authorized use only)
- Tools such as OWASP ZAP, Burp Suite, and sqlmap can detect injection vectors on permitted test targets. These tools are powerful and must only be used with permission. sqlmap’s project page includes an explicit legal disclaimer. sqlmap+1
- Code review and static checks
- The safest and most reliable approach is reviewing server code for unsafe query construction. Look for string concatenation of user input into SQL, missing parameterization, and the use of dynamic SQL where not needed. OWASP provides prevention and code-review guidance. OWASP Cheat Sheet Series
Example 1 — PHP (vulnerable pattern vs prepared statement)
Vulnerable pattern (do not run on production)
<?php
// vulnerable.php <-- DEMONSTRATION ONLY, run on localhost lab
$mysqli = new mysqli("127.0.0.1", "testuser", "testpass", "testdb");
$username = $_GET['username']; // user-controlled input
// Unsafe: concatenating user input into SQL
$sql = "SELECT id, username FROM users WHERE username = '" . $username . "'";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row['username']);
}
?>
Why this is bad: user input is merged into SQL text. If the input contains SQL syntax, the query's meaning changes.
Secure pattern — use prepared statements
<?php
// safe.php <-- preferred approach
$mysqli = new mysqli("127.0.0.1", "testuser", "testpass", "testdb");
$username = $_GET['username']; // user-controlled input
// Safe: parameterized query, database driver handles binding
$stmt = $mysqli->prepare("SELECT id, username FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row['username']);
}
?>
Prepared statements separate code from data, preventing the DB from interpreting user input as SQL.
Example 2 — Python Flask + sqlite3
Vulnerable pattern
# app_vulnerable.py (lab only)
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def get_db():
conn = sqlite3.connect('test.db')
return conn
@app.route('/user')
def user():
username = request.args.get('username', '')
# Unsafe: string formatting of SQL
sql = f"SELECT id, username FROM users WHERE username = '{username}'"
conn = get_db()
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
return '\n'.join([r[1] for r in rows])
Secure pattern — parameterized
# app_safe.py
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def get_db():
conn = sqlite3.connect('test.db')
return conn
@app.route('/user')
def user():
username = request.args.get('username', '')
conn = get_db()
cur = conn.cursor()
# Safe: use parameter substitution provided by DB-API
cur.execute("SELECT id, username FROM users WHERE username = ?", (username,))
rows = cur.fetchall()
return '\n'.join([r[1] for r in rows])
Note: In ORMs such as SQLAlchemy, using the ORM query API also helps avoid raw string building.
Example 3 — Using an ORM (SQLAlchemy) safely
# sqlalchemy_safe.py
from sqlalchemy import create_engine, MetaData, Table, select
from sqlalchemy.orm import Session
engine = create_engine("sqlite:///test.db", echo=False)
metadata = MetaData(bind=engine)
users = Table('users', metadata, autoload_with=engine)
def get_user(username):
with Session(engine) as session:
stmt = select(users.c.id, users.c.username).where(users.c.username == username)
rows = session.execute(stmt).all()
return [r.username for r in rows]
ORM query builders avoid manual string interpolation and bind parameters automatically.
Mitigations and best practices (summary)
- Always use parameterized queries / prepared statements. This is the primary defense. OWASP Cheat Sheet Series
- Use least privilege for DB accounts. Don’t give the web app superuser access. Limit it to only the operations needed. OWASP Cheat Sheet Series
- Use stored procedures carefully. Stored procedures can help but do not automatically prevent injection if they build SQL dynamically.
- ORMs and query builders reduce the risk when used correctly.
- Input validation and output encoding: validate inputs for expected form and encode outputs where appropriate. Input validation is a complement, not a replacement for parameterization. OWASP Cheat Sheet Series
- Error handling: avoid leaking database errors to users. Detailed DB errors can help attackers refine injections.
- Logging and monitoring: detect anomalous queries and repeated errors that may indicate probing.
- Automated testing in CI: include static analysis, dependency scanning, and dynamic security scans in your CI pipeline. OWASP Foundation
- OWASP SQL Injection Prevention Cheat Sheet. Practical prevention patterns. OWASP Cheat Sheet Series
- OWASP Web Security Testing Guide, Testing for SQL Injection. Guidance on testing methodology. OWASP Foundation
- PortSwigger Web Security Academy SQL Injection materials and cheat sheets. Great hands-on interactive labs. PortSwigger
- sqlmap, an automated testing tool. Use responsibly and only on authorized targets. sqlmap project docs include a legal disclaimer. sqlmap+1