What's the safest way to remove data from mysql? (PHP/Mysql)

Question

I want to allow users as well as me(the admin) to delete data in mysql. I used to have remove.php that would get $_GETs from whatever that needed to be deleted such as... remove.php?action=post&posting_id=2. But I learned that anyone can simply abuse it and delete all my data.

So what's the safest way for users and me to delete information without getting all crazy and hard? I am only a beginner :) I'm not sure if I can use POSTs because there is no forms and the data isn't changing. Is sessions good? Or would there be too many with postings, user information, comments, etc.

Ex: James wants to delete one of his postings(it is posting_id=5). So he clicks the remove link and that takes him to remove.php?action=post&posting_id=5.

EDIT: Alright, so now I am a little confused. While I can't be 100% secure, how do I do this with $_POSTs? SOO I should use GETs to get all the data to remove.php, THEN have a confirmation submit button and when users click on it, it put all the data into POSTs and delete from the dbc?

Answer 1

Deleting records is a kind of a scary practice. If you or someone makes a mistake there's no real recourse to resolve the issue. Expunged records are very hard to resurrect.

Instead of deleting records, you could add an "active" bit (e.g. Boolean) column that is toggled off when users "delete" records. Essentially your users would be suspending records by toggling them off and the records would be saved in case mistakes or abuse but appear "deleted" to the user. To make this work with your other queries, just add a where clause of active = 1.

You could then have a utility script that's run at some specific date interval that would clean out deprecated, past dated records. You'd also need some type of timestamp for this type of maintenance.

Just a thought. Take if for what it's worth.

Answer 2

Well you have to start by authenticating the users with a login script.

If you want the simplest solution possible, then I'd suggest protecting the directory in which you have remove.php with a simple .htaccess username and password.

If different users have different rights for deleting database entries, then you probably should create a PHP login script and use PHP session.

Bonk me if I'm stupid, but I searched for quite some time for a simple PHP login tutorial that could be placed on a real site (doesn't use session_register(), uses mysql_real_escape_string(), htmlspecialchars() etc) and I simply couldn't find one!

Probably this one comes the closest, you just have to replace session_register() variables with $_SESSION ones for it to work without register_globals (default in PHP5).

Answer 3

I'll echo gurun8 in preferring to 'mark' records as deleted, instead of actually removing data. And then obviously, you'll need to check that the authenticated user has permission to delete the post.

However, it seems very important to mention that $_GET is not safe even with authentication because of cross-site request forgery.

Imagine if Amazon adding things to your cart based on a GET request. All I'd have to do is put an image on my page with that URL, and everyone who visited that page and logged into Amazon will have products added automatically.

To match your example, I don't like Jame's post, so i put an image on my site like this:

<img src='http://example.com/remove.php?action=post&amp;posting_id=5'&gt;

And I send him a link to my page, and ask him to check it out, hoping that at the time he's logged in to your site. Because, of course, he clicked that little 'keep me logged in' button.

So you are right to be concerned about using GET. If you don't want to litter pages with forms, then confirm the action by POST.