Fwd: Re: [phpesp-dev] phpESP development at UC Berkeley

[Brian W. <br...@uc...>]

>Date: Wed, 28 May 2003 09:30:15 -0700
>To: Chad Bearden <ch...@be...>
>From: Brian Wood <br...@uc...>
>Subject: Re: [phpesp-dev] phpESP development at UC Berkeley
>
>At 12:12 PM 5/28/2003 -0400, you wrote:
>>I'm interested in your implementation of email reminders.  How will you=20
>>implement, perl?
>
>My plan is to use a standard php script run by cron/taskscheduler.  Every=
=20
>night the script checks the last contact for the people receiving=20
>reminders.  If they are due for a reminder, the script calls php's mail()=
=20
>function.  The WHERE of the sql that tests if the people are due contains:
>
>(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(contacted)) >=3D (60*60*24*$period)  #=
=20
>$period allows frequency configuration
>AND contact_count < $max_contacts  # stop annoying them after=
 $max_contacts.
>
>Brian
>
>
>>chad.
>>
>>On Friday, May 23, 2003, at 19:06 US/Eastern, Brian Wood wrote:
>>
>>>Hello,
>>>
>>>First of all, I want to congratulate you all on a fine piece of=20
>>>software. I looked at several web survey packages before settling on=20
>>>phpESP for our project.
>>>
>>>I am in the process of adding features to the latest version (1.6.1) of=
=20
>>>phpESP to meet the needs of a human resources department at UC=20
>>>Berkeley. >From reading the mailing list archives, I think that some of=
=20
>>>the features I am in the process of implementing might be of use to the=
=20
>>>greater phpESP community. I wanted to submit a description of my plans=20
>>>and ask you all for any feedback on my approach in hopes of being able=20
>>>to contribute something useful back to the project. (I've read the=20
>>>coding standards etc. in the included documentation.)  The features I am=
=20
>>>working on are:
>>>
>>>=95       "Auto-Authentication" via tokens passed in the query string
>>>=95       Survey reminders via email
>>>=95       Ability to track response rates for individual surveys
>>>=95       Anonymous response data
>>>
>>>First off, not my decision, but I'm running phpESP on WIMP (Win2k, IIS,=
=20
>>>PHP, MySQL).  This might switch to WAMP when we go into production.
>>>
>>>
>>>+ FEATURE: "Auto-Authentication" via tokens.
>>>
>>>Scope: I'm limiting my concern to auto-published surveys for now.
>>>
>>>Table modification:  Add survey.urlauth enum('Y','N') to indicate which=
=20
>>>surveys are using this feature.
>>>
>>>My understanding is that access to private surveys _must_ be restricted=
=20
>>>using HTTP authentication. When the user follows the survey link the=20
>>>webserver throws up a login box and the user authenticates.  Then the=20
>>>script examines PHP_AUTH_USER and PHP_AUTH_PW and queries the respondent=
=20
>>>table to determine if the user should be allowed.
>>>
>>>I've got a standalone php script that queries a internal data source for=
=20
>>>employees that meet certain criteria and then calls phpESP's bulk upload=
=20
>>>function to insert these users in phpesp.respondent. The script also=20
>>>creates an entry in  respondent_survey, a new table (see below). The=20
>>>script then emails these users a message containing a url to a PRIVATE=20
>>>survey. The link that I generate passes three parameters via the query=
 string:
