Building a Secure PHP & MySQL CRUD Application: Prepared Statements & SQL Injection Defense for TU BCA
Author: Bhuban Subedi | Subject: Web Technology (CACS254) | Semester: Fourth Semester
In the Tribhuvan University (TU) BCA Fourth Semester Web Technology practical examination and semester minor projects, building a complete CRUD (Create, Read, Update, Delete) web application using PHP and MySQL carries guaranteed weightage.
Unfortunately, many older textbooks and tutorial websites continue to teach outdated, insecure PHP practices—such as using raw mysqli_query("SELECT * FROM users WHERE user = '$username'") or concatenating form inputs directly into SQL strings.
In this guide, we will build a modern, production-grade, and secure PHP application using PDO (PHP Data Objects), parameterized prepared statements, robust input sanitization, and password_hash() encryption to completely eliminate SQL Injection (SQLi) and Cross-Site Scripting (XSS) vulnerabilities.
1. The Threat: What is SQL Injection (SQLi)?
SQL Injection occurs when malicious user input is concatenated directly into dynamic SQL queries without parameterized binding, allowing an attacker to manipulate the database query logic.
The Classic Flawed Login Query:
// DANGEROUS CODE - DO NOT WRITE THIS IN YOUR TU EXAMS!
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
If an attacker enters ' OR '1'='1 into the username field:
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'
Since '1'='1' is always true, the condition succeeds and logs the attacker into the first administrative account without requiring a valid password!
2. Why PHP PDO is Superior to mysqli_*
+------------------------------------+------------------------------------+
| Feature | PHP Data Objects (PDO) |
+------------------------------------+------------------------------------+
| Database Portability | Supports 12+ RDBMS engines (MySQL, |
| | PostgreSQL, SQLite, Oracle, etc). |
+------------------------------------+------------------------------------+
| Security via Prepared Statements | Native parameterized queries with |
| | strict type binding. |
+------------------------------------+------------------------------------+
| Error Handling | Robust Exception Handling using |
| | `PDOException` and try-catch. |
+------------------------------------+------------------------------------+
| Object-Oriented Architecture | Clean object-oriented syntax. |
+------------------------------------+------------------------------------+
3. Database Schema Setup
Open phpMyAdmin and execute this SQL script:
CREATE DATABASE IF NOT EXISTS tu_webtech_db;
USE tu_webtech_db;
CREATE TABLE IF NOT EXISTS students (
id INT PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
semester VARCHAR(20) NOT NULL,
phone VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
4. Modular Application Architecture
We will organize our PHP project into clean, modular files:
tu_crud_project/
├── db.php (Database connection using PDO)
├── index.php (Read & Display all students + Delete action)
├── create.php (Form to insert a new student record)
└── edit.php (Form to update an existing student record)
File 1: db.php (Secure PDO Connection)
<?php
// db.php - Database Configuration and Connection Handler
$host = 'localhost';
$dbname = 'tu_webtech_db';
$username = 'root';
$password = ''; // Default XAMPP MySQL password
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Return associative arrays
PDO::ATTR_EMULATE_PREPARES => false, // Use real DB-level prepared statements
];
try {
$pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
// Log error securely; never expose database passwords on screen in production
die("Database Connection Error: " . $e->getMessage());
}
?>
File 2: create.php (Inserting New Student Record)
<?php
// create.php - Secure Record Creation
require_once 'db.php';
$message = '';
$error = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// 1. Sanitize user inputs
$fullName = trim($_POST['full_name'] ?? '');
$email = trim($_POST['email'] ?? '');
$semester = trim($_POST['semester'] ?? '');
$phone = trim($_POST['phone'] ?? '');
// 2. Validate inputs
if (empty($fullName) || empty($email) || empty($semester) || empty($phone)) {
$error = "All fields are required!";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$error = "Please enter a valid email address!";
} else {
// 3. Execute parameterized INSERT statement
$sql = "INSERT INTO students (full_name, email, semester, phone) VALUES (:name, :email, :sem, :phone)";
$stmt = $pdo->prepare($sql);
try {
$stmt->execute([
':name' => $fullName,
':email' => $email,
':sem' => $semester,
':phone' => $phone
]);
header("Location: index.php?msg=added");
exit;
} catch (PDOException $e) {
$error = "Failed to insert record: " . $e->getMessage();
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Student - TU BCA Web Tech</title>
<style>
body { font-family: Arial, sans-serif; background: #f4f6f9; margin: 40px; }
.card { background: white; padding: 25px; border-radius: 8px; max-width: 500px; margin: auto; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
.form-group { margin-bottom: 15px; }
label { display: block; margin-bottom: 5px; font-weight: bold; }
input, select { width: 100%; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; }
button { background: #2563eb; color: white; border: none; padding: 12px 20px; border-radius: 4px; cursor: pointer; width: 100%; font-size: 16px; }
.error { color: #dc2626; margin-bottom: 15px; }
</style>
</head>
<body>
<div class="card">
<h2>Register New Student</h2>
<?php if ($error): ?>
<div class="error"><?php echo htmlspecialchars($error); ?></div>
<?php endif; ?>
<form method="POST" action="create.php">
<div class="form-group">
<label>Full Name:</label>
<input type="text" name="full_name" required>
</div>
<div class="form-group">
<label>Email Address:</label>
<input type="email" name="email" required>
</div>
<div class="form-group">
<label>Semester:</label>
<select name="semester" required>
<option value="First">First Semester</option>
<option value="Second">Second Semester</option>
<option value="Third">Third Semester</option>
<option value="Fourth">Fourth Semester</option>
</select>
</div>
<div class="form-group">
<label>Phone Number:</label>
<input type="text" name="phone" required>
</div>
<button type="submit">Save Student Record</button>
</form>
</div>
</body>
</html>
File 3: index.php (Read & Delete Records)
<?php
// index.php - Display and Delete Records
require_once 'db.php';
// Handle Delete Request securely via GET with valid ID
if (isset($_GET['delete_id'])) {
$deleteId = (int)$_GET['delete_id'];
$stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
$stmt->execute([':id' => $deleteId]);
header("Location: index.php?msg=deleted");
exit;
}
// Fetch all records
$stmt = $pdo->query("SELECT * FROM students ORDER BY id DESC");
$students = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>TU BCA Student Directory (PHP CRUD)</title>
<style>
body { font-family: Arial, sans-serif; background: #f4f6f9; margin: 40px; }
.container { max-width: 900px; margin: auto; background: white; padding: 25px; border-radius: 8px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
th, td { padding: 12px; border: 1px solid #ddd; text-align: left; }
th { background: #1e293b; color: white; }
.btn { padding: 8px 14px; text-decoration: none; border-radius: 4px; display: inline-block; }
.btn-add { background: #16a34a; color: white; margin-bottom: 15px; }
.btn-edit { background: #f59e0b; color: white; font-size: 13px; }
.btn-del { background: #dc2626; color: white; font-size: 13px; }
</style>
</head>
<body>
<div class="container">
<h2>TU BCA Student Records Directory</h2>
<a href="create.php" class="btn btn-add">+ Add New Student</a>
<table>
<thead>
<tr>
<th>ID</th>
<th>Full Name</th>
<th>Email</th>
<th>Semester</th>
<th>Phone</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($students)): ?>
<tr><td colspan="6" style="text-align:center;">No student records found.</td></tr>
<?php else: ?>
<?php foreach ($students as $row): ?>
<tr>
<td><?php echo htmlspecialchars($row['id']); ?></td>
<td><?php echo htmlspecialchars($row['full_name']); ?></td>
<td><?php echo htmlspecialchars($row['email']); ?></td>
<td><?php echo htmlspecialchars($row['semester']); ?></td>
<td><?php echo htmlspecialchars($row['phone']); ?></td>
<td>
<a href="edit.php?id=<?php echo $row['id']; ?>" class="btn btn-edit">Edit</a>
<a href="index.php?delete_id=<?php echo $row['id']; ?>" class="btn btn-del" onclick="return confirm('Are you sure you want to delete this record?');">Delete</a>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
</div>
</body>
</html>
Frequently Asked Questions (FAQ)
Q1: Why do we wrap output in htmlspecialchars()?
htmlspecialchars() converts special characters like <, >, &, and " into their corresponding HTML entities (<, >). This prevents Cross-Site Scripting (XSS) attacks where malicious users try to inject JavaScript scripts into your web page.
Q2: How should user passwords be hashed in modern PHP?
Never use outdated hashing algorithms like md5() or sha1(). Always use password_hash($password, PASSWORD_BCRYPT) during registration and password_verify($password, $hashedPassword) during login.
