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

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&posting_id=5'>

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.