[phpesp-dev] phpESP development at UC Berkeley

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

Hello,

First of all, I want to congratulate you all on a fine piece of software. I 
looked at several web survey packages before settling on phpESP for our 
project.

I am in the process of adding features to the latest version (1.6.1) of 
phpESP to meet the needs of a human resources department at UC Berkeley. 
 From reading the mailing list archives, I think that some of the features 
I am in the process of implementing might be of use to the greater phpESP 
community. I wanted to submit a description of my plans and ask you all for 
any feedback on my approach in hopes of being able to contribute something 
useful back to the project. (I've read the coding standards etc. in the 
included documentation.)  The features I am working on are:
    * "Auto-Authentication" via tokens passed in the query string
    * Survey reminders via email
    * Ability to track response rates for individual surveys
    * Anonymous response data
First off, not my decision, but I'm running phpESP on WIMP (Win2k, IIS, 
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 
surveys are using this feature.

My understanding is that access to private surveys _must_ be restricted 
using HTTP authentication. When the user follows the survey link the 
webserver throws up a login box and the user authenticates.  Then the 
script examines PHP_AUTH_USER and PHP_AUTH_PW and queries the respondent 
table to determine if the user should be allowed.

I've got a standalone php script that queries a internal data source for 
employees that meet certain criteria and then calls phpESP's bulk upload 
function to insert these users in phpesp.respondent. The script also 
creates an entry in  respondent_survey, a new table (see below). The script 
then emails these users a message containing a url to a PRIVATE 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 $ESPCONFIG 
variable that is tested by handler-prefix.php to determine if my url token 
based authentication should be allowed.  If this variable is true:

     - handler-prefix.php allows the sid to be passed via the query string 
(otherwise that continues to be prohibited).

     - $espuser and $esppass are set to the value of the corresponding 
query string parameters and then passed on to survey_auth().

     - survey_auth() expects the password to already be encrypted and does 
not reapply PASSWORD() in the sql.  (We are not passing plan text passwords 
in the url.)

     - handler.php clears the query string, and passes the userid/password 
arguments as hidden variables when the user either continues to the next 
survey page or submits the survey.

The security of this approach depends on the right person receiving the 
email message containing the link.  There's not much motivation 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 which 
surveys are using this feature.

My standalone script runs by cron (or Task Scheduler) and will "remind" 
users who do not appear in the response table or who's response.complete 
value is not 'Y' to fill out the survey.  The reminder sends them the 
auto-authentication url in addition to a pleasant message.  The maximum 
number of reminders to send and the frequency of reminders are configurable.

I've created a new tables "respondent_survey" which links 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 '',  # date 
user 1st informed of access to survey
         contact_last            TIMESTAMP(14) NOT NULL DEFAULT '',  # last 
date that we reminded them
         contact_count           SMALLINT NOT NULL DEFAULT 0           # 
num of times they've been reminded.

This table allows us to keep track of reminder statuses for users of 
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 can 
calculate response rates for various surveys in a realm.

By examining response_survey.contact_first we can produce response rates 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 which 
surveys are using this feature.

When a survey is saved/completed we want to make it so that the survey 
responses cannot be linked to an individual.  In order to do this we will 
set personally identifying information (ie. fname, lname, email) in 
respondent to NULL when the survey is sumbitted.  An exception will be that 
if the save/resume feature is enabled for the survey, and the reminders 
feature is also enabled, the email address will not be NULL'ed when the 
survey is saved.  This allows the program to continue to remind the user to 
complete the survey.

Note: It's assumed that this "anonymous" feature will be used in 
conjunction with the token-based "auto-authentication" feature.  In this 
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