The mailing list for listmasters using Sympa

[Dan Pritts <address@concealed>]

as per previous posts, i am converting a user_table from 3.4 to 6.1

i copied code from Upgrade.pm & tools.pm to convert the crypt.
style stored passwords to the new md5 hash format

it appears that anything that has uppercase characters
does not work.  lowercase characters and numbers are ok.
Not sure about special characters.

I found mention of password_case sensitive/insensitive in
wwsympa.conf.  Changing it from sensitive to insensitive had
no effect.

suggestions?

my code is attached.  pretty simple.

danno
--
Dan Pritts, Sr. Systems Engineer
Internet2
office: +1-734-352-4953 | mobile: +1-734-834-7224

ESCC/Internet2 Joint Techs
July 11-15, 2010 - Columbus, Ohio
http://events.internet2.edu/2010/jt-oarnet/



#!/usr/bin/perl -w


#my $listname=This Is The List;
#my $listname=$ARGV[0];
#
#my $config="/sympa/basie/expl/$listname/config";

my $newsympadbDBI="DBI:mysql:"
my $newsympadbuser='xxx'
my $newsympadbpass='xxx';

#### get old subscriber table data from local copy of basie's mysql
my $oldsympadbDBI="DBI:mysql:";
my $oldsympadbuser="xxx"
my $oldsympadbpass="xxx";

my $oldsympaarc="/sympa/basie/arc";
my $newsympaarc="/sympa/arc";

my $sympaspool="/sympa/spool";

my $passwordcookie="xxx";

$ENV{'PERL5LIB'}='/usr/local/pkg/sympa-perl/lib/perl5/5.8.8:/usr/local/pkg/sympa-perl/lib/perl5/site_perl:/usr/lib/MHonArc';

use DBI();


$DEBUG=1;


sub dprint {
        return 1 unless ($DEBUG);
        my $neednewline=1;
        foreach $boof (@_) {
                if (defined $boof) {
                        print STDERR $boof; 
                        $neednewline=0 if ($boof =~ m{\n});
                }
        }
        if ($neednewline) { print STDERR "\n" };
        return 1;
}



require Crypt::CipherSaber;
require MIME::Base64;
require Digest::MD5;



# from sympa tools.pm
sub decrypt_password {
    my $inpasswd = shift ;
    &dprint( 'tools::decrypt_password $inpasswd');

    return $inpasswd unless ($inpasswd =~ /^crypt\.(.*)$/) ;
    $inpasswd = $1;

    my $cipher = Crypt::CipherSaber->new($passwordcookie);
    #unless (defined($cipher)){
    #    $cipher = ciphersaber_installed();
    #}
    #if ($cipher eq 'no_cipher') {
    #    &dprint('password seems crypted while CipherSaber is not installed 
!');
    #    return $inpasswd ;
    #}
    return ($cipher->decrypt(&MIME::Base64::decode($inpasswd)));
}

# from sympa tools.pm
############################################################
#  md5_fingerprint                                         #
############################################################
#  The algorithm MD5 (Message Digest 5) is a cryptographic #
#  hash function which permit to obtain                    #
#  the fingerprint of a file/data                          #
#                                                          #
# IN : a string                                            #
#                                                          #
# OUT : md5 digest                                         #
#     | undef                                              #
#                                                          #
############################################################
sub md5_fingerprint {

    my $input_string = shift;
    return undef unless (defined $input_string);
    #chomp $input_string;

    my $digestmd5 = new Digest::MD5;
    $digestmd5->reset;
    $digestmd5->add($input_string);
    return (unpack("H*", $digestmd5->digest));
}


# a little bit cribbed from sympa Upgrade.pm md5_encode_password
sub fixpw {

        my $pwstring=$_[0];

        my $clear_password ;

        if ($pwstring =~ /^crypt.(.*)$/) {
            $clear_password = &decrypt_password($pwstring);
        }else{ ## Old style cleartext passwords
            $clear_password = $pwstring;
        }
        &dprint (">> original password was: $clear_password\n");

        my $encryptedpw=&md5_fingerprint($clear_password);
        return $encryptedpw;

}

 



#mysql> desc user_table;
#+-------------------+--------------+------+-----+---------+-------+
#| Field             | Type         | Null | Key | Default | Extra |
#+-------------------+--------------+------+-----+---------+-------+
#| email_user        | varchar(100) | NO   | PRI |         |       | 
#| gecos_user        | varchar(150) | YES  |     | NULL    |       | 
#| password_user     | varchar(40)  | YES  |     | NULL    |       | 
#| cookie_delay_user | int(11)      | YES  |     | NULL    |       | 
#| lang_user         | varchar(10)  | YES  |     | NULL    |       | 
#+-------------------+--------------+------+-----+---------+-------+

&dprint("reading old db");
######################################################################
#### get old user table data from local copy of basie's mysql
my $oldsympadb = DBI->connect($oldsympadbDBI, $oldsympadbuser, 
        $oldsympadbpass, {'RaiseError' => 1});

#my $selectcmd="select * from subscriber_table where list_subscriber = 
\'$listname\' and included_subscriber != '1'";
my $selectcmd=  qq{select * from user_table where email_user like 
'\%internet2.edu\%';};

# stblhsh = subscriber table hash
my $stblhsh = $oldsympadb->selectall_hashref($selectcmd,"email_user");
$oldsympadb->disconnect;
#### end old subscriber table data
######################################################################

&dprint("\n>-done reading old db\n\n");


#mysql> desc user_table;
#+------------------------+--------------+------+-----+---------+-------+
#| Field                  | Type         | Null | Key | Default | Extra |
#+------------------------+--------------+------+-----+---------+-------+
#| attributes_user        | text         | YES  |     | NULL    |       | 
#| cookie_delay_user      | int(11)      | YES  |     | NULL    |       | 
#| data_user              | text         | YES  |     | NULL    |       | 
#| email_user             | varchar(100) | NO   | PRI |         |       | 
#| gecos_user             | varchar(150) | YES  |     | NULL    |       | 
#| lang_user              | varchar(10)  | YES  |     | NULL    |       | 
#| last_login_date_user   | int(11)      | YES  |     | NULL    |       | 
#| last_login_host_user   | varchar(60)  | YES  |     | NULL    |       | 
#| password_user          | varchar(40)  | YES  |     | NULL    |       | 
#| wrong_login_count_user | int(11)      | YES  |     | NULL    |       | 
#+------------------------+--------------+------+-----+---------+-------+




&dprint("connecting to new db");
my $newsympadb = DBI->connect($newsympadbDBI, $newsympadbuser, 
        $newsympadbpass, {'RaiseError' => 1} );

$insertluser = $newsympadb->prepare("INSERT INTO user_table VALUES 
(NULL,?,NULL,?,?,?,NULL,NULL,?,NULL)");

foreach my $luser (keys %{$stblhsh}) {

        foreach $p 
('gecos_user','password_user','cookie_delay_user','lang_user') {
                if ( ! defined ($stblhsh->{$luser}->{$p}) ) {
                        $stblhsh->{$luser}->{$p} = 'NULL';
                }
        }

        my $gecos_user=$stblhsh->{$luser}->{'gecos_user'};
        my $password_user=$stblhsh->{$luser}->{'password_user'};
        my $cookie_delay_user=$stblhsh->{$luser}->{'cookie_delay_user'};
        my $lang_user=$stblhsh->{$luser}->{'lang_user'};

        chomp $gecos_user;
        $gecos_user =~ s/
$//;

        # fix password encoding
        unless (        
                           ($password_user =~ /^[0-9a-f]{32}/)
                        || ($password_user eq "NULL")
                        || ($password_user eq "") 
        ) {

                &dprint("> $luser");
                $password_user=&fixpw($password_user);
        }

        &dprint("> inserting $luser");
        $insertluser->execute( 
$cookie_delay_user,$luser,$gecos_user,$lang_user,$password_user);

}

&dprint(">-closing down database");
$newsympadb->disconnect;