Delete MySQLi record without showing the id in the

Question

I'm trying to delete records from the DB by their ID. The code works as it should but the problem is the ID is coming up in the URL, which from my knowledge it's unsafe.

URL: "http://localhost/Project/includes/delete.php?id=27"

I have used prepared statement to delete the records but the problem comes from the button. IS there any other approach to the one I'm already using to make it safe?

Here is the code:

    while($row = $result->fetch_assoc()) {
        echo '<tr>';
            echo '<td>' . $row['row1'] . '</td>';
            echo '<td>' . $row['row2'] . '</td>';
            echo '<td>' . $row['row3'] . '</td>';
            echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
        echo '</tr>';
    }

if (isset($_GET['id']) && is_numeric($_GET['id'])) {
    $id = $_GET['id'];

    if ($stmt = $mysqli->prepare("DELETE FROM table WHERE id = ? LIMIT 1")) {
        $stmt->bind_param("i", $id);
        $stmt->execute();
        printf("Affected rows (DELETE): %d\n", $mysqli->affected_rows);
        $stmt->close();
    }
}

Basically I'd like to the make the whole row clickable, whenever the user clicks on the row to delete the records, rather than using the buttons. However, my big challenge is how not to show its ID in the URL.

Any help is highly appreciated.

Thank you

Answer 1

To do this you could place a form in the last table cell:

echo '<td><form action="delete.php" method="post">';
echo '<input type="hidden" name="id" value="' . $row['id'] . '/>';
echo '<input type="submit" value="Delete" />';
echo '</form></td>';

The form uses a hidden input for your id value.

In your PHP you would then change to the $_POST array, $_POST['id']

Answer 2

Set the Id in $_SESSION and you can pass it between pages.

OR

You can do it with ajax

while($row = $result->fetch_assoc()) {
    echo '<tr class=\"deleted\">';
        echo '<td>' . $row['row1'] . '</td>';
        echo '<td>' . $row['row2'] . '</td>';
        echo '<td>' . $row['row3'] . '</td>';
        echo '<td><a href="#" id="'. $row['id'] .'" class=\"delete\">Delete</a></td>';
    echo '</tr>';
}

Add this script in page where above code is located ;

<script type="text/javascript">
$(function() {
    $(".delete").click(function(){
    var element = $(this);
    var del_id = element.attr("id");
    var info = 'id=' + del_id;
    if(confirm("Are you sure you want to delete this?")){
     $.ajax({
       type: "POST",
       url: "delete.php",
       data: info,
       success: function(){
     }
    });
      $(this).parents(".deleted").hide(500);
     }
        return false;
    });
});
</script>

create a delete.php in root and it will be like

<?php
//Add your database connection first here
if (isset($_POST['id']) && is_numeric($_POST['id'])) {
    $id = $_POST['id'];

    if ($stmt = $mysqli->prepare("DELETE FROM table WHERE id = ? LIMIT 1")) {
        $stmt->bind_param("i", $id);
        $stmt->execute();
        //Set if condition here to check and show response
        if ($stmt) {
            echo "<font color='red'>Record Deleted Successful</font>";
            } else {
            echo "<font color='red'>Error While Trying To Delete Record, Please Try Again</font>";
            }
        //printf("Affected rows (DELETE): %d\n", $mysqli->affected_rows);
        $stmt->close();
    }
}
?>

With Ajax, your page won't be refreshed and data will be deleted from database and deleted row will be hidden from user

Answer 3

Encode the ID with a two-way encryption, so that it is 'sent' encoded, and decoded to use.