PHPList Export Unconfirmed Users and Blacklist them

By Jimmy Bonney | November 27, 2011

Enveloppe and mail

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)


For the time being, comments are managed by Disqus, a third-party library. I will eventually replace it with another solution, but the timeline is unclear. Considering the amount of data being loaded, if you would like to view comments or post a comment, click on the button below. For more information about why you see this button, take a look at the following article.