#! /usr/bin/perl -T

#----------------------------------------------------------------------------------#
#  The code for this address book is Copyright 2001, Sean C. Nichols. I am         #
#   releasing it under the GNU General Public License (GPL). You are free to take  #
#   it and use/modify it for your own purposes as long as you include some         #
#   reference to the original source. (Also I wouldn't mind you dropping me a      #
#   line at (seanni :AT: canada.com) to let me know what you've done with it!)     #
#   The full text of this license can be found at                                  #
#                     http://www.gnu.org/copyleft/gpl.txt                          #
#----------------------------------------------------------------------------------#

# Main program start

use strict;
$| = 1;

use CGI qw[:standard];
use DBI;

my $gsTable = 'address';
my $gsAuthTable = 'ad_users';

my $gsDBUser;
my $gsDBPassword;
my $gsConnect;

my $gsError;

my @gsFields = ( "lname", "fname", "areacode", "telephone", "street_address",
                 "town_address", "stateprov_address", "zippostal_address",
                 "country_address", "email", "homepage", "comment" );

my $lname = 0;
my $fname = 1;
my $areacode = 2;
my $telephone = 3;
my $street_address = 4;
my $town_address = 5;
my $stateprov_address = 6;
my $zippostal_address = 7;
my $country_address = 8;
my $email = 9;
my $homepage = 10;
my $comment = 11;
my $id = 12;

my $FALSE = 0;
my $TRUE = not $FALSE;

fMain();


# Main program end -- user functions below

sub fMain {
    my $bAuth;
    my $sDB;
    my $sHost;
    my $sPort;

    my $sSubmitName;

    my $sHeader = "Content-type: text/html\r\n\r\n";

    my $sUser = fGetSQL( "user", $TRUE );
    my $sPassword = fGetSQL( "password", $TRUE );
    my $sOperation = param( "op" );
    my $iNumConditions = param( "conditions" );

    if ($iNumConditions eq "") {
        $iNumConditions = 3;
    }

    ($sDB, $gsDBUser, $sHost, $sPort, $gsDBPassword) = fGetDBAccess();

    $gsConnect = "DBI:mysql:database=$sDB;host=$sHost;port=$sPort";

    print $sHeader;

    if ($sOperation eq "auth") {
        # Just logging in. If user/pass ok, then display blank main form, else
        #  just show error message.

        if (fAuth( $sUser, $sPassword )) {
            fShowHTMLHead( "Qadabra: Search $sUser\'s Addresses" );
            fShowResultForm( $sUser, $sPassword, $iNumConditions );
            fShowBreak();
            fShowEntryForm( $sUser, $sPassword, $FALSE );
        } else {
            fShowHTMLHead( "Qadabra: Login Failed" );
            fShowError( "The username / password you supplied was invalid." );
            fShowLoginForm( $sUser );
        }

    } elsif ($sOperation eq "read") {
        # Show all applicable results plus blank entry form.

        fShowHTMLHead( "Qadabra: Results for $sUser" );
        fShowResults( $sUser, $sPassword );
        fShowResultForm( $sUser, $sPassword, $iNumConditions );
        fShowBreak();
        fShowEntryForm( $sUser, $sPassword, $FALSE );
    } elsif ($sOperation eq "write") {
        # Validate, input entry, then show blank entry form, with error message
        #  if applicable.

        if ($sSubmitName = fAddRecord( $sUser, $sPassword )) {
            fShowHTMLHead( "Qadabra: Added <EM>$sSubmitName</EM>" );
            fShowStatus( "Added record for <EM>$sSubmitName</EM>" );
        } else {
            fShowHTMLHead( "Qadabra: Add Record Failed" );
            fShowStatus( "Failed to add record. Reason:\n\n$gsError" );
        }

        fShowBreak();
        fShowEntryForm( $sUser, $sPassword, $FALSE );
        fShowBreak();
        fShowResultForm( $sUser, $sPassword, $iNumConditions );
    } elsif ($sOperation eq "admin") {
        # Future expansion to allow changing username/password, adding users, etc.
        #  for now, it doesn't do anything (not yet implemented).

        fShowHTMLHead( "Qadabra: Not Implemented" );
        fShowError( "Remote administration features are not yet implemented." );
        fShowLoginForm( $sUser );
    } elsif ($sOperation eq "update") {
        # Update a given record. Same as an entry form, except show existing
        #  entries, plus an "Update" button instead of an "Add" button.

        fShowHTMLHead( "Qadabra: Update entry for " . fGetSQL( "lname", $FALSE ) );
        fShowEntryForm( $sUser, $sPassword, fGetSQL( "recordnum", $TRUE ) );
        fShowBreak();
        fShowResultForm( $sUser, $sPassword, $iNumConditions );
    } elsif ($sOperation eq "updated") {
        # Record has just been updated. Same as "write".

        if ($sSubmitName = fUpdateRecord( $sUser, $sPassword, param( "recnum" ) )) {
            fShowHTMLHead( "Qadabra: Updated <EM>$sSubmitName</EM>" );
            fShowStatus( "Updated record for <EM>$sSubmitName</EM>" );
        } else {
            fShowHTMLHead( "Qadabra: Update Record Failed" );
            fShowStatus( "Failed to update record. Reason:\n\n$gsError" );
        }

        fShowBreak();
        fShowEntryForm( $sUser, $sPassword, $FALSE );
        fShowBreak();
        fShowResultForm( $sUser, $sPassword, $iNumConditions );
    } else {
        # "Login" form

        fShowHTMLHead( "Qadabra: Login" );
        fShowLoginForm( "" );
    }

    fShowHTMLFoot();
}

