Strange DBI problem

[Gil Vautour]

I have a Perl CGI script that was failing with a Server 500 error and
I think I have traced it back to a DBI subroutine that is inserting
the form data into a MySql table. I can submit the form with
identical data except for one field (email) and it will work sometimes
and sometimes not depending on the email address that is submitted.
Has anyone seem anything like this or have any idea of what I should
be looking for?

Thanks,

 

[Paul Lalli]

I have a Perl CGI script that was failing with a Server 500 error and
I think I have traced it back to a DBI subroutine that is inserting
the form data into a MySql table. I can submit the form with
identical data except for one field (email) and it will work sometimes
and sometimes not depending on the email address that is submitted.
Has anyone seem anything like this or have any idea of what I should
be looking for?

The very first thing you should be looking for is the error message in
your server logs. What does it say? If you don't have direct access to
your server logs, add the following lines to the top of your script:

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
warningsToBrowser(1);

That will cause the Perl error messages to be printed to the browser
instead of (or may in addition to? I forget) the server logs.

Paul Lalli

 

[Sherm Pendley]

Has anyone seem anything like this or have any idea of what I should
be looking for?

One (or both) of two things:

1. Look in your web server's error logs to get the error message from Perl
that triggered the "500 Server Error" response.

2. "use CGI::Carp qw(fatalsToBrowser);" in your script, so that Perl error
messages are reported in the output, instead of buried in the server log.

sherm--

 

[David K. Wall]

I have a Perl CGI script that was failing with a Server 500 error
and I think I have traced it back to a DBI subroutine that is
inserting the form data into a MySql table. I can submit the form
with identical data except for one field (email) and it will work
sometimes and sometimes not depending on the email address that is
submitted. Has anyone seem anything like this or have any idea of
what I should be looking for?

I have some guesses, but without code or error messages, guesses are
all they would be.

Check the server logs and see what the error is. If you don't have
access to the server logs, put

use CGI::Carp qw(fatalsToBrowser);

in your program and try it again. That will send warnings and error
messages to the browser. If you can't figure it out from that, post a
*short* piece of code that exhibits the problem.

OK, now the guess: I suspect you're not using placeholders and
instead are constructing the SQL something like this:

my $email = param('email');
my $sql = "insert into TableName (email) values ($email)";
my $dbh = DBI->connect( ... );
$dbh->do($sql);

Or maybe the '@' in the email address is being interpreted as the
beginning of an array name. (not as likely, IMO, but possible)

 

[Gil Vautour]

I have some guesses, but without code or error messages, guesses are
all they would be.

Check the server logs and see what the error is. If you don't have
access to the server logs, put

use CGI::Carp qw(fatalsToBrowser);

in your program and try it again. That will send warnings and error
messages to the browser. If you can't figure it out from that, post a
*short* piece of code that exhibits the problem.

OK, now the guess: I suspect you're not using placeholders and
instead are constructing the SQL something like this:

my $email = param('email');
my $sql = "insert into TableName (email) values ($email)";
my $dbh = DBI->connect( ... );
$dbh->do($sql);

Or maybe the '@' in the email address is being interpreted as the
beginning of an array name. (not as likely, IMO, but possible)

Well I have been using Carp but it still only produces a 500 error. I
don't have direct access to the server logs, I had a sys admin take a
look for me. The only thing he could find was a mal-formed Header
problem, but it seemed like this was caused by the script failing in
general. Here is a snippet of the DBI code that I think is where the
problem occurs:

my $dbh = DBI->connect("DBI:mysql:$database:$hostname",$user,$password)||
die "Connect failed: $dbh->errstr\n";

my($query) = @_;
my(@form,$values,$key);

foreach $key ($query->param) {
if ($query->param($key) ne "") {
$values = $query->param($key);
$values = $dbh->quote($values);
push @form,$values;
} else {
$values = "null";
push @form,$values;
}
}

my $sql = "insert into Responses
values(null,$form[0],$form[1],$form[2],$form[3],$form[4],".
"$form[5],$form[6],$form[7],$form[8],$form[9],$form[10],$form[11],$form[12],$form[13],NOW());";

my $sth = $dbh->prepare($sql) || die "Can't prepare $sql:
$dbh->errstr\n";

my $rv = $sth->execute || die "Can't execute $sql: $sth->errstr\n";

my $rc = $sth->finish;
$rc = $dbh->disconnect;

}

Thanks,

 

[David K. Wall]

I have some guesses, but without code or error messages, guesses
are all they would be.

Check the server logs and see what the error is. If you don't
have access to the server logs, put

use CGI::Carp qw(fatalsToBrowser);

in your program and try it again. That will send warnings and
error messages to the browser. If you can't figure it out from
that, post a *short* piece of code that exhibits the problem.

[snip my guess]

Well I have been using Carp but it still only produces a 500
error.

Not Carp. CGI::Carp. There *is* a difference; please be precise. Do
you mean you put the statement

use CGI::Carp qw(fatalsToBrowser);

in your program and you *still* get a 500 error?

What happens when you run it from the command line with the same
data?

I don't have direct access to the server logs, I had a sys
admin take a look for me. The only thing he could find was a
mal-formed Header problem, but it seemed like this was caused by
the script failing in general. Here is a snippet of the DBI code
that I think is where the problem occurs:

my $dbh =
DBI->connect("DBI:mysql:$database:$hostname",$user,$password)||
die "Connect failed: $dbh->errstr\n";

my($query) = @_;
my(@form,$values,$key);

foreach $key ($query->param) {

It's better to confine $key to the loop, e.g.;

foreach my $key ( $query->param ) {

I'd put $values inside the loop, too.

my $values = $query->param($key);
if ($values ne '') {
# ...

if ($query->param($key) ne "") {

^^
As a matter of style, single quotes are often preferred when there's
no variable interpolation happening.

$values = $query->param($key);
$values = $dbh->quote($values);

Hmm, the use of quote() makes my guess about placeholders wrong, as
placeholders implicitly use the quote method.

push @form,$values;
} else {
$values = "null";
push @form,$values;
}
}

my $sql = "insert into Responses
values(null,$form[0],$form[1],$form[2],$form[3],$form[4],".
"$form[5],$form[6],$form[7],$form[8],$form[9],$form[10],$form[11],$
form[12],$form[13],NOW());";

This seems to me a roundabout way of doing things. Someone please
correct me if I'm wrong, but I've generally done something like this:

# untested
my $sth = $dbh->prepare(q{
insert into Responses (colname1, colname2)
values (?,?)
})
or die $dbh->errstr;

$sth->execute( param('column1'), param('column2') )
or die $sth->errstr;

It seems odd that you never actually say which columns into which
you're inserting data, but a quick test shows that it works. I never
knew that. (and now that I know it, I don't really like it. it seems
sloppy.)

my $sth = $dbh->prepare($sql) || die "Can't prepare $sql:
$dbh->errstr\n";

my $rv = $sth->execute || die "Can't execute $sql:
$sth->errstr\n";

my $rc = $sth->finish;
$rc = $dbh->disconnect;

}

I don't see anything obviously wrong, but I've been mistaken before.

As I said above, run the program from the command line with the same
data and see what happens. It's difficult to debug something without
error and warning messages.

 

[Tore Aursand]

my($query) = @_;
my(@form,$values,$key);

foreach $key ($query->param) {
if ($query->param($key) ne "") {
$values = $query->param($key);
$values = $dbh->quote($values);
push @form,$values;
} else {
$values = "null";
push @form,$values;
}
}

my $sql = "insert into Responses
values(null,$form[0],$form[1],$form[2],$form[3],$form[4],".
"$form[5],$form[6],$form[7],$form[8],$form[9],$form[10],$form[11],$form[12],$form[13],NOW());";

my $sth = $dbh->prepare($sql) || die "Can't prepare $sql:
$dbh->errstr\n";

my $rv = $sth->execute || die "Can't execute $sql: $sth->errstr\n";

my $rc = $sth->finish;

IMO, better written as (with error checking removed, but I would have used
'eval' instead of a 'die');

my $query = shift;
my @form = ();
foreach ( $query->param() ) {
push( @form, $_ );
}

my $sth = $dbh->prepare( 'INSERT INTO Responses
VALUES (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())' );
$sth->execute( @form );
$sth->finish();

You should also be careful when using SQL queries as the one above; You
don't specify which columns to insert the data into. What do you think
happen if you change the layout of the 'Responses' table (ie. add, remove
or rearrange the table's columns)?

Please read 'perldoc DBI'; there's a lot of information for you there!

 

[ctcgag]

Well I have been using Carp but it still only produces a 500 error. I
don't have direct access to the server logs, I had a sys admin take a
look for me. The only thing he could find was a mal-formed Header
problem, but it seemed like this was caused by the script failing in
general.

If you are using plain old Carp, that's the problem. You need to use
CGI::Carp. If you are using CGI::Carp, which version? Some versions had a
problem in that only dies within your script got redirected to CGI::Carp,
the die done by used modules would not. I think CGI::Carp 1.24 had that
problem and 1.26 fixed it. It doesn't look like you are using RaiseError
anyway, but I'd still check into it.

Also, add a "print $query->header()" as soon as it would be legal to do so,
just for debugging purposes. (What the heck, print $query->Dump() too)

Here is a snippet of the DBI code that I think is where the
problem occurs:

my $dbh = DBI->connect("DBI:mysql:$database:$hostname",$user,$password)||
die "Connect failed: $dbh->errstr\n";

my($query) = @_;
my(@form,$values,$key);

foreach $key ($query->param) {
if ($query->param($key) ne "") {
$values = $query->param($key);
$values = $dbh->quote($values);
push @form,$values;
} else {
$values = "null";
push @form,$values;
}
}

my $sql = "insert into Responses
values(null,$form[0],$form[1],$form[2],$form[3],$form[4],".
"$form[5],$form[6],$form[7],$form[8],$form[9],$form[10],$form[11],
$form[12],$form[13],NOW());";

Dude, that is bloody aweful. You should probably check to see what the
field names are, and you should definately use bind variables.

Xho