Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Nov 2007
    Posts
    61
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Deleting data from database depending on 'id'

    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

    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

    PHP Code:
    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

  • #2
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    1- Can you connect to the DB? If you keep the connection details outside the function, your function will fail to connect.

    2-
    Code:
    <a href='javascript:void()' onlick='<?php echo deleteComment() ?>'>delete comment</a>
    This is your source. Why do you use JS?

    Try:

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

    delete_comment.php:

    PHP Code:
    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?

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,026
    Thanks
    2
    Thanked 315 Times in 307 Posts
    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.
    Last edited by CFMaBiSmAd; 11-15-2007 at 03:01 AM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #4
    New Coder
    Join Date
    Nov 2007
    Posts
    61
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    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.

    Quote Originally Posted by CFMaBiSmAd
    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.

    Quote Originally Posted by CFMaBiSmAd
    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).

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,026
    Thanks
    2
    Thanked 315 Times in 307 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    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.

  • #7
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,642
    Thanks
    2
    Thanked 405 Times in 397 Posts
    Quote Originally Posted by aedrin View Post
    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.

  • #8
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Or even better, have a special form for people to enter their requests (and don't allow normal topic creation) with required fields.

  • #9
    New Coder
    Join Date
    Nov 2007
    Posts
    61
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by aedrin View Post
    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
    Last edited by helraizer; 11-15-2007 at 07:04 PM.

  • #10
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,642
    Thanks
    2
    Thanked 405 Times in 397 Posts
    Quote Originally Posted by aedrin View Post
    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.

  • #11
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    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

  • #12
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    but we'll bring you security issues tomorrow.
    We'll even ship it to you!

    (Will vs. We'll)

    Sorry, I had to. :P


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •