W3-mSQL (MySQL & mSQL)

12.2. W3-mSQL

W3-mSQL is actually a single CGI program called w3-msql. The program filters HTML pages that have embedded W3-mSQL commands and sends the resultant purified HTML to the client. W3-mSQL commands are written in a custom programming language called Lite. Lite is in many ways similar to Perl and C, but is specifically designed to interact with the mSQL database. A quick reference of Lite functions is included at the end of Chapter 18, "PHP and Lite Reference". To accomplish this, the path of the W3-mSQL enhanced HTML file is added to the w3-msql URL, e.g. http://www.me.com/cgi-bin/w3-msql/~me/mypage.html.

Within the HTML file, anything within the <! > tag is interpreted as Lite commands. For instance, the Lite equivalent of the "Hello world!" program would be:

<HTML><HEAD><TITLE>Hello world!</title></head>
<BODY>
<!
   echo("Hello world!");
>
</body></html>

Anything in the file that is not within <! > tags is left as plain HTML.

mSQL installs the w3-msql program automatically, so all you should have to do is place it into your cgi-bin directory and you will be set to go.

12.2.1. W3-Auth

W3-Auth is a mechanism for providing security to W3-mSQL driven pages. It is included with W3-mSQL and is installed automatically along with mSQL. With W3-Auth you can create a hierarchy of users and groups that are allowed to use various W3-mSQL enhanced pages.

W3-Auth works on the idea of using three separate levels of security access: user, group, and area. A user is a single name that usually refers to a single person, much like usernames in Unix. A group is just a collection of users. An area is a section of your web site that you wish to protect.

This scheme is particularly useful for sites that use multiple virtual hosts on the same web server. For instance, let's say that your machine goes by the names server1, server2, and server3. A different group of people administers each of these different names. With W3-Auth you can set up usernames for each person, then set up groups for each site name. Finally, you can create three different areas encompassing the three sites. The members of each group would then be able to administer the permissions for their W3-mSQL enhanced page without being able to affect the other sites.

12.2.2. Installation

Both the W3-mSQL and W3-Auth programs are compiled and installed automatically with the mSQL distribution. After installation they can be found in MSQL_HOME/bin where MSQL_HOME is the location of the mSQL files -- /usr/local/Hughes by default. Both the w3-msql and w3-auth binaries should be copied to the cgi-bin directory or its equivalent of your web server.

Upon installation W3-Auth assumes that the w3-msql program and itself will be made available through the cgi-bin directory of your web site. If you wish to place these programs in another directory, you must manually modify the source code before installing mSQL. Using Perl, this may be done as follows. From the src/w3-msql directory of the mSQL distribution type perl -pi -e 's/cgi-bin/yourcgidirectory/g' *.c. Alternatively, the following shell script will do the job:

#!/bin/sh

# Run this from the src/w3-msql directory of your mSQL source distribution.

for file in 'ls *.c';
do
        sed -e "s/cgi-bin/$1/" $file > $file.tmp
        mv $file.tmp $file
done

Copy this script into your src/w3-msql directory and type the following:

./scriptname yourcgidirectory

Where scriptname is the name of the script and yourcgidirectory is the name of the directory that will hold the w3-msql and w3-auth binaries.

W3-Auth is currently incompatible with the distributed version of the Apache web server due to a minor security feature of Apache. Apache currently does not allow CGI programs to access authentication information. Without this ability, no CGI program (including W3-Auth) can display a standard username/password box to the user and retrieve the results. Because of the importance of Apache to the mSQL community, a patch was quickly released which allows W3-Auth to run with Apache. After applying this patch, you must recompile Apache. Note that applying this patch allows all CGI programs to retrieve username and password information from users browsing the site. Unless you do not trust the people with access to the CGI programs on your machine, this patch is relatively safe.

After you have installed mSQL, there is a script in the misc directory of your mSQL home called setup_www. Running this script creates the databases and tables needed to use W3-Auth on your machine. The script will create a username and password for a person with total control over W3-Auth. Once this script is finished, you can use W3-Auth itself to create and modify other permissions.

12.2.3. W3-mSQL Example

To illustrate the use of W3-mSQL and Lite, consider again the marine foundation. The foundation runs its own web site. On this web site is an interactive database containing information on the various species of sharks in the oceans. For our example, the user will first encounter a plain HTML page that contains a form. By choosing the values on the form, the user can search through the shark database to retrieve information about a specific species. The HTML form could look something like the following:

