Can't Delete Entry From MySQL Database - PHP

Question

I'm trying to do a very simple task here, just delete an entry from one of my tables. I'm sure I'm just missing something obvious here but I can't for the life of me work out where.

Can anyone see where I'm going wrong here?

my code here

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

   $sql = "DELETE FROM `collectionsystemdocs` WHERE `collectionsystemdocs`.`name` = $id";

   if (mysqli_query($conn, $sql)) {  

      echo 'Entry Deletion Successful<br>';

   } 

   else {

      echo 'Entry Deletion Unsuccessful<br>';

   } 

}

What exactly happens? A run time error in PHP part? MySQL returns an error? No error, but the record is not deleted? Does the record exist with the ID your are trying to delete? — mentallurg, Jun 02 '18 at 14:44
You should also add some error handling for your queries: http://php.net/manual/en/mysqli.error.php. You should also look into using Prepared Statements. — M. Eriksson, Jun 02 '18 at 14:47
Sorry I should have added that the $id is just being past through an Issey which is first grabbed from the database, so I'm confident the variable isn't the issue here. — Matt Hutch, Jun 02 '18 at 14:52
Looks like its vulnerable to sql injection. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Mathias, Jun 02 '18 at 14:54
_"I'm confident the variable isn't the issue here"_ - We're not though. Have you tried echoing the `$sql` variable to see if it looks correct? Do that and try run the query you get in PHPMyAdmin or some MySQL admin tool and check. What happens? Do you get the success or the error message? — M. Eriksson, Jun 02 '18 at 14:55
@MagnusEriksson I used `echo` to check the `$sql` then used it in PHPMyAdmin and noticed that the name value was missing the `''` so I put the $id in quotes like this `'$id'` and problem solved. Thank you! — Matt Hutch, Jun 02 '18 at 14:59
If you wish to post it as the answer I'll accept it for you. — Matt Hutch, Jun 02 '18 at 15:01
Since I didn't really give you the solution, but rather a way to help you find it, I would recommend that you post an answer yourself, explaining what the issue was and how you solved it. I would also _strongly_ recommend you to use Prepared Statements. If someone calls this script with the id set to something like: `' OR 1=1`, your whole table will be wiped. — M. Eriksson, Jun 02 '18 at 15:04
One thing to check is https://stackoverflow.com/questions/18921088/how-to-check-if-mysqli-query-deleted-any-rows. The fact the query ran doesn't mean that it deleted anything. — Nigel Ren, Jun 02 '18 at 15:15

Philip Thomson · Accepted Answer · 2018-06-02T15:08:11.153

I would reccomend adjusting your code to prevent SQL Injection,

$sql = "DELETE FROM `collectionsystemdocs` WHERE `collectionsystemdocs`.`name`=?";
if($query = $conn->prepare($sql)) {
        $query->bind_param("s", $id);
        $query->execute();
        echo 'Entry Deletion Successful<br>';
} else {
        echo "Entry Deletion Unsuccessful: ". $conn->error;
        }
$query->close();

If you would like some more information, check out: How can I prevent SQL injection in PHP?

Can't Delete Entry From MySQL Database - PHP

1 Answers1