>>>
>>>    userid               a random unique string
>>>    password             a random string encrypted by MySQL PASSWORD()
>>>    sid                  the surveyid
>>>
>>>The users follow this link and "auto-authenticate".
>>>
>>>The survey directory is not HTTP Authenticated.  I've added an=20
>>>$ESPCONFIG variable that is tested by handler-prefix.php to determine if=
=20
>>>my url token based authentication should be allowed.  If this variable=20
>>>is true:
>>>
>>>     - handler-prefix.php allows the sid to be passed via the query=20
>>> string (otherwise that continues to be prohibited).
>>>
>>>     - $espuser and $esppass are set to the value of the corresponding=20
>>> query string parameters and then passed on to survey_auth().
>>>
>>>     - survey_auth() expects the password to already be encrypted and=20
>>> does not reapply PASSWORD() in the sql.  (We are not passing plan text=
=20
>>> passwords in the url.)
>>>
>>>     - handler.php clears the query string, and passes the=20
>>> userid/password arguments as hidden variables when the user either=20
>>> continues to the next survey page or submits the survey.
>>>
>>>The security of this approach depends on the right person receiving the=
=20
>>>email message containing the link.  There's not much motivation I can=20
>>>imagine for an imposter to fill out someone else's survey...
>>>
>>>
>>>+ FEATURE: Survey reminders via email
>>>
>>>Table modification:  Add survey.reminders enum('Y','N') to indicate=20
>>>which surveys are using this feature.
>>>
>>>My standalone script runs by cron (or Task Scheduler) and will "remind"=
=20
>>>users who do not appear in the response table or who's response.complete=
=20
>>>value is not 'Y' to fill out the survey.  The reminder sends them the=20
>>>auto-authentication url in addition to a pleasant message.  The maximum=
=20
>>>number of reminders to send and the frequency of reminders are=
 configurable.
>>>
>>>I've created a new tables "respondent_survey" which links respondents to=
=20
>>>surveys and keeps track of their reminders (contacts):
>>>
>>>           username              char(64),
>>>         survey_id                       int(10) unsigned,
>>>         contact_first           TIMESTAMP(14) NOT NULL DEFAULT '',  #=20
>>> date user 1st informed of access to survey
>>>         contact_last            TIMESTAMP(14) NOT NULL DEFAULT '',  #=20
>>> last date that we reminded them
>>>         contact_count           SMALLINT NOT NULL DEFAULT 0           #=
=20
>>> num of times they've been reminded.
>>>
>>>This table allows us to keep track of reminder statuses for users of=20
>>>multiple surveys in a realm.
>>>
>>>
>>>+ FEATURE: Ability to track response rates for individual surveys
>>>
>>>By querying respondent_survey and left/right joining it to response we=20
>>>can calculate response rates for various surveys in a realm.
>>>
>>>By examining response_survey.contact_first we can produce response rates=
=20
>>>in relationship to the length of time the user has had access to the=
 survey.
>>>
>>>+ FEATURE: Anonymous response data
>>>
>>>Table modification:  Add survey.anonymous enum('Y','N') to indicate=20
>>>which surveys are using this feature.
>>>
>>>When a survey is saved/completed we want to make it so that the survey=20
>>>responses cannot be linked to an individual.  In order to do this we=20
>>>will set personally identifying information (ie. fname, lname, email) in=
=20
>>>respondent to NULL when the survey is sumbitted.  An exception will be=20
>>>that if the save/resume feature is enabled for the survey, and the=20
>>>reminders feature is also enabled, the email address will not be NULL'ed=
=20
>>>when the survey is saved.  This allows the program to continue to remind=
=20
>>>the user to complete the survey.
>>>
>>>Note: It's assumed that this "anonymous" feature will be used in=20
>>>conjunction with the token-based "auto-authentication" feature.  In this=
=20
>>>scenario, username is a random string and not personally identifying.
>>>
>>>Thanks for reading if you made it this far.  Comments welcome.
>>>
>>>Brian Wood
>>>Programmer/Analyst III
>>>UC Berkeley Human Resources
>></blockquote></x-html>
>
>Brian Wood
>Programmer/Analyst III
>UC Berkeley Human Resources
>230 University Hall
>Berkeley, CA 94720
>Voice: (510) 643-4094

Brian Wood
Programmer/Analyst III
UC Berkeley Human Resources
230 University Hall
Berkeley, CA 94720
Voice: (510) 643-4094