How to Safely Delete Records from the Database Using PDOStatement Based on Conditions? A Guide to Get You Started

M66 2025-06-22

1. Why Use PDO and Prepared Statements

When constructing SQL DELETE statements, many developers tend to concatenate strings directly, like this:

$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = $id";

This method is highly vulnerable to SQL injection attacks. For example, if a malicious user provides 1 OR 1=1, it will delete all records from the table.

Using PDO with prepared statements completely avoids this issue. By separating SQL from the data, it ensures that input data is not treated as part of the SQL query.

2. Basic Example of Deleting Records with PDO

Here’s a safe way to delete a user record using PDO:

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'root';
$password = 'your_password';
<p>try {<br>
$pdo = new PDO($dsn, $username, $password);<br>
// Set error mode to exception<br>
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);</p>
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");

// Bind the parameter and execute
$id = 5; // For example, the user ID from a form or URL
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

$stmt->execute();

if ($stmt->rowCount()) {
    echo "Record successfully deleted.";
} else {
    echo "No matching records found.";
}

} catch (PDOException $e) {
echo "Database error: " . $e->getMessage();
}
?>

In this example, we used the :id placeholder and bound the variable using bindParam to ensure consistent data types.

3. Deleting Records Based on Multiple Conditions

Sometimes, the deletion condition involves more than one factor, such as deleting login records for a user from a specific time:

<?php
$stmt = $pdo->prepare("DELETE FROM login_logs WHERE user_id = :user_id AND login_time < :before_time");
<p>$user_id = 3;<br>
$before_time = '2024-01-01 00:00:00';</p>
<p>$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);<br>
$stmt->bindParam(':before_time', $before_time);</p>
<p>$stmt->execute();<br>

4. Handling Dynamic Conditions

If the deletion conditions are generated dynamically, such as for bulk deletions from checkboxes in the frontend, the query can be constructed like this:

<?php
$ids = [2, 5, 7]; // List of IDs from a form or frontend
<p>// Construct the SQL with placeholders<br>
$placeholders = implode(',', array_fill(0, count($ids), '?'));<br>
$sql = "DELETE FROM users WHERE id IN ($placeholders)";<br>
$stmt = $pdo->prepare($sql);</p>
<p>// Execute the statement and bind all parameters<br>
$stmt->execute($ids);<br>

This method uses the ? placeholder and the execute method with an array, which is both secure and flexible.

5. Security Recommendations

Never concatenate user input directly into SQL queries.
When using parameter binding, specifying the data type (e.g., PDO::PARAM_INT) enhances both security and performance.
Before deletion, you can first perform a SELECT check to confirm the existence of the record.
Use transaction handling (beginTransaction() / commit()) to avoid accidental deletions.

6. Deleting Records via API (RESTful API Example)

If you want to delete a record based on an HTTP request, such as through the API https://m66.net/api/delete_user.php?id=9, you can write the following:

<?php
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
    $id = (int) $_GET['id'];
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

echo json_encode([
    'success' => true,
    'message' => 'User deleted successfully',
]);

} else {
http_response_code(400);
echo json_encode([
'success' => false,
'message' => 'Invalid request',
]);
}
?>

API design must also focus on input validation and parameter binding to prevent attacks through the API.

Related Tags:
PDOStatement

PDOStatement