Safely deleting a db record with php

**Jiri Fogl** · Apr 19 '06, 12:35 PM

Re: Safely deleting a db record with php

You should have more detailed authorization - not only auth'd
non-auth'd, but every user must have its access information, so system
can recognize who is that man who wants to delete.

Then you should specify (for example) owner of a record - add a column
to your table, which contains identifier of user, who is allowed to
delete it (or do any other operation with it).

If you need even more details, you may consider definition of usergroups
(need one extra simple table).

MaXX wrote:[color=blue]
> Hi,
> I hope I'm not OT.
>
> I have the following issue:
> I want to delete a record from my db with a php script. Let's say I'm auth'd
> and I want to delete the record id 440. With a simple form (get or post), I
> send the id to my script and delete the record (DELETE FROM table WHERE
> id=some_validat ed_input).
>
> The problem is if I'm a nasty guy I just write my own form and delete any
> record I want (since I'm auth'd) by just sending another id.
>
> Is there any way to make arbitrary record deletion non-trivial in php? I'm
> thinking about a hash function to replace the real db id (DELETE FROM table
> WHERE record_hash=val idated_form_has h), if possible without adding an
> awfull lot of server side computation.
>
> How do you guys deal with that kind of situation?
>
> Thanks,[/color]

**MaXX** · Apr 19 '06, 01:15 PM

Re: Safely deleting a db record with php

Jiri Fogl wrote:[color=blue]
> You should have more detailed authorization - not only auth'd
> non-auth'd, but every user must have its access information, so system
> can recognize who is that man who wants to delete.[/color]
The problem in my particular case, is that the system can't know who will
delete as there is no explicit ownership. The table in question is a log
and the creator is a script.

Your suggestion can be very usefull for another area of my project...

Another idea is to only allow the php script to set a deleted flag wich only
hide the record and wipe or undelete them by other means ...
[color=blue]
> Then you should specify (for example) owner of a record - add a column
> to your table, which contains identifier of user, who is allowed to
> delete it (or do any other operation with it).
> If you need even more details, you may consider definition of usergroups
> (need one extra simple table).[/color]
The database (postgresql) is already aware of this, the rights are set by
groups (creators INSERT, R-O users SELECT, Admins UPDATE[mark as
read]/DELETE). Some major events have a "protected" boolean to avoid
deletion by the php script. When I want to get rid of those I use PgAdmin
or psql as superuser to delete them.

Time to rethink the system...

Thanks,
--
MaXX

**Good Man** · Apr 19 '06, 01:45 PM

Re: Safely deleting a db record with php

MaXX <bs139412@skyne t.be> wrote in
news:e258nf$pbt $1@talisker.lac ave.net:

[color=blue]
> The problem is if I'm a nasty guy I just write my own form and delete
> any record I want (since I'm auth'd) by just sending another id.[/color]

in your database, add a column called "keystring" and index it. populate
it with 18 characters or so (write a PHP function that does this at the
same time you enter the info in the database). So, this 'keystring' for
record 1 might be '9jfhdsufs8ywre ' while record 2 might be
'agsadgiwqegiqw '.

Since the keystring is indexed, you can delete it from your DB by calling
"DELETE FROM so_and_so WHERE Keystring='9jfh dsufs8ywre'" Chances are
pretty damn slim that someone will be able to guess any keystring and
therefore alter records.

I use this technique often, especially when allowing users access to pick
up files.

See ya

**David Haynes** · Apr 19 '06, 01:45 PM

Re: Safely deleting a db record with php

MaXX wrote:[color=blue]
> Hi,
> I hope I'm not OT.
>
> I have the following issue:
> I want to delete a record from my db with a php script. Let's say I'm auth'd
> and I want to delete the record id 440. With a simple form (get or post), I
> send the id to my script and delete the record (DELETE FROM table WHERE
> id=some_validat ed_input).
>
> The problem is if I'm a nasty guy I just write my own form and delete any
> record I want (since I'm auth'd) by just sending another id.
>
> Is there any way to make arbitrary record deletion non-trivial in php? I'm
> thinking about a hash function to replace the real db id (DELETE FROM table
> WHERE record_hash=val idated_form_has h), if possible without adding an
> awfull lot of server side computation.
>
> How do you guys deal with that kind of situation?
>
> Thanks,[/color]

Along with the other suggestions:

Make deleted an attribute (column) of the table and then access the data
via a view that filters deleted items. If a record is deleted by
accident, it can still be re-created by changing the deleted attribute.
Some other process may come along and remove the deleted rows at some
regulated time (e.g. after a backup, after so many days, etc.)

-david-

**MaXX** · Apr 19 '06, 03:05 PM

