DBI quote: invalid number of parameters

[Baiul]

Hi all,

I am having great difficulties in getting my script to work with perl/mysql. I am no Guru but basically I am making a registration page for my users and the script just takes their details and inputs them into the mysql DB. I am getting the following error

DBI quote: invalid number of parameters: handle + 0 Usage: $h->quote($
+string [, $data_type ]) at usersetup2.cgi line 360.

Here is the snippet of code which is affected,

my ($nickname, $pass1, $firstname, $lastname, $email, $imtype, $imid, 
+$info, $country, $homepage) = (.
   $nickname = $dbh->quote(param('username')),.
   $pass1 = $dbh->quote(param('password1')),
   $firstname = $dbh->quote(param('firstname')),
   $lastname = $dbh->quote(param('lastname')),
   $email = $dbh->quote(param('email')),
   $imtype = $dbh->quote(param('imtype')),
   $imid = $dbh->quote(param('imid')),
   $info = $dbh->quote(param('info')),
   $country = $dbh->quote(param('country')),
   $homepage = $dbh->quote(param('homepage'))
);

# Query the database.
my $sth = $dbh->prepare( "INSERT INTO member (nickname, password, firs
+t_name, last_name, email, country, homepage, im_type, im_id, info) VA
+LUES ($nickname, $pass1, $firstname, $lastname, $email, $country, $ho
+mepage, $imtype, $imid, $info)");
$sth->execute or die "execute failed: $DBI::errstr\n";
$sth->finish or die "execute failed: $DBI::errstr\n";<

Basically I got in this mess by trying to escape the characters like @ from screwing up the DB. This is on a paid host and I cannot upgrade the DBI module so I was wondering if anyone had any other ideas? The connection works fine and all of the params are being picked up ok too. They work ok if I just want to print them.

Thanks for the help!
Baiul.

Ubi Concordia Ibi Victoria

[Trimbach]

An easier way is to let DBI do the escaping automatically for you with placeholders. Try this:

my $sth = $dbh->prepare( 
"INSERT INTO member (nickname, password, first_name, last_name, email,
+ country, homepage, im_type, im_id, info)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

$sth->execute($nickname, $pass1, $firstname, $lastname, $email, $count
+ry, $homepage, $imtype, $imid, $info)
   or die "execute failed: $DBI::errstr\n";

I suspect you have an undef value in one of your variables that's throwing off your INSERT. This way you shouldn't have to worry about it.

Gary Blackburn
Trained Killer

[tantarbobus]

DBI is throwing an error because you are not passing anything to quote. The code you posted looks like it is passing something to quote; however, param('something') can retrun an empty list when called in list context. So what you basically end up doing is $dbi->quote(), and DBI throws an error to protect you from yourself.

So, to get it to do what you want, you can do a $dbi->quote(scalar(param('username'))), or maybe $dbi->quote(param(('username') or undef)).

Also you might want to try using placeholders instead of using quote() on each value. While placeholders will not get you around the 'param returning an empty list problem', placeholders end up being much cleaner. Something like this:

my $sth = $dbh->prepare("INSERT INTO member (nickname, password, first
+_name, last_name, email, country, homepage, im_type, im_id, info) VAL
+UES (?,?,?,?,?,?,?,?,?,?)");

$sth->execute(
    map {scalar(param($_))} qw(username password1 firstname 
         lastname email imtype imid info
    )
);

[Baiul]

First off thanks for the help to both of you :).


Next I tried both methods of using the placeholders and now I am getting a different error as follows:


DBD::mysql::st execute failed: Column 'nickname' cannot be null at usersetup2.cgi line 378.


There is most definately a value in the param so I'm not sure what is going wrong with it. Any ideas?
Thanks again,
Baiul.
Ubi Concordia Ibi Victoria

[Baiul]

Ok ignore that last post that was an error to due with my own problem hehe. I fixed that up and now it is working perfectly.

I just wanted to say thanks again to you both! You have saved me a lot of problems and I am very happy!

Baiul.
Ubi Concordia Ibi Victoria