Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

DBI quote: invalid number of parameters

by Baiul (Acolyte)
on Feb 23, 2003 at 02:38 UTC ( #237844=perlquestion: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re: DBI quote: invalid number of parameters
by Trimbach (Curate) on Feb 23, 2003 at 02:55 UTC
    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

Re: DBI quote: invalid number of parameters
by tantarbobus (Hermit) on Feb 23, 2003 at 15:55 UTC

    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 ) );
      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

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://237844]
Approved by data64
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2021-11-30 15:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?