Re: Safely deleting a db record with php

Good Man wrote:[color=blue]
> MaXX <bs139412@skyne t.be> wrote in
> news:e258nf$pbt $1@talisker.lac ave.net:[color=green]
>> The problem is if I'm a nasty guy I just write my own form and delete
>> any record I want (since I'm auth'd) by just sending another id.[/color]
> in your database, add a column called "keystring" and index it. populate
> it with 18 characters or so (write a PHP function that does this at the
> same time you enter the info in the database). So, this 'keystring' for
> record 1 might be '9jfhdsufs8ywre ' while record 2 might be
> 'agsadgiwqegiqw '.[/color]
It's the idea I have, but I need a to find a way to do this with an
absolutly unique "keystring" (md5*/sha1??) to avoid duplicate (may be
extremely rare, but this is the kind of bug you don't want to hunt one
day ;-) ...)

The uniqueness is not realy important in this project but things can
change...
[color=blue]
> Since the keystring is indexed, you can delete it from your DB by calling
> "DELETE FROM so_and_so WHERE Keystring='9jfh dsufs8ywre'" Chances are
> pretty damn slim that someone will be able to guess any keystring and
> therefore alter records.[/color]
[...]
[*] In my knowledge collisions can exist with md5 but avoiding md5 collision
is a WMD vs fly in that case...

Thanks,
--
MaXX

**MaXX** · Apr 19 '06, 03:05 PM

Re: Safely deleting a db record with php

David Haynes wrote:[color=blue]
> MaXX wrote:[/color]
[...][color=blue][color=green]
>> How do you guys deal with that kind of situation?
>> Thanks,[/color]
> Along with the other suggestions:
> Make deleted an attribute (column) of the table and then access the data
> via a view that filters deleted items. If a record is deleted by
> accident, it can still be re-created by changing the deleted attribute.
> Some other process may come along and remove the deleted rows at some
> regulated time (e.g. after a backup, after so many days, etc.)[/color]
Thanks for the suggestion, I keep that in mind.

--
MaXX

**Good Man** · Apr 19 '06, 03:15 PM

Re: Safely deleting a db record with php

MaXX <bs139412@skyne t.be> wrote in
news:e25ivo$17v f$1@talisker.la cave.net:
[color=blue]
> Good Man wrote:[color=green]
>> MaXX <bs139412@skyne t.be> wrote in
>> news:e258nf$pbt $1@talisker.lac ave.net:[color=darkred]
>>> The problem is if I'm a nasty guy I just write my own form and
>>> delete any record I want (since I'm auth'd) by just sending another
>>> id.[/color]
>> in your database, add a column called "keystring" and index it.
>> populate it with 18 characters or so (write a PHP function that does
>> this at the same time you enter the info in the database). So, this
>> 'keystring' for record 1 might be '9jfhdsufs8ywre ' while record 2
>> might be 'agsadgiwqegiqw '.[/color]
> It's the idea I have, but I need a to find a way to do this with an
> absolutly unique "keystring" (md5*/sha1??) to avoid duplicate (may be
> extremely rare, but this is the kind of bug you don't want to hunt one
> day ;-) ...)[/color]

to make a unique keystring, you could always md5 the current unix
timestamp.

if you're concerned about duplicates, load up the keystrings from the
database into an array and see if your newly generated one has any
duplicates with in_array()

**Oli Filth** · Apr 19 '06, 03:15 PM

Re: Safely deleting a db record with php

MaXX said the following on 19/04/2006 15:54:[color=blue]
> Good Man wrote:[color=green]
>> MaXX <bs139412@skyne t.be> wrote in
>> news:e258nf$pbt $1@talisker.lac ave.net:[color=darkred]
>>> The problem is if I'm a nasty guy I just write my own form and delete
>>> any record I want (since I'm auth'd) by just sending another id.[/color]
>> in your database, add a column called "keystring" and index it. populate
>> it with 18 characters or so (write a PHP function that does this at the
>> same time you enter the info in the database). So, this 'keystring' for
>> record 1 might be '9jfhdsufs8ywre ' while record 2 might be
>> 'agsadgiwqegiqw '.[/color]
> It's the idea I have, but I need a to find a way to do this with an
> absolutly unique "keystring" (md5*/sha1??) to avoid duplicate (may be
> extremely rare, but this is the kind of bug you don't want to hunt one
> day ;-) ...)
>[/color]

You could define the keystring column as a unique index. If on your
first insert you get back an error (implying a duplicate), then you can
just modify the keystring and insert again. Repeat until success!

Of course, if this is the method you go for, then using some sort of
hash is redundant; you might as well just generate random integers or
strings of a suitable length.

--
Oli