sub fGetDBAccess {
    # Necessary to access the mysql database yet hide user / password / etc
    #  from prying eyes.

    my $sDB;
    my $sDBUser;
    my $sHost;
    my $sPort;
    my $sDBPassword;

    open( FILE_IN, '/home/httpd/mysql-access' );

    while (<FILE_IN>) {
        if (m/^(\w+)\s*=\s*(\w*)/i) {
            if ($1 eq "db")          { $sDB = $2 };
            if ($1 eq "user")        { $sDBUser = $2 };
            if ($1 eq "host")        { $sHost = $2 };
            if ($1 eq "port")        { $sPort = $2 };
            if ($1 eq "password" )   { $sDBPassword = $2 };
        }
    }

    close( FILE_IN );

    return ($sDB, $sDBUser, $sHost, $sPort, $sDBPassword);
}

sub fShowError {
    my ($sErrorMsg) = @_;

    print "        <FONT COLOR=\"#e70000\">$sErrorMsg</FONT><BR><BR>\n";
}

sub fShowHTMLHead {
    my ($sTitle) = @_;

    print "<HTML>\n" .
          "    <HEAD>\n" .
          "        <TITLE>$sTitle</TITLE>\n" .
          "        <LINK REL=\"shortcut icon\" HREF=\"/favicon.ico\" TYPE=\"image/x-icon\" />\n" .
          "    </HEAD>\n" .
          "    <BODY BGCOLOR=\"#769fc9\"><FONT FACE=\"Arial,Helvetica\">\n" .
          "        <H1>$sTitle</H1>\n" .
          "        <HR ALIGN=\"left\" WIDTH=\"90%\">\n\n";
}

sub fShowHTMLFoot {
    print "\n" .
          "        <HR ALIGN=\"left\" WIDTH=\"90%\">\n" .
          "        Return <A HREF=\"http://trichotomy.ca/index.php\">Home</A>.<BR>\n" .
          "        View Perl <A HREF=\"http://trichotomy.ca/cgi-bin/printcode.pl?filename=qadabra.pl\">Source Code</A>.<BR>\n" .
          "        <FONT SIZE=\"-1\">Qadabra code copyright 2001 Sean C. Nichols</FONT>\n" .
          "    </FONT></BODY>\n" .
          "</HTML>\n";
}

