Database CRUD Operations: INSERT, UPDATE & DELETE

Study Deep: Preventing SQL Injection

SQL Injection is one of the most common web vulnerabilities. It occurs when malicious SQL code is inserted into a query via user input.

The Solution: Prepared Statements

1. Prepare: The SQL template is sent to the server.
2. Bind: User input is sent separately as parameters.
3. Execute: The server combines them safely.

This ensures that even if a user enters ' OR '1'='1, it is treated as a literal string, not as part of the SQL command.

1. What is CRUD?

CRUD stands for the four fundamental operations performed on persistent data in any database-driven application:

Every web application (social media, e-commerce, banking) is essentially a series of CRUD operations.

2. INSERT — Adding Records

Full Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

PHP Example — Insert a single record:

<?php
$conn = new mysqli("localhost", "root", "", "CollegeDB");

$name   = "Rohit Kumar";
$email  = "rohit@example.com";
$course = "BCA";
$marks  = 87.5;

$sql = "INSERT INTO students (name, email, course, marks)
        VALUES ('$name', '$email', '$course', $marks)";

if ($conn->query($sql) === TRUE) {
  echo "Record inserted! New ID: " . $conn->insert_id;
} else {
  echo "Error: " . $conn->error;
}
$conn->close();
?>

Note: $conn->insert_id returns the AUTO_INCREMENT ID of the last inserted row.

Insert Multiple Records at Once (Efficient):

$sql = "INSERT INTO students (name, course) VALUES
        ('Priya', 'BCA'),
        ('Amit', 'BCA'),
        ('Neha', 'BCA')";
$conn->query($sql);
echo "3 records inserted!";

3. Prepared Statements — Preventing SQL Injection

Definition: A Prepared Statement is a pre-compiled SQL template where user input is bound as parameters after the query is compiled. This completely prevents SQL Injection attacks.

WITHOUT Prepared Statements (VULNERABLE):

$name = $_POST['name']; // If user enters: ' OR '1'='1
$sql = "SELECT * FROM users WHERE name='$name'"; // SQL INJECTION! All records returned.

WITH Prepared Statements (SAFE):

<?php
$stmt = $conn->prepare("INSERT INTO students (name, email, course) VALUES (?, ?, ?)");
// 's' = string, 'd' = double/int, 'i' = integer
$stmt->bind_param("sss", $name, $email, $course);

$name   = "Rahul";
$email  = "rahul@email.com";
$course = "BCA";
$stmt->execute();

echo "Inserted safely! ID: " . $stmt->insert_id;
$stmt->close();
?>

4. UPDATE — Modifying Records

CRITICAL RULE: Always use WHERE with UPDATE. Without WHERE, every single row will be modified.

Syntax:

UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;

PHP Example — Update a specific student's marks:

<?php
$newMarks  = 92.0;
$studentId = 1;

$sql = "UPDATE students SET marks = $newMarks WHERE id = $studentId";

if ($conn->query($sql) === TRUE) {
  $affected = $conn->affected_rows; // How many rows were changed
  echo "Update successful! $affected row(s) modified.";
} else {
  echo "Error updating: " . $conn->error;
}
?>

Update Multiple Columns at Once:

$sql = "UPDATE students
        SET marks = 95.0, course = 'MCA'
        WHERE id = 2";
$conn->query($sql);

5. DELETE — Removing Records

CRITICAL RULE: Always use WHERE with DELETE. Without WHERE, the ENTIRE TABLE will be emptied.

Syntax:

DELETE FROM table_name WHERE condition;

PHP Example — Delete a specific student:

<?php
$studentId = 3;

$sql = "DELETE FROM students WHERE id = $studentId";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully! Rows affected: " . $conn->affected_rows;
} else {
  echo "Error deleting: " . $conn->error;
}
?>

Soft Delete Pattern (Best Practice): Instead of actually deleting, mark records as inactive. This preserves data history.

// Instead of DELETE, set is_active = 0
$sql = "UPDATE students SET is_active = 0 WHERE id = 3";
$conn->query($sql);
echo "Student deactivated (soft deleted).";

6. Summary Comparison of DML Operations