source: branches/stable/cgi-bin/access-pwd-update.pl@ 862

#!/usr/bin/perl
# Update IPDB users table with user/password data from 5-minute
# cron'ed push from billing
###
# SVN revision info
# $Date: 2010-07-26 21:00:00 +0000 (Mon, 26 Jul 2010) $
# SVN revision $Rev: 445 $
# Last update by $Author: kdeugau $
###
# Copyright (C) 2007-2010 - Kris Deugau

use strict;
use warnings;
use DBI;

# don't remove!  required for GNU/FHS-ish install from tarball
##uselib##

use MyIPDB;

my $ip_dbh;
my $errstr;
($ip_dbh,$errstr) = connectDB_My;
if (!$ip_dbh) {
  die "Database error: $errstr\n";
}
initIPDBGlobals($ip_dbh);

my %userhash;
my $passfile = "/var/www/ipdb.example.com/ip/.htpasswd";

die ".htpasswd error:  file is empty!\n"
        if -z $passfile;

die ".htpasswd error:  file seems too small: ".(-s $passfile)."\n"
        if (-s $passfile <3000);

open HTPASS, "<$passfile";

my $sth = $ip_dbh->prepare("select count(*) from users where username=?");
my $sth2;
while (<HTPASS>) {
  chomp;
  my ($user,$pass) = split /:/;
  $sth->execute($user);
  my @data = $sth->fetchrow_array();
  my $sql;
  if ($data[0] == 0) {
    $sql = "insert into users (username,password) values ('$user','$pass')";
    print "new user: $user\n";
  } else {
    $sql = "update users set password='$pass' where username='$user'";
  }
  $sth2 = $ip_dbh->prepare($sql);
  $sth2->execute or print "error executing $sql: ".$DBI::errstr."\n";
  $userhash{$user} = $pass;
}

# and now to delete users that have been removed
$sth = $ip_dbh->prepare("select username,acl from users order by username");
$sth2 = $ip_dbh->prepare("delete from users where username=?");
$sth->execute;
while (my @data = $sth->fetchrow_array()) {
  if (!$userhash{$data[0]}) {
    print "deleting $data[0] (acl $data[1])\n";
    $sth2->execute($data[0])
        or print "error deleting $data[0]: ".$DBI::errstr."\n";
  }
}