sub fAuth {
    my ($sUser, $sPassword ) = @_;

    my $bResult;
    my $sSQL;
    my $sID;

    my $dbhQadabra;

    $sSQL = "SELECT id FROM $gsAuthTable WHERE name=\'$sUser\' AND " .
            "password=PASSWORD( \'$sPassword\' );";

    # Connect
    $dbhQadabra = DBI->connect( $gsConnect, $gsDBUser, $gsDBPassword );

    # Get search result
    $sID = $dbhQadabra->selectrow_array( $sSQL );

    # Disconnect
    $dbhQadabra->disconnect;

    # A couple of things to explain. Returning the query result in scalar context
    #  just returns the first field (in this case, "id"). Now, id will never be
    #  null, so a null (actually undef) value means that no records returned. If
    #  we got any record match at all, our $sID will not be null (in other words,
    #  it will evaluate as True). Returning this means that we can test in a
    #  boolean context whether we got a match or not. Or if we want, we can just
    #  simply grab the ID. Either way, as per our needs.

    return $sID;
}

sub fShowLoginForm {
    my ($sUser) = @_;

    print "        Login to Qadabra:<BR>\n" .
          "        <FORM NAME=\"login\" METHOD=\"post\" ACTION=\"/cgi-bin/qadabra.pl\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"op\" VALUE=\"auth\">\n" .
          "            <TABLE BORDER=\"0\">\n" .
          "                <TR>\n" .
          "                    <TD>Username:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sUser\" MAXLENGTH=\"40\" NAME=\"user\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Password:</TD>\n" .
          "                    <TD><INPUT TYPE=\"password\" SIZE=\"35\" VALUE=\"\" MAXLENGTH=\"40\" NAME=\"password\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>&nbsp;</TD>\n" .
          "                    <TD><INPUT TYPE=\"submit\" VALUE=\"Login\"></TD>\n" .
          "                </TR>\n" .
          "            </TABLE>\n" .
          "        </FORM>\n";
}

sub fShowResultForm {
    my ($sUser, $sPassword, $iNumConditions) = @_;

    my $iCount;

    # Ensure it's within the range 1..15 (if it's too big, the HTML generated will
    #  be larger than ideal)
    if ($iNumConditions < 1) {
        $iNumConditions = 1;
    } elsif ($iNumConditions > 15) {
        $iNumConditions = 15;
    }

    print "        Show all entries where:<BR><BR>\n" .
          "        <FORM NAME=\"getentry\" METHOD=\"post\" ACTION=\"/cgi-bin/qadabra.pl\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"op\" VALUE=\"read\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"user\" VALUE=\"$sUser\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"password\" VALUE=\"$sPassword\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"conditions\" VALUE=\"$iNumConditions\">\n" .
          "            <TABLE BORDER=\"0\">\n" .
          "                <TR>\n" .
          "                    <TD>&nbsp;</TD>\n" .
          "                    <TD>\n" . fReturnFieldOption( 0 ) .
          "                    </TD>\n" .
          "                    <TD ALIGN=\"right\">= <INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"\" MAXLENGTH=\"40\" NAME=\"fieldval0\"></TD>\n" .
          "                </TR><TR>\n";

    if ($iNumConditions > 1) {
        for ($iCount = 1; $iCount < $iNumConditions; $iCount++) {
            print "                    <TD>\n" . fReturnBoolOption( $iCount ) .
                  "                    </TD>\n" .
                  "                    <TD>\n" . fReturnFieldOption( $iCount ) .
                  "                    </TD>\n" .
                  "                    <TD ALIGN=\"right\">= <INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"\" MAXLENGTH=\"40\" NAME=\"fieldval$iCount\"></TD>\n" .
                  "                </TR><TR>\n";
        }
    }

    print "                    <TD>&nbsp;</TD>\n" .
          "                    <TD>&nbsp;</TD>\n" .
          "                    <TD ALIGN=\"right\"><INPUT TYPE=\"submit\" VALUE=\"Submit\"></TD>\n" .
          "                </TR>\n" .
          "            </TABLE>\n" .
          "        </FORM>\n";
}

