Prepared Statements

Introduction

The previous lesson ended with a warning: building SQL queries by directly inserting user input is dangerous. This lesson explains exactly why, and shows you the standard fix that every serious PHP application uses — prepared statements.

By the end of this lesson, you will be able to rewrite any query that touches user input so that it can no longer be hijacked by malicious data.

What is SQL Injection?

SQL injection happens when untrusted input (typically from a form, a URL, or an API request) is inserted directly into a SQL query string. If that input contains SQL syntax of its own, the database may execute it as part of your query — completely bypassing your intended logic.

This is one of the most well-known and most damaging web application vulnerabilities. It can let an attacker read data they should never see, modify or delete records, or in the worst case, take control of the entire database.

A Vulnerable Example

Imagine a login form that checks a username and password by concatenating them straight into a query.

<?php
// NEVER do this with real user input
$username = $_POST["username"];
$password = $_POST["password"];

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql);
?>

If an attacker enters `' OR '1'='1` as the username, the query becomes logically true for every row, potentially logging them in as the first user in the table without ever knowing a real password.

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'

What Are Prepared Statements?

A prepared statement sends the SQL structure to the database first, with placeholders standing in for actual values, and sends the real data separately afterward. Because the database already knows the query's shape before it ever sees the data, that data can never be reinterpreted as SQL syntax — no matter what characters it contains.

Prepared Statements with MySQLi

In MySQLi, you prepare a query using `?` as a placeholder for each value, bind real values to those placeholders with `bind_param()`, then run the query with `execute()`.

<?php
$username = $_POST["username"];
$password = $_POST["password"];

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

$result = $stmt->get_result();
$user = $result->fetch_assoc();

$stmt->close();
?>

Binding Multiple Types

When a query has placeholders of different types, list the type letters in the same order as the values.

<?php
$stmt = $conn->prepare("INSERT INTO students (name, age, course) VALUES (?, ?, ?)");
$stmt->bind_param("sis", $name, $age, $course);

$name = "Amit Verma";
$age = 21;
$course = "PHP Development";

$stmt->execute();
echo "Rows inserted: " . $stmt->affected_rows;

$stmt->close();
?>

Rows inserted: 1

Prepared SELECT, UPDATE & DELETE

The exact same prepare / bind_param / execute pattern applies to every kind of query, not just INSERT.

<?php
// UPDATE
$stmt = $conn->prepare("UPDATE students SET course = ? WHERE id = ?");
$stmt->bind_param("si", $newCourse, $id);
$newCourse = "Advanced PHP";
$id = 1;
$stmt->execute();
$stmt->close();

// DELETE
$stmt = $conn->prepare("DELETE FROM students WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 3;
$stmt->execute();
$stmt->close();
?>

Vulnerable vs Safe, Side by Side

$sql = "SELECT * FROM users WHERE username = '$username'";
$conn->query($sql);

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Prepared statements are the standard, reliable defense against SQL injection: the query structure is sent to the database first, and real values are bound afterward as pure data.

In this lesson, you saw how a vulnerable, concatenated query can be exploited, and rewrote it safely using mysqli's prepare(), bind_param(), and execute(). Next, you will meet PDO, an alternative, database-agnostic way of doing all of this.