#!/usr/bin/perl -w
#QED: Question Editing Database
#(C) 2006-2007 by Carlo Angiuli
use CGI::Carp 'fatalsToBrowser';
use strict;
use warnings;
use DBI;
use CGI;
use Digest::MD5 'md5_hex';
#use MIME::Lite; # These two are imported later if needed.
#use Net::SMTP;
my ($mail_from, $org_name,
$mysql_database, $mysql_host, $mysql_port, $mysql_user, $mysql_pass,
@types, @categories, $dtbu, $dtbq, $isadmin, $isround);
##########################
##### User Variables #####
##########################
$mail_from = '';
$org_name = "";
$mysql_database = "";
$mysql_host = "";
$mysql_port = 3306;
$mysql_user = "";
$mysql_pass = "";
$dtbu = "";
$dtbq = "";
@types = ("Tossup", "Bonus");
@categories = (
"Fine Arts: Music",
"Fine Arts: Visual Art",
"Fine Arts: Other",
"Literature: Language Arts",
"Literature: Literature",
"Literature: Mythology",
"Math: Algebra",
"Math: Calculus",
"Math: General",
"Math: Geometry",
"Math: Other",
"Miscellaneous: Entertainment",
"Miscellaneous: Interdisciplinary",
"Miscellaneous: Sports",
"Miscellaneous: Technology",
"Miscellaneous: Other",
"Science: Astronomy",
"Science: Biology",
"Science: Chemistry",
"Science: Earth Science",
"Science: Physics",
"Social Studies: Current Events",
"Social Studies: Geography",
"Social Studies: U.S. History",
"Social Studies: World History",
"Social Studies: Other"
);
##########################
###### General Code ######
##########################
my $prgm_version = "1.40";
sub login_screen;
sub gui;
sub gui_home;
sub edit_question;
sub new_question;
sub gui_edit;
sub gui_list;
sub gui_list_table;
sub search;
sub gui_about;
sub gui_delete;
sub gui_delete_done;
sub gui_search;
sub admin_backup;
sub admin_gui_config;
sub admin_gui_users;
sub admin_gui_stats;
sub admin_gui_rounds;
sub round_gui_options;
sub round_gui_acquire;
sub round_gui_acquire_table;
sub round_acquire;
sub round_unacquire;
sub round_gui_organize;
sub round_organize;
sub round_gui_order;
sub round_order;
sub round_gui_stats;
sub round_gui_output;
sub round_output;
sub order_string;
sub rtrim;
sub ceiling;
sub parseget;
sub output_question;
print "Content-type: text/html\n\n";
my $cgi= new CGI;
my $ip = $ENV{'REMOTE_ADDR'};
my $filename = $ENV{'SCRIPT_NAME'};
my $pagename = lc($ENV{'SCRIPT_URL'}); #follows qed.pl/ in URL
$pagename =~ /$filename\/(.*)/;
$pagename = $1;
my $query;
my ($username, $password, $round);
for (split(';',$ENV{'HTTP_COOKIE'})) {
if (/qed=([a-zA-Z0-9]+)\|([a-f0-9]+)/) {
$username = $1;
$password = $2;
} elsif (/qed-rnd=([a-zA-Z0-9]+)/) {
$round = $1;
}
}
#CREATE TABLE users (
# user CHAR(10),
# password CHAR(32),
# flags INT(11));
#The password column contains the md5 hash of the password,
#or if it's a round, the number of packets it contains.
#
#CREATE TABLE questions (
# id INTEGER AUTO_INCREMENT PRIMARY KEY,
# type TEXT,
# category TEXT,
# question TEXT,
# answer TEXT,
# owner TEXT,
# round TEXT,
# comment TEXT,);
my $dbh = DBI->connect("DBI:mysql:database=${mysql_database};host=${mysql_host};port=${mysql_port}",$mysql_user,$mysql_pass);
$query = $dbh->prepare("SELECT * FROM $dtbu WHERE user=?");
$query->execute($username);
my @sqluser = $query->fetchrow_array();
$query->finish();
#A cookie is set client-side when logging in, with the format qed=(username)|(md5hash),
#where the hash is calculated client-side, so only the username is transmitted plaintext.
#The hash is md5(ip . md5(pass)), so only the same IP address can use that hash.
if ($password ne md5_hex($ENV{'REMOTE_ADDR'} . $sqluser[1])) {
login_screen;
} elsif ($pagename =~ /^output=(\d{1,2})/) {
round_output($1);
} elsif ($pagename =~ /^delete=(\d)/) {
gui_delete;
} elsif ($cgi->param("cgi") && $cgi->param("delete")) {
gui_delete_done;
} elsif ($sqluser[2] == 0) {
$round = ""; gui;
} elsif ($sqluser[2] == 1) {
$isadmin = 1; gui;
}
##########################
#### Normal Interface ####
##########################
sub login_screen {
print qq`
QED $prgm_version | Login
QED
Question Editing Database
Version $prgm_version
$org_name
©2006-2007 by Carlo Angiuli
`;
exit;
}
sub gui {
my $usertype = ($isadmin ? "Admin" : "Writer");
my $usercolor = ($isadmin ? "#ff0000" : "#000000");
my $usercss = ($isadmin ? "#menu a.admin {color: #ee6897;}
#menu a.admin:hover {background-color: #f4c5cb;}
#menu a.round {color: #2e8b57;}
#menu a.round:hover {background-color: #bce9bf;}
#adminusers td {padding-right:1em;}
.roundlist {text-decoration: none; color:#000000; font-size:1.2em;}
.roundlist:hover {text-decoration:underline;}
.orgqs td {padding-right:.7em;}
" : "#questions {width: 85%;}");
my $userjs = ($pagename =~ /^round-acquire/ ? qq` onload="count();"` : "");
print qq`
QED $prgm_version | $usertype
QED $prgm_version |
$org_name |
$username ($usertype) |
Log out
`;
admin_backup if $cgi->param("backup-yes") && $isadmin;
edit_question if $cgi->param("cgi") && $cgi->param("edit");
new_question if $cgi->param("cgi") && not($cgi->param("edit"));
if ($cgi->param("acquire") && $isadmin) {
$cgi->delete("acquire");
round_acquire($cgi->param());
}
if ($isadmin && $pagename =~ /^admin-config/) {admin_gui_config;}
elsif ($isadmin && $pagename =~ /^admin-users/) {admin_gui_users;}
elsif ($isadmin && $pagename =~ /^admin-stats/) {admin_gui_stats;}
elsif ($isadmin && $pagename =~ /^admin-rounds/) {admin_gui_rounds;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-stats/) {round_gui_stats;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-options/) {round_gui_options;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-acquire/) {round_gui_acquire;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-organize/) {round_gui_organize;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-order/) {round_gui_order;}
elsif ($isadmin && $round ne "" && $pagename =~ /^round-output/) {round_gui_output;}
elsif ($pagename =~ /^list/) {gui_list;}
elsif ($pagename =~ /^about/) {gui_about;}
elsif ($pagename =~ /^edit/) {gui_edit;}
else {gui_home;}
print qq`