sub fShowResults {
    my ($sUser, $sPassword) = @_;

    my $iCount;
    my $iUserNum;
    my $sField;
    my $sValue;
    my $sBool;
    my $iNumConds;

    # First get the user id number (at the same time verifying user/password).
    $iUserNum = fAuth( $sUser, $sPassword );

    $iNumConds = param( "conditions" );

    my @sRecord;
    my $dbhQadabra;
    my $rstQadabra;

    # Now create the SQL statement.
    my $sSQL = "SELECT " . join( ", ", @gsFields ) . ", id FROM $gsTable " .
               "WHERE user=\'$iUserNum\'";

    for ($iCount = 0; $iCount < $iNumConds; $iCount++) {
        $sBool =  fGetSQL( "bool$iCount", $TRUE );
        $sField = fGetSQL( "fieldname$iCount", $TRUE );
        $sValue = fGetSQL( "fieldval$iCount", $FALSE );

        if (not $sBool) {
            $sBool = "AND";
        }

        if ($sField) {
            $sSQL .= " $sBool $sField=\'$sValue\'";
        }
    }

    $sSQL .= ";";

    # Connect
    $dbhQadabra = DBI->connect( $gsConnect, $gsDBUser, $gsDBPassword );

    # Get rows
    $rstQadabra = $dbhQadabra->prepare( $sSQL );
    $rstQadabra->execute;

    # Loop through each returned row
    while (@sRecord = $rstQadabra->fetchrow_array) {
        # Format the email, homepage and comment fields to display as desired
        if ($sRecord[ $email ]) {
           $sRecord[ $email ] = "<A HREF=\"mailto:$sRecord[ $email ]\">$sRecord[ $email ]</A>";
        }
        if ($sRecord[ $homepage ]) {
           $sRecord[ $homepage ] = "<A HREF=\"http://$sRecord[ $homepage ]\">$sRecord[ $homepage ]</A>";
        }
        $sRecord[ $comment ] =~ s/\n/<BR>/g;

        # Output appropriate HTML
        print "        <FORM METHOD=\"post\" ACTION=\"/cgi-bin/qadabra.pl\">\n" .
              "            <INPUT TYPE=\"hidden\" NAME=\"op\" VALUE=\"read\">\n" .
              "            <INPUT TYPE=\"hidden\" NAME=\"user\" VALUE=\"$sUser\">\n" .
              "            <INPUT TYPE=\"hidden\" NAME=\"password\" VALUE=\"$sPassword\">\n" .
              "            <INPUT TYPE=\"hidden\" NAME=\"recnum\" VALUE=\"$sRecord[ $id ]\">\n" .
              "            <TABLE BORDER=\"0\">\n" .
              "                <TR>\n" .
              "                    <TD>Last Name:</TD>\n" .
              "                    <TD>$sRecord[ $lname ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>First Name:</TD>\n" .
              "                    <TD>$sRecord[ $fname ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Telephone:<BR>&nbsp;</TD>\n" .
              "                    <TD>$sRecord[ $areacode ] $sRecord[ $telephone ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Street Address:</TD>\n" .
              "                    <TD>$sRecord[ $street_address ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Town / City:</TD>\n" .
              "                    <TD>$sRecord[ $town_address ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>State / Province:</TD>\n" .
              "                    <TD>$sRecord[ $stateprov_address ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>ZIP / Postal Code:</TD>\n" .
              "                    <TD>$sRecord[ $zippostal_address ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Country:<BR>&nbsp;</TD>\n" .
              "                    <TD>$sRecord[ $country_address ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>E-mail:</TD>\n" .
              "                    <TD>$sRecord[ $email ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Homepage:<BR>&nbsp;</TD>\n" .
              "                    <TD>$sRecord[ $homepage ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>Comment:</TD>\n" .
              "                    <TD>$sRecord[ $comment ]</TD>\n" .
              "                </TR><TR>\n" .
              "                    <TD>&nbsp;</TD>\n" .
              "                    <TD ALIGN=\"right\"><INPUT TYPE=\"submit\" VALUE=\"Update This Record\"></TD>\n" .
              "                </TR>\n" .
              "            </TABLE>\n" .
              "        </FORM>\n";

        fShowBreak();
    }

    # Disconnect
    $dbhQadabra->disconnect;
}

