#!/usr/bin/perl -T -w

use CGI;
$q = new CGI;

print $q->header, $q->start_html('Query results'), $q->h1('Query results');

print $q->p($q->b("Don't"), "include the trailing semicolon. The database name to query on is ", $q->tt("testbooks2"), ".");

print $q->table({-border=>1},
        $q->caption('Common Queries'),
        $q->Tr({-align=>LEFT},[
            $q->td(['select publisher, count(*) from testbooks2 group by publisher order by count(*) desc, publisher', 'Returns the publishers and the number of books that have them, in descending order.']),
            $q->td(['select * from testbooks2 where publisher ~ \'O\\\'Reilly\' order by title', 'Returns all books published by O\'Reilly.']),
            $q->td(['select * from testbooks2 where categories ~ \'CS\' order by title', 'Returns all books in category CS.'])
            ]
            ));

print $q->start_multipart_form(),
    "Enter query: ",
    $q->textfield('rawquery'),
    $q->br,
    $q->submit('submit','Process query'), $q->reset(),
    $q->endform();

my $newquery = $q->param('rawquery');
#if (defined($newquery) && !($newquery eq ""))  {
#    $query = $newquery;
#} else {
    $query = "select * from testbooks2 order by title";
#}
# Taint check
if ($query =~ /^([\w0123456789'"\\.*=~(), %&]+)$/) {
    $query = $1;
} else {
    print $q->p("Taint checking failed!");
    die("Taint checking failed!");
}
if ($query !~ m/^\w*SELECT/i) {
    print $q->p("Taint checking failed!");
    die("Taint checking failed!");
}
$query = "$query;";
print $q->p('Query was:'), $q->pre($query);
#$command = "psql -H -l";
#@output = `$command`;
#print $q->p(@output);
$command = "/usr/bin/psql realbooks -H -c \"$query\"";
$ENV{PATH} = "/bin:/usr/bin";
@output = `$command`;
print $q->p(@output);

print $q->end_html;