OnlineCBT
1. The SQL Injection Threat
SQL Injection happens when untrusted user input is directly concatenated into a raw SQL query string:
2. Prepared Statements to the Rescue
Prepared statements separate SQL syntax compilation from data values. The query structure is compiled first, and parameters are sent separately as raw data values, making injection technically impossible.
3. Implementing CRUD with PDO
A. Create (INSERT)
prepare("INSERT INTO students (full_name, email, age) VALUES (:name, :email, :age)");
$stmt->execute([
":name" => "Vikram Sen",
":email" => "vikram@example.com",
":age" => 21
]);
$newStudentId = $pdo->lastInsertId();
echo "Inserted student ID: $newStudentId";
B. Read (SELECT)
prepare("SELECT * FROM students WHERE id = :id");
$stmt->execute([":id" => $newStudentId]);
$student = $stmt->fetch();
if ($student) {
echo "Found student: " . htmlspecialchars($student["full_name"]);
}
// Fetching multiple records
$stmt = $pdo->prepare("SELECT * FROM students WHERE age > :minAge ORDER BY full_name ASC");
$stmt->execute([":minAge" => 18]);
$allStudents = $stmt->fetchAll();
foreach ($allStudents as $row) {
echo $row["full_name"] . " (" . $row["email"] . ")
";
}
C. Update (UPDATE)
prepare("UPDATE students SET age = :age WHERE id = :id");
$stmt->execute([
":age" => 22,
":id" => $newStudentId
]);
echo "Rows updated: " . $stmt->rowCount();
D. Delete (DELETE)
prepare("DELETE FROM students WHERE id = :id");
$stmt->execute([":id" => $newStudentId]);
echo "Rows deleted: " . $stmt->rowCount();
