SQL Injection - How It Works & How to Prevent It
SQL injection is the #1 web vulnerability. An attacker embeds SQL syntax in user input - if your code interpolates that input into a query, the attacker can read any data, bypass authentication, delete tables, or execute system commands (in some configurations). The fix is structurally simple: never concatenate user input into SQL.
SQL Injection - Attack & Defense
<?php
declare(strict_types=1);
// ── How SQL injection works ────────────────────────────────────
// Vulnerable login - NEVER do this:
$username = $_POST["username"];
$password = $_POST["password"];
// If username = admin'-- (attacker crafted)
// SQL becomes:
// SELECT * FROM users WHERE username = 'admin'--' AND password = '...'
// The -- comments out the password check - attacker is logged in as admin!
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$user = $pdo->query($sql)->fetch();
// Another injection - username = ' OR '1'='1
// SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'
// '1'='1' is always true - returns all users, logs in as first user
// ── Prevention: Prepared Statements ───────────────────────────
// SQL structure and data are SEPARATE - data is never interpreted as SQL
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute([":username" => $username]);
$user = $stmt->fetch();
// The password is then verified with password_verify()
// No matter what the user types, it's ALWAYS treated as literal data
// ── Second-order SQL injection ────────────────────────────────
// Dangerous pattern - using stored data without prepared statements:
// 1. Attacker stores: O'Brien in username field (via prepared stmt - safe)
// 2. Some code later reads $name = "O'Brien" from DB
// 3. Uses it in: "SELECT * FROM orders WHERE customer = '$name'"
// 4. The apostrophe breaks the query even though it came from the DB
// ✅ Solution: ALWAYS use prepared statements regardless of data source.
// Even if data came from your own trusted DB, parameterise it.
// ── Dynamic ORDER BY columns (can't be parameterised) ─────────
// ❌ Vulnerable:
$order = $_GET["sort"] ?? "created_at";
$sql = "SELECT * FROM posts ORDER BY $order"; // injection possible!
// ✅ Allowlist approach:
$allowedSortColumns = ["created_at", "title", "views", "published_at"];
$order = in_array($_GET["sort"] ?? "", $allowedSortColumns, true)
? $_GET["sort"]
: "created_at";
$direction = ($_GET["dir"] ?? "desc") === "asc" ? "ASC" : "DESC";
// Column names and SQL keywords cannot be parameterised - validate with allowlist
$sql = "SELECT * FROM posts ORDER BY $order $direction";Quick Quiz
Tip
Tip
Practice SQL Injection How It Works How to Prevent It in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Visual architecture and concepts for SQL Injection - How It Works & How to Prevent It
Practice Task
Note
Practice Task - (1) Write a working example of SQL Injection How It Works How to Prevent It from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Common Mistake
Warning
A common mistake with SQL Injection How It Works How to Prevent It is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready php code.