How to update a database row from a form?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Though I program in php for some years, I started thinking a lot about  
the security in my php scripts.

When I add/insert/delete to a form I usually have a hidden input box  
with the (auto-increment id) of the record I want to update. So when I  
UPDATE I put where id=intval($id).
This is quite a security risk but these forms are administration forms,  
assuming that the administrator won't edit hidden values and mess with  
it's data.

But what happens if this is a public form? How can you avoid having the  
id in a hidden (and easily changed) form field? What techniques do  
exist? I have some in mind as a workaround (like puting id in $_SESSION)  
but I would like to hear other opinions.


Re: How to update a database row from a form?

Harris Kosmidhs wrote:
Quoted text here. Click to load it

Never send critical information like this to the browser in a hidden  
field.  Rather, put it in the session.

Alternatively, you could encrypt it before sending it to the browser,  
then decrypt it when it comes back.  But that's less safe.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: How to update a database row from a form?

Harris Kosmidhs wrote:
Quoted text here. Click to load it
well I dont see any real way around this. There isn't any mechanism that  
couldn't be busted. The question is why anyone would want to.

Assuming they do, you could validate the data so that if it is an  
update, at least *some* of it matches what it was...

Theres a few other things you might do, like storing in the database a  
field that is 'last IP address that updated this' and at least flagging  
an update from a different IP address..

But there might be many reasons why that would not be valid in perfectly  
innocent cases.

The tendency with all databases is to avoid the updates altogether. Its  
more usual to cancel a transaction and start a new one, than modify teh  
existing data.

I have a more or less general rule.

Only the system admin under extreme duress ever deletes data.

Only the internal admins ever update it.

'Users' only add to it.

I.e. take a shopping cart. The users creates a new 'cart'

Then s/he creates order lines to add to that cart.

Then he closes the transaction, or abandons it. Thats is the OINLY  
update to te master transaction record he can do. Typically HE dooesn't  
do it either. Paypal etc will be giving the thumbs up to it.

Even if he modifies his numbers, you can do that by cancelling the  
original orderlines and adding new ones. So the ONLY data update that  
can be done by the user is to set a cancellation flag. Every other  
amendment to e.g,. an order, is in fact a additional orderline  
associated with it, or cancellation of an existing one. ONLY permitted  
on 'open' transactions.

So there is a full audit trail. If he tries to add to someone else's  
transaction, it won't work unless its 'open' and that means that someone  
has to pay to close it, and they will probably notices if a million  
quids worth of gold bars have been added to it. ;-)

This isn't about php or web or anything per se, its about a durable  
approach to database design. You simply do not allow a low grade class  
of users (data entry really) to do anything more than they have to via  
the forms they get provided with. And even though it means you have a  
load of redundant data on your disks, you will bless it if you ever need  
to unpick an audit trail back to find out where a million gold bars went  

The prisons are full of people who were given more tools than they  
needed to do their jobs..and discovered what else they would do.

Don't give them the tools, because you were too lazy to code round the  
need to.

You may decide that e.g. you will allow users to change their names and  
addresses and passwords online. Fair enough. But thats ALL they should  
be allowed to do. With a special form. And if that has implications of  
malice or fraud that may result, I strongly suggest you create a NEW  
customer record and flag the old as 'invalid' in case the original user  
comes back and wants to know WTF is going on.

This implies that the user cannot magically update data that he hasn't  
got acess to.

I.e. too simply load a whole record into hidden variables is bollocks.  
You load the ID and what he is allowed to update, alone. If you are  
really paaraanoid, nd your form needs to cter for higher lecvel ccess on  
  conditionl basis, you can read the record data out, and use that to  
reinsert with except in the specific cases that user is allowed to have  
access to. That is an implementation detail.

You COULD even use SQL usernames to have granular access to parts of te  
data base. I do;t like that personally, as I prefer to write a form for  
a specific single purpose, and let that be my control mechanism.

Re: How to update a database row from a form?

On Jan 28, 10:12 am, Harris Kosmidhs
Quoted text here. Click to load it

You know what someone is allowed to do and what they aren't.  So just
check it when processing the form.  Something like:

if(isset($_POST['submit']) { //they submitted the form
    $id = $_GET['id']; //the id of the record they're trying to delete

    if(!user_is_allowed_to_delete($_SESSION['uid'], $id)) {
        //they're trying to delete something that they aren't supposed
    else {
        //they can delete this

What you decide to put into user_is_allowed_to_delete is up to you.

Re: How to update a database row from a form?

Quoted text here. Click to load it

Howzabout using a basic crypt function (with a key embedded in the php
script) to encrypt the ID of the record for display as a hidden field.

When the form is submitted, decrypt the hidden field using the same
key and hey-presto your record ID is known.

I sure there are other variants or ways to do it that may be more (or
less!) secure.  Presumably this is not bulletproof (as nothing is)
however it may be sufficient for your task.

addiakogiannis has posted a function on that
would do the job.

Site Timeline