view release on metacpan or  search on metacpan

lib/Apache2/AuthAny/DB.pm  view on Meta::CPAN

package Apache2::AuthAny::DB;

use strict;

use DBI;
use Data::Dumper;
use Digest::MD5 qw(md5_hex);

my $dbHandle;
our $VERSION = '0.201';

sub new {
    my $class = shift;
    my $self = {};

    unless ($dbHandle) {
        my $dbUser = $ENV{AUTH_ANY_DB_USER} || die "Env variable AUTH_ANY_DB_USER required";
        my $dbPasswordFile = $ENV{AUTH_ANY_DB_PW_FILE} || die "Env variable AUTH_ANY_DB_PW_FILE required";
        open(PWD, "<$dbPasswordFile") || die "Could not read password file, '$dbPasswordFile'. $!";
        my $dbPassword = <PWD>;
        close(PWD) || die "ouch $!";
        chomp $dbPassword;      #remove the trailing new line
        die "Could not get password" unless $dbPassword;
        my $dbName = $ENV{AUTH_ANY_DB_NAME} || die "Env variable AUTH_ANY_DB_NAME required";
        my $db;
        $db = $ENV{AUTH_ANY_DB} || "mysql";
        my $dsn = "database=$dbName";
        my $dbHost = $ENV{AUTH_ANY_DB_HOST};

        $dsn .= ";host=$dbHost" if $dbHost;
        $dbHandle = DBI->connect("DBI:$db:$dsn", $dbUser, $dbPassword) or die "user: $dbUser, errstr: $DBI::errstr";
        $dbHandle->do('SET CHARACTER SET utf8');
    }

    bless ($self, $class);
    return $self;
}

sub useDB {
    return;
    my $self = shift;
    my $auth_any_db = $self->{auth_any_db};
    unless ($dbHandle->do("use $auth_any_db") ) {
        die $dbHandle->errstr;
    }
}

sub getValidRoles {
    my $self = shift;
    $self->useDB();
    return $dbHandle->selectcol_arrayref('SELECT DISTINCT role FROM userRole');
}

sub getUserCookieByPID {
    my $self = shift;
    $self->useDB();
    my $pid = shift;
    return unless $pid;
    my $getCookieSql = 'select * from userAACookie where PID = ? limit 1';

    my $res = $dbHandle->selectrow_hashref($getCookieSql, undef, $pid);

    if ($res) {
        return $res;
    } elsif ($dbHandle->errstr) {
        die $dbHandle->errstr;
    } else {
        warn "DB entry for PID cookie, '$pid' missing";
        return;
    }
}

sub getUserByUID {
    my $self = shift;
    $self->useDB();
    my ($UID) = @_;
        my $SQL = 'SELECT * FROM user WHERE UID = ?';

    return $dbHandle->selectrow_hashref($SQL, undef, $UID);
}

sub searchUsers {
    my $self = shift;

lib/Apache2/AuthAny/DB.pm  view on Meta::CPAN

         my $SQL = 'SELECT username
                    FROM user
                    LEFT JOIN userRole ON user.UID = userRole.UID AND role = ?
                    WHERE role IS NULL';
         my $unames = $dbHandle->selectcol_arrayref($SQL, undef, $role);
         foreach my $n (@$unames) {
             $usernames{$n}++;
        }
    }

    if ($ident) {
        $queries++;
        my $SQL = 'SELECT username FROM user, userIdent
                   WHERE user.UID = userIdent.UID
                   AND userIdent.authId LIKE ?';
        my $unames = $dbHandle->selectcol_arrayref($SQL, undef, "%$ident%");
        my %identUsers;
        foreach my $n (@$unames) {
            $identUsers{$n}++;
        }
        foreach my $n (keys %identUsers) {
            $usernames{$n}++;
        }
    }

    # each query must find the name in order to return the name
    my @usernames;
    foreach my $n (keys %usernames) {
        push @usernames, $n if $usernames{$n} == $queries;
    }

    return \@usernames;
}

sub getUserByUsername {
    my $self = shift;
    my ($username) = @_;
    my $SQL = 'SELECT * FROM user WHERE username = ?';

    $self->useDB();
    return $dbHandle->selectrow_hashref($SQL, undef, $username);
}

sub getUserByAuthIdAndProvider {

    my $self = shift;
    my ($authId, $authProvider) = @_;
    return unless $authId && $authProvider;

    my $getUserSql = 'select a.* from user a, userIdent b WHERE a.UID = b.UID
                      AND authId = ? AND authProvider = ? limit 1';

    $self->useDB();
    return $dbHandle->selectrow_hashref($getUserSql, undef, $authId, $authProvider);
}

sub getBasicUser {
    my $self = shift;
    my ($user) = @_;

    my $sql = 'select user, password from basicAuth WHERE user = ?';

    $self->useDB();
    return $dbHandle->selectrow_hashref($sql, undef, $user);
}

sub getUserRoles {
    my $self = shift;
    my ($UID) = @_;

    my $getRoleSql = 'select role from userRole WHERE UID = ?';

    $self->useDB();
    return $dbHandle->selectcol_arrayref($getRoleSql, undef, $UID);
}

sub getUserRoleChoices {
    my $self = shift;
    my ($UID) = @_;

    my $getRoleSql = 'select role from userRoleChoice WHERE UID = ?';

    $self->useDB();
    return $dbHandle->selectcol_arrayref($getRoleSql, undef, $UID);
}

sub getUserIdentities {
    my $self = shift;
    my ($UID) = @_;

    my $sql = 'SELECT * FROM userIdent WHERE UID = ?';

    $self->useDB();
    return $dbHandle->selectall_arrayref($sql, { Slice => {} }, $UID);
}

sub getUserTiers {
    my $self = shift;
    my ($UID) = @_;

    my $getTierSql = 'select tier from userTier WHERE UID = ?';

    $self->useDB();
    return $dbHandle->selectall_arrayref($getTierSql, { Slice => {} }, $UID);
}

sub addUser {
    my $self = shift;
    my %user = @_;

    my @valid_cols = qw[username firstName lastName active];
    my @cols;
    my @passed_values;
    my @values;
    foreach my $col (@valid_cols) {
        if (exists $user{$col}) {
            push @cols, $col;
            push @passed_values, $user{$col};
            push @values, '?';
        }
    }