Help with best method of using forms for users to verify database information

[Clarrie]

I've been asked to create a form/mini application for users to check / verify personal information in a database, and upon confirming the data is correct to a) e-mail notification to an administrator, and b) be redirected onto a new page to download a relevant data sheet.

The catch is, the form page needs to be accessed in a link from an e-mail sent by the administrator, and pre-populate the form with the existing data from the database, so all the user has to do is check his info and submit any changes.

I'm not sure of the best way to approach this - and I wondered if anyone had some advice to offer?

Maybe even if there's a existing stock script or package (like some form of CRM) that already has this functionality?

Thanks

[southplatte]

Hmmm.....

One thought is to create a table that will contain some form of hashcode (doesn't have to be difficult, just a random string ran through something like md5 in PHP to generate a decent string that is not easily guessable) that is linked to the users profile/data. Then, when you generate the email, you append the hashcode on the end of the url of the check page (ex http://domain.com/uservalidate.php?h...mk&uid=user123). The handling script on the validate/verify page will need to do several things:

It will first need to check the url get parameter in the database table to ensure it is a valid hashcode, secondly it will check that the userid parameter matches the assigned userid parameter that is associated with the said hashcode.

Thirdly, and most importantly, you will need some form of user input to validate against - such as a birthdate, zipcode, password, pet name or something if you have it. This is for minimal security - if your data should happen to have any type of mistakes, when you generate the email you could give links to people to validate information for other users, thus exposing potential personally identifable information to someone other than the intended user.

If the hashcode checks, but not the userid match, you give an error - if the hashcode and userid match up but the answer question (name, bday, password etc) don't match, you give an error. If all three items check out, you pull the data from the database and populate your page/form with it and then create the processing script to handle the form input to send the email and display the printable datasheet.

Unless these users have logins of some sort already - you are playing a bit of risk in just sending emails with URLs that directly link to the user information without much of a validation system - you will have to ensure that the only the intended recipient recieves the intended hashcode/user link, which depending upon your data's age, email accounts can change hands, get comprimised or otherwise create an inherent security risk, which should be relayed to the client/manager/responsible party.

[Dubbya]

southplatte is correct with regard to this being a potential security risk. You simply can't allow people to modify company or personal contact information without logging in. You'd be allowing any amateur hacker to cause all sorts of mischief.

If you've already got user information stored in an account (database) which requires the user to log in to modify, you're not far from completing your mission.

You may want to email a link with a querystring appended to it. The querystring value will be their customer id (encrypted or not) from a customer database table. (www.yoursite.org/userinfo.php?userid=10345)

Clicking the link would take the user to the information verification script on your site where they'd see a page displaying their contact information and with the options to either "Verify" or "Modify" their information.

Clicking "Verify" creates a session named "isvalid" with a value of "True", then submits a form containing their customer id from the customer table to an email script.

The email script reads the querystring value, decrypts the ID key sent by the form, checks to see that a session named "isvalid" containing a value of "True" exists, compares the customer ID and email address to the information stored in the database, then checks a new column named "hasdatasheet" for a value of "False" to see if they've already been emailed the information.

If the information jives and they've not yet received their data sheet, the script emails them the appropriate data sheet, or PDF file, closes the "isvalid" session and their user login session (if it exists).

Upon completion, the column named "hasdatasheet" would be flagged to "True" and the user would see a "Thanks" screen telling them that the process is complete and that the file has been sent to them at "customername@customeremail.org". Done deal.

Clicking "Modify" checks for the "isvalid" session, closes it if it exists, then requires them to log in to change their information. Once the changes are complete, bounce them back to the verify/modify page again so that they can view the information, confirm the changes, modify it again or verify it as correct.

The only person that would receive the data sheet would be the individual whose email address is stored in the customer table. Subsequent visits to the verification page would tell the user that the information has already been sent, show them the recipient email address and customer information but allow them to resend it if needed.

Optionally, you could check the "hasdatasheet" column for "True" and tell them to contact your Site Admin or other company representative.

At least, that's how I'd tackle it...

.02

[Clarrie]

southplatte and Dubbya

Thanks very much for taking the time over such detailed answers - its greatly appreciated. I'd already figured that appending the client id was the only way to start the process, but hadn't really thought through the security implications at that time. Another developer I talked to also threw up the same concerns over the security of the whole exercise, so looks like there's a bridge to cross.

We've just inherited this as a project from somebody else, and today I got a few more details from the client about how the previous company handled the problem. Or didn't. Seems they just did as the client asked and populated the form without any sort of verification without querying it.

Still, now I've raised it, they're worried about it, so its true, "a problem shared is a problem multiplied" (think that's right!).

Again, thanks for your input.