**Oli Filth** · Apr 19 '06, 03:25 PM

Re: Safely deleting a db record with php

Oli Filth said the following on 19/04/2006 16:01:[color=blue]
> MaXX said the following on 19/04/2006 15:54:[color=green]
>> Good Man wrote:[color=darkred]
>>> MaXX <bs139412@skyne t.be> wrote in
>>> news:e258nf$pbt $1@talisker.lac ave.net:
>>>> The problem is if I'm a nasty guy I just write my own form and delete
>>>> any record I want (since I'm auth'd) by just sending another id.
>>> in your database, add a column called "keystring" and index it.
>>> populate
>>> it with 18 characters or so (write a PHP function that does this at the
>>> same time you enter the info in the database). So, this 'keystring' for
>>> record 1 might be '9jfhdsufs8ywre ' while record 2 might be
>>> 'agsadgiwqegiqw '.[/color]
>> It's the idea I have, but I need a to find a way to do this with an
>> absolutly unique "keystring" (md5*/sha1??) to avoid duplicate (may be
>> extremely rare, but this is the kind of bug you don't want to hunt one
>> day ;-) ...)
>>[/color]
>
> You could define the keystring column as a unique index. If on your
> first insert you get back an error (implying a duplicate), then you can
> just modify the keystring and insert again. Repeat until success!
>
> Of course, if this is the method you go for, then using some sort of
> hash is redundant; you might as well just generate random integers or
> strings of a suitable length.
>[/color]

Integers are probably better, because it will take less work for the DB
to index them.

--
Oli

**MaXX** · Apr 19 '06, 03:25 PM

Re: Safely deleting a db record with php

Good Man wrote:
[color=blue]
> MaXX <bs139412@skyne t.be> wrote in
> news:e25ivo$17v f$1@talisker.la cave.net:
>[color=green]
>> Good Man wrote:[color=darkred]
>>> MaXX <bs139412@skyne t.be> wrote in
>>> news:e258nf$pbt $1@talisker.lac ave.net:
>>>> The problem is if I'm a nasty guy I just write my own form and
>>>> delete any record I want (since I'm auth'd) by just sending another
>>>> id.
>>> in your database, add a column called "keystring" and index it.
>>> populate it with 18 characters or so (write a PHP function that does
>>> this at the same time you enter the info in the database). So, this
>>> 'keystring' for record 1 might be '9jfhdsufs8ywre ' while record 2
>>> might be 'agsadgiwqegiqw '.[/color]
>> It's the idea I have, but I need a to find a way to do this with an
>> absolutly unique "keystring" (md5*/sha1??) to avoid duplicate (may be
>> extremely rare, but this is the kind of bug you don't want to hunt one
>> day ;-) ...)[/color]
> to make a unique keystring, you could always md5 the current unix
> timestamp.[/color]
to be sure I've md5'd a concat of the timestamp (2005-11-12
19:11:14.043195 +01) and the message and it seem to work at least with a few
hundreds of rows and I don't see how I can get any duplicates. Even if that
(unique constraint violation) the message will be logged again but a
slightly different timestamp...

Now I have a 32 chars unique identifier for each row, a bit too long but it
is doing the job fine.

[...]

Thanks again,
--
MaXX

**Gordon Burditt** · Apr 19 '06, 04:15 PM

Re: Safely deleting a db record with php

>I want to delete a record from my db with a php script. Let's say I'm auth'd[color=blue]
>and I want to delete the record id 440. With a simple form (get or post), I
>send the id to my script and delete the record (DELETE FROM table WHERE
>id=some_valida ted_input).
>
>The problem is if I'm a nasty guy I just write my own form and delete any
>record I want (since I'm auth'd) by just sending another id.[/color]

Validate they have the authority to delete the record they want
to delete *AT THE TIME OF THE SUBMIT*.
[color=blue]
>Is there any way to make arbitrary record deletion non-trivial in php? I'm
>thinking about a hash function to replace the real db id (DELETE FROM table
>WHERE record_hash=val idated_form_has h),[/color]

You still need to validate that they have the authority to delete the
record *AT THE TIME OF THE SUBMIT*. The owner of the record may have
changed. The person deleting the record may have had the authority
to delete the record yesterday, but he was fired today, or his membership
expired.
[color=blue]
>if possible without adding an
>awfull lot of server side computation.[/color]

Add a lot of server-side computation. You had to decide if he had the
authority to delete the record when the form was sent to the user. It can't
be that hard.

Gordon L. Burditt

**Peter Fox** · Apr 19 '06, 04:45 PM

Re: Safely deleting a db record with php

Following on from MaXX's message. . .

