Safely deleting a db record with php

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_validated_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=validated_form_hash), if possible without adding an
awfull lot of server side computation.

How do you guys deal with that kind of situation?

Thanks,
--
MaXX

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:
> 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_validated_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=validated_form_hash), if possible without adding an
> awfull lot of server side computation.
>
> How do you guys deal with that kind of situation?
>
> Thanks,

Jiri Fogl wrote:
> 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.
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 ...

> 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).
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

MaXX <bs139412@skynet.be> wrote in
news:e258nf$pbt$1@talisker.lacave.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'.

Since the keystring is indexed, you can delete it from your DB by calling
"DELETE FROM so_and_so WHERE Keystring='9jfhdsufs8ywre'" 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

MaXX wrote:
> 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_validated_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=validated_form_hash), if possible without adding an
> awfull lot of server side computation.
>
> How do you guys deal with that kind of situation?
>
> Thanks,

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-

Good Man wrote:
> MaXX <bs139412@skynet.be> wrote in
> news:e258nf$pbt$1@talisker.lacave.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'.
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...

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

Thanks,
--
MaXX

David Haynes wrote:
> MaXX wrote:
[...]
>> How do you guys deal with that kind of situation?
>> Thanks,
> 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.)
Thanks for the suggestion, I keep that in mind.

--
MaXX

MaXX <bs139412@skynet.be> wrote in
news:e25ivo$17vf$1@talisker.lacave.net:

> Good Man wrote:
>> MaXX <bs139412@skynet.be> wrote in
>> news:e258nf$pbt$1@talisker.lacave.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'.
> 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 ;-) ...)

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()

MaXX said the following on 19/04/2006 15:54:
> Good Man wrote:
>> MaXX <bs139412@skynet.be> wrote in
>> news:e258nf$pbt$1@talisker.lacave.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'.
> 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 ;-) ...)
>

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 said the following on 19/04/2006 16:01:
> MaXX said the following on 19/04/2006 15:54:
>> Good Man wrote:
>>> MaXX <bs139412@skynet.be> wrote in
>>> news:e258nf$pbt$1@talisker.lacave.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'.
>> 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 ;-) ...)
>>
>
> 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.
>

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



--
Oli