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;
    $self->useDB();
    my %usernames;
    my ($u, $r, $n, $ident) = @_;
    my %user = %$u;
    my @role = @$r;
    my @norole = @$n;

    # username must be found in each query (AND) to be listed
    my $queries = 0;
    if ($user{username} || $user{lastName} || $user{firstName} || $user{organization} ) {
        $queries++;
        my @where;
        my @val;
        if ($user{username}) {
            push @where, "username LIKE ?";
            push @val, "%$user{username}%";

        }
        if ($user{lastName}) {
            push @where, "lastName LIKE ?";
            push @val, "%$user{lastName}%";

        }

        if ($user{firstName}) {
            push @where, "firstName LIKE ?";
            push @val, "%$user{firstName}%";

        }
        if ($user{organization}) {
            push @where, "organization LIKE ?";
            push @val, "%$user{organization}%";

        }
        my $where_clause = join " OR ", @where;

        my $SQL = "SELECT username FROM user WHERE $where_clause";
        my $unames = $dbHandle->selectcol_arrayref($SQL, undef, @val);

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

}

sub addUserRole {
    my $self = shift;
    my ($UID, $role) = @_;
    if ( $UID !~ /^(\d+)$/) {
        warn "bad input, '@_'";
        return;
    }

    # Make sure there is a user with $UID
    # This would not be necessary if we were using tables with foreign keys
    unless ($self->getUserByUID($UID)) {
        warn "UID, '$UID' not found in user table";
        return;
    }

    my $sql =  'INSERT INTO userRole       (UID, role) VALUES (?, ?)';
    my $sql2 = 'INSERT INTO userRoleChoice (UID, role) VALUES (?, ?)';

    $self->useDB();
    $self->removeUserRole($UID, $role); # prevent duplicate role errors
    if (   $dbHandle->do($sql,  undef, $UID, $role)
        && $dbHandle->do($sql2, undef, $UID, $role)) {
        return 1;
    } else {
        warn $dbHandle->errstr;
        return undef;
    }
}


sub removeUserRole {
    my $self = shift;
    my ($UID, $role) = @_;
    if ( $UID !~ /^(\d+)$/) {
        warn "bad input, '@_'";
        return;
    }

    # Make sure there is a user with $UID
    # This would not be necessary if we were using tables with foreign keys
    unless ($self->getUserByUID($UID)) {
        warn "UID, '$UID' not found in user table";
        return;
    }

    my $sql  = 'DELETE FROM userRole       WHERE UID = ? AND role = ?';
    my $sql2 = 'DELETE FROM userRoleChoice WHERE UID = ? AND role = ?';

    $self->useDB();
    if (   $dbHandle->do($sql,  undef, $UID, $role)
        && $dbHandle->do($sql2, undef, $UID, $role)) {
        return 1;
    } else {
        warn $dbHandle->errstr;
        return undef;
    }
}

sub loginPCookie {
    my $self = shift;
    my ($pCookie, $sCookie, $authId, $authProvider) = @_;
    unless ($pCookie && $authId && $authProvider) {
        warn "Missing pid, authId, or authProvider. Got input @_";
        return 0;
    }

    my $sql = "UPDATE userAACookie 
               SET authId = ?, authProvider = ?, SID = ?, state = ?, last = ?
               WHERE PID = ?";

    $self->useDB();
    if ($dbHandle->do($sql, undef,
                      $authId, $authProvider, $sCookie, 'authenticated', time, $pCookie)) {
        return 1;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}

sub logoutPCookie {
    my $self = shift;
    my ($pid) = @_;
    my $pCookie = $pid->{PID};
    unless ($pCookie) {
        warn "Missing pid. Got input @_";
        return 0;
    }
    my $logout_key = md5_hex(time . rand);
    my $sql = "UPDATE userAACookie SET state = ?, logoutKey = ?
               WHERE PID = ?";

    $self->useDB();
    if ($dbHandle->do($sql, undef, 'logged_out', $logout_key, $pCookie)) {
        $pid->{state} = 'logged_out';
        return 1;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}

sub statePCookie {
    my $self = shift;
    my ($pid, $state) = @_;
    my $pCookie = $pid->{PID};
    unless ($pCookie && $state) {
        warn "Missing pid or state. Got input @_";
        return 0;
    }
    my $sql = "UPDATE userAACookie SET state = ?
               WHERE PID = ?";

    $self->useDB();
    if ($dbHandle->do($sql, undef, $state, $pCookie)) {
        $pid->{state} = $state;
        return 1;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}

sub insertPCookie {
    my $self = shift;
    my ($pCookie, $sCookie, $logout_key) = @_;
    unless ($pCookie && $sCookie && $logout_key) {
        warn "Missing cookies or logout_key. Got input @_";
        return 0;
    }

    my $sql = "INSERT INTO userAACookie (PID, SID, logoutKey, last, created)
                 VALUES (?, ?, ?, ?, now())";

    $self->useDB();
    if ($dbHandle->do($sql, undef, $pCookie, $sCookie, $logout_key, time)) {
        return 1;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}

sub updatePCookieLastAccess {
    my $self = shift;
    my ($pCookie) = @_;

    unless ($pCookie) {
        warn "Missing pid";
        return 0;
    }

    my $sql = "UPDATE userAACookie
               SET last = ?
               WHERE pid = ?";

    $self->useDB();
    if ($dbHandle->do($sql, undef, time, $pCookie) eq 1) {
        return 1;
    } else {
        warn "Could not update DB with PID, '$pCookie'" . $dbHandle->errstr;
        return 0;
    }
}

sub updatePCookieLogoutKey {
    my $self = shift;
    my ($pCookie) = @_;
    unless ($pCookie) {
        warn "Missing pid";
        return 0;
    }

    my $sql = "UPDATE userAACookie
               SET logoutKey = ?
               WHERE pid = ?";

    $self->useDB();
    my $logout_key = md5_hex(time . rand);
    if ($dbHandle->do($sql, undef, $logout_key, $pCookie)) {
        return 1;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}

sub cleanupCookies {
    my $self = shift;
    $self->useDB();
    my $sql = qq[DELETE FROM userAACookie WHERE authId IS NULL and now() - created > 300];

    my $rc = $dbHandle->do($sql);
    if ($rc) {
        return $rc;
    } else {
        warn $dbHandle->errstr;
        return 0;
    }
}
1;