1<?php 2 3verifyCsrfGetToken(); 4 5$access = accessLevel('export'); 6$list = $_SESSION['export']['list']; 7 8#https://owasp.org/www-community/attacks/CSV_Injection 9function cleanForExport($text) { 10 $text = preg_replace('/^=/','\' =',$text); 11 $text = preg_replace('/^\+/','\' +',$text); 12 $text = preg_replace('/^\-/','\' -',$text); 13 $text = preg_replace('/^@/','\' @',$text); 14 return $text; 15} 16 17switch ($access) { 18 case 'owner': 19 if ($list) { 20 $check = Sql_Fetch_Assoc_Query(sprintf('select id from %s where owner = %d and id = %d', 21 $GLOBALS['tables']['list'], $_SESSION['logindetails']['id'], $list)); 22 if (empty($check['id'])) { 23 echo Error(s('That is not your list')); 24 25 return; 26 } 27 } 28 29 $querytables = $GLOBALS['tables']['list'].' list INNER JOIN '.$GLOBALS['tables']['listuser'].' listuser ON listuser.listid = list.id'. 30 ' INNER JOIN '.$GLOBALS['tables']['user'].' user ON listuser.userid = user.id'; 31 $subselect = ' and list.owner = '.$_SESSION['logindetails']['id']; 32 $listselect_and = ' and owner = '.$_SESSION['logindetails']['id']; 33 break; 34 case 'all': 35 if ($list) { 36 $querytables = $GLOBALS['tables']['user'].' user'.' INNER JOIN '.$GLOBALS['tables']['listuser'].' listuser ON user.id = listuser.userid'; 37 $subselect = ''; 38 } else { 39 $querytables = $GLOBALS['tables']['user'].' user'; 40 $subselect = ''; 41 } 42 $listselect_and = ''; 43 break; 44 case 'none': 45 default: 46 $querytables = $GLOBALS['tables']['user'].' user'; 47 $subselect = ' and user.id = 0'; 48 $listselect_and = ' and owner = 0'; 49 break; 50} 51 52if ($_SESSION['export']['column'] == 'nodate') { 53 //# fetch dates as min and max from user table 54 if ($list) { 55 $dates = Sql_Fetch_Row_Query(sprintf('select date(min(user.modified)),date(max(user.modified)) from %s where listid = %d %s', 56 $querytables, $list, $subselect)); 57 } else { 58 //# this takes begin and end of all users, regardless of whether they are on the list of this admin @TODO 59 $dates = Sql_Fetch_Row_Query(sprintf('select date(min(user.modified)),date(max(user.modified)) from %s ', 60 $querytables)); 61 } 62 63 $fromdate = $dates[0]; 64 $todate = $dates[1]; 65} else { 66 $fromdate = $_SESSION['export']['fromdate']; 67 $todate = $_SESSION['export']['todate']; 68} 69ob_end_clean(); 70if ($list) { 71 $filename = s('phpList Export on %s from %s to %s (%s).csv', ListName($list), $fromdate, $todate, date('Y-M-d')); 72} else { 73 $filename = s('phpList Export from %s to %s (%s).csv', $fromdate, $todate, date('Y-M-d')); 74} 75$filename = trim(strip_tags($filename)); 76 77if (!empty($_SESSION['export']['fileready']) && is_file($_SESSION['export']['fileready'])) { 78 header('Content-type: '.$GLOBALS['export_mimetype'].'; charset=UTF-8'); 79 header("Content-disposition: attachment; filename=\"$filename\""); 80 $fp = fopen($_SESSION['export']['fileready'], 'r'); 81 while (!feof($fp)) { 82 echo fgets($fp); 83 } 84 fclose($fp); 85 unlink($_SESSION['export']['fileready']); 86 unset($_SESSION['export']); 87 exit; 88} 89 90$exportfileName = tempnam($GLOBALS['tmpdir'], $GLOBALS['installation_name'].'-export'.time()); 91$exportfile = fopen($exportfileName, 'w'); 92$col_delim = "\t"; 93if (EXPORT_EXCEL) { 94 $col_delim = ','; 95} 96$row_delim = "\n"; 97 98if ($GLOBALS['commandline'] && VERBOSE) { 99 cl_output(s(' * exporting to %s',$exportfileName)); 100} 101 102if (is_array($_SESSION['export']['cols'])) { 103 foreach ($DBstruct['user'] as $key => $val) { 104 if (in_array($key, $_SESSION['export']['cols'])) { 105 if (strpos($val[1], 'sys') === false) { 106 fwrite($exportfile, cleanForExport($val[1]).$col_delim); 107 } elseif (preg_match('/sysexp:(.*)/', $val[1], $regs)) { 108 if ($regs[1] == 'ID') { // avoid freak Excel bug: http://mantis.phplist.com/view.php?id=15526 109 $regs[1] = 'id'; 110 } 111 fwrite($exportfile, cleanForExport($regs[1]).$col_delim); 112 } 113 } 114 } 115} 116$attributes = array(); 117if (is_array($_SESSION['export']['attrs'])) { 118 $res = Sql_Query("select id,name,type from {$tables['attribute']}"); 119 while ($row = Sql_fetch_array($res)) { 120 if (in_array($row['id'], $_SESSION['export']['attrs'])) { 121 fwrite($exportfile, trim(stripslashes($row['name'])).$col_delim); 122 array_push($attributes, array('id' => $row['id'], 'type' => $row['type'])); 123 } 124 } 125} 126$exporthistory = 0; 127if ($_SESSION['export']['column'] == 'listentered') { 128 $column = 'listuser.entered'; 129} elseif ($_SESSION['export']['column'] == 'historyentry') { 130 $column = 'user_history.date'; 131 $querytables .= ', '.$GLOBALS['tables']['user_history'].' user_history '; 132 $subselect .= ' and user_history.userid = user.id and user_history.summary = "Profile edit"'; 133 fwrite($exportfile, 'IP'.$col_delim); 134 fwrite($exportfile, 'Change Summary'.$col_delim); 135 fwrite($exportfile, 'Change Detail'.$col_delim); 136 $exporthistory = 1; 137} else { 138 switch ($_SESSION['export']['column']) { 139 case 'entered': 140 $column = 'user.entered'; 141 break; 142 default: 143 $column = 'user.modified'; 144 break; 145 } 146} 147 148//#$subselect .= ' limit 500'; // just to test the progress meter 149 150if ($list) { 151 $query = sprintf('select * from 152 %s where user.id = listuser.userid and listuser.listid = %d and %s >= "%s 00:00:00" and %s <= "%s 23:59:59" %s 153 ', $querytables, $list, $column, $fromdate, $column, $todate, $subselect) 154 ; 155} else { 156 $query = sprintf(' 157 select * from %s where %s >= "%s 00:00:00" and %s <= "%s 23:59:59" %s', 158 $querytables, $column, $fromdate, $column, $todate, $subselect); 159} 160 161if (VERBOSE) { 162 cl_output(' SQL '.$query); 163} 164$result = Sql_query($query); 165$todo = Sql_Affected_Rows(); 166$done = 0; 167 168fwrite($exportfile, s('List Membership').$row_delim); 169 170while ($user = Sql_fetch_assoc($result)) { 171 //# re-verify the blacklist status 172 if (empty($user['blacklisted']) && isBlackListed($user['email'])) { 173 $user['blacklisted'] = 1; 174 Sql_Query(sprintf('update %s set blacklisted = 1 where email = "%s"', $GLOBALS['tables']['user'], 175 $user['email'])); 176 } 177 178 set_time_limit(500); 179 if ($done % 50 == 0) { 180 if (!$GLOBALS['commandline']) { 181 echo '<script type="text/javascript"> 182 var parentJQuery = window.parent.jQuery; 183 parentJQuery("#progressbar").updateProgress("' .$done.','.$todo.'"); 184 </script>'; 185 flush(); 186 } else { 187 cl_progress($done.' out of '.$todo); 188 } 189 } 190 ++$done; 191 reset($_SESSION['export']['cols']); 192 foreach ($_SESSION['export']['cols'] as $key => $val) { 193 if (VERBOSE) { 194 cl_output($key.' '.$val.' '.$user[$val].' '.strtr($user[$val], $col_delim, ',').$col_delim); 195 } 196 fwrite($exportfile, strtr(cleanForExport($user[$val]), $col_delim, ',').$col_delim); 197 } 198 reset($attributes); 199 foreach ($attributes as $key => $val) { 200 $value = UserAttributeValue($user['id'], $val['id']); 201 if (VERBOSE) { 202 cl_output($key.' '.$val.' '.quoteEnclosed($value, $col_delim, $row_delim).$col_delim); 203 } 204 fwrite($exportfile, quoteEnclosed(cleanForExport($value), $col_delim, $row_delim).$col_delim); 205 } 206 if ($exporthistory) { 207 fwrite($exportfile, quoteEnclosed($user['ip'], $col_delim, $row_delim).$col_delim); 208 fwrite($exportfile, quoteEnclosed($user['summary'], $col_delim, $row_delim).$col_delim); 209 fwrite($exportfile, quoteEnclosed($user['detail'], $col_delim, $row_delim).$col_delim); 210 } 211 212 $lists = Sql_query("select listid,name from 213 {$tables['listuser']},{$tables['list']} where userid = ".$user['id']." and 214 {$tables['listuser']}.listid = {$tables['list']}.id $listselect_and"); 215 if (!Sql_Affected_rows($lists)) { 216 fwrite($exportfile, 'No Lists'); 217 } 218 while ($list = Sql_fetch_array($lists)) { 219 fwrite($exportfile, stripslashes($list['name']).'; '); 220 } 221 fwrite($exportfile, $row_delim); 222} 223 224if (!$GLOBALS['commandline']) { 225 echo '<script type="text/javascript"> 226 var parentJQuery = window.parent.jQuery; 227 parentJQuery("#busyimage").show(); 228 parentJQuery("#progressbar").updateProgress("' .$todo.','.$todo.'"); 229 parentJQuery("#busyimage").hide(); 230 parentJQuery("#progresscount").html("' .s('All done').'"); 231 </script>'; 232 flush(); 233 $_SESSION['export']['fileready'] = $exportfileName; 234 echo '<script type="text/javascript"> 235 document.location = document.location; 236 </script>'; 237} else { 238 rename($exportfileName,$GLOBALS['tmpdir'].'/'.$filename); 239 cl_output(s('File available as "%s"',$GLOBALS['tmpdir'].'/'.$filename)); 240} 241