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

Hello.
I'm working on a script that should update a field in a table in MySQL. For some reason, when I submit the form, it just hangs there and seems to do nothing. Here's the code:
#!usr/bin/perl

use DBI;
use CGI qw/:standard/;


print header();

my $q = new CGI;
my $oldpw = $q->param('txtPasswordOld');
my $newpw1 = $q->param('txtPasswordNew1');
my $newpw2 = $q->param('txtPasswordNew2');
my $name = $q->param('txtName');

my $user = "";
my $pwd = "";
my $server = "localhost";
my $db = "my_db";

my $dbh = DBI->connect("dbi:mysql:$db:$server",$user,$pwd);

print "<html><body>";

if ($newpw1 eq $newpw2){
  my $query = "UPDATE User SET Password = Password($newpw1) where User
+='$name'";
  my $sth = $dbh->prepare($query);
  $sth->execute();
  print "Password changed.";

}

print "</body></html>";
  
$dbh->disconnect;

[download]


Any thoughts on why this is?
_____________________________________________________
mojobozo
word (wûrd)
interj. Slang. Used to express approval or an affirmative response to
something. Sometimes used with up. Source

Replies are listed 'Best First'.
Re: CGI and DBI, new to me...
by Ryszard (Priest) on Dec 13, 2002 at 19:07 UTC
    dude, put set raiseerror => 1 and use placeholders.. its all described in the doco.

    RaiseError:

      $dbh = DBI->connect($dsn, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });

    [download]

    Placeholders:

      my $sth = $dbh->prepare(q{
    INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
  }) or die $dbh->errstr;
  while (<>) {
      chomp;
      my ($product_code, $qty, $price) = split /,/;
      $sth->execute($product_code, $qty, $price) or die $dbh->errstr;
  }
  $dbh->commit or die $dbh->errstr;

    [download]

    Both snippets lifed with care from the official doco.

[reply]
[d/l]
[select]
      If you set RaiseError => 1 you don't need all those 'or die's since the dbh will die for you on any error. Wrap the whole thing into an eval and then check $@ afterwards. 
      
eval {
 my $sth=$dbh->prepare( q{ your sql here } );
 $sth->execute( ... ); # params in the parens to be inserted as above
 $dbh->commit;
};

if($@)
{
  $dbh->rollback;
  print STDERR "Bad things happened: $@";
}

      [download]
      The commit line will never get called unless the execute went ok. Your program rolls back the query if the eval died. If you are worried about rollback not working, it should also be wrapped in another eval.
[reply]
[d/l]
(jeffa) Re: CGI and DBI, new to me...
by jeffa (Bishop) on Dec 13, 2002 at 20:37 UTC
    You really shouldn't mix the functional-oriented and object-oriented styles of CGI.pm - just use one. Since you have already typed $query numerous times, just remove qw/:standard/ from use CGI;.

    If you only need one CGI instance, the FO style is very convienent and just looks cleaner, IMHO. The OO style goes quite well with HTML::Template: 

    use strict;
use CGI;
use HTML::Template;

my $q = CGI->new();
my $template = HTML::Template->new(
   filename  => 'some.tmpl',
   associate => $q,
);

# assign params to $template according to user input/validation

print $q->header, $template->output;

    [download]
    The OO style also works nice for changing the behavior of CGI.pm, such as this silly example which changes the <h1> tag to 42: 
    use strict;
my $q = My::CGI->new;
print $q->h1('hello world'),$/;

package My::CGI;
use base qw(CGI);
sub h1 { "<42>$_[1]</42>" }

    [download]

    jeffa

    perl -MCGI=foo -le "print foo{bar=>baz},qux"

    [download]
[reply]
[d/l]
[select]
Re: CGI and DBI, new to me...
by tadman (Prior) on Dec 13, 2002 at 19:49 UTC
    A giant robot might be warbling about warnings becasuse that code is truly scary. You're taking values from the user and injecting them directly into your SQL without even checking what they are. Madness.

    You should, at least, use the quote() method to keep things sensible, or placeholders as Ryszard suggests.

    For diagnostics, I would run the script from the command-line with parameters to see if you can replicate the problem there. It could be that your RDBMS isn't accepting connections.

    Also, without linefeeds, you might have to set $|++ to turn off buffering. Put this near the top of your program, if so required.
[reply]
      Warnings aside, this is just a rough draft and I want to see it work before I make it safe. First, make sure gun shoots, then add safety.
      _____________________________________________________
      mojobozo
      word (wûrd)
      interj. Slang. Used to express approval or an affirmative response to
      something. Sometimes used with up. Source
[reply]

        Ahh, the Zapp Branigan school of tactics. "I conquered the killer robots by throwing wave after wave of men at them until the robots were tired of killing!" :)

[reply]
Re: CGI and DBI, new to me...
by scain (Curate) on Dec 13, 2002 at 19:19 UTC
    There doesn't appear to be anything fundementally wrong with your code, though you may want to look into DBI->quote() for quoting strings. Also, use strict and warnings.

    For debugging, I've found DBI->trace([1-4]) very useful as well. If you are running this as a cgi, it will spit all kinds of useful information to the error log.

    Scott
    Project coordinator of the Generic Model Organism Database Project

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom