I have an assignment which requires us to create a quiz form in perl cgi accepting new questions from our local web browser. Each insertion of question need to be updated in state table, and at the end should display number of questions exists in database as well as new questions inserted at each session.I used HTML for forms and Mysql as my database and running on localhost. When I try to add a new quesion the only thing that makes it into the table is the Question, nothing else gets added. I can see the values in the URL being passed to the seerver but the data never makes it to the SQL statment. Please help.

#! /usr/bin/perl #use strict; #use warnings; #use diagnostics; print "Content-type: text/html



"; if ($ENV{"REQUEST_METHOD"} eq "POST") { read(STDIN, $datastring, $ENV{"CONTENT_LENGTH"}); } elsif (exists $ENV{"REQUEST_METHOD"}) { # data from GET transaction (or HEAD or other) $datastring = $ENV{"QUERY_STRING"}; } else { print "Offline execution detected

"; print "Please enter some data.

"; $datastring = <>; chomp $datastring; print "== data accepted == HTML output follows ==



"; } ###decode###################################################### $datastring =~s/%0D%0A/

/g; #step to deal with line #breaks in text areas @nameValuePairs = split(/&/, $datastring); #step 1 foreach $pair (@nameValuePairs) { ($name, $value) = split(/=/, $pair); #step 2 $name =~tr/+/ /; #step 3 $name =~s/%([\da-fA-F]{2})/pack("C",hex($1))/eg; #step 3 $value =~tr/+/ /; #step 3 $value =~s/%([\da-fA-F]{2})/pack("C",hex($1))/eg; #step 3 if(exists $formHash{$name}) { #improved step 4, $formHash{$name} = $formHash{$name}.";".$value; #now handles multiple } #select menus else { $formHash{$name} = $value; } } ###done decoding############################################### ### global variables ########################################## use DBI; $dbhandle = DBI->connect("DBI:mysql:databasexx", "idyy", "passzz") or &errorPage("Can't connect to database". DBI->errstr()); $file_life_span = 1.0/24; # in days (so is 1 hours) $time_out = 1.0/24; $time_out = 1.0/24; # in days $cache_limit = 300; $state_table_name = "stable"; # name of state table $quiz_table_name = "qtable"; # name of quiz table %stateHash=(); ### end of global variables ##################################### ### app logic ################################################### if($formHash{"request"} eq "menu") { &menu; } elsif($formHash{"request"} eq "add") { &add; } elsif($formHash{"request"} eq "add2") { &add2; } elsif($formHash{"request"} eq "list") { &list; } else { &welcome; } ### end app logic ################################################ ################################################################## sub welcome{ my $sessionID = &get_long_id_db($dbhandle, $state_table_name, $cache_limit, $file_life_span); $qnumber=1; %stateHash = ("qnumber"=>$qnumber); &write_state_db($dbhandle, $state_table_name, $sessionID, %stateHash); print <<PAGE; <html><head><title>Welcome</title></head> <body> <h2>Welcome</h2> <form action="$ENV{SCRIPT_NAME}" method="GET"> <input type="hidden" name="qnumber" value="$qnumber"> <input type="hidden" name="id" value="$sessionID"/> <input type="hidden" name="request" value="menu"> <input type="submit" value="Main Menu"> </form> </body> </html> PAGE } ################################################################## sub menu{ my $sessionID = $formHash{"id"}; my $qnumber = $fromHash{"qnumber"}; print <<PAGE; <html><head><title>Menu</title></head> <body> <form action="$ENV{SCRIPT_NAME}" method="GET"> <input type="hidden" name="qnumber" value="$qnumber"/> <input type="hidden" name="id" value="$sessionID"/> List the questions.<br> <button type="submit" name="request" value="list">List Questions</button> <br><br> Add a question.<br> <button type="submit" name="request" value="add">Add Question</button> <br><br> </form> </body> </html> PAGE } ################################################################## sub add{ my $sessionID = $formHash{"id"}; my $qnumber = $fromHash{"qnumber"}; $sql = "SELECT * from $quiz_table_name"; $qObj = $dbhandle -> prepare($sql); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> fetchall_arrayref(); # Fetch all rows, no need to use them my $number_of_questions = $qObj->rows(); $qObj -> finish(); print <<PAGE; <html><head><title>Add Question</title></head> <body> There are $number_of_questions in the database now.<br> This will be your $qnumber question this session. <form action="$ENV{SCRIPT_NAME}" method="GET"> <input type="hidden" name="qnumber" value="$qnumber"/> <input type="hidden" name="id" value="$sessionID"/> <input type="hidden" name="request" value="add2"> <br/> Enter the Question.<br> <INPUT TYPE="text" NAME="question" VALUE="Question"><br> Correct Answer.<br> <INPUT TYPE="text" NAME="answer" VALUE="Answer"><br> Choce #1.<br> <INPUT TYPE="text" NAME="choice1" VALUE="1"><br> Choce #2.<br> <INPUT TYPE="text" NAME="choice2" VALUE="2"><br> Choce #3.<br> <INPUT TYPE="text" NAME="choice3" VALUE="3"><br> Choce #4.<br> <INPUT TYPE="text" NAME="choice4" VALUE="4"><br> Choce #5.<br> <INPUT TYPE="text" NAME="choice5" VALUE="5"><br> <br/> <input type="submit" value="Submit"> </form> </body> </html> PAGE } ################################################################## sub add2{ my $sessionID = $formHash{"id"}; my $qnumber = $fromHash{"qnumber"}; my $question = $formHash{"question"}; my $answer = $fromHash{"answer"}; my $choice1 = $fromHash{"choice1"}; my $choice2 = $fromHash{"choice2"}; my $choice3 = $fromHash{"choice3"}; my $choice4 = $fromHash{"choice4"}; my $choice5 = $fromHash{"choice5"}; $stateHash{"qnumber"}++; # The next question number. &write_state_db($dbhandle, $state_table_name, $sessionID, %stateHash); $sql = "INSERT INTO $quiz_table_name(question, answer, choice1, choice2, choice3, choice4, choice5) VALUES(?,?,?,?,?,?,?)"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare"); $qObj -> execute($question, $answer, $choice1, $choice2, $choice3, $choice4, $choice5) or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> finish(); $request = menu; } ################################################################## sub list { my $sessionID = $formHash{"id"}; my $qnumber = $fromHash{"qnumber"}; print <<PAGE; <html><head><title>List Questions</title></head> <body> <h2>List Questions</h2><br/> <style> table, th, td { border: 1px solid black; } th { text-align: left; } </style> <table> <tr><th>Number</th><th>Question</th><th>Answer</th><th>Choice 1</th><th>Choice 2</th><th>Choice 3</th><th>Choice 4</th><th>Choice 5</th></tr> PAGE # DEFINE A MySQL QUERY $sql = "SELECT qnumber, question, answer, choice1, choice2, choice3, choice4, choice5 FROM $quiz_table_name"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare"); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); my $arry_ref = $qObj->fetchall_arrayref(); $qObj -> finish(); foreach my $row(@$arry_ref) { my ($qnumber, $question, $answer, $choice1, $choice2, $choice3, $choice4, $choice5) = @$row; print "<tr><th>$qnumber</th><th>$question</th><th>$answer</th><th>$choice1</th><th>$choice2</th><th>$choice3</th><th>$choice4</th><th>$choice5</th></tr>"; } # PRINT THE RESULTS print <<BOTTOM; </table> </body> </html> <form action="$ENV{SCRIPT_NAME}" method="POST"> <input type="hidden" name="qnumber" value="$qnumber"/> <input type="hidden" name="id" value="$sessionID"/> <input type="hidden" name="request" value="menu"> <input type="submit" value="Main Menu"> </form> </body> </html> BOTTOM } ################################################################# ################################################################# # end app logic functions # begin toolkit functions ################################################################# ################################################################# ################################################################# sub write_state_db { my ($dbhandle, $table_name, $sessionID, %states) = @_; ### add the updated last-modified time to the front of the incoming state hash my $currtime = time; my @updates = ("last_modified = '$currtime'"); foreach $key (keys %states){ push @updates, "$key = '$states{$key}'"; } ### update the state record $sql = "UPDATE $table_name set " . join(",", @updates) . " WHERE id = '$sessionID'"; $qObj = $dbhandle -> prepare($sql); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> finish(); } ################################################################# sub read_state_db { my ($dbhandle, $table_name, $sessionID, $time_out, $time_out_function, $time_out_message) = @_; ### $time_out is in days ### read the desired state record into the query object $sql = "SELECT * FROM $table_name WHERE ID = '$sessionID'"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare."); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); my $rowhashref = $qObj->fetchrow_hashref(); $qObj -> finish(); if(! $rowhashref) { ### $rowhashref is an empty reference, which means no such id... &errorPage("No such session."); } my %hash = %$rowhashref; ### get the actual hash containing the state record ### timeout test if(($time_out > 0) && ($hash{"last_modified"} < time - $time_out*24*60*60)){ ### timed out... if($time_out_function) { &$time_out_function($time_out_message); exit; } else{ &errorPage("Your session has timed out"); } } ### touch the record $sql = "UPDATE $table_name SET last_modified = " . time . " WHERE ID = '$sessionID'"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare."); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> finish(); ### only need to return the actual state data delete $hash{"id"}; delete $hash{"last_modified"}; return %hash; } ################################################################# sub get_long_id_db { my ($dbhandle, $table_name, $cache_limit, $file_life_span) = @_; ### count number of sessions my $sql = "SELECT id FROM $table_name"; my $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare."); $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> fetchall_arrayref(); if($qObj->rows() >= $cache_limit) { ### Need to police table? my $expiredtime = int(time - $file_life_span*24*60*60); ### in seconds ### $qObj -> finish(); ### police the table $sql = "DELETE FROM $table_name WHERE last_modified < $expiredtime"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare.");; $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> finish(); ### count number of sessions again $sql = "SELECT id FROM $table_name"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare.");; $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); $qObj -> fetchall_arrayref(); if($qObj->rows() >= $cache_limit) { ### still over limit? # should generate e-mail message to warn administrator &errorPage("Site busy. Please try again later."); } } $qObj -> finish(); my $id = &generate_random_string(32); my $currtime = time; ### create new state record $sql = "INSERT INTO $table_name (id, last_modified) values ('$id', $currtime)"; $qObj = $dbhandle -> prepare($sql) or &errorPage("Can't prepare.");; $qObj -> execute() or &errorPage("Can't execute " . $qObj->errstr()); return $id; } ################################################################# sub generate_random_string { my $n = $_[0]; my $result = ""; my @chars = (0..9, 'a'..'z', 'A'..'Z'); my $which; for($i = 1 ; $i <= $n ; $i++) { $which=int rand 62; $result = $result . $chars[$which]; } return $result; } ################################################################# sub errorPage { my $message = $_[0]; # the incoming parameter, store in localized variable print<<ALL; <html> <head> <title>Error encountered</title> </head> <body> <h1>Error Encountered</h1> <h3>$message</h3> Please try again, or report the problem to the webmaster. </body> </html> ALL exit; }