PHPList Export Unconfirmed Users and Blacklist them

Before making a new bulk import in PHPList it is important to ensure that the imported users will not override the “unconfirmed” status of existing users. Since PHPList does not offer options to blacklist existing unconfirmed users we will proceed in two steps:

1. Export unconfirmed users
2. Blaklist unconfirmed users

To export the unconfirmed users, follow the steps in the first resource link below and simply edit the file admin/export.php to replace

1
$subselect = '';

by

1
$subselect = ' user.confirmed = 0 ';

Once done, head over to the export user page (lists/admin/?page=export) to get the list of unconfirmed users. Clean the file to only keep the email column and save it into a textfile (blacklist.txt).

The second step consists of blacklisting the list previously exported. Follow the second link under the resources section below. Here is a summary of the actions to take:

1. Upload the users list (blacklist.txt) to the root of the PHPList installation (usually under the lists folder).

2. Create a file (for instance massblacklist.php) and copy the following code inside.

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   47
   48
   49
   50
   51
   52
   53
   54
   55
   56
   57
   58
   59
   60
   61
   62
   63
   64
   65
   66
   67
   68
   69
   70
   71
   72
   73
   74
   75
   76
   77
   78
   79
   80
   81
   82
   83
   84
   85
   86
   87
   88
   89
   90
   91
   92
   93
   94
   95
   96
   97
   98
   99
   100
   101
   102
   103
   104
   105
   106
   107
   108
   109
   110
   111
   112
   113
   114
   115
   116
   117
   118
   <?php
   //Here is your file with the list of emails with one email on each line, and nothing else..
   $emailfile="blacklist.txt";
   
   //here is a modified version of the addUserToBlackList function, so that the user_user table is also changed
   function addUserToBlackListUpdate($email) {
     $id= getUniqid();
     Sql_Query(sprintf('insert ignore into %s (email, blacklisted, uniqid) values (\'%s\', 1, \'%s\') on duplicate key update blacklisted = 1, uniqid =\'%s\'',
       $GLOBALS["tables"]["user"],addslashes($email), $id, $id));
       }
   
   //this code section is copied from the root index file, not sure how much of it you need, but doesn't seem to hurt..
   ob_start();
   $er = error_reporting(0);
   require_once dirname(__FILE__) .'/admin/commonlib/lib/magic_quotes.php';
   require_once dirname(__FILE__).'/admin/init.php';
   ## none of our parameters can contain html for now
   $_GET = removeXss($_GET);
   $_POST = removeXss($_POST);
   $_REQUEST = removeXss($_REQUEST);
   
   if (isset($_SERVER["ConfigFile"]) && is_file($_SERVER["ConfigFile"])) {
   #  print '<using>'."\n";
     include $_SERVER["ConfigFile"];
   } elseif (isset($_ENV["CONFIG"]) && is_file($_ENV["CONFIG"])) {
   #  print '<using>'."\n";
     include $_ENV["CONFIG"];
   } elseif (is_file("config/config.php")) {
   #  print '<using>'."\n";
     include "config/config.php";
   } else {
     print "Error, cannot find config file\n";
     exit;
   }
   if (0) {#isset($GLOBALS["developer_email"]) && $GLOBALS['show_dev_errors']) {
     error_reporting(E_ALL);
   } else {
     if (isset($error_level)) {
       error_reporting($error_level);
     } else {
       error_reporting($er);
     }
   }
   
   require_once dirname(__FILE__).'/admin/'.$GLOBALS["database_module"];
   require_once dirname(__FILE__)."/texts/english.inc";
   include_once dirname(__FILE__)."/texts/".$GLOBALS["language_module"];
   require_once dirname(__FILE__)."/admin/defaultconfig.inc";
   require_once dirname(__FILE__).'/admin/connect.php';
   include_once dirname(__FILE__)."/admin/languages.php";
   include_once dirname(__FILE__)."/admin/lib.php";
   $I18N= new phplist_I18N();
   
   if ($require_login || ASKFORPASSWORD) {
     # we need session info if an admin subscribes a user
     if (!empty($GLOBALS["SessionTableName"])) {
       require_once dirname(__FILE__).'/admin/sessionlib.php';
     }
     @session_start(); # it may have been started already in languages
   }
   
   if (!isset($_POST) && isset($HTTP_POST_VARS)) {
       require "admin/commonlib/lib/oldphp_vars.php";
   }
   
   /*
     We request you retain the inclusion of pagetop below. This will add invisible
     additional information to your public pages.
     This not only gives respect to the large amount of time given freely
     by the developers  but also helps build interest, traffic and use of
     PHPlist, which is beneficial to it's future development.
   
     Michiel Dethmers, Tincan Ltd 2000,2006
   */
   include "admin/pagetop.php";
   if (isset($_GET['id'])) {
     $id = sprintf('%d',$_GET['id']);
   } else {
     $id = 0;
   }
   
   //here is the section that processes and displays the (hopefully) changed or added emails
   //this code is sorta modified from some of the links in my original post
   echo '<html>';
   echo 'phplistadd running <br><br>';
   
   require_once dirname(__FILE__) .'/admin/commonlib/lib/userlib.php';
   
   //init
   $emails= array();
   $attributes=NULL;
   $rownum=0;
   
   // open file and loop, processing each line in turn
   $fh = fopen($emailfile, 'r');
   echo "file opened";
   if ($fh)
      {
      while (!feof($fh))
         {
         $theline = fgets($fh);
         $emails[$rownum] = trim($theline);
         addUserToBlackList($emails[$rownum], 'Admin Blacklisted');
         addUserToBlackListUpdate($emails[$rownum]);
         echo " yep - blacklisted: ".$emails[$rownum]."<br>";
         ++$rownum;
         }
         $count = 0;
         return;
      }
   else
      {
      echo 'Failed to open file "' . $emailfile . '".<br>';
      }
   
   // done
   
   ?>

3. Navigate to the file newly created (usually under lists/massblacklist.php)
4. If any problem, try to verify the permissions of the file (permissions 644)