1- Go ahead with CGI::Session and let it generate and send the session ID to the client

2- Store in the session data not only the primary key but also the original values of the fields you sent to the client (probably in a hash that will be serialized in order to be stored in the session data)

When the client submits the form:

3- Retrieve the session data from CGI::Session

4- Compare each field the client sent with the fields you have stored in your session data

5- Update to the database only the fields that have been changed

If you're concerned about concurrent changes to the data, you could calculate the MD5 of all fields during step (2) and store this MD5 in the session data. Between steps (3) and (4), read the data from the DB again, calculate the MD5 and compare it with the one in the session data. If they're not the same, you'll know that someone else has changed your data.

As for the second problem, you may want to change your db design to either use timestamps (if your database allows, like MySQL) which will UPDATE every time you update a record but NOT when someone reads from it. You can check to see if the timestamp in the current record is greater than the one being updated by the form's fields. If it is, you know that the record was changed in some way.

BTW, if your database doesn't keep track of time in the timestamp down to the millisecond you may want to use a module like Time::HiRes to create the timestamp and convert the DB table to a string type.

Here's an article which discusses the problem in a different language and suggests you use GUIDs (which I think you can do in Perl too. Check CPAN).

If you already have the primary key I don't understand your problem with the first issue. Just update the records with that primary key.

The problem is that primary key (rowid in this case) is embeded in the input fieldnames. I originally did this so that I could have multiple inputs with names like '100_foo', '100_bar', '101_foo', '101_bar', etc. That way, I could loop through my values on the submit, and know what hooks up to what. And that does work, technically.

The security issue is this: Essentially, that primary key is going back to the client, and then being submitted as part of my form. There's no reason that they couldn't alter the fieldname to be '900_foo' and '900_bar' and then submit the form. While convenient for me, they end up supplying the primary key to update, and I don't trust 'them'.

My current thinking is that I'll store a mini lookup table using CGI::Session. That will map the real primary keys to some temporary dummy values that I use to name my fields. After the submission comes back, I'll look there to get my keys for INSERT/UPDATE. I'm just wondering how everyone else does this, I can't be the first to go down this road...

There's no reason that they couldn't alter the fieldname to be '900_foo' and '900_bar' and then submit the form.

So is there some column in the database indicating that the user owns 100 and 101 but not 900? What I do is always check that the user submitting the form has authority over the object/row he is trying to modify.

For example, if my primary object is a note, and note has columns id, created, updated, title, body, I'll have another table note_user that has columns note_id, user_id. If the user submits a form to edit note with id 100, I make sure there's a row in note_user with note_id 100 and user_id corresponding to the user, and if not I disallow the transaction.

This way I do not have to keep any data about the transaction in the session. My sessions are very thin, just id, user_id, created, updated.

In your case it sounds like you're trying to give users the freedom to create their own database table(s). Perhaps you can create a table_user map and update it whenever a user creates a new table. There could also be a row_user map for whenever a user creates a new row in a shared table. Then whenever the user tries to modify a particular row or table or insert to a particular table, you have a permissions table to check and make sure the transaction is OK.

Hi pboin,

You might want to try CGI::EncryptForm to encrypt the input fieldnames. When the form is submitted, you decrypt that fieldname back to the original. If the encrypted fieldname was corrupted, nothing gets decrypted and you get 'undef', so you reject the submsission.