Mysql DELETE query strange behaviour

Question

I have populated an html form with MySQL data from a table.

I have included in that table a form, which if submitted, should delete that row of data from the MySQL table.

This is the code that creates populates the table with the MySQL data from my table.(missed out db connection code and other code I have deemed irrelevant).

 while($row_data=mysql_fetch_array($table_data)){
    echo "<tr>";
    echo "<td>" . $row_data['ID'] . "</td>";
    echo "<td>" . $row_data['Site'] . "</td>";
    echo "<td>" . $row_data['Date'] . "</td>";
    echo "<td>" . $row_data['Target_Site'] . "</td>";
    echo "<td>" . $row_data['Target_Contact_Email'] . "</td>";
    echo "<td>" . $row_data['Target_Contact_Name'] . "</td>";
    echo "<td>" . $row_data['Link_Type'] . "</td>";
    echo "<td>" . $row_data['Link_Acquired'] . "</td>";
    echo "<td>" . $row_data['Notes'] . "</td>";
    echo "<td>" . $row_data['Link_URL'] . "</td>";
    echo "<td></td>";
    echo "<td><form action='delete.php' method='post'><input type='hidden' name='delete_id' value=" .  $row_data['ID'] . "><input type='submit' value='&#x2713;' name='delete' style='background:none;' /></form></td>";
    echo "</tr>";
    }

As you can see in that code, there is a table data on the end, which is a form, and if clicked it is meant to delete that given row. As you can see from the form, the action is delete.php.

This is the code for delete.php (missed out db connection code)

$ID = $_POST['delete_id'];
$Delete = $_POST['delete'];



if(isset($Delete)){ 

mysql_query("DELETE FROM link_building WHERE 'ID'=" . $ID);

header("location:link_building.php?success2=1");
}else{
header("location:link_building.php?fail2=1");
}

Now, it sort of works, but only deletes rows of data that have an ID of 0. Whenever I try to delete a row of data with an ID of 2 for example, it says it succesfully deleted the data, but doesnt actually delete it. But when I click delete on a row with an id of 0 it deletes all the data instead of just that row.

Try building the query in a variable(string) and output it, and comment-out the mysql_query & redirect. And try running this outputted query manually(phpmyadmin), This will probably tell you what is wrong — Stijn_d
– Stijn_d, Commented Mar 9, 2012 at 13:59
@Ryan - where did you read that you have to quote column names? I'm really interested if there's a tutorial or something out there that teaches people to do that. — N.B.
– N.B., Commented Mar 9, 2012 at 14:02
I cant really remember to be honest. Its amazing how such a small thing can make a big difference — RSM
– RSM, Commented Mar 9, 2012 at 14:08
Well, it just shows you it's not a small thing. To experienced user, it means it's a string of data. To inexperienced, it's a small thing, almost of no meaning. I've see quite a few questions these days revolving around the same problem - using single quotes to quote column names instead of ` character so I was wondering whether there was a tutorial teaching people to use single quotes. — N.B.
– N.B., Commented Mar 9, 2012 at 14:10
Tip: use HEREDOCs to make that html blob easier to read and eliminate the repetitive echoes. — Marc B
– Marc B, Commented Mar 9, 2012 at 14:36

Michael Berkowski · Accepted Answer · 2012-03-09 14:02:54Z

Your issue is that you have quoted 'ID' with single quotes. An integer 0 compared to an any string equates to TRUE in MySQL, and the quoted 'ID' is a string literal rather than a column name, hence your deletion occurs when you pass in the ID=0, but fails in every other circumstance.

Remove the quotes from ID:

mysql_query("DELETE FROM link_building WHERE ID=" . $ID);
//------------------------------------------^^^^

Also, your code is vulnerable to SQL injection. Be sure to properly filter the value of $ID.

if (isset($_POST['delete_id']) && !ctype_digit($_POST['delete_id'])) {
   // Non-integer value! error! bail out!
}
else {
   $ID = $_POST['delete_id'];
   // Do your query...
}

Note that the above code differs from your original in that it checks for the presence of $_POST['delete_id'] and its validity before proceeding with the rest of the operation. In your original, you set the values of $ID and $Delete without checking if they exist. It isn't really necessary to check for $Delete since you only have the one other form input.

A final note: We don't see any authentication code in this post, but be sure that if you are accepting SQL deletions from a form input that you check any permissions on the row being deleted before you delete it. Otherwise, any user could modify the form to delete any other user's rows (if this applies to your situation).

Collectives™ on Stack Overflow

Mysql DELETE query strange behaviour

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related