mc-cgi and a database -- ideas, please

[Dave Cragg]

At 11:13 pm +0900 13/11/03, kweto wrote:
>Hello List,
>
>Sorry for the off-topic'ness -- and vagueness! -- of this bit of
>self-indulgence but since this is the best and gentlest group to ask, I'll
>fire away anyway.
>
>I'm looking to put together a language-learning website that my students --
>and my students *only* -- can access from their home pc's and which,
>invisibly in the background, updates a database of student records (eg,
>names, passwords, dates site was accessed, time spent on the various
>language activities, grades). Of course, I want to involve mc-cgi in this as
>much as possible. (I've already been relying successfully on mc-cgi for a
>while now to help run my other internet-based doo-dahs.)
>
>The following "model" is my stab-in-the-dark attempt at envisioning the
>basic pieces of this puzzle and how to co-ordinate them:
>
>1. on the web-server (a paid-for hosting service), I password-protect the
>website's top-folder and then manually enter individual usernames and
>passwords for each of my 100 or so students
>
>2. place all my mc-cgi doo-dahs inside that password-protected folder
>
>3. create a MySQL database using my web-hosting service's "SQL Database"
>control panel
>
>4. script some kind of mc-cgi doohickey that acts as an invisible
>intermediary between that database and
>    (a) (to keep track of who and when) the log-in window that pops up when a
>student accesses that top-folder
>    (b) (to keep track of what and how) the various language activities,
>a.k.a, mc-cgi doo-dahs
>
>Now, assuming that I'm not too far off-track with that "plan" -- and that
>there aren't better alternative models -- what I don't understand is how to
>do Step 3. Actually, as far as Step 3(b) goes, I guess that given enough
>still-active brain cells and plenty of searches thru the List Archives I
>might eventually churn out some kind of scripting solution all on my own;
>however, I cannot fathom out at all how to go about scripting something for
>Step 3(a), namely, getting mc to "talk" with whatever process underlies the
>web-server calling up a log-in window for password-protected folders.

If you use standard password protection on the folder, it's the 
server that would handle the password stuff before it gets to your 
cgi, so keeping track might be tricky. An alternative way would be to 
put the cgi scripts in the normal cgi-bin location, and instead of 
password protecting the folder, use some custom headers in the http 
request that would let the cgi script do the authentication.

A simple example:

On the client side:
Say the client app contacts the server to request data for one of 
four available exercises. To get exercise 1, you could do something 
like this:

# assume tID contains the student's ID

put "kweto_doohick: exerciseplease,1" & tID into tHeaders
set the httpHeaders to theaders
put url "http://www.kweto.com/cgi-bin/thingie.mt" into tExercise_1_Data
##do the usual check on the result
## etc, etc

Then in the "thingie.mt" script:

#!/somewhere/mc
on startup
   put $HTTP_kweto_doohick into tActionString
   if item 1 of tActionString = "exerciseplease" then
     put item 3 of tActionString into tUserID
    ## do the big brother stuff on the user here if needed
     put item 2 of tActionString into tExNumber
     switch tExNumber
        case 1
          ##get the exercise 1 data
          ## send it back in the normal way
        case 2
        ### etc, etc
     end switch
   else ## no proper header,
     put "error" && "Not Welcome!" & cr into tErrString
     put "Status: 410" && "go away" & cr
     put "Content-Type: text/plain" & cr
     put "Content-Length:" && length(tErrString) & cr
     put cr
     put tErrString
   end if

This will stop casual browsing of your cgi. If necessary, you could 
add more secure features to the custom headers. (session keys, MD5 
digests, and what have you)

Cheers
Dave