Maddening bug: Why do quotes fix it?

[Arvin Portlock]

This one is driving me crazy. I have a perl cgi script,
ActiveState perl 5.8.0, running on Apache 2.0.46,
Windows 2000. The bug happens both under mod_perl and
under regular cgi perl. I found I could fix the problem
by putting the difficult variable in quotes, or applying
a trivial regular expression to it. But I want to know
why!

The program takes input from a web form and uses ADO
to add it to an Access 97 database. The difficult field
is of type 'text', not required and not indexed (although
changing these parameters in various ways didn't seem to
affect the bug). The bug is that the text string always
gets added to the database as a zero (0) UNLESS I surround
it with quotes or apply a RE to it first. Then it gets
added as the correct string! There's either something very
subtle going on here or very stupid. If it's the latter I
will take my lumps like a good sport.

I can't reproduce the problem in any other way except my
exact database and setup. The script is a bit too long to
post here so I will try and post what I hope are the two
subroutines in question (unchanged except for the addition
of the comments):

# $post_data is a hash reference containing the form data
# $rs is the open recordset object
# $conn is the open connection object
sub UpdateDatabase {
my ($post_data, $rs, $conn) = @_;
my $title = $post_data->{'Event Title'};
my $type = $post_data->{'Event Type'};
my $statform = $post_data->{'Stat Form'};
my $category = $post_data->{'Category'};
# $category prints: "A valid string"
my $instructor = $post_data->{'Instructor'};
my $staff = $post_data->{'Staff'};
my $dept = $post_data->{'Department'};
my $number = $post_data->{'Course Number'};
my $faculty = $post_data->{'Faculty/GSI Name'};
my $attendees = $post_data->{'Attendees'};
my $comments = $post_data->{'Comments'};
my $month = $post_data->{'Month'};
my $day = $post_data->{'Day'};
my $year = $post_data->{'Year'};
my $hour1 = $post_data->{'Start Hour'};
my $minute1 = $post_data->{'Start Minute'};
my $ampm1 = $post_data->{'Start AM/PM'};
my $hour2 = $post_data->{'End Hour'};
my $minute2 = $post_data->{'End Minute'};
my $ampm2 = $post_data->{'End AM/PM'};
my $date = "$month/$day/$year";
my $start = "$hour1:$minute1 $ampm1";
my $end = "$hour2:$minute2 $ampm2";

# Since $category is the only variable displaying this problem
# the fact that we're doing something special with it alone here
# is suspicious. However deleteing all of this still results in
# in the bug.
unless ($category) {
if ($type eq 'Library Staff') {
$category = "E. Library Staff";
} else {
$category = "C. Training";
}
}

# $category prints: "A valid string"
$post_data->{Category} = $category;
# $category prints: "A valid string"
# $category =~ s/(\w)/$1/g; also solves the bug

my %fieldvalues = ("Instructor" => $instructor,
"Event Title" => $title,
"Event Type" => $type,
"Category" => "$category",
"Stat Form" => $statform,
"Assisting Staff" => $staff,
"Course Department" => $dept,
"Course Number" => $number,
"Faculty Name" => $faculty,
"Number of Attendees" => $attendees,
"Course Event" => $event,
"Date" => $date,
"Time Start" => $start,
"Time End" => $end,
"Comments" => $comments);
# $category prints: "A valid string"
# $fieldvalues{Category} prints: "A valid string"
my ($fields, $values) = &GetFieldValues (\%fieldvalues);
# Looping through @$fields and @$values retrieves: "A valid string"
$rs->AddNew ($fields, $values);
# BUG! Database field 'Category' gets 0 and not 'A valid string'
# if $category is not in quotes above!
&CheckDBErrors($conn, \@dberrors, "Update");
}

sub GetFieldValues {
my $fieldvalues = shift;
my (@fields, @values);
foreach my $field (keys %{$fieldvalues}) {
if ($fieldvalues->{$field} or $fieldvalues->{$field} eq "0") {
push @fields, $field;
push @values, $fieldvalues->{$field};
}
}
return (\@fields, \@values);
}

__END__

I put in several comments that reflect the result of printing
the variable at various stages, and no matter where I put it or
which variable I am printing I always see "A valid string" which
is my test value. The only place I have EVER seen a 0 is in the
database when I don't put the quotes around $category.

So why does the simple fact of putting $category in quotes above
solve this bug?

[James Willmore]

On Sun, 14 Sep 2003 12:57:22 -0700
Arvin Portlock <apollock11@hotmail.com> wrote:
<sniped for brevity>

> So why does the simple fact of putting $category in quotes above
> solve this bug?

No where in your post do you indicate how you're communicating with
the database. If you're using the DBI module, you may want to
consider using the 'quote' method for your data.

Also consider how you're getting the values in the first place.
You're using a CGI script. If the user should happen to put a bunch
of spaces into a text field, then the data you're working with will
have spaces (GIGO). Are you taking this into account? Are you even
validating what the user sends in the form?

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. [url]http://www.gnu.org/licenses/gpl.txt[/url]
for more information.

a fortune quote ...
Jesus Saves, Moses Invests, But only Buddha pays Dividends.

[Arvin Portlock]

James Willmore wrote:

> On Sun, 14 Sep 2003 12:57:22 -0700
> Arvin Portlock wrote:
>

> >So why does the simple fact of putting $category in quotes above
> >solve this bug?

>
> No where in your post do you indicate how you're communicating with
> the database. If you're using the DBI module, you may want to
> consider using the 'quote' method for your data.

I'm using ADO via Win32::OLE. I didn't even know you could use ADO in
the DBI module! That would be very useful.

[James Willmore]

On Sun, 14 Sep 2003 13:36:46 -0700
Arvin Portlock <apollock11@hotmail.com> wrote:
<snip>

> I'm using ADO via Win32::OLE. I didn't even know you could use ADO
> in the DBI module! That would be very useful.

Yes, yes there is a DBD::ADO module for use with DBI.

Enjoy!

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. [url]http://www.gnu.org/licenses/gpl.txt[/url]
for more information.

a fortune quote ...
"Even the best of friends cannot attend each other's funeral."
-- Kehlog Albran, "The Profit"

[Eric Schwartz]

James Willmore <jwillmore@cyberia.com> writes:

> On Sun, 14 Sep 2003 13:36:46 -0700
> Arvin Portlock <apollock11@hotmail.com> wrote:
> <snip>

>> I'm using ADO via Win32::OLE. I didn't even know you could use ADO
>> in the DBI module! That would be very useful.

>
> Yes, yes there is a DBD::ADO module for use with DBI.

Even better than using quote() explicitly is using a prepare-execute
pair, which automatically quotes things for you:

my $sth = $dbh->prepare("select * from users where userid=?");
my $rc = $sth->execute($userid);

For more information, see the DBI and DBD docs for your database.

-=Eric
--
Come to think of it, there are already a million monkeys on a million
typewriters, and Usenet is NOTHING like Shakespeare.
-- Blair Houghton.