What Are We Doing in This Post?
In Episode 11 we connected PHP to MySQL and built a working CRUD application. We used mysqli_real_escape_string() to protect against SQL injection.
But here is the truth — that approach, while functional, is old-fashioned. Professional PHP developers do not use it anymore.
The modern, secure, and universally recommended way to interact with databases in PHP is PDO with Prepared Statements. This is also exactly what Laravel uses internally. Understanding this episode means you will understand what Laravel is doing under the hood when it queries your database.
What is SQL Injection — And Why Should You Lose Sleep Over It?
Before we jump into PDO, let us truly understand the problem it solves.
Imagine your login form. A user enters their username and you build this SQL query:
<?php
$username = $_POST["username"];
$sql = "SELECT * FROM users WHERE username = '$username'";
?>
Now imagine a malicious user types this into the username field:
' OR '1'='1
Your query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1'
The condition '1'='1' is always true. This query now returns every single user in your database. The attacker is logged in without a valid password. They have access to everything.
Real world analogy: Imagine a security guard checking IDs at a club entrance. He asks for your name and checks it against a list. But instead of your name, you say "Anyone OR everyone." The guard, following instructions literally, lets in literally everyone because "everyone" matches.
SQL injection has caused some of the biggest data breaches in history. It brought down companies, exposed millions of passwords, and leaked government data. It is not theoretical — it is the most common web attack in the world.
Prepared statements eliminate this completely — not by escaping, but by separating the SQL structure from the data entirely.
What is PDO?
PDO stands for PHP Data Objects. It is a database abstraction layer built into PHP.
MySQLi only works with MySQL. PDO works with twelve different database systems — MySQL, PostgreSQL, SQLite, Oracle, and more — using the exact same code. You switch databases by changing one line.
PDO also has a cleaner, more modern API that is much closer to how Laravel's Eloquent works internally.
Connecting to MySQL With PDO
Create a new file called pdo_db.php in your phplearning folder:
<?php
$host = "localhost";
$dbname = "phplearning";
$username = "root";
$password = "";
try {
$pdo = new PDO(
"mysql:host=$host;dbname=$dbname;charset=utf8mb4",
$username,
$password
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
?>
Let us break this down carefully.
new PDO() creates a new PDO connection. The first argument is called the DSN — Data Source Name. It tells PDO which database driver to use (mysql), where the server is (localhost), which database to connect to (phplearning), and what character encoding to use (utf8mb4 — the proper encoding that supports all characters including emoji).
The second and third arguments are the username and password.
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) tells PDO to throw exceptions when something goes wrong — instead of silently failing. This means database errors will not pass quietly and you will always know when something breaks.
setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC) tells PDO to return rows as associative arrays by default — so $row["name"] instead of $row[0]. Much more readable.
The try/catch block handles connection failures gracefully. If the connection fails, the PDOException is caught and we display a clean error message instead of a confusing PHP crash.
What Are Prepared Statements?
A prepared statement splits a database query into two completely separate steps.
Step 1 — Send the SQL structure to MySQL first, with placeholders where the user data will go. MySQL parses and compiles the query at this point.
Step 2 — Send the actual data separately. MySQL inserts the data into the already-compiled query.
Because MySQL compiled the query before it ever saw the user data, it is impossible for user data to change the structure of the query. The data is just data — it can never become SQL code.
Real world analogy: Think of a prepared statement like a pre-printed form. The form structure — the boxes, labels, and fields — is fixed and printed in advance. The user only fills in the blank boxes. No matter what the user writes in the boxes, they cannot change the structure of the form itself. Prepared statements work exactly the same way — the SQL structure is fixed, the user data just fills the blanks.
Prepared Statements in Action — INSERT
Create pdo_insert.php:
<?php
require_once "pdo_db.php";
$name = "Rahul Verma";
$email = "rahul@example.com";
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([
":name" => $name,
":email" => $email
]);
echo "User added. ID: " . $pdo->lastInsertId();
?>
$pdo->prepare() sends the SQL structure to MySQL with named placeholders — :name and :email — where the real values will go. MySQL compiles this query template.
$stmt->execute() sends the actual data as an array. PDO binds each value to its placeholder and MySQL executes the query safely. The user data never touches the SQL structure.
lastInsertId() returns the auto-incremented ID of the row that was just inserted — useful when you need to immediately reference the new record.
Named placeholders (:name, :email) are cleaner and more readable than positional placeholders (?). You can use either — we will see both shortly.
Prepared Statements — SELECT With One Row
<?php
require_once "pdo_db.php";
$email = "rahul@example.com";
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([":email" => $email]);
$user = $stmt->fetch();
if ($user) {
echo "Found: " . $user["name"] . " — " . $user["email"];
} else {
echo "No user found with that email.";
}
?>
fetch() retrieves a single row. Because we set FETCH_ASSOC as the default, it comes back as an associative array automatically.
If no row matches, fetch() returns false — which is why we check if ($user) before accessing it.
Prepared Statements — SELECT Multiple Rows
<?php
require_once "pdo_db.php";
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY id DESC");
$stmt->execute();
$users = $stmt->fetchAll();
foreach ($users as $user) {
echo $user["id"] . " — " . $user["name"] . " — " . $user["email"];
echo "<br>";
}
?>
fetchAll() retrieves all matching rows at once and returns them as an array of associative arrays. You loop through them with foreach exactly like any other array.
Prepared Statements — UPDATE
<?php
require_once "pdo_db.php";
$id = 1;
$name = "Gagan Singh";
$email = "gagan.singh@example.com";
$stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");
$stmt->execute([
":name" => $name,
":email" => $email,
":id" => $id
]);
echo "Rows updated: " . $stmt->rowCount();
?>
rowCount() returns the number of rows affected by the last INSERT, UPDATE, or DELETE query. Useful for confirming that the operation actually changed something.
Prepared Statements — DELETE
<?php
require_once "pdo_db.php";
$id = 2;
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([":id" => $id]);
echo "User deleted. Rows affected: " . $stmt->rowCount();
?>
Positional Placeholders — The ? Syntax
Instead of named placeholders like :name, you can use question marks as positional placeholders. The values are then passed in order.
<?php
require_once "pdo_db.php";
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["Priya Sharma", "priya@example.com"]);
echo "User added.";
?>
The first ? maps to the first value in the array, the second ? to the second value, and so on. Named placeholders are generally preferred because they are clearer and order-independent, but positional placeholders are shorter when you have many parameters.
A Complete PDO CRUD Application
Let us rebuild our user management system from Episode 11 — this time using PDO and prepared statements properly.
Create pdo_users.php:
<?php
require_once "pdo_db.php";
$message = "";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$action = $_POST["action"] ?? "";
if ($action == "add") {
$name = trim($_POST["name"] ?? "");
$email = trim($_POST["email"] ?? "");
if (!empty($name) && !empty($email) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([":name" => $name, ":email" => $email]);
$message = "User added successfully. ID: " . $pdo->lastInsertId();
} else {
$message = "Please provide a valid name and email.";
}
}
if ($action == "delete") {
$id = (int) ($_POST["id"] ?? 0);
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([":id" => $id]);
$message = "User deleted.";
}
}
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY id DESC");
$stmt->execute();
$users = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<title>User Management — PDO</title>
</head>
<body style="font-family:sans-serif; max-width:720px; margin:40px auto; padding:0 16px;">
<h2>User Management — PDO Version</h2>
<?php if ($message): ?>
<p style="color:green; font-weight:bold;"><?php echo htmlspecialchars($message); ?></p>
<?php endif; ?>
<h3>Add New User</h3>
<form method="POST" action="pdo_users.php">
<input type="hidden" name="action" value="add" />
<input type="text" name="name" placeholder="Full Name" style="padding:8px; margin-right:8px;" required />
<input type="email" name="email" placeholder="Email Address" style="padding:8px; margin-right:8px;" required />
<button type="submit" style="padding:8px 16px; background:#6366f1; color:#fff; border:none; border-radius:4px; cursor:pointer;">Add User</button>
</form>
<h3 style="margin-top:32px;">All Users (<?php echo count($users); ?>)</h3>
<?php if (!empty($users)): ?>
<table border="1" cellpadding="10" cellspacing="0" style="width:100%; border-collapse:collapse;">
<tr style="background:#f1f5f9;">
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Joined</th>
<th>Action</th>
</tr>
<?php foreach ($users as $user): ?>
<tr>
<td><?php echo $user["id"]; ?></td>
<td><?php echo htmlspecialchars($user["name"]); ?></td>
<td><?php echo htmlspecialchars($user["email"]); ?></td>
<td><?php echo $user["created_at"]; ?></td>
<td>
<form method="POST" action="pdo_users.php" style="display:inline;">
<input type="hidden" name="action" value="delete" />
<input type="hidden" name="id" value="<?php echo $user['id']; ?>" />
<button type="submit"
style="background:#ef4444; color:#fff; border:none; padding:4px 10px; border-radius:4px; cursor:pointer;"
onclick="return confirm('Delete this user?')">
Delete
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
</table>
<?php else: ?>
<p>No users yet. Add one above.</p>
<?php endif; ?>
</body>
</html>
Visit http://localhost:8080/phplearning/pdo_users.php
This does everything the Episode 11 version did — but now with proper prepared statements, email validation, and PDO's clean API. Zero SQL injection risk.
PDO vs MySQLi — Quick Comparison
PDO works with 12 database systems. MySQLi works only with MySQL.
PDO has a cleaner and more consistent API. MySQLi mixes procedural and object-oriented styles.
PDO prepared statements use named placeholders. MySQLi uses positional only.
PDO is what Laravel uses internally. Understanding PDO makes Laravel feel familiar immediately.
For any new project, always use PDO. The only reason to learn MySQLi was so that you understand legacy code — which you will encounter in real jobs.
What Did We Learn in This Post?
SQL injection is the most common web attack — user input manipulates the SQL query structure to bypass security or destroy data.
PDO is PHP's modern database abstraction layer. It works with twelve database systems and has a clean, consistent API.
Prepared statements separate SQL structure from user data completely — making SQL injection impossible by design.
prepare() sends the SQL template to MySQL. execute() sends the data separately. fetch() retrieves one row. fetchAll() retrieves all rows. rowCount() returns affected rows. lastInsertId() returns the new row's ID.
Named placeholders (:name) are cleaner than positional placeholders (?). Both are valid.
PDO is what Laravel uses internally — mastering it now means Laravel's database layer will feel completely natural.
What is Coming in Episode 13?
We have now covered the core of PHP — variables, operators, conditionals, loops, arrays, functions, forms, sessions, and databases.
Episode 13 starts the final Core PHP section: Object Oriented PHP. This is where we learn classes, objects, properties, methods, constructors, inheritance, and access modifiers. OOP is absolutely essential before starting Laravel — because Laravel is built entirely on object oriented principles. Every controller, model, and middleware in Laravel is a class.
See you in the next one.
Next Episode: Object Oriented PHP — Classes, Objects, and the Foundation of Laravel
This is Episode 12 of the PHP and Laravel — Zero to Hero series.
No comments:
Post a Comment