Editing Data in SQL database

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

I am trying to accomplish the following: Have users select data based on branch and then either print the data out or modify it by hitting a modify button. The printing works fine, as well as the button to take user to new screen. However, I want the new screen to automatically be populated with the correct data from the SQL database. I am (I think) sending the data from the first form into to the new page via a POST. But when I try to bring up the new page, I get a very generic error message of ‘500 Internal Server Error’. I have gotten this a lot while working on this project, but each time I have been able to figure out what I did wrong, and correct it with properly written PERL code. I am not able to figure this one out. My code to modify the data (to direct user to a new page) is as follows using AssetTag as the variable I want to pass to next screen (from inventory.pl):

if($c->param("searchTerm")) {
  while ($row = $q->fetchrow_hashref) {
    my $bgc = ($i % 2 == 0) ? 'background:#DDD;' : '';
    $results .= qq!<tr style="height:30px;border-bottom:1px;$bgc">
  <td>$row->{SerialNumber}</td>
  <td>$row->{AssetTag}</td>
  <td>$row->{AssetType}</td>
  <td>$row->{Branch}</td>
  <td>$row->{Status}</td>
  <td>$row->{Comments}</td>
  <td>
      <form method="post" action="modify.pl">
            <input type="hidden" name="AssetTag" value="$row->{AssetTa
+g}">
        <input type="submit" name="modify" value="Modify" style="font-
+size
     +:15px"/>
  </form>
  </td></tr>!;
    $i++;
  }
}

} else {
    $results .= "<b>No results matched your asset query.</b>";
}
[download]

The code for the new screen (modify.pl) is as follows:

#!perl

use DBI;
use CGI;

my $assetTag = param('AssetTag');

print "Content-Type: text/html\n\n";
$head = <<'HEAD';
<html>
<head>
    <title>Inventory Edit</title>
</head>
<body style="font-size:11px;font-family:tahoma;background:#F9F9DD;padd
+ing:0;margin:0;">
HEAD

$tail = <<'TAIL';
</body>
</html>
TAIL

$body = <<'BODY';
<div style="padding-left:20px;padding-top:10px;background:#F9F9DD;"><h
+1><font color="#006600"> Branch Inventory Edit</font></h1>
<p>This page allows you to edit the Branch Inventory.</p></div>
<div style="font-size:18px;letter-spacing:5px;padding-top:5px;text-ali
+gn:center;background:#F9F9DD;width:100%;">
<form action="http://webnm/cgi-bin/Modify.pl" method="get">
    <Table>
        <tr>
            <td>Asset Tag</td>
            <td><input style="font-size:15px;" type="text" name="asset
+tag"/></td></tr>
        <tr>
            <td>SerialNumber</td>
            <td><input style="font-size:15px;" type="text" name="Seria
+lNumber"/></td></tr>
<tr>
            <td>AssetType</td>
            <td><input style="font-size:15px;" type="text" name="Asset
+Type"/></td></tr>
        <tr>
            <td>Branch</td>
            <td><input style="font-size:15px;" type="text" name="Branc
+h"/></td></tr>
<tr>
            <td>Status</td>
            <td><select name="Status" id="Status">
                <option value="Inventory">Inventory</option>
                <option value="Production">Production</option>
<option value="Testing">Testing</option>  
            </td></tr>
    </table>
</form>
</div>
BODY

results = <<'RESULTS';
<div style="padding:5px;position:absolute;top:275px;height:100px;width
+:100%;background:#F9F9DD;">
<table cellspacing="0" cellpadding="3" style="padding:5px;font-size:11
+px;border:1px black solid;" bgcolor="#EEEEEE" width="100%">
RESULTS

$i = 0;
$c = new CGI();


$field = $c->param("field");

$field =~ s/'/\\'/;


$h = DBI->connect("dbi:mysql:inventory:localhost","ID","PW") or die("c
+ouldn’t connect to database");
$query = "SELECT SerialNumber, AssetTag, AssetType, Branch, Status, FR
+OM inv WHERE AssetTag = $AssetTag";
$q = $h->prepare($query);
%columns = (
    SerialNumber => "Serial Number",
    AssetTag => "Asset Tag",
    AssetType => "Asset Type",
    Branch => "Branch",
    Status => "Status",
);

$results .= "</tr>";

$h->disconnect();

$results .= <<'RESULTS';
</table>
</div>
RESULTS

print $head;
print $body;
print $results;
print $tail;
[download]

I am trying to use the AssetTag field that I THINK is being sent via the POST in order to read the database and populate the few fields on the screen. After this I need to be able to modify and then have a “Save” button to update the SQL database with the modified information from the user. I am doing this in baby steps as I am still pretty new to PERL. Thank you for any suggestions, comments, and ideas I may get on resolving this!!!

Comment on Editing Data in SQL database Select or Download Code

Replies are listed 'Best First'.
Re: Editing Data in SQL database by CountZero (Bishop) on Mar 01, 2011 at 19:37 UTC
I am trying to use the AssetTag field that I THINK is being sent via the POST The first thing to do is to make sure that the data is indeed being sent and is well received. Write two simple pages: the first one which sends the data and the second one which receives the data and prints it to the screen. If that works, you can add the other stuff. Oh, and NEVER EVER interpolate your SQL queries with external data. ALWAYS ALWAYS use placeholders so you will never get hit by a Little Bobby Tables attack. Update: It looks like you are re-inventing wheels. Did you ever think of looking into web-frameworks such as Catalyst or Dancer, or templating engines such as Template Toolkit and an ORM like DBIx::Class? Together they provide easy and fool-proof solutions to matters you are now struggling with. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re^2: Editing Data in SQL database by cavac (Prior) on Mar 01, 2011 at 22:53 UTC
Depending on the amount of clicks you expect, you should also avoid connect/disconnect to the database in every script call. Use persistent database connections instead. I can't recommend a solution from the top of my head (i have my own MAPLAT webframework for that, but thats a bit overkill for that purpose and anyway is also still subject to change).	[reply]