MDL-60174 core_dml: get_recordset on Postgres eats all the RAM
[moodle.git] / admin / tool / spamcleaner / index.php
CommitLineData
2e3f66d7 1<?php
2
3/**
4 * Spam Cleaner
5 *
6 * Helps an admin to clean up spam in Moodle
7 *
2ef8ff9e
SH
8 * @author Dongsheng Cai
9 * @author Martin Dougiamas
10 * @author Amr Hourani
2e3f66d7 11 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
12 */
13
2e3f66d7 14// List of known spammy keywords, please add more here
15
9edea336
PS
16/////////////////////////////////////////////////////////////////////////////////
17
18require_once('../../../config.php');
19require_once($CFG->libdir.'/adminlib.php');
20
21
22// Configuration
23
2e3f66d7 24$autokeywords = array(
25 "<img",
26 "fuck",
27 "casino",
28 "porn",
29 "xxx",
30 "cialis",
31 "viagra",
32 "poker",
33 "warcraft"
34 );
35
2e3f66d7 36$keyword = optional_param('keyword', '', PARAM_RAW);
37$autodetect = optional_param('autodetect', '', PARAM_RAW);
38$del = optional_param('del', '', PARAM_RAW);
39$delall = optional_param('delall', '', PARAM_RAW);
40$ignore = optional_param('ignore', '', PARAM_RAW);
41$reset = optional_param('reset', '', PARAM_RAW);
42$id = optional_param('id', '', PARAM_INT);
43
44require_login();
17a14fbd 45admin_externalpage_setup('toolspamcleaner');
2e3f66d7 46
47// Delete one user
48if (!empty($del) && confirm_sesskey() && ($id != $USER->id)) {
49 if (isset($SESSION->users_result[$id])) {
50 $user = $SESSION->users_result[$id];
51 if (delete_user($user)) {
52 unset($SESSION->users_result[$id]);
53 echo json_encode(true);
54 } else {
55 echo json_encode(false);
56 }
57 } else {
58 echo json_encode(false);
59 }
60 exit;
61}
62
63// Delete lots of users
64if (!empty($delall) && confirm_sesskey()) {
65 if (!empty($SESSION->users_result)) {
66 foreach ($SESSION->users_result as $userid => $user) {
67 if ($userid != $USER->id) {
68 if (delete_user($user)) {
69 unset($SESSION->users_result[$userid]);
70 }
71 }
72 }
73 }
74 echo json_encode(true);
75 exit;
76}
77
78299973 78if (!empty($ignore) && confirm_sesskey()) {
2e3f66d7 79 unset($SESSION->users_result[$id]);
80 echo json_encode(true);
81 exit;
82}
83
17a14fbd 84$PAGE->requires->js_init_call('M.tool_spamcleaner.init', array(me()), true);
9edea336 85$strings = Array('spaminvalidresult','spamdeleteallconfirm','spamcannotdelete','spamdeleteconfirm');
17a14fbd 86$PAGE->requires->strings_for_js($strings, 'tool_spamcleaner');
2e3f66d7 87
61ef8f9f 88echo $OUTPUT->header();
2e3f66d7 89
90// Print headers and things
17a14fbd 91echo $OUTPUT->box(get_string('spamcleanerintro', 'tool_spamcleaner'));
2e3f66d7 92
20486a5a 93echo $OUTPUT->box_start(); // The forms section at the top
2e3f66d7 94
95?>
96
97<div class="mdl-align">
98
270b2e44 99<form method="post" action="index.php" class="form-inline spamcleanerform">
6acb6a3d 100 <div>
aa414527 101 <label class="accesshide" for="keyword_el"><?php print_string('spamkeyword', 'tool_spamcleaner') ?></label>
270b2e44 102 <input type="text" class="form-control" name="keyword" id="keyword_el" value="<?php p($keyword) ?>" />
2e3f66d7 103 <input type="hidden" name="sesskey" value="<?php echo sesskey();?>" />
270b2e44 104 <input type="submit" class="btn btn-primary" value="<?php echo get_string('spamsearch', 'tool_spamcleaner')?>" />
6acb6a3d 105 </div>
2e3f66d7 106</form>
17a14fbd 107<p><?php echo get_string('spameg', 'tool_spamcleaner');?></p>
2e3f66d7 108
109<hr />
110
6acb6a3d 111<form method="post" action="index.php">
112 <div>
270b2e44
SL
113 <input type="submit" class="btn btn-primary" name="autodetect"
114 value="<?php echo get_string('spamauto', 'tool_spamcleaner');?>" />
6acb6a3d 115 </div>
2e3f66d7 116</form>
117
118
119</div>
120
121<?php
20486a5a 122echo $OUTPUT->box_end();
2e3f66d7 123
124echo '<div id="result" class="mdl-align">';
125
126// Print list of resulting profiles
127
128if (!empty($keyword)) { // Use the keyword(s) supplied by the user
129 $keywords = explode(',', $keyword);
130 foreach ($keywords as $key => $keyword) {
131 $keywords[$key] = trim($keyword);
132 }
133 search_spammers($keywords);
134
135} else if (!empty($autodetect)) { // Use the inbuilt keyword list to detect users
136 search_spammers($autokeywords);
137}
138
139echo '</div>';
140
141/////////////////////////////////////////////////////////////////////////////////
142
143
20486a5a 144/// Functions
2e3f66d7 145
146
147function search_spammers($keywords) {
148
20486a5a 149 global $CFG, $USER, $DB, $OUTPUT;
2e3f66d7 150
151 if (!is_array($keywords)) {
152 $keywords = array($keywords); // Make it into an array
153 }
154
4287c1c2 155 $params = array('userid'=>$USER->id);
2e3f66d7 156
157 $keywordfull = array();
d38f419d 158 $i = 0;
2e3f66d7 159 foreach ($keywords as $keyword) {
d38f419d
PS
160 $keywordfull[] = $DB->sql_like('description', ':descpat'.$i, false);
161 $params['descpat'.$i] = "%$keyword%";
162 $keywordfull2[] = $DB->sql_like('p.summary', ':sumpat'.$i, false);
163 $params['sumpat'.$i] = "%$keyword%";
e45b444e
RT
164 $keywordfull3[] = $DB->sql_like('p.subject', ':subpat'.$i, false);
165 $params['subpat'.$i] = "%$keyword%";
166 $keywordfull4[] = $DB->sql_like('c.content', ':contpat'.$i, false);
167 $params['contpat'.$i] = "%$keyword%";
168 $keywordfull5[] = $DB->sql_like('m.fullmessage', ':msgpat'.$i, false);
169 $params['msgpat'.$i] = "%$keyword%";
170 $keywordfull6[] = $DB->sql_like('fp.message', ':forumpostpat'.$i, false);
171 $params['forumpostpat'.$i] = "%$keyword%";
172 $keywordfull7[] = $DB->sql_like('fp.subject', ':forumpostsubpat'.$i, false);
173 $params['forumpostsubpat'.$i] = "%$keyword%";
d38f419d 174 $i++;
2e3f66d7 175 }
176 $conditions = '( '.implode(' OR ', $keywordfull).' )';
177 $conditions2 = '( '.implode(' OR ', $keywordfull2).' )';
e45b444e
RT
178 $conditions3 = '( '.implode(' OR ', $keywordfull3).' )';
179 $conditions4 = '( '.implode(' OR ', $keywordfull4).' )';
180 $conditions5 = '( '.implode(' OR ', $keywordfull5).' )';
181 $conditions6 = '( '.implode(' OR ', $keywordfull6).' )';
182 $conditions7 = '( '.implode(' OR ', $keywordfull7).' )';
2e3f66d7 183
7e1da23d
AA
184 $sql = "SELECT *
185 FROM {user}
186 WHERE deleted = 0
187 AND id <> :userid
188 AND $conditions"; // Exclude oneself
189 $sql2 = "SELECT u.*, p.summary
190 FROM {user} u, {post} p
191 WHERE $conditions2
192 AND u.deleted = 0
193 AND u.id=p.userid
194 AND u.id <> :userid";
195 $sql3 = "SELECT u.*, p.subject AS postsubject
196 FROM {user} u, {post} p
197 WHERE $conditions3
198 AND u.deleted = 0
199 AND u.id=p.userid
200 AND u.id <> :userid";
201 $sql4 = "SELECT u.*, c.content
202 FROM {user} u, {comments} c
203 WHERE $conditions4
204 AND u.deleted = 0
205 AND u.id=c.userid
206 AND u.id <> :userid";
207 $sql5 = "SELECT u.*, m.fullmessage
208 FROM {user} u, {message} m
209 WHERE $conditions5
210 AND u.deleted = 0
211 AND u.id=m.useridfrom
212 AND u.id <> :userid";
213 $sql6 = "SELECT u.*, fp.message
214 FROM {user} u, {forum_posts} fp
215 WHERE $conditions6
216 AND u.deleted = 0
217 AND u.id=fp.userid
218 AND u.id <> :userid";
219 $sql7 = "SELECT u.*, fp.subject
220 FROM {user} u, {forum_posts} fp
221 WHERE $conditions7
222 AND u.deleted = 0
223 AND u.id=fp.userid
224 AND u.id <> :userid";
e45b444e 225
d38f419d
PS
226 $spamusers_desc = $DB->get_recordset_sql($sql, $params);
227 $spamusers_blog = $DB->get_recordset_sql($sql2, $params);
e45b444e
RT
228 $spamusers_blogsub = $DB->get_recordset_sql($sql3, $params);
229 $spamusers_comment = $DB->get_recordset_sql($sql4, $params);
230 $spamusers_message = $DB->get_recordset_sql($sql5, $params);
231 $spamusers_forumpost = $DB->get_recordset_sql($sql6, $params);
232 $spamusers_forumpostsub = $DB->get_recordset_sql($sql7, $params);
2e3f66d7 233
234 $keywordlist = implode(', ', $keywords);
17a14fbd 235 echo $OUTPUT->box(get_string('spamresult', 'tool_spamcleaner').s($keywordlist)).' ...';
2e3f66d7 236
e45b444e
RT
237 print_user_list(array($spamusers_desc,
238 $spamusers_blog,
239 $spamusers_blogsub,
240 $spamusers_comment,
241 $spamusers_message,
242 $spamusers_forumpost,
243 $spamusers_forumpostsub
244 ),
245 $keywords);
2e3f66d7 246}
247
248
249
250function print_user_list($users_rs, $keywords) {
251 global $CFG, $SESSION;
252
253 // reset session everytime this function is called
254 $SESSION->users_result = array();
255 $count = 0;
256
257 foreach ($users_rs as $rs){
258 foreach ($rs as $user) {
259 if (!$count) {
270b2e44
SL
260 echo '<table class="table table-bordered" border="1" width="100%" id="data-grid"><tr><th>&nbsp;</th>
261 <th>'.get_string('user', 'admin').'</th><th>'.get_string('spamdesc', 'tool_spamcleaner').'</th>
262 <th>'.get_string('spamoperation', 'tool_spamcleaner').'</th></tr>';
2e3f66d7 263 }
264 $count++;
265 filter_user($user, $keywords, $count);
266 }
267 }
268
269 if (!$count) {
17a14fbd 270 echo get_string('spamcannotfinduser', 'tool_spamcleaner');
2e3f66d7 271
272 } else {
273 echo '</table>';
274 echo '<div class="mld-align">
270b2e44 275 <button id="removeall_btn" class="btn btn-secondary">'.get_string('spamdeleteall', 'tool_spamcleaner').'</button>
2e3f66d7 276 </div>';
277 }
278}
279function filter_user($user, $keywords, $count) {
280 global $CFG;
281 $image_search = false;
282 if (in_array('<img', $keywords)) {
283 $image_search = true;
284 }
285 if (isset($user->summary)) {
17a14fbd 286 $user->description = '<h3>'.get_string('spamfromblog', 'tool_spamcleaner').'</h3>'.$user->summary;
2e3f66d7 287 unset($user->summary);
e45b444e
RT
288 } else if (isset($user->postsubject)) {
289 $user->description = '<h3>'.get_string('spamfromblog', 'tool_spamcleaner').'</h3>'.$user->postsubject;
290 unset($user->postsubject);
291 } else if (isset($user->content)) {
292 $user->description = '<h3>'.get_string('spamfromcomments', 'tool_spamcleaner').'</h3>'.$user->content;
293 unset($user->content);
294 } else if (isset($user->fullmessage)) {
295 $user->description = '<h3>'.get_string('spamfrommessages', 'tool_spamcleaner').'</h3>'.$user->fullmessage;
296 unset($user->fullmessage);
297 } else if (isset($user->message)) {
298 $user->description = '<h3>'.get_string('spamfromforumpost', 'tool_spamcleaner').'</h3>'.$user->message;
299 unset($user->message);
300 } else if (isset($user->subject)) {
301 $user->description = '<h3>'.get_string('spamfromforumpost', 'tool_spamcleaner').'</h3>'.$user->subject;
302 unset($user->subject);
2e3f66d7 303 }
e45b444e 304
5d3b9994 305 if (preg_match('#<img.*src=[\"\']('.$CFG->wwwroot.')#', $user->description, $matches)
2e3f66d7 306 && $image_search) {
307 $result = false;
308 foreach ($keywords as $keyword) {
309 if (preg_match('#'.$keyword.'#', $user->description)
310 && ($keyword != '<img')) {
311 $result = true;
312 }
313 }
314 if ($result) {
315 echo print_user_entry($user, $keywords, $count);
316 } else {
317 unset($user);
318 }
319 } else {
320 echo print_user_entry($user, $keywords, $count);
321 }
322}
323
324
325function print_user_entry($user, $keywords, $count) {
326
327 global $SESSION, $CFG;
328
a226a972 329 $smalluserobject = new stdClass(); // All we need to delete them later
2e3f66d7 330 $smalluserobject->id = $user->id;
331 $smalluserobject->email = $user->email;
332 $smalluserobject->auth = $user->auth;
333 $smalluserobject->firstname = $user->firstname;
334 $smalluserobject->lastname = $user->lastname;
9edea336 335 $smalluserobject->username = $user->username;
2e3f66d7 336
337 if (empty($SESSION->users_result[$user->id])) {
338 $SESSION->users_result[$user->id] = $smalluserobject;
339 $html = '<tr valign="top" id="row-'.$user->id.'" class="result-row">';
340 $html .= '<td width="10">'.$count.'</td>';
209c122b 341 $html .= '<td width="30%" align="left"><a href="'.$CFG->wwwroot."/user/view.php?course=1&amp;id=".$user->id.'" title="'.s($user->username).'">'.fullname($user).'</a>';
2e3f66d7 342
343 $html .= "<ul>";
344 $profile_set = array('city'=>true, 'country'=>true, 'email'=>true);
345 foreach ($profile_set as $key=>$value) {
346 if (isset($user->$key)){
347 $html .= '<li>'.$user->$key.'</li>';
348 }
349 }
350 $html .= "</ul>";
351 $html .= '</td>';
352
353 foreach ($keywords as $keyword) {
1e12b912 354 $user->description = highlight($keyword, $user->description);
2e3f66d7 355 }
356
8bdc9cac
SH
357 if (!isset($user->descriptionformat)) {
358 $user->descriptionformat = FORMAT_MOODLE;
359 }
360
367a75fa 361 $html .= '<td align="left">'.format_text($user->description, $user->descriptionformat, array('overflowdiv'=>true)).'</td>';
2e3f66d7 362 $html .= '<td width="100px" align="center">';
270b2e44
SL
363 $html .= '<button class="btn btn-primary" onclick="M.tool_spamcleaner.del_user(this,'.$user->id.')">'.
364 get_string('deleteuser', 'admin').'</button><br />';
365 $html .= '<button class="btn btn-secondary" onclick="M.tool_spamcleaner.ignore_user(this,'.$user->id.')">'.
366 get_string('ignore', 'admin').'</button>';
2e3f66d7 367 $html .= '</td>';
368 $html .= '</tr>';
369 return $html;
370 } else {
371 return null;
372 }
373
374
375}
376
73d6f52f 377echo $OUTPUT->footer();