in reply to Print Array into 4 Column Table

I have the following code that is erroring out with "Software error: Couldn't execute query: DBI::db=HASH(0x820c7ac)->errstr at cityshow.pl line 34.:

Line 34 reads '$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";'

Here is the contents of my perl script, it is supposed to print the cities of a state in a table that is 4 columns wide:


#!/usr/local/bin/perl

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

$buffer = $ENV{'QUERY_STRING'};

@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
  ($name, $value) = split(/=/, $pair);
  $name =~ tr/+/ /;
  $name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
  $value =~ tr/+/ /;
  $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
  $FORM{$name} = $value;
}


##Start database connections##########################################
+##########
$database = "database";
$db_server = "localhost";
$user = "user";
$password = "password";

##Connect to database, insert statement, & disconnect ################
+##########
$dbh = DBI->connect("DBI:mysql:$database:$db_server", $user, $password
+);

$statement = "SELECT city FROM database WHERE $FORM{state}";


$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: $
+sth->errstr";

$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";

$rc = $sth->finish;
$rc = $dbh->disconnect;
######################################################################
+##########






print "Content-type: text/html\n\n";

#*******************************************Print to the first half of
+ page
print <<EOF;

<head>
<title>xxx</title>
<meta http-equiv="Content-Type" content="text/html; charset=xxx">
<link href="xxx.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style1 {font-size: 36px}
-->
</style>
</head>
<body bgcolorxxx" leftmargin="0" topmargin="0" marginwidth="0" marginh
+eight="0">
<table bgcolor="#FFFFFF" id="xxx" width="xxx" height="xxx" border="0" 
+cellpadding="0" cellspacing="0" align="center">
        <tr>
                  <td colspan="5">
                          <img src="xxx.gif" width="543" height="27"><
+/td>
                  <td colspan="2">
                          <a href="../"><img src="xxx.gif" width="xx" 
+height="xx" border="0"></a></td>
                  <td colspan="2">
                          <a href="https://xxx.htm"><img src="xxx.gif"
+ width="x" height="x" border="0"></a></td>
                  <td>
                          <a href="xxx.htm" class="xxx" target="_blank
+"
 onclick="window.open('../xxx.html','xxx','height=xxx,width=xxx,left=x
+xx,top=xxx,toolbar=0,resizable=0,menubar=0'); return false"><img src=
+"x.gif" width="102" height="27" border="0"></a></td>
          </tr>
        <tr>
                <td rowspan="3">
                        <img src="images/xxx.jpg" width="x" height="x"
+></td>
                <td colspan="2" rowspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
                <td colspan="3">
                        <img src="x" width="x" height="x"></td>
                <td colspan="4" rowspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
        </tr>
        <tr>
                <td colspan="3">
                        <img src="x.gif" width="x" height="x"></td>
        </tr>
        <tr>
                <td colspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
        </tr>
        <tr>
                  <td>
                          <img src="x.jpg" width="x" height="x"></td>
                  <td>
                          <a href="x.htm"><img src="x.gif" width="x" h
+eight="x" border="0"></a></td>
                  <td colspan="2">
                          <a hrefx"><img src="x.gif" width="x" height=
+"x" border="0"></a></td>
                  <td>
                          <a><img src="x.gif" width="x" height="x" bor
+der="0"></a></td>
                  <td colspan="3">
                          <a href=x"><img src="x.gif" width="x" height
+="x" border="0"></a></td>
                  <td colspan="2">
                          <a href=x"><img src="x.gif" width="x" height
+="x" border="0"></a></td>
          </tr>
        <tr>
                <td colspan="10">
                        <table id="x" width="x" height="x" border="0" 
+cellpadding="0" cellspacing="0">
                <tr>
                    <td height="1" colspan="3" nowrap valign="top"><di
+v class="tabs">
                            <ul id="tablist">
                                <li id="overview" class="">
             <a href="x">x</a>
</li>
                                <li id="x" class="selected">
             <a href="x.html">x</a>





                                </ul>
  <div style="clear:left;"></div>
  </div>
                            <p align="center">

                            <script language="JavaScript" src="../x.js
+">

                            </script>
<FONT face="Arial Black" size=+1>$FORM{state} x</FONT></p>

                            <P><DIV align=center></DIV>
<TABLE cellSpacing=2 cellPadding=7 width="90%" align=center border=1>
                                <TBODY>

EOF
#********************************************** Stop printing the firs
+t half of the page

$x=1;
while (@row = $sth->fetchrow_array;) {
     print <<EOF;

<TR>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#2f2cff>$row[$x]</FONT><BR></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+2]</FONT><BR></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+3]</FONT><BR></A></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+4]</FONT><BR></B></FONT></DIV></TD>
                                </TR>

EOF
$x=x+4;
}