<HTML><HEAD><TITLE>SHARKS!</title></head>
<BODY BGCOLOR="white">
<h1>Search the shark database</h1>
<p>
<FORM METHOD=POST ACTION="/cgi-bin/w3-msql/~sharks/search_result.html">
Species: <SELECT NAME="species">
<OPTION>
<OPTION>Heterodontus Portusjackson
<OPTION>Galeocerdo Cuvier
<OPTION>Carcharodon Carcharias
<OPTION>Isurus Paucus
</select> 
Age: <SELECT NAME="age">
<OPTION>
<OPTION>Young
<OPTION>Adult
<OPTION>Old
</select> 
Location: <SELECT NAME="location">
<OPTION>
<OPTION>Atlantic
<OPTION>Pacific
<OPTION>Caribean 
<select>
<p>
<INPUT TYPE="SUBMIT" VALUE=" SUBMIT "> <INPUT TYPE=RESET>
</form>
</body>
</html>

The mSQL containing the information about the sharks has the following structure:

+-----------------+----------+--------+----------+--------------+
 |     Field       |   Type   | Length | Not Null | Unique Index |
 +-----------------+----------+--------+----------+--------------+
 | id              | int      | 100    | Y        | N/A          |
 | species         | char     | 1000   | N        | N/A          |
 | age             | int      | 2000   | N        | N/A          |
 | location        | char     | 1000   | N        | N/A          |
 +-----------------+----------+--------+----------+--------------+

The HTML file /~sharks/search_result.html is a W3-mSQL enhanced file that retrieves the information about the requested species and displays an information file about that shark.

<HTML>
<HEAD><TITLE>Shark Search Result</title></head>
<BODY>
<H1>Here are the sharks that match your search...</h1>
<p>
<!
   $sock = msqlConnect();
   if ($sock < 0) {
      echo("Error : $ERRMSG\n");
      exit(1);
   }
   if (msqlSelectDB($sock,"sharks") < 0) {
      echo("Error : $ERRMSG\n");
      exit(1);
   }
   /* We now start to build the query. When finished, a typical query
	* will look something like this:
	* SELECT * FROM SHARK WHERE SPECIES='Isurus Paucus' AND AGE=2
	*/
   $query = "select * from sharks ";

   if ($species || $age || $location) {
      $query += " where ";
   }

   if ($species) { $query += "species = '$species'"; }
   if ($age) {
      if ($species) { $query += " and "; }
      $query += "age = $age";
   }
   if ($location) {
      if ($species || $age) { $query += " and "; }
      $query += "location = '$location''";
   }
   if (msqlQuery($sock,$query) < 0) {
      echo("Error : $ERRMSG\n");
      exit(1);
   }
   $result = msqlStoreResult();
   $numresults = msqlNumRows($result);
>

<UL>

<!
   if (! $numresults ) {
      echo ("<H2>No results matched</h2>");
   else {
      $shark = msqlFetchRow($result);
      while (#$shark > 0) {
         $id = $shark[0];
         echo("<LI>");
         printf("<IMG SRC=\"graphics/shark%s.gif\" ALIGN=LEFT>", $shark[0]);
         echo("<B>Species:</b> $shark[1]<br>");
         if ($shark[2] == 1) { $age = "Young"; }
         else if ($shark[2] == 2) { $age = "Adult"; }
         else if {$shark[2] == 3) { $age = "Old"; }
         echo("<B>Age:</b> $age<br>");
         echo("<B>Location</b> $shark[3]<br>");
         $shark = msqlFetchRow($result);
      }
   }
>
</ul>
<A HREF="search.html">Search again</a>
</body></html>

Notice that the Lite code and the HTML can be arbitrarily intermixed. Anywhere that there is static HTML you can end the Lite code and enter just the HTML. This becomes particularly useful when you have a largely static page where some dynamic content is desired.

Also, notice that at one point in the page, a tag for an image of the current shark is generated using the ID number of the shark. This is a useful way to include information that is not conveniently stored in a database. Because of mSQL's inability to handle blobs, it is often useful to store pictures, other binary data, or even large amounts of text as plain files tagged with the unique ID of the database entry.