I use a number of approaches
(1) Instead of auto-incrementing ID use a 32 bit random number.
(Obviously you have the creation overhead of making sure you can't
retrieve the record before creating it to catch. the theoretical 1 in
24billion chance of a clash)

This DOESNT solve your problem if any other IDs are available for
inspection. eg a selection table with click on button to delete
functionality because the other IDs can be harvested. AND WORSE if you
use this ID anywhere at all eg a table of click on button to _edit_
functionality the same thing applies.

However it WILL work for customer accounts where individual customers
never get to see a list of other customers. They can't then think let's
change ".../custdetails.php ?custid=42" to "....?custid=43 "

(2) Keep track of page visiting history in the session and boot out
people coming back via bookmarks without going through the right path.
Here's the outline:
In page 1
$_SESSION['LastPage']='Page1';
At top of page 2 :
if($_SESSION['LastPage']!='Page1'){...

(3) Wrap up each of your actions for each page in a handy object or
array and pop them into the session keyed by a random number. Use this
in your links
<a href="screenser ver.php?A=23425 42542">Delete record 1</a>
<a href="screenser ver.php?A=71452 726">Delete record 2</a>
Then in screenserver.ph p you do (in outline)
$a = $_GET[A];
$action = $_SESSION[actions[$a]];
$action = $_SESSION[actions] = array();
if($action->do=='del'}{... .
if($action->do=='edit'}{.. ..
$id = $action->id;

(4) If you want persistent links, for example you send a contact an
email with a "visit http:// ...?A=123435245 " in it then do something
similar in a database table.

**** PS ****
I'm not doing much PHP at the minute. I've had the objects for (3) and
(4) working but never bothered to finish them off for public
consumption. If there's anyone who wants to finish them off then you
can probably work out the real email address if you feel like dropping
me a line.

--
PETER FOX Not the same since the cardboard box company folded
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

**Gordon Burditt** · Apr 19 '06, 05:45 PM

Re: Safely deleting a db record with php

>I use a number of approaches[color=blue]
>(1) Instead of auto-incrementing ID use a 32 bit random number.
>(Obviously you have the creation overhead of making sure you can't
>retrieve the record before creating it to catch. the theoretical 1 in
>24billion chance of a clash)
>
>This DOESNT solve your problem if any other IDs are available for
>inspection. eg a selection table with click on button to delete
>functionalit y because the other IDs can be harvested. AND WORSE if you
>use this ID anywhere at all eg a table of click on button to _edit_
>functionalit y the same thing applies.[/color]

All of this is a very poor substitute for validating that the user
in question has the authority to delete the record *AT THE TIME OF
THE FORM SUBMISSION*. If the user with administrator authority
always has the authority to delete *any* record, and a user without
administrator authority cannot delete any record (even his own),
there's nothing wrong with just using trivially-guessable record
numbers. But you need to re-check his administrator status at the
time of the form submission. He might have been fired between the
form being sent (and possibly cached in a browser for a year) and
submitting it.

If the user can only delete *his own* records, then check, when he
submits the form, that he still has the authority to delete it: he
still owns it, his membership hasn't expired, he's still logged in
as the same user, etc.
[color=blue]
>However it WILL work for customer accounts where individual customers
>never get to see a list of other customers. They can't then think let's
>change ".../custdetails.php ?custid=42" to "....?custid=43 "[/color]

Someone malicious can still try running through all the numbers.
[color=blue]
>(2) Keep track of page visiting history in the session and boot out
>people coming back via bookmarks without going through the right path.
>Here's the outline:
> In page 1
> $_SESSION['LastPage']='Page1';
> At top of page 2 :
> if($_SESSION['LastPage']!='Page1'){...[/color]

Unless you store which records the last page offered him the chance
to delete, and that it would still offer him the chance to delete
the record that he is trying to delete, this check is ineffective
and spoofable.

Gordon L. Burditt

**Chung Leong** · Apr 19 '06, 05:55 PM

Re: Safely deleting a db record with php

MaXX wrote:[color=blue]
> Jiri Fogl wrote:[color=green]
> > You should have more detailed authorization - not only auth'd
> > non-auth'd, but every user must have its access information, so system
> > can recognize who is that man who wants to delete.[/color]
> The problem in my particular case, is that the system can't know who will
> delete as there is no explicit ownership. The table in question is a log
> and the creator is a script.
>
> Your suggestion can be very usefull for another area of my project...
>
> Another idea is to only allow the php script to set a deleted flag wich only
> hide the record and wipe or undelete them by other means ...[/color]

The question isn't so much about ownership but authorization. If
deleting arbituary records in the table is not supposed to happen, then
logically it follows that there is a rule governing which records can
be deleted.

Safely deleting a db record with php

Safely deleting a db record with php

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment