Re: Tracking records to change/insert during CGI

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.

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

Celebrate Intellectual Diversity

Comment on Re: Tracking records to change/insert during CGI

Replies are listed 'Best First'.
Re^2: Tracking records to change/insert during CGI by pboin (Deacon) on Oct 17, 2005 at 17:52 UTC
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...	[reply]
Re^3: Tracking records to change/insert during CGI by ryantate (Friar) on Oct 17, 2005 at 21:10 UTC
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.	[reply]
Re^3: Tracking records to change/insert during CGI by kiat (Vicar) on Oct 18, 2005 at 16:11 UTC
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.	[reply]