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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

That sounds reasonable.

chad.

On Wednesday, May 28, 2003, at 12:30 US/Eastern, Brian Wood wrote:

> At 12:12 PM 5/28/2003 -0400, you wrote:
>> I'm interested in your implementation of email reminders.  How will=20=

>> you implement, perl?
>
> My plan is to use a standard php script run by cron/taskscheduler. =20
> Every night the script checks the last contact for the people=20
> receiving reminders.  If they are due for a reminder, the script calls=20=

> php's mail() function.  The WHERE of the sql that tests if the people=20=

> 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=20
> $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)=20=

>>> of phpESP to meet the needs of a human resources department at UC=20
>>> Berkeley. >=46rom reading the mailing list archives, I think that =
some=20
>>> of the features I am in the process of implementing might be of use=20=

>>> to the greater phpESP community. I wanted to submit a description of=20=

>>> my plans and ask you all for any feedback on my approach in hopes of=20=

>>> being able to contribute something useful back to the project. (I've=20=

>>> read the coding standards etc. in the included documentation.)  The=20=

>>> features I am 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,=20=

>>> IIS, PHP, MySQL).  This might switch to WAMP when we go into=20
>>> 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=20
>>> which surveys are using this feature.
>>>
>>> My understanding is that access to private surveys _must_ be=20
>>> restricted using HTTP authentication. When the user follows the=20
>>> survey link the webserver throws up a login box and the user=20
>>> authenticates.  Then the script examines PHP_AUTH_USER and=20
>>> PHP_AUTH_PW and queries the respondent table to determine if the=20
>>> user should be allowed.
>>>
>>> I've got a standalone php script that queries a internal data source=20=

>>> for employees that meet certain criteria and then calls phpESP's=20
>>> bulk upload function to insert these users in phpesp.respondent. The=20=

>>> script also creates an entry in  respondent_survey, a new table (see=20=

>>> below). The script then emails these users a message containing a=20
>>> url to a PRIVATE survey. The link that I generate passes three=20
>>> 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=20
>>> determine if my url token based authentication should be allowed. =20=

>>> If this variable 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=20
>>> corresponding query string parameters and then passed on to=20
>>> 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=20
>>> text 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=20=

>>> the email message containing the link.  There's not much motivation=20=

>>> I can 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=20
>>> "remind" users who do not appear in the response table or who's=20
>>> response.complete value is not 'Y' to fill out the survey.  The=20
>>> reminder sends them the auto-authentication url in addition to a=20
>>> pleasant message.  The maximum number of reminders to send and the=20=

>>> frequency of reminders are configurable.
>>>
>>> I've created a new tables "respondent_survey" which links=20
>>> respondents to 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=20=

>>> we can calculate response rates for various surveys in a realm.
>>>
>>> By examining response_survey.contact_first we can produce response=20=

>>> rates in relationship to the length of time the user has had access=20=

>>> 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=20
>>> survey responses cannot be linked to an individual.  In order to do=20=

>>> this we will set personally identifying information (ie. fname,=20
>>> lname, email) in respondent to NULL when the survey is sumbitted. =20=

>>> An exception will be that if the save/resume feature is enabled for=20=

>>> the survey, and the reminders feature is also enabled, the email=20
>>> address will not be NULL'ed when the survey is saved.  This allows=20=

>>> the program to continue to remind 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=20=

>>> this scenario, username is a random string and not personally=20
>>> 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
>