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