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