print <<EOF;

</TBODY>

                            </TABLE>
<BR>

                        </td>
                    </tr>
                        </table></td>
        </tr>
        <tr>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                </tr>
</table><p>&nbsp;</p>

</body>
</html>
EOF

[download]

Replies are listed 'Best First'.
Re: Trying to print HTML table, erroring out.
by dws (Chancellor) on Jun 26, 2004 at 05:49 UTC

    Eeek. For one, unless you really, really trust your users to type 

      state = 'CA'

    [download]
    into the form, you're asking for trouble. Google for "SQL injection attack" or use super search here.

    Far better is to do something like 

      my $sql = "SELECT city FROM table WHERE state = ?";
  my $sth = $dbh->prepare($sql);
  $dbh->execute($FORM{state});

    [download]
    By doing this, the value will be quoted automagically as appropriate for MySQL, elimating the possibility that someone will type 
      1; drop table database

    [download]
    into the form and have the results ruin your day.

    Then you have to read the results before finishing the statement handle and disconnecting.

    Also, since you're using CGI, it is completely unecessary to attempt to disect the query string yourself (and dissecting query strings by hand can be trickier than it first appears). CGI is quite good at handling that for you. You can drop that entire chunk of code that sets up %FORM, and instead do 

      use CGI;

  my $cgi = CGI->new();
  ...
  my $sth = $dbh->prepare($sql);
  $sth->execute($cgi->param('state')) or die ...

    [download]
[reply]
[d/l]
[select]
Re: Trying to print HTML table, erroring out.
by etcshadow (Priest) on Jun 26, 2004 at 04:54 UTC
    Well, there are two things wrong with your prepare/execute. First of all, putting $dbh->errstr inside of a double-quoted string doesn't do what you want it to (it stringifies $dbh, and then it just keeps "->errstr" as part of the literal string). That should be: 
    ... or die "Couldn't execute query: ".$dbh->errstr;

    [download]
    So that you can get some useful debugging information from the database.

    Second (just to take a wild guess here), your query string looks something like "...&state=TX&..." which means that the way you need to generate your sql statement is more like: 

    $statement = "SELECT city FROM database WHERE state='$FORM{state}'";

    [download]
    Of course, this would be very BAD because you're not stripping single-quotes from your state, and besides, queries should use bound placeholders (in general). So what it should actually be is: 
    $statement = "SELECT city FROM database WHERE state=?";

$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: "
+.$sth->errstr;

$rv = $sth->execute($FORM{state}) or die "Couldn't execute query: ".$d
+bh->errstr;

    [download]
    If that's over your head, the short answer is that that question-mark in the query works kind of like a %d would in printf. Anyway, you should read the DBI docs about placeholders (also called "bind variables" or "binds") for more info.

    Oops... see UPDATE comment below.

    Last, you're actually going to want to fetch the data from that query and do something with it. That will probably look something like (and there are many ways to do this... but here's one way): 

    # inside the html <table>
foreach my $row ($sth->fetchall_arrayref) {
   my $state = $row->[0];
   print "... some html or other based on $state...";
}

    [download]

    Anyway, it appears that you're gonna want to do some documentation reading and possibly looking at more example code.

    Oh... and I didn't bother to look any further down than the query, because that is clearly going wrong right now... you need to resolve this problem before you worry about how you the html in exactly whatever way you want. Learn to walk before you learn to run, and all.

    Good luck.

    P.S. Oh, and I'll leave the rant about how you should be using CGI.pm to someone else :-)

    UPDATE: Actually, I made a mistake by not looking closely enough all the way to the bottom... you are fetching the results (which is good). BUT since you are calling $sth->finish and $dbh->disconnect at the top of the script and doing your fetching at the bottom of the script (after finishing and disconnecting), that's gonna break. (Also, this is why I missed the fetching down below... I assumed (like the code would) that once you have disconnect from the DB, you're not doing any further calls against the DB.) What this is means is: move the finish and disconnect to the bottom after you have fetched. 

    ------------
:Wq
Not an editor command: Wq

    [download]
[reply]
[d/l]
[select]
Re: Trying to print HTML table, erroring out.
by neniro (Priest) on Jun 26, 2004 at 09:15 UTC
    The important answers on how to use DBI and CGI are above. I'd like to add just a small hint. Separate your code from html using a template-tool like HTML::Template or the Template Toolkit makes it much easier to maintain your script in the future.
[reply]

In Section Seekers of Perl Wisdom