...

View Full Version : Deleting data from database depending on 'id'



helraizer
11-15-2007, 02:56 AM
Hi all, I am making a comments page for my website in php and mysql.

I have a mysql database for this (obviously), which has an auto-increment 'id' field. So everytime someone posts a comment, it creates an id for said comment.

it then posts the: post id, comment, username (if entered) , email address (if entered) and date to a div in which the comment sits.

test version (http://www.helraizer.co.uk/count/test1.php)

The link shows you more what I mean. It also saves your IP address to the database. If your ip address ($_SERVER['REMOTE_ADDR']) then matches the IP pulled from the database for any of the comments it allows you to delete your post (or at least gives you the option to)

Now...Here's the predicament...

I'm not entirely sure how I'd delete the database entry based on that id, in the terms of php.

I've tried


function deleteComment()
{

$sql3 = "SELECT * FROM `Comment`";

$result = mysql_query($sql3) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {


$sql2 = "DELETE * FROM `Comment` WHERE `Comment`.`id` =" . $row['id'] .
"LIMIT 1;";

mysql_query($sql2) or die(mysql_error());

}
}


That doesn't appear to work. Does anyone have any ideas?

Thanks,

Sam

guvenck
11-15-2007, 03:21 AM
1- Can you connect to the DB? If you keep the connection details outside the function, your function will fail to connect.

2-


<a href='javascript:void()' onlick='<?php echo deleteComment() ?>'>delete comment</a>


This is your source. Why do you use JS?

Try:



<a href="delete_comment.php?comment_id=<?php echo $row['id']; ?>">delete comment</a>


and

delete_comment.php:



if(isset($_GET['comment_id']) && is_numeric($_GET['comment_id']))
{

$comment_id = $_GET['comment_id'];
$my_ip = $_SERVER['REMOTE_ADDR'];

// connect to DB
$result = mysql_query("DELETE FROM Comments WHERE id='$comment_id' AND ip='$my_ip'") or die(mysql_error());

if(mysql_affected_rows == 1)
{
// comment deleted
}
else
{
// delete comment failed
}


}


What if the visitor comes back with a different IP address?

CFMaBiSmAd
11-15-2007, 03:43 AM
Using the IP address to identify a visitor for the purpose of allowing them to delete content is not a workable way of doing this.

Most of the people on the planet connect to the Internet using a dynamically assigned IP address. Dial up connections receive a new IP address for each connection and a cable/DSL connection will receive a new IP address whenever the modem/router is turned off and on or is otherwise reset. About the only people that connect to the Internet with a static IP address are those connecting from a company that has a dedicated Internet connection and even in this situation, every person connecting to the Internet using that connection will have the same IP address.

So, you cannot guarantee that the same visitor will have the same IP address for any two visits and because IP addresses are recycled, some random person on the same network can receive the same IP address that someone else just had, or you could have 10, 100, or a 1000+ people within a company that would all have the same IP address.

To identify someone for the purpose of deleting content, you need to use a register/login system with user names and passwords. Your current scheme of displaying a username and email address cannot be used because everyone can see that information in the posts.

Also, just deleting an entry using an ID number would allow someone to sequentially post a range of numbers 1 to 9999+ and delete all your content. You must have a registration/login system to limit access and to authenticate the visitor.

You could also generate a unique id for a visitor and save it in a cookie and in your database with each post. As long as a visitor accepts and keeps their cookie with the unique id, their computer would be identified and they could delete posts made by that computer. This method by itself suffers from an access problem. Any person with access to that computer looks like that visitor, which is where a login system with a user name and a password comes in again.

helraizer
11-15-2007, 11:33 AM
Using the IP address to identify a visitor for the purpose of allowing them to delete content is not a workable way of doing this.

Most of the people on the planet connect to the Internet using a dynamically assigned IP address. Dial up connections receive a new IP address for each connection and a cable/DSL connection will receive a new IP address whenever the modem/router is turned off and on or is otherwise reset. About the only people that connect to the Internet with a static IP address are those connecting from a company that has a dedicated Internet connection and even in this situation, every person connecting to the Internet using that connection will have the same IP address.

So, you cannot guarantee that the same visitor will have the same IP address for any two visits and because IP addresses are recycled, some random person on the same network can receive the same IP address that someone else just had, or you could have 10, 100, or a 1000+ people within a company that would all have the same IP address.

Yeah, I know. but normally you don't want to delete your comment 3 days later, you'd want to delete it there and then so there is a time frame (until your IP changes) to delete the post.



To identify someone for the purpose of deleting content, you need to use a register/login system with user names and passwords. Your current scheme of displaying a username and email address cannot be used because everyone can see that information in the posts.

I admit, displaying the email address is probably not the best idea for various reasons, so I will change that.



Also, just deleting an entry using an ID number would allow someone to sequentially post a range of numbers 1 to 9999+ and delete all your content. You must have a registration/login system to limit access and to authenticate the visitor.

The database creates the unique ID number for the post id, the user has no control over this number (if I correctly understood what you meant).

CFMaBiSmAd
11-15-2007, 03:20 PM
The database creates the unique ID number for the post id, the user has no control over this number (if I correctly understood what you meant).Just using the ID as the condition to delete, as the thread title says and the code in the first post is doing, would allow anyone to submit a range if ID numbers and delete all your content. Because the id numbers are created sequentially, they will have values that are predictable and cannot be relied upon alone as the condition to delete content.

aedrin
11-15-2007, 05:09 PM
That doesn't appear to work. Does anyone have any ideas?

What doesn't work?

What do you see? (Errors?)

What do you expect?

Do you even have error reporting turned on? (You're debugging after all)

We're going to have to ask for a special button that automatically posts a reply like this, so we don't have to type it all out... If you're asking for help, give information. If you don't, we can't help.

Inigoesdr
11-15-2007, 07:07 PM
We're going to have to ask for a special button that automatically posts a reply like this, so we don't have to type it all out... If you're asking for help, give information. If you don't, we can't help.

You've got my vote. :thumbsup:

aedrin
11-15-2007, 07:21 PM
Or even better, have a special form for people to enter their requests (and don't allow normal topic creation) with required fields.

helraizer
11-15-2007, 07:24 PM
What doesn't work?

What do you see? (Errors?)

What do you expect?

Do you even have error reporting turned on? (You're debugging after all)

We're going to have to ask for a special button that automatically posts a reply like this, so we don't have to type it all out... If you're asking for help, give information. If you don't, we can't help.

I know, sorry.

Thanks to guvenck, you had the right idea. I used your response, adapted it and now it works =D Thank you.

Also with Guvenck's solution you can only edit the post if your IP address matches that of the one in the database. Thus, even if you change the id number in the URL you can't delete that post because your IP won't match.

Sam

Inigoesdr
11-15-2007, 07:26 PM
Or even better, have a special form for people to enter their requests (and don't allow normal topic creation) with required fields.

Yes! I was going to suggest that. :D

guvenck
11-16-2007, 03:34 AM
Sam, I just posted a solution that fixes your problem. However people who commented on your post, are right. This is not the definitive solution. It solves your problem today, but we'll bring you security issues tomorrow.

Think of a company who share the same internet connection and IP. This way, your team mate will have the same IP as yours and can delete your post . Second, if you use a dynamic IP and you use that IP to post, a day after you will have a different IP and you won't be able to delete your post. Worse, your previous IP will be allocated to some other visitor and although the possibility is low, he can delete your post. If you write a program you'll have to think of all these scenarios, and that makes our code much more bigger than it should be under ideal circumstances.




I know, sorry.

Thanks to guvenck, you had the right idea. I used your response, adapted it and now it works =D Thank you.

Also with Guvenck's solution you can only edit the post if your IP address matches that of the one in the database. Thus, even if you change the id number in the URL you can't delete that post because your IP won't match.

Sam

aedrin
11-16-2007, 05:23 PM
but we'll bring you security issues tomorrow.

We'll even ship it to you!

(Will vs. We'll)

Sorry, I had to. :P



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum