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

Dear fellow monks,

I played a little with mod_perl and Apache::Session this afternoon, and, just for fun, decided to create kind of a forum, where everyone can read, but only authenticated users can write messages.

Mod_perl is a tricky thing, especially concerning global vars, so I put most of my code into a module and use it. The first quick and dirty steps were all successful, I could connect to the database, print out the messages and so on. But when I wanted to implement the authentication functions, I got stuck.
Well, here's my code: 
# this is index.pl
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use strict;
use Perlnews;

my $q = new CGI;
my $n = new Perlnews;

$n->html_head();

if ($q->param("op") eq "login") {
    $n->auth($q->param("login"), $q->param("password"));
    $n->main_page();
} else {
    $n->main_page();
}

$n->html_foot();




----------
# And this is a part of the module I wrote

### Apache Session thingies
my $request = Apache->request;
my $cookie = $request->header_in('Cookie');
$cookie =~ s/PN_SESSION_ID=(\w*)/$1/;

my %session;
tie %session, 'Apache::Session::MySQL', $cookie, { Handle => $dbh, Loc
+kHandle => $dbh };
my $session_cookie = "PN_SESSION_ID=$session{_session_id};";
$request->header_out("Set-Cookie" => $session_cookie);

## Authentication subroutine
sub auth {
    my ($login, $passwd) = @_;
    $sth = $dbh->prepare("SELECT id, login FROM users WHERE login = ? 
+AND passwd = ENCRYPT(?, passwd)");
    $sth->execute($login, $passwd);
    if ($sth->rows != 1) {
        $error = "Oops, the was something wrong with your login or pas
+sword!";
    } else {
        my $r = $sth->fetchrow_arrayref();
        $session{userid} = $r->[0];
        $session{login} = $r->[1];
    }
    $sth->finish;
}

[download]
Without having the if-else clause, everything went fine, it just printed out the main page. But after putting in the Session and auth stuff, I sometimes get the page, but mostly the server seems to hang in an endless loop... Netscapes comets keep moving, but there's no response. There seems to be nothing wrong on the server, system load and process list show nothing unusual. I guess it has to do with table locking, but actually I didn't even try to enter a login and passwd, I just hit Reload. Any ideas?

Replies are listed 'Best First'.
Re: mod_perl
by httptech (Chaplain) on Jul 10, 2000 at 06:05 UTC
    There's a potential bug in your code that might affect you down the road.

    The bug would occur if you ended up with two usernames that were identical, except for case, and the password was the same. So, if you have a user 'foo' registered, but lets say he forgot to capitalize his name, so he hit the back button and entered 'Foo' as the username with the same password.

    Since MySQL comparisons with the '=' operator are case-insensitive, your rows returned would be 2, which is != 1, so foo would be locked out using either username.

    Of course, you can get around this with careful attention to detail on the input side. How you fix it depends on how much leniency you want to give your users. It's a rare situation, but one you should watch out for when making username/password checking scripts with MySQL.

[reply]
      Since MySQL comparisons with the '=' operator are case-insensitive,...

      Whoops, I didn't know that! Shame on me! Thanks for pointing that out.
[reply]
Re: mod_perl, Apache::Session
by Ovid (Cardinal) on Jul 10, 2000 at 01:19 UTC
    Hmm... I will confess that I don't know much about mod_perl or Apache::Session, but I'll speculate a little since no one has replied to this yet.

    First, I notice that you are not checking your return codes on your SQL. For example: 

    $sth->execute($login, $passwd) or die $dbh->errstr;

    [download]
    If your SQL is not executing, this should give you some clues. Also, are you checking the return codes on your connect and disconnects? 
    $dbh = DBI->connect($data_source, $user, $password) or die $DBI::errst
+r;

    [download]
    Or, you can use RaiseError to avoid checking the return value of each call: 
    $dbh = DBI->connect($data_source, $user, $password, {
             RaiseError => 1});

    [download]
    Note that failure to connect or disconnect returns undef and sets $DBI::err and $DBI::errstr. Do not bother checking $! as it will not be set. I've never tried to execute SQL against a database that I have not successfully connected to, so I am curious if that may cause problems.

    Second, are you properly disconnecting from the database? I believe, like open files, that mod_perl will choke if you forget to close a database handle and then try to reopen it.

    Here's another bug that has bitten me: I have written code that connects or disconnects from databases based upon conditionals. As a result, I have sometimes had (shame on me) a close get skipped because I wrote my conditionals poorly.

    Are you locking the table and forgetting to unlock it? Perhaps your code is waiting for a table to unlock? And are you accessing locked and unlocked tables at the same time? Here an annoying little snippet from the book "MySQL & mSQL":

      Using locked and unlocked tables at the same time can cause the process thread to freeze. You must lock all of the tables you will be accessing during the time of the lock. Tables you access only before or after the lock do not need to be locked.
    Incidentally, newer versions of MySQL do not have this problem.

    I know most of this is probably irrelevant, but hopefully there may be a useful nugget or two.

    Cheers,
    Ovid

[reply]
[d/l]
[select]
      RaiseError is set, so I don't think I have to bother with additional error checking.

      I believe I don't have to care about database handles, since you can pre-connect to a database and server startup, and the database connection stays the same anyway. Hmm.

      And yes, it was indeed a locking issue, but see my other post on that.
[reply]
RE: mod_perl, Apache::Session
by Russ (Deacon) on Jul 10, 2000 at 01:13 UTC
    I'm a bit worried about the lack of my in auth(). You are using a "global" variable in $sth. Won't this cause $sth to be "shared"?

    I don't claim to be well-versed in mod_perl and Apache authentication handlers, but I seem to think that the auth handler is separate from the Apache processes. You would then be re-using $sth on each access, possibly interrupting the previous execution.

    ???

    Russ

[reply]
      Well, I actually overlooked that, but it didn't give my any error, though mod_perl is usually a really complaining b***h :) No, there was no "shared" message in the logfiles. But I actually found out, what caused the hang, and I have to say, I'm pretty stupid that I posted before I looked: when you tie the session to the database it gets locked until you untie it again or goes out of scope. I tied the session and then tried to access the database for pulling out the messages... Stupid, isn't it? Ok, I rewrote it, and I have no more hangs now.

      But I still can't get any usable results. I added a timestamp column to the session table to see if the session table actually gets accessed. The timestamp values change, as long as I get an Apache child which hasn't compiled the script yet, but then they don't. Plus, there isn't anything written into the database (no session values I want to put in).
[reply]

Back to Seekers of Perl Wisdom