LinuxQuestions.org - Sync LDAP users with MySQL

I'm trying to get the below script to work. It is supposed to sync ldap users to MySQL users with passwords and attributes. I followed instructions inhttp://mailwatch.sourceforge.net/dok...pcryptauthsync and here is the script:

Code:

#!/usr/bin/perl

 

#

# Gets mail users from an ldapsearch and creates the user in the mailscanner

# mysql database if they not yet exist.  Updates the full name and password

# values for all existing users when run as well.  The script simply drops

# and re-adds the users-filters entries for all users.

#

# - Leah Cunningham <leah@heinous.org>

#

# INCLUDES

#

use DBI;

use MIME::Base64::Perl;

use Data::Dumper;

use Net::LDAP;

#

# CONFIGURATION VARIABLES

#

ReadConfig("ldap_conf.pl");

my $LDAPPASS = command_output("cat /etc/ldap.secret");

my $ADM_GROUP = "useradmins";

my $SQL_DB_HOST = "localhost";

my $SQL_DB_PORT = "3306";

my $SQL_DB_USER = "root";

my $SQL_DB_PASS = "secret";

my $SQL_DB_NAME = "mailscanner";

#

# GLOBAL VARIABLES

#

my @ADMINS;

my @USERS_QUEUE;

my @USERS_UPDATE_QUEUE;

my @USER_FILTERS_QUEUE;

my $DBH = DBI->connect( "dbi:mysql:database=$SQL_DB_NAME;host=$SQL_DB_HOST;port=$SQL_DB_PORT",

        $SQL_DB_USER,$SQL_DB_PASS, {

                RaiseError => 1,

                AutoCommit => 0,

        }

) || die "Database connection not made: $DBI::errstr";

my $LDAP = LDAP_Bind($Config::ldap_server,$Config::ldap_admin,$LDAPPASS);

 

&main;

$DBH->disconnect();

 

sub main {

        #&drop_users;

        &drop_user_filters;

        &fetch_admins;

        my @users = command_output_array(

                "ldapsearch -x -D $Config::ldap_admin -w$LDAPPASS uid=*@*.* mail | awk '/uid/ {print \$2}' | awk -F, '{print \$1}' | awk -Fuid= '{print \$2}'"

        );

        foreach my $user (@users) {

                next if ($user !~ m/\S@\S/) or ($user =~ m/.*@.*@/);

                my ($username,$fqdomain) = split(/@/, $user);

                my ($domain,$ext) = split(/\./, $fdomain);

                #

                # Getting the user settings from LDAP

                #

                my $filter = "(uid=$username\@$fqdomain)";

                my $mesg = $LDAP->search(filter=>$filter,

                        base=>$Config::usersdn,

                        attrs=> ["uid","userPassword","sn","givenName","mail"] );

                my @entries = $mesg->entries;

                my $sn = $entries[0]->get_value("sn");

                my $givenName = $entries[0]->get_value("givenName");

                my $password = $entries[0]->get_value("userPassword");

                my @mail = $entries[0]->get_value("mail");

                my $fullname = $givenName . ' ' . $sn;

                # print "username: $username, fdomain: $fdomain, domain: $domain, ext: $ext\n";

                if (! check_exists($username . '@' . $fqdomain)) {

                        #print "user: $username\@$fqdomain not yet in database ... queuing for addition\n";

                        #

                        # Now we need to queue up the user for addition

                        #

                        queue_user($username . '@' . $fqdomain,$password,$fullname,@mail);

                        #print "username: $username, password: $password\n";

                } else {

                        ### MAYBE SOME UPDATE CODE HERE ###

                        queue_user_update($username . '@' . $fqdomain,$password,$fullname,@mail);

                        #print "user: $username\@$fqdomain is in the database ... skipping\n";

                }

        }

        if (@USERS_QUEUE) {

                &create_users;

        } else { print "No users to add $USERS_QUEUE\n"; print Dumper(@USERS_QUEUE); }

        if (@USERS_UPDATE_QUEUE) {

                &update_users;

        } else { print "No users to update $USERS_UPDATE_QUEUE\n"; print Dumper(@USERS_QUEUE); }

        if (@USER_FILTERS_QUEUE) {

                &create_user_filters;

        } else { print "No user filter entries to add @USER_FILTERS_QUEUE\n"; }

}

