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

I'm trying to get a small CGI script to save information from an HTML form to a database, but I keep getting errors from the DBI->connect call. What's more puzzling is that my script runs fine from the command line. Even more puzzling, DBI::errmsg is undefined after the connect call fails. My script is pretty straight forward: 
#!/usr/bin/perl -w
use DBI;

#connect to DB
$dbHandle = DBI->connect('DBI:mysql:mydb','ID','pw');

[download]
The error found in error_log is: 
DBI->connect(ID) failed: Access denied for user:
'ID@localhost' (Using password: YES) at
/home/user/website/cgi-bin/script.pl line 33

[download]
The user name and password are valid for the database, since everything works fine if I run the script from the command line. It only fails when I run it through CGI.

Any help would be greatly appreciated. Thanks.

Phemur

Replies are listed 'Best First'.
Re: DBI connection through CGI
by DigitalKitty (Parson) on May 20, 2002 at 20:36 UTC
    Hi Phemur,

    Try changing this line: 

    $dbHandle = DBI->connect('DBI:mysql:mydb','ID','pw');

    [download]

    to this: 

    
my $dbh = DBI->connect("DBI:mysql:database=dbname;host=host", "$id","$
+pw") || die "Can't connect: $DBI::errstr";

    [download]

    If you start your CGI form like this: 

    
#!/usr/bin/perl -wT
use strict;
use CGI qw( :standard );
use CGI::Carp qw( fatalsToBrowser );
use DBI;
use DBD::mysql;

#Store each field in a variable.
my $name = param("name");
my $phone = etc..
my $age = etc..
my $sth;

#Then insert each of them into a table:

$sth = $dbh->prepare("INSERT INTO userdata VALUES (?,?,?)");
$sth->execute($name, $phone, $age);

#Make sure you clean up when finished:
$sth->finish();
$dbh->disconnect();

    [download]
    Warning: This code is untested but it should help you complete the project.

    Hope this helps,
    -Katie.
[reply]
[d/l]
[select]
      Hi Katie,

      Thanks for the information and code sample. I tried your suggestions, and although it didn't help that particular problem, it did polish my code and teach me a few things. I've only been trying my hand at Perl for a week or so now, and this does help.

      Do you have any tips on making a C programmer's Perl scripts look less like C and more like Perl? :-)

      Thanks a bunch.

      Nat (Phemur)

[reply]
Re: DBI connection through CGI
by cLive ;-) (Prior) on May 20, 2002 at 22:46 UTC
    It's gonna be the username/password permissions. Log in from command line as root and: 
    use mysql;
SELECT User,Host FROM user;

    [download]
    Now look at results. Is there a user 'ID' on Host 'localhost'? Or is it user 'ID' with host 'localhost.localdomain'?

    That's my bet. Change Host to % for user 'ID'. Does it work now? If so, amend Host to relevant 'localhost.localdomain' to suit (don't leave it as % unless you're happy with outside connections being allowed ! ;-)

    .02

    cLive ;-)

    --
    seek(JOB,$$LA,0);

[reply]
[d/l]
Re: DBI connection through CGI
by virtualsue (Vicar) on May 20, 2002 at 21:35 UTC
    Whenever something works at the command line, but doesn't through CGI, that almost always means that you have a permissions/access problem due to the fact that your webserver runs as a different userid than the one you are logged in as. Find out what userid your webserver runs as, and grant that userid privileges on your database.
[reply]
      Thanks Sue,

      I'm still going through all of the access permissions, but at this point, my guess is that the problem lies with MySQL. I changed the permissions to root for testing, and it worked. I then tried logging into MySQL with the previous username/password, and that failed, even though that signon was part of the user table.

      To get back to Perl, does anyone know why DBI::errmsg would not be set if connect failed?

      Thanks again,

      Nat

[reply]
        does anyone know why DBI::errmsg would not be set...

        Perhaps you might find $DBI::errstr a little more useful :)

        cLive ;-)

        --
        seek(JOB,$$LA,0);

[reply]

Back to Seekers of Perl Wisdom