| Subject: | [IMPROVEMENT]: New additions to CGI::Session::Auth::DBI |
Dear Mr. Lillich
This patch add some modifications I think you can be interested in.
The three most important are:
1) sprintf() is not used anymore in query building
String interpolation is faster and cleaner.
2) Instead of just parameters for database connection, the constructor now accepts a single parameter holding a DBI Object.
Usually I share a DBI connection throught all my application source code
3) Introduced a new parameter into the constructor to tell CGI::Session::Auth::DBI how to update and compare password values (I protect my database-stored passwords with MD5 Digest armoring).
Hope you enjoy!
Thank you!
Best regards.
--- /usr/local/share/perl/5.6.1/CGI/Session/Auth.old/DBI.pm Tue Sep 21 17:03:36 2004
+++ ./DBI.pm Thu Sep 23 17:31:32 2004
@@ -4,7 +4,7 @@
# Copyright (c) 2003 Jochen Lillich <jl@teamlinux.de>
###########################################################
#
-# $Id: DBI.pm,v 1.6 2003/10/31 08:28:33 jlillich Exp $
+# $Id: DBI.pm,v 1.6.1 2004/09/23 16:13:23 champs Exp $
#
package CGI::Session::Auth::DBI;
@@ -16,7 +16,7 @@
use Carp;
use DBI;
-our $VERSION = do { my @r = (q$Revision: 1.6 $ =~ /\d+/g); sprintf "%d." . "%03d" x (scalar @r - 1), @r; };
+our $VERSION = do { my @r = (q$Revision: 1.6.1 $ =~ /\d+/g); sprintf "%d." . "%03d" x (scalar @r - 1), @r; };
# column names in database
my $COL_USERID = 'userid';
@@ -24,7 +24,7 @@
my $COL_PASSWORD = 'passwd';
my $COL_IPUSERID = 'userid';
my $COL_IPADDR = 'network';
-my $COL_IPMASK = "netmask";
+my $COL_IPMASK = 'netmask';
###########################################################
###
@@ -52,6 +52,8 @@
# class specific parameters
#
+ # Check to see if we got a Database Handler.
+ unless( $params->{DBH} ){
# parameter 'DSN': DBI data source name
my $dsn = $params->{DSN} || croak("No DSN parameter");
# parameter 'DBUser': database connection username
@@ -60,20 +62,28 @@
my $dbpasswd = $params->{DBPasswd} || "";
# parameter 'DBAttr': optional database connection attributes
my $dbattr = $params->{DBAttr} || {};
+ # database handle
+ $self->{dbh} =
+ DBI->connect( $dsn, $dbuser, $dbpasswd, $dbattr)
+ or croak( "DB error: " . $DBI::errstr );
+ }else{
+ $self->{dbh} = $params->{DBH};
+ }
+
# parameter 'UserTable': name of user data table
$self->{usertable} = $params->{UserTable} || 'auth_user';
# parameter 'GroupTable': name of user data table
$self->{grouptable} = $params->{GroupTable} || 'auth_group';
# parameter 'IPTable': name of ip network table
$self->{iptable} = $params->{IPTable} || 'auth_ip';
+ # parameter 'PwdPlaceHolder': string to be used
+ # as placeholder for retrieve / set encripted password field
+ $self->{pwdplaceholder} = $params->{PwdPlaceHolder} || '?';
#
# class members
#
- # database handle
- $self->{dbh} = DBI->connect($dsn, $dbuser, $dbpasswd, $dbattr) or croak("DB error: " . $DBI::errstr);
-
# blessed are the greek
bless($self, $class);
@@ -101,23 +111,21 @@
my $result = 0;
- my $query = sprintf(
- "SELECT * FROM %s WHERE %s ='%s' AND %s = '%s'",
- $self->{usertable},
- $COL_USERNAME,
- $username,
- $COL_PASSWORD,
- $password
- );
+ my $query = qq{SELECT *
+ FROM "$self->{usertable}"
+ WHERE $COL_USERNAME = ?
+ AND $COL_PASSWORD = $self->{pwdplaceholder}
+ };
+
$self->_debug("query: $query");
# search for username
my $sth = $self->_dbh->prepare($query);
- $sth->execute or croak _dbh->errstr;
+ $sth->execute( $username, $password ) or croak _dbh->errstr;
if (my $rec = $sth->fetchrow_hashref) {
$self->_debug("found user entry");
$self->_extractProfile($rec);
$result = 1;
- $self->info("user '$username' logged in");
+ $self->_info("user '$username' logged in");
}
$sth->finish;
@@ -164,8 +172,7 @@
$self->_extractProfile($user);
$result = 1;
last;
- }
- else {
+ } else {
$self->_debug("no member of this network");
}
@@ -186,14 +193,14 @@
my $self = shift;
my ($userid) = @_;
- my $query = sprintf(
- "SELECT * FROM %s WHERE userid='%s'",
- $self->{usertable},
- $userid
- );
+ my $query = qq{SELECT *
+ FROM $self->{usertable}
+ WHERE userid = ?
+ };
+
$self->_debug("query: $query");
my $sth = $self->_dbh->prepare($query);
- $sth->execute() or croak $self->_dbh()->errstr;
+ $sth->execute( $userid ) or croak $self->_dbh()->errstr;
if (my $rec = $sth->fetchrow_hashref) {
$self->_debug("Found user entry");
$self->_extractProfile($rec);
@@ -216,7 +223,9 @@
my $first = 1;
foreach (keys %{$self->{profile}}) {
if ($_ ne $COL_USERID) {
- $query .= (($first) ? '' : ', ') . $_ . " = ?";
+ $query .= ( ($first) ? '' : ', ' ) .
+ $_ . ' = ' . ( $_ eq $COL_PASSWORD ? $self->{pwdplaceholder} : '?' );
+
push @values, $self->{profile}{$_};
$first = 0;
}
@@ -226,7 +235,6 @@
my $sth = $self->_dbh()->prepare($query);
$sth->execute(@values, $self->{userid}) or croak $self->_dbh()->errstr;
-
}
###########################################################
@@ -273,7 +281,8 @@
foreach ( keys %$rec ) {
$self->{profile}{$_} = $rec->{$_};
}
-};
+}
+;
###########################################################
@@ -288,16 +297,15 @@
$self->_debug("get data for userid: ", $userid);
- my $query = sprintf(
- "SELECT * FROM %s WHERE %s='%s'",
- $self->{usertable},
- $COL_USERID,
- $userid
- );
+ my $query = qq{SELECT *
+ FROM $self->{usertable}
+ WHERE $COL_USERID = ?
+ };
+
$self->_debug("query: $query");
# search for username
my $sth = $self->_dbh->prepare($query);
- $sth->execute or croak _dbh->errstr;
+ $sth->execute( $userid ) or croak _dbh->errstr;
return $sth->fetchrow_hashref;
}
@@ -318,24 +326,23 @@
=head1 SYNOPSIS
use CGI;
- use CGI::Session;
- use CGI::Session::Auth::DBI;
+use CGI::Session;
+use CGI::Session::Auth::DBI;
- my $cgi = new CGI;
- my $session = new CGI::Session(undef, $cgi, {Directory=>'/tmp'});
- my $auth = new CGI::Session::Auth({
+my $cgi = new CGI;
+my $session = new CGI::Session(undef, $cgi, {Directory=>'/tmp'});
+my $auth = new CGI::Session::Auth({
CGI => $cgi,
Session => $session,
DSN => 'dbi:mysql:host=localhost,database=cgiauth',
});
- $auth->authenticate();
+$auth->authenticate();
- if ($auth->loggedIn) {
+if ($auth->loggedIn) {
showSecretPage;
- }
- else {
+} else {
showLoginPage;
- }
+}
@@ -388,14 +395,16 @@
=over 1
-=item B<DSN>: Data source name for the database connection (mandatory).
+=item B<DBH>: DBI Object ready to use (conflicts with B<DSN>, B<DBUser>, B<DBPasswd>, B<DBAttr> - those will be ignored)
+
+=item B<DSN>: Data source name for the database connection (mandatory, unless B<DBH> is present).
For an explanation, see the L<DBI> documentation.
-=item B<DBUser>: Name of the user account used for the database connection. (Default: none)
+=item B<DBUser>: Name of the user account used for the database connection. (Default: none, no effect if B<DBH> present)
-=item B<DBPasswd>: Password of the user account used for the database connection. (Default: none)
+=item B<DBPasswd>: Password of the user account used for the database connection. (Default: none, no effect if B<DBH> present)
-=item B<DBAttr>: Optional attributes used for the database connection. (Default: none)
+=item B<DBAttr>: Optional attributes used for the database connection. (Default: none, no effect if B<DBH> present)
=item B<UserTable>: Name of the table containing the user authentication data and profile. (Default: 'auth_user')