sub drop_users {

        my $drop = "DROP TABLE `users`";

        my $create = "CREATE TABLE `users` (  `username` varchar(60) NOT NULL default '',  `password` varchar(32) default NULL,  `fullname` varchar(50) NOT NULL default '',  `type` enum('A','D','U','R','H') default NULL,  `quarantine_report` tinyint(1) default '0',  `spamscore` tinyint(4) default '0',  `highspamscore` tinyint(4) default '0',  `noscan` tinyint(1) default '0',  `quarantine_rcpt` varchar(60) default NULL,  PRIMARY KEY  (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        $DBH->do($drop);

        $DBH->do($create);

}

sub update_users {

 

        foreach my $user (@USERS_UPDATE_QUEUE) {

                #[$user,$pass,$fullname]

                my $username = $DBH->quote($user->[0]);

                my $password = $DBH->quote($user->[1]);

                my $fullname = $DBH->quote($user->[2]);

                #UPDATE `users` SET `password` = '{CRYPT}IssbC.vCx0TzI', `fullname` = 'Leah R. M. Cunningham' WHERE CONVERT(`username` USING utf8) = 'leahc@iplink.net' LIMIT 1;

                my $update = "UPDATE `users` SET `password` = $password, `fullname` = $fullname WHERE CONVERT(`username` USING utf8) = $username LIMIT 1;";

                $DBH->do($update);

        }

}

sub create_users {

        my $sql = qq{ INSERT INTO users VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) };

        my $sth = $DBH->prepare($sql);

        for(@USERS_QUEUE) {

                eval {

                        $sth->bind_param( 1, @$_->[0]);

                        $sth->bind_param( 2, @$_->[1]);

                        $sth->bind_param( 3, @$_->[2]);

                        $sth->bind_param( 4, @$_->[3]);

                        $sth->bind_param( 5, @$_->[4]);

                        $sth->bind_param( 6, @$_->[5]);

                        $sth->bind_param( 7, @$_->[6]);

                        $sth->bind_param( 8, @$_->[7]);

                        $sth->bind_param( 9, @$_->[8]);

                };

                $sth->execute();

                $DBH->commit();

                if( $@ ) {

                        warn "Database error: $DBI::errstr\n";

                        $dbh->rollback(); #just die if rollback is failing

                }

        }

}

sub drop_user_filters {

        my $drop = "DROP TABLE `user_filters`";

        my $create = "CREATE TABLE `user_filters` (  `username` varchar(60) NOT NULL default '',  `filter` text,  `verify_key` varchar(32) NOT NULL default '',  `active` enum('N','Y') default 'N',  KEY `user_filters_username_idx` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        $DBH->do($drop);

        $DBH->do($create);

}

sub create_user_filters {

        my $sql = qq{ INSERT INTO user_filters VALUES ( ?, ?, ?, ? ) };

        my $sth = $DBH->prepare($sql);

        for(@USER_FILTERS_QUEUE) {

                eval {

                        $sth->bind_param( 1, @$_->[0]);

                        $sth->bind_param( 2, @$_->[1]);

                        $sth->bind_param( 3, @$_->[2]);

                        $sth->bind_param( 4, @$_->[3]);

                };

                $sth->execute();

                $DBH->commit();

                if( $@ ) {

                        warn "Database error: $DBI::errstr\n";

                        $dbh->rollback(); #just die if rollback is failing

                }

        }

}