sub fShowEntryForm {
    my ($sUser, $sPassword, $iRecordNum) = @_;

    my @sRecord;

    my $sButtonText = $iRecordNum ? "Update" : "Add";
    my $sOperation = $iRecordNum ? "updated" : "write";

    my $sRequired = "<FONT COLOR=\"#e70000\"><STRONG>*</STRONG></FONT>";

    if ($iRecordNum) {
        @sRecord = fGetFields( $sUser, $sPassword, $iRecordNum, $TRUE );
    }

    print "        Add Qadabra Entry:<BR><BR>\n" .
          "        <FONT SIZE=\"-1\">($sRequired indicates a required field)</FONT><BR><BR>\n" .
          "        <FORM NAME=\"addentry\" METHOD=\"post\" ACTION=\"/cgi-bin/qadabra.pl\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"op\" VALUE=\"$sOperation\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"user\" VALUE=\"$sUser\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"password\" VALUE=\"$sPassword\">\n" .
          "            <INPUT TYPE=\"hidden\" NAME=\"recnum\" VALUE=\"$iRecordNum\">\n" .
          "            <TABLE BORDER=\"0\">\n" .
          "                <TR>\n" .
          "                    <TD>Last Name:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $lname ]\" MAXLENGTH=\"40\" NAME=\"lname\"> $sRequired</TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>First Name:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $fname ]\" MAXLENGTH=\"40\" NAME=\"fname\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Telephone:<BR>&nbsp;</TD>\n" .
          "                    <TD>\n" .
          "                        <INPUT TYPE=\"text\" SIZE=\"6\" VALUE=\"$sRecord[ $areacode ]\" MAXLENGTH=\"6\" NAME=\"areacode\">\n" .
          "                        <INPUT TYPE=\"text\" SIZE=\"8\" VALUE=\"$sRecord[ $telephone ]\" MAXLENGTH=\"8\" NAME=\"telephone\"><BR>&nbsp;\n" .
          "                    </TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Street Address:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $street_address ]\" MAXLENGTH=\"40\" NAME=\"street_address\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Town / City:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $town_address ]\" MAXLENGTH=\"40\" NAME=\"town_address\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>State / Province:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $stateprov_address ]\" MAXLENGTH=\"40\" NAME=\"stateprov_address\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>ZIP / Postal Code:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"7\" VALUE=\"$sRecord[ $zippostal_address ]\" MAXLENGTH=\"7\" NAME=\"zippostal_address\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Country:<BR>&nbsp;</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"35\" VALUE=\"$sRecord[ $country_address ]\" MAXLENGTH=\"40\" NAME=\"country_address\"><BR>&nbsp;</TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>E-mail:</TD>\n" .
          "                    <TD><INPUT TYPE=\"text\" SIZE=\"40\" VALUE=\"$sRecord[ $email ]\" MAXLENGTH=\"50\" NAME=\"email\"></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Homepage:<BR>&nbsp;</TD>\n" .
          "                    <TD>http:// <INPUT TYPE=\"text\" SIZE=\"40\" VALUE=\"$sRecord[ $homepage ]\" MAXLENGTH=\"50\" NAME=\"homepage\"><BR>&nbsp;</TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>Comment:</TD>\n" .
          "                    <TD><TEXTAREA NAME=\"comment\" ROWS=\"5\" COLS=\"35\">$sRecord[ $comment ]</TEXTAREA></TD>\n" .
          "                </TR><TR>\n" .
          "                    <TD>&nbsp;</TD>\n" .
          "                    <TD><INPUT TYPE=\"submit\" VALUE=\"$sButtonText\"></TD>\n" .
          "                </TR>\n" .
          "            </TABLE>\n" .
          "        </FORM>\n";
}

