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

[Chad B. <ch...@be...>]

I'm interested in your implementation of email reminders.  How will you=20=

implement, perl?

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 to=20=

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

> 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.)=A0 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).=A0 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:=A0 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 survey=20=

> link the webserver throws up a login box and the user authenticates.=A0=20=

> Then the script examines PHP_AUTH_USER and PHP_AUTH_PW and queries the=20=

> respondent table to determine if the 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 bulk=20=

> upload function to insert these users in phpesp.respondent. The script=20=

> also creates an entry in=A0 respondent_survey, a new table (see =
below).=20
> The script then emails these users a message containing a url to a=20
> PRIVATE survey. The link that I generate passes three parameters via=20=

> the query string:
>
> =A0=A0 userid =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0a random =
unique string
> =A0=A0 password =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0a random string =
encrypted by MySQL=20
> PASSWORD()=A0=A0=A0
> =A0=A0 sid =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0the =
surveyid
>
> The users follow this link and "auto-authenticate".
>
> The survey directory is not HTTP Authenticated.=A0 I've added an=20
> $ESPCONFIG variable that is tested by handler-prefix.php to determine=20=

> if my url token based authentication should be allowed.=A0 If this=20
> variable is true:
>
> =A0=A0=A0 - handler-prefix.php allows the sid to be passed via the =
query=20
> string (otherwise that continues to be prohibited).=A0
>
> =A0=A0=A0 - $espuser and $esppass are set to the value of the =
corresponding=20
> query string parameters and then passed on to survey_auth().=A0
>
> =A0=A0=A0 - survey_auth() expects the password to already be encrypted =
and=20
> does not reapply PASSWORD() in the sql.=A0 (We are not passing plan =
text=20
> passwords in the url.)
>
> =A0=A0=A0 - 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.=A0 There's not much motivation =
I=20
> can imagine for an imposter to fill out someone else's survey...
>
>
> + FEATURE: Survey reminders via email
>
> Table modification:=A0 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.=A0 The=20
> reminder sends them the auto-authentication url in addition to a=20
> pleasant message.=A0 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 respondents=20=

> to surveys and keeps track of their reminders (contacts):
>
> =A0=A0=A0=A0=A0=A0=A0=A0=A0 username=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0char(64),
> =A0=A0=A0=A0=A0=A0=A0=A0survey_id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 int(10) unsigned,
> =A0=A0=A0=A0=A0=A0=A0=A0contact_first=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0TI=
MESTAMP(14) NOT NULL DEFAULT '',=A0 #=20
> date user 1st informed of access to survey
> =A0=A0=A0=A0=A0=A0=A0=A0contact_last=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
TIMESTAMP(14) NOT NULL DEFAULT '',=A0 #=20
> last date that we reminded them
> =A0=A0=A0=A0=A0=A0=A0=A0contact_count=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0SM=
ALLINT NOT NULL DEFAULT 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=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=20
> rates in relationship to the length of time the user has had access to=20=

> the survey.
>
> + FEATURE: Anonymous response data
>
> Table modification:=A0 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.=A0 In order to do this we=20=

> will set personally identifying information (ie. fname, lname, email)=20=

> in respondent to NULL when the survey is sumbitted.=A0 An exception =
will=20
> be 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=20
> NULL'ed when the survey is saved.=A0 This allows the program to =
continue=20
> 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.=A0 In=20=

> this scenario, username is a random string and not personally=20
> identifying.
>
> Thanks for reading if you made it this far.=A0 Comments welcome.
>
> Brian Wood
> Programmer/Analyst III
> UC Berkeley Human Resources
>