sub queue_user {

        my ($user,$pass,$fullname,@mail) = @_;

        chomp($user,$pass,$fullname,@mail);

        my @users_entry;

        my $is_admin = 'no';

        foreach my $admin (@ADMINS) {

                chomp( $admin );

                if ($admin =~ m/$user/) {

                        @users_entry = [$user,$pass,$fullname,'A','0','0','0','0','0'];

                        $is_admin = 'yes';

                        print "$admin equals $user\n";

                }

        }

        if ($is_admin =~ m/^no$/ ) {

                @users_entry = [$user,$pass,$fullname,'U','0','0','0','0','0'];

        }

        #print Dumper(@users_entry);

        my $rand = rand();

        foreach $mail (@mail) {

                my @user_filters_entry = [$user,$mail,$rand,'Y'];

                push(@USER_FILTERS_QUEUE,@user_filters_entry);

        }

        #print Dumper(@user_filters_entry);

        push(@USERS_QUEUE,@users_entry);

        #print Dumper(@USERS_QUEUE);

}

sub queue_user_update {

        my ($user,$pass,$fullname,@mail) = @_;

        chomp($user,$pass,$fullname,@mail);

        my $users_entry = [$user,$pass,$fullname];

        my $rand = rand();

        foreach $mail (@mail) {

                my @user_filters_entry = [$user,$mail,$rand,'Y'];

                push(@USER_FILTERS_QUEUE,@user_filters_entry);

        }

        #print Dumper(@user_filters_entry);

        push(@USERS_UPDATE_QUEUE,$users_entry);

        #print Dumper(@USERS_QUEUE);

}

sub check_exists {

        my ($user) = @_;

        chomp($user);

        my $sql = qq{ SELECT * FROM users where USERNAME LIKE ?};

        my $sth = $DBH->prepare($sql);

        $sth->bind_param(1, $user);

        $sth->execute;

        while ( my @rows = $sth->fetchrow ) {

                if ($rows[0]) {

                        return 1;

                } else {

                        return 0;

                }

        }

}

sub run_command {

        my (@command) = @_;

        system("@command 2>/dev/null") == 0

                or return "system command: \"@command\"\n\tfailed: $?";

}

sub command_output_array {

        my (@command) = @_;

        open(HANDLE, "@command|");

        my @output = <HANDLE>;

        close(HANDLE);

        my $count = 0;

        my $n_entries = $#output;

        for ( $count = $count; $count <= $n_entries; $count++ ) {

                chomp($output[$count]);

        }

        return @output;

}

sub command_output {

        my (@command) = @_;

        open(HANDLE, "@command|");

        my $output = <HANDLE>;

        close(HANDLE);

        chomp $output;

        return $output;

}

sub ReadConfig {

        my ($readfile) = @_;

        if ((! -r "$ENV{PWD}/$readfile") and (! -r "/etc/$readfile")) {

                die "        Error reading " . $readfile . " from working directory or /etc\n";

                } elsif (-r "$ENV{PWD}/$readfile") {

                        $readfile="$ENV{PWD}/$readfile";

                        package Config;

                        do $readfile;

                } else {

                        $readfile="/etc/$readfile";

                        package Config;

                        do $readfile;

        }

}

#

# Returns a connection to the ldap server

#

sub LDAP_Bind {

        my ($ldap_server,$ldap_admin,$ldap_pass) = @_;

        my $ldap = Net::LDAP->new($ldap_server) or die "Unable to connect to $ldap_server: $@\n";

        $ldap->bind    ("$ldap_admin",

                        password => "$ldap_pass") or die "Unable to bind: $@\n";

        return $ldap;

}

sub fetch_admins {

        #

        # Getting the user settings from LDAP

        #

        my $filter = "(cn=$ADM_GROUP)";

        my $mesg = $LDAP->search(filter=>$filter,

                base=>$Config::groupdn,

                attrs=> ["memberUid"] );

        my @entries = $mesg->entries;

        @ADMINS = $entries[0]->get_value("memberUid");

}

When i run the script it comes back with the following error:

Code:

[root@server]# ./ldapsync

#Can't call method "get_value" on an undefined value at ./ldapsync line #272.

what does this error mean? Am I missing a module?
Any help would be appreciated.