sub fShowStatus {
    my ($sStatusMsg) = @_;

    print "        <FONT COLOR=\"#00e700\">$sStatusMsg</FONT><BR><BR>\n";
}

sub fShowBreak {
    print "\n" .
          "        <HR ALIGN=\"left\" WIDTH=\"90%\">\n\n";
}

sub fReturnFieldOption {
    # Returns a drop-down listbox with all field names.

    my ($iNum) = @_;

    my $sOutput = "                        <SELECT NAME=\"fieldname$iNum\">\n" .
                  "                            <OPTION VALUE=\"\" SELECTED>---\n" .
                  "                            <OPTION VALUE=\"lname\">Last Name\n" .
                  "                            <OPTION VALUE=\"fname\">First Name\n" .
                  "                            <OPTION VALUE=\"areacode\">Area Code\n" .
                  "                            <OPTION VALUE=\"telephone\">Telephone\n" .
                  "                            <OPTION VALUE=\"street_address\">Street Address\n" .
                  "                            <OPTION VALUE=\"town_address\">Town / City\n" .
                  "                            <OPTION VALUE=\"stateprov_address\">State / Province\n" .
                  "                            <OPTION VALUE=\"zippostal_address\">ZPI / Postal Code\n" .
                  "                            <OPTION VALUE=\"country_address\">Country\n" .
                  "                            <OPTION VALUE=\"email\">E-mail\n" .
                  "                            <OPTION VALUE=\"homepage\">Homepage\n" .
                  "                        </SELECT>\n";
}

sub fReturnBoolOption {
    # Returns a drop-down listbox with And/Or options.

    my ($iNum) = @_;

    my $sOutput = "                        <SELECT NAME=\"bool$iNum\">\n" .
                  "                            <OPTION VALUE=\"AND\" SELECTED>AND\n" .
                  "                            <OPTION VALUE=\"OR\">OR\n" .
                  "                        </SELECT>\n";

    return $sOutput;
}

sub fGetFields {
    # Returns all the fields from the given record.

    my ($sUser, $sPassword, $iRecord, $bValidate) = @_;

    my $iCount;
    my $sSQL;

    # If we don't have to validate user/password (ie: if we've already done it),
    #  then we can save a bit of time by not doing it here.
    if ($bValidate) {
        $sSQL = "SELECT a." . join( ", a.", @gsFields ) . " FROM $gsTable AS " .
                "a, $gsAuthTable AS b WHERE a.id=\'$iRecord\' AND a.user=b.id AND " .
                "b.name=\'$sUser\' AND b.password=PASSWORD( \'$sPassword\' );";
    } else {
        $sSQL = "SELECT " . join( ", ", @gsFields ) . " FROM $gsTable " .
                "WHERE id=\'$iRecord\');";
    }

    my @sFields;
    my $dbhQadabra;

    # Connect
    $dbhQadabra = DBI->connect( $gsConnect, $gsDBUser, $gsDBPassword );

    # Get search result
    @sFields = $dbhQadabra->selectrow_array( $sSQL );

    # Disconnect
    $dbhQadabra->disconnect;

    return @sFields;
}

sub fAddRecord {
    # Gets the fields submitted, validates them, and creates a new record, if
    #  applicable. If there are any errors, these errors are logged in $gsError.

    my ($sUser, $sPassword) = @_;

    my $iUserNum;
    my $sSQL;
    my @sRecord;

    my $dbhQadabra;

    $gsError = "";

    for (@gsFields) {
        push( @sRecord, fGetSQL( $_, $FALSE ) );
    }

    my $bResult = $TRUE;

    # This is the primary field, so it had better be right.
    if ((not $sRecord[ $lname ]) or ($sRecord[ $lname ] eq "_ERROR_")) {
        $bResult = $FALSE;
        $gsError .= "Invalid last name supplied.\n";
    }

    # Generate SQL
    $iUserNum = fAuth( $sUser, $sPassword );
    $sSQL = "INSERT INTO $gsTable (user, " . join( ", ", @gsFields ) .
            ") VALUES ($iUserNum, \'" . join( "\', \'", @sRecord ) . "\');";

    # Connect
    $dbhQadabra = DBI->connect( $gsConnect, $gsDBUser, $gsDBPassword );

    # Insert
    if (not $dbhQadabra->do( $sSQL ) ) {
        $bResult = $FALSE;
        $gsError .= "$dbhQadabra->errstr\n";
    }

    # Disconnect
    $dbhQadabra->disconnect;

    # Return "lname, fname" (if applicable)
    return $bResult ? ($sRecord[ $lname ] .
                       ($sRecord[ $fname ] ? ", $sRecord[ $fname ]" : "")) : 0;
}

