1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8//this script may only be included - so its better to die if called directly. 9if (strpos($_SERVER["SCRIPT_NAME"], basename(__FILE__)) !== false) { 10 header("location: index.php"); 11 exit; 12} 13 14/** 15 * 16 */ 17class BanLib extends TikiLib 18{ 19 /** 20 * @param $banId 21 * @return mixed 22 */ 23 function get_rule($banId) 24 { 25 $query = "select * from `tiki_banning` where `banId`=?"; 26 27 $result = $this->query($query, [$banId]); 28 $res = $result->fetchRow(); 29 $aux = []; 30 $query2 = "select `section` from `tiki_banning_sections` where `banId`=?"; 31 $result2 = $this->query($query2, [$banId]); 32 $aux = []; 33 34 while ($res2 = $result2->fetchRow()) { 35 $aux[] = $res2['section']; 36 } 37 38 $res['sections'] = $aux; 39 return $res; 40 } 41 42 /** 43 * @param $banId 44 * @return TikiDb_Pdo_Result|TikiDb_Adodb_Result 45 */ 46 function remove_rule($banId) 47 { 48 $query = "delete from `tiki_banning_sections` where `banId`=?"; 49 $this->query($query, [$banId]); 50 $query = "delete from `tiki_banning` where `banId`=?"; 51 return $this->query($query, [$banId]); 52 } 53 54 /** 55 * @param $offset 56 * @param $maxRecords 57 * @param $sort_mode 58 * @param $find 59 * @return array 60 */ 61 function list_rules($offset, $maxRecords, $sort_mode, $find) 62 { 63 64 if ($find) { 65 $findesc = '%' . $find . '%'; 66 67 $mid = " where ((`message` like ?) or (`title` like ?))"; 68 $bindvars = [$findesc, $findesc]; 69 } else { 70 $mid = ""; 71 $bindvars = []; 72 } 73 74 $query = "select * from `tiki_banning` $mid order by " . $this->convertSortMode($sort_mode); 75 $query_cant = "select count(*) from `tiki_banning` $mid"; 76 $result = $this->query($query, $bindvars, $maxRecords, $offset); 77 $cant = $this->getOne($query_cant, $bindvars); 78 $ret = []; 79 80 while ($res = $result->fetchRow()) { 81 $aux = []; 82 83 $query2 = "select * from `tiki_banning_sections` where `banId`=?"; 84 $result2 = $this->query($query2, [$res['banId']]); 85 86 while ($res2 = $result2->fetchRow()) { 87 $aux[] = $res2; 88 } 89 90 $res['sections'] = $aux; 91 $ret[] = $res; 92 } 93 94 $retval = []; 95 $retval["data"] = $ret; 96 $retval["cant"] = $cant; 97 $query = "select `banId` from `tiki_banning` where `use_dates`=? and `date_to` < FROM_UNIXTIME(?)"; 98 $result = $this->query($query, ['y', $this->now]); 99 100 while ($res = $result->fetchRow()) { 101 $this->remove_rule($res['banId']); 102 } 103 104 return $retval; 105 } 106 107 /** 108 * @param $rules 109 * @return string 110 */ 111 function export_rules($rules) 112 { 113 $csv = "banId,mode,title,ip1,ip2,ip3,ip4,user,date_from,date_to,use_dates,created,created_readable,message,sections\n"; 114 foreach ($rules as $rule) { 115 if (! isset($rule['title'])) { 116 $rule['title'] = ''; 117 } 118 if (isset($rule['user'])) { 119 $rule['ip1'] = ''; 120 $rule['ip2'] = ''; 121 $rule['ip3'] = ''; 122 $rule['ip4'] = ''; 123 } 124 if ($rule['mode'] == 'ip') { 125 $rule['user'] = ''; 126 } 127 if ($rule['use_dates'] != 'y') { 128 $rule['date_from'] = ''; 129 $rule['date_to'] = ''; 130 } 131 if (! isset($rule['message'])) { 132 $rule['message'] = ''; 133 } 134 $csv .= '"' . $rule['banId'] 135 . '","' . $rule['mode'] 136 . '","' . $rule['title'] 137 . '","' . $rule['ip1'] 138 . '","' . $rule['ip2'] 139 . '","' . $rule['ip3'] 140 . '","' . $rule['ip4'] 141 . '","' . $rule['user'] 142 . '","' . $rule['date_from'] 143 . '","' . $rule['date_to'] 144 . '","' . $rule['use_dates'] 145 . '","' . $rule['created'] 146 . '","' . $this->date_format("%y%m%d %H:%M", $rule['created']) 147 . '","' . $rule['message'] . '","'; 148 149 if (! empty($rule['sections'])) { 150 foreach ($rule['sections'] as $section) { 151 $csv .= $section['section'] . '|'; 152 } 153 $csv = rtrim($csv, '|'); 154 } 155 $csv .= "\"\n"; 156 } 157 return $csv; 158 } 159 160 /** 161 * @param $fname 162 * @param $import_as_new 163 * @return int 164 * @throws Exception 165 */ 166 function importCSV($fname, $import_as_new) 167 { 168 $fields = false; 169 if ($fhandle = fopen($fname, 'r')) { 170 $fields = fgetcsv($fhandle, 1000); 171 } 172 if ($fields === false) { 173 $smarty = TikiLib::lib('smarty'); 174 175 $smarty->assign('msg', tra("The file has incorrect syntax or is not a CSV file")); 176 $smarty->display("error.tpl"); 177 die; 178 } 179 $nb = 0; 180 while (($data = fgetcsv($fhandle, 1000)) !== false) { 181 $d = ["banId" => "", "mode" => "", "title" => "", "ip1" => "", "ip2" => "", 182 "ip3" => "", "ip4" => "", "user" => "", "date_from" => "", "date_to" => "", "use_dates" => "", "created" => "", "created_readable" => "", "message" => ""]; 183 foreach ($fields as $field) { 184 $d[$field] = $data[array_search($field, $fields)]; 185 } 186 if (empty($d['message'])) { 187 $d['message'] = tra('Spam is not welcome here'); 188 } 189 if ($import_as_new) { 190 $d['banId'] = 0; 191 } 192 $nb++; 193 194 $this->replace_rule( 195 $d['banId'], 196 $d['mode'], 197 $d['title'], 198 $d['ip1'], 199 $d['ip2'], 200 $d['ip3'], 201 $d['ip4'], 202 $d['user'], 203 strtotime($d['date_from']), 204 strtotime($d['date_to']), 205 $d['use_dates'], 206 $d['message'], 207 explode('|', $d['sections']) 208 ); 209 } 210 fclose($fhandle); 211 return $nb; 212 } 213 214 /* 215 banId integer(12) not null auto_increment, 216 mode enum('user','ip'), 217 title varchar(200), 218 ip1 integer(3), 219 ip2 integer(3), 220 ip3 integer(3), 221 ip4 integer(3), 222 user varchar(200), 223 date_from timestamp, 224 date_to timestamp, 225 use_dates char(1), 226 message text, 227 primary key(banId) 228 */ 229 /** 230 * @param $banId 231 * @param $mode 232 * @param $title 233 * @param $ip1 234 * @param $ip2 235 * @param $ip3 236 * @param $ip4 237 * @param $user 238 * @param $date_from 239 * @param $date_to 240 * @param $use_dates 241 * @param $message 242 * @param $sections 243 * @return TikiDb_Pdo_Result|TikiDb_Adodb_Result 244 */ 245 function replace_rule($banId, $mode, $title, $ip1, $ip2, $ip3, $ip4, $user, $date_from, $date_to, $use_dates, $message, $sections) 246 { 247 if (empty($title)) { 248 $title = empty($user) ? "$ip1.$ip2.$ip3.$ip4" : $user; 249 } 250 251 $count = TikiDb::get()->table('tiki_banning')->fetchCount(['banId' => $banId]); 252 if ($banId && $count > 0) { 253 $query = "update `tiki_banning` set `title`=?, `ip1`=?, `ip2`=?, `ip3`=?, `ip4`=?, `user`=?, " . 254 "`date_from` = FROM_UNIXTIME(?), `date_to` = FROM_UNIXTIME(?), `use_dates` = ?, `message` = ? where `banId`=?"; 255 256 $resultUpdate = $this->query($query, [$title, $ip1, $ip2, $ip3, $ip4, $user, $date_from, $date_to, $use_dates, $message, $banId]); 257 } else { 258 $query = "insert into `tiki_banning`(`mode`,`title`,`ip1`,`ip2`,`ip3`,`ip4`,`user`,`date_from`,`date_to`,`use_dates`,`message`,`created`) " . 259 "values(?,?,?,?,?,?,?,FROM_UNIXTIME(?),FROM_UNIXTIME(?),?,?,?)"; 260 $resultInsert = $this->query($query, [$mode, $title, $ip1, $ip2, $ip3, $ip4, $user, $date_from, $date_to, $use_dates, $message, $this->now]); 261 $banId = $this->getOne("select max(`banId`) from `tiki_banning` where `created`=?", [$this->now]); 262 } 263 264 $oldSections = TikiDb::get()->table('tiki_banning_sections')->fetchColumn('section',['banId' => $banId]); 265 $query = "delete from `tiki_banning_sections` where `banId`=?"; 266 $this->query($query, [$banId]); 267 268 foreach ($sections as $section) { 269 $query = "insert into `tiki_banning_sections`(`banId`,`section`) values(?,?)"; 270 271 $resultSections = $this->query($query, [$banId, $section]); 272 } 273 $newSections = TikiDb::get()->table('tiki_banning_sections')->fetchColumn('section',['banId' => $banId]); 274 275 if (isset($resultInsert)) { 276 $result = $resultInsert; 277 } elseif (isset($resultUpdate)) { 278 // for updates, must check both tiki_banning and tiki_banning_sections to see if anything changed 279 if ($resultUpdate->numRows()) { 280 // something was changed in tiki_banning 281 $result = $resultUpdate; 282 } else { 283 if ($oldSections != $newSections) { 284 // something was changed in tiki_banning_sections 285 $result = $resultSections; 286 } else { 287 // update didn't change anything 288 $result = $resultUpdate; 289 } 290 } 291 } else { 292 $result = false; 293 } 294 return $result; 295 } 296} 297 298$banlib = new BanLib; 299