Passing database handles

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

I have written a script that passes a DB handle by ref to a sub in another package. This works fine... BUT I am getting a mysql 'too many connections' error so when I say SHOW PROCESSLIST it says I have 100 sleeping processes. It appears that whenever I execute my script a new sleeping process is created. I have got $dbh->disconnect in the called sub and the main cgi but it seems to make no differance. I call the sub in the other package with

my $result=Select($id,\$dbh); 
$dbh->disconnect;
[download]

heres the called sub.

sub SelectProfile { 
     my $id=$_[0]; my $dbh=$_[1];

     my $selectquery = "SELECT *
                 FROM people
                  WHERE id = '$id'";

        my $sth= $$dbh->prepare($selectquery);
        $sth->execute();
        my $rowdata=$sth->fetchrow_hashref;
        $$dbh->disconnect;
     
        
         return $rowdata;
}##END Select
############################################################
[download]

None of my other scripts create these sleeping threads so I reckon it's cos I'm passing the db handle.

Comment on Passing database handles Select or Download Code

Replies are listed 'Best First'.
(ichimunki) Re: Passing database handles by ichimunki (Priest) on Dec 05, 2001 at 22:39 UTC
You don't have to pass a reference to the handle. It already is a reference to a blessed hash. I suggest connecting and disconnecting in the main script only, and then passing the handle straight off to the sub, not as a reference. Just a thought...	[reply]
(Ovid - potential security concern) Re: Passing database handles by Ovid (Cardinal) on Dec 05, 2001 at 23:54 UTC
You have a potential for a whopping security hole here, depending on how this is used. With MySQL (unless this has changed recently) you can't execute multiple SQL statements with one query, so this hole doesn't apply, but I bring it up in case you need to port this code or some else tries to use it. What happens if someone passes an $id with the following value: `$id = q\|3';DROP TABLE people;SELECT * FROM people WHERE id = '3\|;` [download] Needless to say, that's going to ruin your day with many databases (the excess SQL is simply to ensure that this query parses correctly). The solution is to quote the $id or to use placeholders: `sub SelectProfile { my ( $id, $dbh ) = @_; $id = $dbh->quote( $id ); my $selectquery = "SELECT * FROM people WHERE id = $id"; my $sth= $dbh->prepare($selectquery); $sth->execute(); my $rowdata=$sth->fetchrow_hashref; return $rowdata; }` [download] A couple of other things. One, why do you disconnect the database handle in this subroutine? That means someone trying to reuse this sub is in for a rude shock. This should be handled elsewhere. The other issue is the "SELECT *...". This is usually bad practice. See (Ovid: Death to Select Star!) Re: MySQL DBI Help for details. Cheers, Ovid Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.	[reply] [d/l] [select]
Re: (Ovid - potential security concern) Re: Passing database handles by hakkr (Chaplain) on Dec 06, 2001 at 15:30 UTC
Thanks for the tips I'm sure they will imporove my code. I have solved the problem by reducing my mysql wait_timeout variable and increasing my max connections variable. So all the sleeping mysql processes now timeout. sth->finish unfortunately does not seem to make any differance.	[reply]
Re: Passing database handles by MZSanford (Curate) on Dec 05, 2001 at 23:27 UTC
you need to add a line like the following to the end of the function: `$sth->finish();` [download] `$ perl -e 'do() \|\| ! do() ;' Undefined subroutine &main::try` [download]	[reply] [d/l] [select]
Re: Passing database handles by strat (Canon) on Dec 06, 2001 at 16:49 UTC
The $dbh is already a blessed reference (=object), so just write: my $result=SelectProfile($id, $dbh); An interesting idea might be: `my $result = $dbh->SelectProfile($id); .... package DBI; # or whatever package shell be extended sub SelectProfile { my ($self, $id) = @_; # $self is $dbh my $selectquery = "SELECT * FROM people WHERE id = '$id'"; my $sth= $self->prepare($selectquery) or die "Error in prepare: $DBI::errstr\n"; $sth->execute() or die "Error in execute: $DBI::errstr\n"; my $rowdata=$sth->fetchrow_hashref; $self->disconnect; # imho better done in main-program... + return $rowdata; }##END SelectProfile` [download] I suggest to evaluate returncodes if there's the slightest possibility that an error happenes, so nearly all the time. Best regards, `perl -e "print a\|r,p\|d=>b\|p=>chr 3**2 .7=>t and t"`	[reply] [d/l] [select]
Re: Passing database handles by perrin (Chancellor) on Dec 05, 2001 at 22:58 UTC
Are you running under mod_perl?	[reply]
Re: Re: Passing database handles by hakkr (Chaplain) on Dec 06, 2001 at 14:22 UTC
no mod_perl just CGI with dbi 1.2	[reply]
Re: Re: Re: Passing database handles by perrin (Chancellor) on Dec 06, 2001 at 20:05 UTC
Okay. I just asked because Apache::DBI intentionally disables the disconnect method. Under CGI, you should definitely see your db handles go away because the CGI processes will go away after serving a request.	[reply]