sub fGetSQL {
    # A wrapper around the CGI module's param() function that does additional
    #  checks on the return value to make sure that it's ok to put directly into
    #  an SQL expression. Basically prevents haxoring of the type where you
    #  enter "'; DROP DATABASE mysql;" or a permutation thereof to trick the script
    #  into fuX0ring mysql. If any "naughty SQL" is encountered, this function will
    #  render it harmless (or try to, anyway) and pass it on, unless explicitly
    #  bad SQL is encountered, in which case, it will just return an error.
    #
    # The idea being that a lot of potentially bad SQL is actually innocent (like
    #  people trying to add an apostraphe and wind up ending the quoted SQL string).
    #  This is forgiveable, and is easily nullified. But people don't write stuff
    #  like "DROP TABLE" by accident. And if they try to pull stuff like that,
    #  there's likely other "bad" SQL in there as well, that I might not notice,
    #  and certainly don't want to take a chance on. Besides, they've already proven
    #  themselves untrustworthy, so I don't see any reason to give them any sympathy.

    my ($sParam, $bStrict) = @_;

    my $sReturn = param( $sParam );

    if ($bStrict) {
        # This only allows a single, alphanumeric word.

        s/^(\w*).*/$1/ for $sReturn;
    } else {
        # This is a bit more liberal, but still doesn't allow trick SQL.

        # First have to ensure we escape any \, ' or ; chars (this should get rid of
        #  most problems right off the bat).

        for ($sReturn) {
            s/\\/\\\\/g;
            s/'/\\'/g;
            s/;/\\;/g;
        }

        # Now for the explicitly naughty stuff.
        for ($sReturn) {
            if ((m/drop table/i)    ||
                (m/drop database/i) ||
                (m/delete from/i)   ||
                (m/update.*set/i)   ||
                (m/select.*into/i))    {

                # Bad juju.
                $sReturn = "_ERROR_";
            }
        }
    }

    return $sReturn;
}

sub fUpdateRecord {
    # Gets the fields submitted, validates them, and updates the indicated record, if
    #  applicable. If there are any errors, these errors are logged in $gsError.

    my ($sUser, $sPassword, $iRecNum) = @_;

    my $iUserNum;
    my $sSQL;
    my @sRecord;

    my $dbhQadabra;

    $gsError = "";

    for (@gsFields) {
        push( @sRecord, fGetSQL( $_, $FALSE ) );
    }

    my $bResult = $TRUE;

    # This is the primary field, so it had better be right.
    if ((not $sRecord[ $lname ]) or ($sRecord[ $lname ] eq "_ERROR_")) {
        $bResult = $FALSE;
        $gsError .= "Invalid last name supplied.\n";
    }

    # Generate SQL
    $iUserNum = fAuth( $sUser, $sPassword );

    ###########
    ## TODO: ##
    ###########
    $sSQL = "";

    # Connect
    $dbhQadabra = DBI->connect( $gsConnect, $gsDBUser, $gsDBPassword );

    # Insert
    if (not $dbhQadabra->do( $sSQL ) ) {
        $bResult = $FALSE;
        $gsError .= "$dbhQadabra->errstr\n";
    }

    # Disconnect
    $dbhQadabra->disconnect;

    # Return "lname, fname" (if applicable)
    return $bResult ? ($sRecord[ $lname ] .
                       ($sRecord[ $fname ] ? ", $sRecord[ $fname ]" : "")) : 0;
}