081:114G Fall 2008
Data Base Management Systems(3 hours)

 Last updated: Sept 14, 2008
  • Apache on Ubuntu

    Install Apache using the Synaptic Package Manager as part of the LAMP (Linux Apache MySql PHP) systenm:

    • Suystem | Sysnaptic Pakcage Manager
    • Edit | Mark Packages By Task
    • Select LAMP | OK
    • approve and apply - this will take a while
    • It will prompt you to configure a MySql password.

    Apache will now start each time you boot your system.

    In a terminal window, cd to /usr/lib/cgi-bin

    Many of the following commands require root privs. You can preceed each command with sudo, or, type:

    sudo su

    which will (after you enter your password) put you in a shell where you are root and the sudo is no longer necessary. You return to non-priv state by typing exit.

    create a file named test-cgi.cgi with the following contents (cut and paste): 

    #!/bin/sh

# disable filename globbing
set -f

echo Content-type: text/plain
echo

echo CGI/1.0 test script report:
echo

echo argc is $#. argv is "$*".
echo

echo SERVER_SOFTWARE = $SERVER_SOFTWARE
echo SERVER_NAME = $SERVER_NAME
echo GATEWAY_INTERFACE = $GATEWAY_INTERFACE
echo SERVER_PROTOCOL = $SERVER_PROTOCOL
echo SERVER_PORT = $SERVER_PORT
echo REQUEST_METHOD = $REQUEST_METHOD
echo HTTP_ACCEPT = "$HTTP_ACCEPT"
echo PATH_INFO = "$PATH_INFO"
echo PATH_TRANSLATED = "$PATH_TRANSLATED"
echo SCRIPT_NAME = "$SCRIPT_NAME"
echo QUERY_STRING = "$QUERY_STRING"
echo REMOTE_HOST = $REMOTE_HOST
echo REMOTE_ADDR = $REMOTE_ADDR
echo REMOTE_USER = $REMOTE_USER
echo AUTH_TYPE = $AUTH_TYPE
echo CONTENT_TYPE = $CONTENT_TYPE
echo CONTENT_LENGTH = $CONTENT_LENGTH

    Reset permissions of test-cgi: chmod a+x test-cgi.cgi In your Ubuntu browser, type the URL: 127.0.0.1/cgi-bin/test-cgi.cgi This should run the test-cgi script which will report some details about your operating environment. The results will look something like:

    cd to /var/www and modify the default web page index.html to something like:

    <html> <body> Hello World! </body> </html> Change its permissions:

    chmod a+r index.html

    In your browser, type the URL:

    127.0.0.1

    You should see your default page.

    To run an executable program, try the following Mumps script (see http://cns2.uni.edu/~okane/source/MUMPS-MDH/. Download and install from the highest numbered mumpscompiler...)

    In /usr/lib/cgi-bin create mtest.mps with the following contents:

    zmain +#include <mumpsc/cgi.h> html Content-type: text/html &!&! html <html><body bgcolor=silver> html test test test </body></html> Compile it:

    mumpsc mtest.mps

    Its protections should be correct. In the browser, type:

    127.0.0.1/cgi-bin/mtest.cgi

    You should see "test test test" on a gray screen.

    Try a bash script to access PostgreSQL through apache cgi-bin. Write btest.cgi in /usr/lib/cgi-bin as follows: 

    
      #!/bin/bash

      # disable filename globbing
      set -f
      echo Content-type: text/plain
      echo
      echo Database test
      echo `whoami`
      psql -f f1 -d medical

    

Note: if you cut and paste these, be sure to remove any
leading blanks or tabs from the lines.

    

Write the file named f1 in /usr/lib/cgi-bin to contain the lines:

      \l
      \d
      select namelast, namefirst, ptid from ptname;
      \q

Set the protections:

      chmod a+x btest.cgi
      chmod a+r f1

Make the web server a Postgresql user:

createuser www-data

permit www--data to be super user when prompted. This is
not a good idea in general but it will make table
access permissions much easier for the time being.

A complete list of psql commands is at:

http://www.postgresql.org/docs/8.3/static/app-psql.html 

    
See also dropuser, grant, and revoke SQL commands.

Note: www-data is the name of the Apache user on my Ubuntu.
On some systems, it may have a different name.

Make the files to be owned by Apache:

chown www-data *
chgrp www-data *

Type the URL:

      127.0.0.1/cgi-bin/btest.cgi



  •  Obtaining a command from a web page.

Build an HTML form and put it in /var/www/:

      <html>
      <body>
      Hello World
      <p>
      <form method="get" action="cgi-bin/formtest.cgi">
      <input type="text" name="command" size=70 value="">
      <input type="submit" value="Run Command">
      </form>
      </body>
      </html>

Write a shell script named formtest.cgi to be called by the web server:

      #!/bin/bash
      set -f
      echo Content-type: text/plain
      echo
      echo Database test
      echo `whoami`
      echo Command is `/usr/lib/cgi-bin/fixer.cgi`
      psql -d medical -c "`/usr/lib/cgi-bin/fixer.cgi`"

Put it in /usr/lib/cgi-bin with world execute privs.

Write a brief Mumps program named fixer.mps to parse QUERY_STRING:

        zmain
+#include <mumpsc/cgi.h>
        write command,!

Place it in /usr/lib/cgi-bin, compile it and rename fixer
to be fixer.cgi.  Be sure it has world execute privs.

Run the browser on form.html and enter a command: