Unable to insert a record into MySQL database using DBI

Question

I am trying to insert a record into a MySQL database using Perl DBI. I am not getting any errors but the insert is not working. However, I am able to successfully fetch records from the database using DBI.

Here is the code that does the insert:

#!"C:\xampp\perl\bin\perl.exe"

use diagnostics;
use DBI;
use strict;
use warnings;

my $driver = "mysql"; 
my $database = "mysql";
my $dsn = "DBI:$driver:database=$database";
my $userid = "root";
my $password = "password";

my $buffer;
my @pairs;
my $pair;
my $name;
my $value;
my %FORM;

# Read in text
my $ENV;
$ENV{'REQUEST_METHOD'} =~ tr/a-z/A-Z/;
if ($ENV{'REQUEST_METHOD'} eq "GET")
{
    $buffer = $ENV{'QUERY_STRING'};
}

# Split information into name/value pairs
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
    ($name, $value) = split(/=/, $pair);
    $value =~ tr/+/ /;
    $value =~ s/%(..)/pack("C", hex($1))/eg;
    $FORM{$name} = $value;
}

my $first_name= $FORM{name};
my $address = $FORM{address};
my $city  = $FORM{city};
my $occupation  = $FORM{occupation};
my $age  = $FORM{age};

my $dbh = DBI->connect("dbi:mysql:dbname=mysql", "root", "password",{ AutoCommit =>  0,RaiseError => 1}, ) or die ("Couldn't connect to database: ") , $DBI::errstr;

# my $sth = $dbh->prepare("INSERT INTO persons
                        # (FirstName, LastName,Address,City)
                        # values
                        # ($first_name, $last_name,$address,$city)");
my $query = "insert into userrecords(Address,Age,City,Name,Occupation)
             values (?, ?, ?, ?, ?) ";

my $statement = $dbh->prepare($query) or die ("Couldn't connect to database: "), $DBI::errstr;
$statement->execute($address,$age,$city,$name,$occupation) or die ("Couldn't connect to database: "), $DBI::errstr;

$dbh->disconnect();

my $URL = "http://.....:81/cgi-bin/showdata.cgi";
print "Location: $URL\n\n";

exit(0);

When I run my code in the Padre IDE, I get the following errors:

****Error*********
Useless use of a variable in void context at InsertRecord.cgi line 50 (#1)
    (W void) You did something without a side effect in a context that does
    nothing with the return value, such as a statement that doesn't return a
    value from a block, or the left side of a scalar comma operator.  Very
    often this points not to stupidity on your part, but a failure of Perl
    to parse your program the way you thought it would.  For example, you'd
    get this if you mixed up your C precedence with Python precedence and
    said

        $one, $two = 1, 2;

    when you meant to say

        ($one, $two) = (1, 2);

    Another common error is to use ordinary parentheses to construct a list
    reference when you should be using square or curly brackets, for
    example, if you say

        $array = (1,2);

    when you should have said

        $array = [1,2];

    The square brackets explicitly turn a list value into a scalar value,
    while parentheses do not.  So when a parenthesized list is evaluated in
    a scalar context, the comma is treated like C's comma operator, which
    throws away the left argument, which is not what you want.  See
    perlref for more on this.

    This warning will not be issued for numerical constants equal to 0 or 1
    since they are often used in statements like

        1 while sub_with_side_effects();

    String constants that would normally evaluate to 0 or 1 are warned
    about.

Useless use of a variable in void context at InsertRecord.cgi line 59 (#1)
Useless use of a variable in void context at InsertRecord.cgi line 60 (#1)

Use of uninitialized value in transliteration (tr///) at InsertRecord.cgi line
        23 (#2)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.

    To help you figure out what was undefined, perl will try to tell you the
    name of the variable (if any) that was undefined. In some cases it cannot
    do this, so it also tells you what operation you used the undefined value
    in.  Note, however, that perl optimizes your program and the operation
    displayed in the warning may not necessarily appear literally in your
    program.  For example, "that $foo" is usually optimized into "that "
    . $foo, and the warning will refer to the concatenation (.) operator,
    even though there is no . in your program.

Use of uninitialized value $ENV{"REQUEST_METHOD"} in string eq at
        InsertRecord.cgi line 24 (#2)
Use of uninitialized value $buffer in split at InsertRecord.cgi line 29 (#2)
Location: http://.......:81/cgi-bin/showdata.cgi

Press any key to continue . . .

***********END***********************

What is the issue?

Answer 1

The warning comes from this:

or die ("Couldn't connect to database: ") , $DBI::errstr;

The , $DBI::errstr is outside of the die and nothing is done with it, thus being in void context. You want something like this:

or die ("Couldn't connect to database:  $DBI::errstr");

Also, your form handling code has some issues. If you're writing CGI scripts, you may as well use the CGI module. Here's a quick cleanup of your code:

#!"C:\xampp\perl\bin\perl.exe"
use diagnostics;
use CGI ':standard';
use DBI;
use strict;
use warnings;

my $driver   = "mysql";
my $database = "mysql";
my $dsn      = "DBI:$driver:database=$database";
my $userid   = "root";
my $password = "password";

my $name       = param('name');
my $address    = param('address');
my $city       = param('city');
my $occupation = param('occupation');
my $age        = param('age');

my $dbh = DBI->connect( $dsn, $userid, $password,
    { AutoCommit => 1, RaiseError => 1 },
) or die("Couldn't connect to database: $DBI::errstr");

my $query = <<'END';
INSERT INTO userrecords(Address,Age,City,Name,Occupation)
                VALUES (      ?,   ?,  ?,    ?,        ?)
END

my $statement = $dbh->prepare($query);
$statement->execute( $address, $age, $city, $name, $occupation );
$dbh->disconnect();

my $URL = "http://.....:81/cgi-bin/showdata.cgi";
print "Location: $URL\n\n";

Note that I've removed many or die statements because you already have RaiseError set to a true value.

For simplicity's sake, I've also (reluctantly) turned on AutoCommit.

Answer 2

What happens when you replace your code with this:

#!"C:\xampp\perl\bin\perl.exe"
use strict;
use warnings;

use diagnostics;
use DBI;
use CGI qw[param redirect];    

my $driver   = "mysql"; 
my $database = "mysql";
my $dsn      = "DBI:$driver:database=$database";
my $userid   = "root";
my $password = "password";

my $dbh = DBI->connect("dbi:mysql:dbname=mysql", "root", "password",
    { AutoCommit =>  0,RaiseError => 1}, )
    or die "Couldn't connect to database: ", $DBI::errstr;

my $query = "insert into userrecords(Address,Age,City,Name,Occupation)
values (?, ?, ?, ?, ?) ";

my $statement = $dbh->prepare($query)
    or die "Couldn't connect to database: " , $DBI::errstr;
$statement->execute(param('address'), param('age'), param('city'),
                    param('name'), param('occupation'))
    or die "Couldn't connect to database: " , $DBI::errstr;    
$dbh->disconnect();

my $URL = "http://.....:81/cgi-bin/showdata.cgi";
print redirect($URL);

I've basically made two changes:

1. Use the CGI.pm module to handle the CGI interaction (getting the parameters and printing the redirection header).
2. Fixed your "void context" errors by removing the misplaced parentheses in all of your calls to die.

I'm made no substantive changes to the code, but at least we now have a clean version to go with.

Update: D'oh. It's obvious now the code is cleaned up a bit. If you have "Autocommit" turned off, then you need to commit your changes. Add $dbh->commit between the calls to execute() and disconnect().

Answer 3

When I was editing your code so that it was more readable, I stumbled upon what I assume is the solution:

You are using $name when inserting into the database, but you use $first_name when getting the value $FORM{name}. So since you used $name above, it has the value of the last name used, whatever that might be. The relevant code snippets:

($name, $value) = split(/=/, $pair);
...
$FORM{$name} = $value;
...
my $first_name = $FORM{name};
...
$statement->execute($address,$age,$city,$name,$occupation)
#                                       ^^^^^--- should be $first_name

Your problem would have been solved if you had used proper scope on your variables, namely something like this:

 foreach my $pair (@pairs) {
     my ($name, $value) = split(/=/, $pair);
     $value =~ tr/+/ /;
     $value =~ s/%(..)/pack("C", hex($1))/eg;
     $FORM{$name} = $value;
 }

Then when you later would try to use $name, you would get the error

Global variable "$name" requires explicit package name ...

Which would alert you to your mistake and save you hours in debugging time. When you declare variables at the top of the script, instead of in the smallest possible scope, you effectively disable the protection that use strict 'vars' offers. So don't do that.

Also, you should probably use the CGI module instead of trying to handle it manually. It will make things easier, and safer. Don't forget to perform sanity checks on your data to prevent database injection attacks.

Your script when cleaned up and properly formatted looks like this.