Form not parsing correctly

intranetman has asked for the wisdom of the Perl Monks concerning the following question:

I have a page which pulls a listing of out of office employees. Next to each name there is a delete button which allows users to delete themselves from the out of office list. My problem is concerned with how to delete the correct individual:

      $future[$n] = "<tr><td><form method=post>
             <input type=hidden name=confname
value=\'$firstName\'>
             <input type=hidden name=starttime
value=\'$lastName\'>
             <input type=hidden name=endtime
value=\'$start_date\'>
             <input type=hidden name=reservee
value=\'$end_date\'>
                     <input type=submit
value=delete></td><td><center>$firstName
$lastName</center><td><center>$email</center><td><center>$voicemail</c
+enter><td><center>$start_date2</center><td><center>$end_date2</center
+></form></tr>";
   print qq~<h3 align=center>Future Out of Office List
</h3>~;
   print qq~<br><br>~;
   print qq~The following will be out of the office
until the date specified:~;
   print qq~<br><br><br>~;
   print qq~<TABLE border="2"
align="center"><tr><th><th>Employee<th>Checking
E-mail<th>Checking Voicemail<th>Leaving<th>Returning~;
   for($i = 0; $i < @future; $i++) {      
      print qq~$future[$i]~;
   }
   print qq~</TABLE>~;
}
[download]

I then check to see if delete is pressed:

   if($session_cgi->param("delete")) {
      $firstName = $session_cgi->param("firstName");
$lastName = $session_cgi->param("lastName");
      $startdate = $session_cgi->param("start_date");
$enddate = $session_cgi->param("end_date"); 

      $db = dbLoad($db);
      $select_statement = "SELECT id FROM Employees
WHERE lastName = '$lastName' and firstName =
'$firstName'";
      $select_it = $db->prepare($select_statement);
      $rc = $select_it->execute() or die "Could not
run query '$select_statement'\n";
      $select_it->bind_columns(undef, \$user_id);
      $select_it->fetchrow_arrayref(); 

      $statement = "DELETE FROM Out_Of_Office WHERE
id = '$user_id' and start_date = '$start_date' and
end_date = '$end_date'";
      $statement = $db->prepare($statement);
      $rv = $statement->execute() or die "Couldn't
execute query '$query' \n";
      $db->disconnect();
[download]

However, nothing is ever deleted and the information still all the information appears on the screen. I've ensured that the date formats are the same as well as user ids, lastnames, firstnames, etc. I know I could do a join and maybe only use a delete statement, but that's for future improvements.

Comment on Form not parsing correctly Select or Download Code

Replies are listed 'Best First'.
Re: Form not parsing correctly by Zaxo (Archbishop) on Apr 26, 2005 at 21:44 UTC
I suspect that either your quoting is going awry, or your date formats are going wrong (In spite of your checking them). Try doing it with placeholders and see if that helps. `$statement = "DELETE FROM Out_Of_Office WHERE id = ? and start_date = ? and end_date = ?";` [download] That will also cure the SQL injection attack your code allows. After Compline, Zaxo	[reply] [d/l]
Re^2: Form not parsing correctly by intranetman (Acolyte) on Apr 27, 2005 at 16:21 UTC
Zaxo, I've never actually thought of using the placeholder technique. I've read up on some interesting use, thanks for the info very helpful. Also, I use mod_security and check/strip the input of any bad characters with a regex. If a user where to enter a SQL query of some sort, mod_security logs the attempt and throws an error. I think maybe the placeholder would add another layer of security...?	[reply]
Re: Form not parsing correctly by trammell (Priest) on Apr 26, 2005 at 21:59 UTC
Your form has this: `<input type=hidden name=starttime value=\'$lastName\'>` [download] and your Perl has this: `$startdate = $session_cgi->param("start_date");` [download] So I'm thinking you need to fix your parameter names.	[reply] [d/l] [select]
Re^2: Form not parsing correctly by Kevad (Scribe) on Apr 27, 2005 at 02:32 UTC
Good catch. The variable names are likely causing the problem. One additional suggestion I have... Instead of running the actual SQL query during your debugging process, try just printing the SQL statement out to the browser. That way you can make sure the SQL statement is constructed properly. It will also help determine if you are having a perl/cgi problem vs. an SQL problem.	[reply]
Re^3: Form not parsing correctly by intranetman (Acolyte) on Apr 27, 2005 at 15:53 UTC
Okay, I'm embarassed. I never actually passed a delete parameter so I was checking to see if delete was sent, but it never existed in the first place. Ack! Thanks for the extra set of eyes on this, I'm incorporating some of your suggestions.	[reply]