1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* XOOPS Criteria parser for database query |
4
|
|
|
* |
5
|
|
|
* You may not change or alter any portion of this comment or credits |
6
|
|
|
* of supporting developers from this source code or any supporting source code |
7
|
|
|
* which is considered copyrighted (c) material of the original comment or credit authors. |
8
|
|
|
* This program is distributed in the hope that it will be useful, |
9
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
10
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
11
|
|
|
* |
12
|
|
|
* @copyright (c) 2000-2016 XOOPS Project (www.xoops.org) |
13
|
|
|
* @license GNU GPL 2 (https://www.gnu.org/licenses/gpl-2.0.html) |
14
|
|
|
* @package kernel |
15
|
|
|
* @subpackage database |
16
|
|
|
* @since 2.0.0 |
17
|
|
|
* @author Kazumi Ono <[email protected]> |
18
|
|
|
* @author Nathan Dial |
19
|
|
|
* @author Taiwen Jiang <[email protected]> |
20
|
|
|
*/ |
21
|
|
|
defined('XOOPS_ROOT_PATH') || exit('Restricted access'); |
22
|
|
|
|
23
|
|
|
/** |
24
|
|
|
* A criteria (grammar?) for a database query. |
25
|
|
|
* |
26
|
|
|
* Abstract base class should never be instantiated directly. |
27
|
|
|
* |
28
|
|
|
* @abstract |
29
|
|
|
*/ |
30
|
|
|
class CriteriaElement |
31
|
|
|
{ |
32
|
|
|
/** |
33
|
|
|
* Sort order |
34
|
|
|
* |
35
|
|
|
* @var string |
36
|
|
|
*/ |
37
|
|
|
public $order = 'ASC'; |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* |
41
|
|
|
* @var string |
42
|
|
|
*/ |
43
|
|
|
public $sort = ''; |
44
|
|
|
|
45
|
|
|
/** |
46
|
|
|
* Number of records to retrieve |
47
|
|
|
* |
48
|
|
|
* @var int |
49
|
|
|
*/ |
50
|
|
|
public $limit = 0; |
51
|
|
|
|
52
|
|
|
/** |
53
|
|
|
* Offset of first record |
54
|
|
|
* |
55
|
|
|
* @var int |
56
|
|
|
*/ |
57
|
|
|
public $start = 0; |
58
|
|
|
|
59
|
|
|
/** |
60
|
|
|
* |
61
|
|
|
* @var string |
62
|
|
|
*/ |
63
|
|
|
public $groupby = ''; |
64
|
|
|
|
65
|
|
|
/** |
66
|
|
|
* Constructor |
67
|
|
|
*/ |
68
|
|
|
public function __construct() |
69
|
|
|
{ |
70
|
|
|
} |
71
|
|
|
|
72
|
|
|
/** |
73
|
|
|
* Render the criteria element |
74
|
|
|
* @return string |
75
|
|
|
*/ |
76
|
|
|
public function render() |
77
|
|
|
{ |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* |
82
|
|
|
* @param string $sort |
83
|
|
|
*/ |
84
|
|
|
public function setSort($sort) |
85
|
|
|
{ |
86
|
|
|
$this->sort = $sort; |
87
|
|
|
} |
88
|
|
|
|
89
|
|
|
/** |
90
|
|
|
* |
91
|
|
|
* @return string |
92
|
|
|
*/ |
93
|
|
|
public function getSort() |
94
|
|
|
{ |
95
|
|
|
return $this->sort; |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
/** |
99
|
|
|
* |
100
|
|
|
* @param string $order |
101
|
|
|
*/ |
102
|
|
|
public function setOrder($order) |
103
|
|
|
{ |
104
|
|
|
if ('DESC' === strtoupper($order)) { |
105
|
|
|
$this->order = 'DESC'; |
106
|
|
|
} |
107
|
|
|
} |
108
|
|
|
|
109
|
|
|
/** |
110
|
|
|
* |
111
|
|
|
* @return string |
112
|
|
|
*/ |
113
|
|
|
public function getOrder() |
114
|
|
|
{ |
115
|
|
|
return $this->order; |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
/** |
119
|
|
|
* |
120
|
|
|
* @param int $limit |
121
|
|
|
*/ |
122
|
|
|
public function setLimit($limit = 0) |
123
|
|
|
{ |
124
|
|
|
$this->limit = (int)$limit; |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
/** |
128
|
|
|
* |
129
|
|
|
* @return int |
130
|
|
|
*/ |
131
|
|
|
public function getLimit() |
132
|
|
|
{ |
133
|
|
|
return $this->limit; |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
/** |
137
|
|
|
* |
138
|
|
|
* @param int $start |
139
|
|
|
*/ |
140
|
|
|
public function setStart($start = 0) |
141
|
|
|
{ |
142
|
|
|
$this->start = (int)$start; |
143
|
|
|
} |
144
|
|
|
|
145
|
|
|
/** |
146
|
|
|
* |
147
|
|
|
* @return int |
148
|
|
|
*/ |
149
|
|
|
public function getStart() |
150
|
|
|
{ |
151
|
|
|
return $this->start; |
152
|
|
|
} |
153
|
|
|
|
154
|
|
|
/** |
155
|
|
|
* |
156
|
|
|
* @param string $group |
157
|
|
|
*/ |
158
|
|
|
public function setGroupBy($group) |
159
|
|
|
{ |
160
|
|
|
$this->groupby = $group; |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* |
165
|
|
|
* @return string |
166
|
|
|
*/ |
167
|
|
|
public function getGroupby() |
168
|
|
|
{ |
169
|
|
|
return $this->groupby ? " GROUP BY {$this->groupby}" : ''; |
170
|
|
|
} |
171
|
|
|
/** |
172
|
|
|
* *#@- |
173
|
|
|
*/ |
174
|
|
|
} |
175
|
|
|
|
176
|
|
|
/** |
177
|
|
|
* Collection of multiple {@link CriteriaElement}s |
178
|
|
|
* |
179
|
|
|
*/ |
180
|
|
|
class CriteriaCompo extends CriteriaElement |
181
|
|
|
{ |
182
|
|
|
/** |
183
|
|
|
* The elements of the collection |
184
|
|
|
* |
185
|
|
|
* @var array Array of {@link CriteriaElement} objects |
186
|
|
|
*/ |
187
|
|
|
public $criteriaElements = array(); |
188
|
|
|
|
189
|
|
|
/** |
190
|
|
|
* Conditions |
191
|
|
|
* |
192
|
|
|
* @var array |
193
|
|
|
*/ |
194
|
|
|
public $conditions = array(); |
195
|
|
|
|
196
|
|
|
/** |
197
|
|
|
* Constructor |
198
|
|
|
* |
199
|
|
|
* @param CriteriaElement|null $ele |
200
|
|
|
* @param string $condition |
201
|
|
|
*/ |
202
|
|
|
public function __construct(CriteriaElement $ele = null, $condition = 'AND') |
203
|
|
|
{ |
204
|
|
|
if (isset($ele)) { |
205
|
|
|
$this->add($ele, $condition); |
206
|
|
|
} |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
/** |
210
|
|
|
* Add an element |
211
|
|
|
* |
212
|
|
|
* @param CriteriaElement|object $criteriaElement |
213
|
|
|
* @param string $condition |
214
|
|
|
* @return object reference to this collection |
215
|
|
|
*/ |
216
|
|
|
public function &add(CriteriaElement $criteriaElement, $condition = 'AND') |
217
|
|
|
{ |
218
|
|
|
if (is_object($criteriaElement)) { |
219
|
|
|
$this->criteriaElements[] =& $criteriaElement; |
220
|
|
|
$this->conditions[] = $condition; |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
return $this; |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** |
227
|
|
|
* Make the criteria into a query string |
228
|
|
|
* |
229
|
|
|
* @return string |
230
|
|
|
*/ |
231
|
|
|
public function render() |
232
|
|
|
{ |
233
|
|
|
$ret = ''; |
234
|
|
|
$count = count($this->criteriaElements); |
235
|
|
|
if ($count > 0) { |
236
|
|
|
$render_string = $this->criteriaElements[0]->render(); |
237
|
|
|
for ($i = 1; $i < $count; ++$i) { |
238
|
|
|
if (!$render = $this->criteriaElements[$i]->render()) { |
239
|
|
|
continue; |
240
|
|
|
} |
241
|
|
|
$render_string .= (empty($render_string) ? '' : ' ' . $this->conditions[$i] . ' ') . $render; |
242
|
|
|
} |
243
|
|
|
$ret = empty($render_string) ? '' : "({$render_string})"; |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
return $ret; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
/** |
250
|
|
|
* Make the criteria into a SQL "WHERE" clause |
251
|
|
|
* |
252
|
|
|
* @return string |
253
|
|
|
*/ |
254
|
|
|
public function renderWhere() |
255
|
|
|
{ |
256
|
|
|
$ret = $this->render(); |
257
|
|
|
$ret = ($ret != '') ? 'WHERE ' . $ret : $ret; |
258
|
|
|
|
259
|
|
|
return $ret; |
260
|
|
|
} |
261
|
|
|
|
262
|
|
|
/** |
263
|
|
|
* Generate an LDAP filter from criteria |
264
|
|
|
* |
265
|
|
|
* @return string |
266
|
|
|
* @author Nathan Dial [email protected] |
267
|
|
|
*/ |
268
|
|
|
public function renderLdap() |
269
|
|
|
{ |
270
|
|
|
$retval = ''; |
271
|
|
|
$count = count($this->criteriaElements); |
272
|
|
|
if ($count > 0) { |
273
|
|
|
$retval = $this->criteriaElements[0]->renderLdap(); |
274
|
|
|
for ($i = 1; $i < $count; ++$i) { |
275
|
|
|
$cond = strtoupper($this->conditions[$i]); |
276
|
|
|
$op = ($cond === 'OR') ? '|' : '&'; |
277
|
|
|
$retval = "({$op}{$retval}" . $this->criteriaElements[$i]->renderLdap() . ')'; |
278
|
|
|
} |
279
|
|
|
} |
280
|
|
|
|
281
|
|
|
return $retval; |
282
|
|
|
} |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
/** |
286
|
|
|
* A single criteria |
287
|
|
|
* |
288
|
|
|
*/ |
289
|
|
|
class Criteria extends CriteriaElement |
290
|
|
|
{ |
291
|
|
|
/** |
292
|
|
|
* |
293
|
|
|
* @var string |
294
|
|
|
*/ |
295
|
|
|
public $prefix; |
296
|
|
|
public $function; |
297
|
|
|
public $column; |
298
|
|
|
public $operator; |
299
|
|
|
public $value; |
300
|
|
|
|
301
|
|
|
/** |
302
|
|
|
* Constructor |
303
|
|
|
* |
304
|
|
|
* @param string $column |
305
|
|
|
* @param string $value |
306
|
|
|
* @param string $operator |
307
|
|
|
* @param string $prefix |
308
|
|
|
* @param string $function |
309
|
|
|
*/ |
310
|
|
|
public function __construct($column, $value = '', $operator = '=', $prefix = '', $function = '') |
311
|
|
|
{ |
312
|
|
|
$this->prefix = $prefix; |
313
|
|
|
$this->function = $function; |
314
|
|
|
$this->column = $column; |
315
|
|
|
$this->value = $value; |
316
|
|
|
$this->operator = $operator; |
317
|
|
|
|
318
|
|
|
/** |
319
|
|
|
* A common custom in some older programs was to use "new Criteria(1, '1')" to |
320
|
|
|
* create an always true clause, WHERE 1 = "1" |
321
|
|
|
* |
322
|
|
|
* This is no longer needed and now no longer works. Instead, use "new Criteria('')" |
323
|
|
|
* or "new CriteriaCompo()", either of which will produce no WHERE clause. |
324
|
|
|
* |
325
|
|
|
* The following is a temporary workaround for the old technique |
326
|
|
|
*/ |
327
|
|
|
if ((int) $column === 1 && (int) $value === 1 && $operator === '=') { |
328
|
|
|
$this->column = ''; |
329
|
|
|
$this->value = ''; |
330
|
|
|
} |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
/** |
334
|
|
|
* Make a sql condition string |
335
|
|
|
* |
336
|
|
|
* @return string |
337
|
|
|
*/ |
338
|
|
|
public function render() |
339
|
|
|
{ |
340
|
|
|
$backtick = (false === strpos($this->column, '.')) ? '`' : ''; |
341
|
|
|
$backtick = (false !== strpos($this->column, '(')) ? '' : $backtick; |
342
|
|
|
$clause = (!empty($this->prefix) ? "{$this->prefix}." : '') . $backtick . $this->column . $backtick; |
343
|
|
|
if (!empty($this->function)) { |
344
|
|
|
$clause = sprintf($this->function, $clause); |
345
|
|
|
} |
346
|
|
|
if (in_array(strtoupper($this->operator), array('IS NULL', 'IS NOT NULL'))) { |
347
|
|
|
$clause .= ' ' . $this->operator; |
348
|
|
|
} else { |
349
|
|
|
if ('' === ($value = trim((string)$this->value))) { |
350
|
|
|
return ''; |
351
|
|
|
} |
352
|
|
|
if (!in_array(strtoupper($this->operator), array('IN', 'NOT IN'))) { |
353
|
|
|
if ((substr($value, 0, 1) !== '`') && (substr($value, -1) !== '`')) { |
354
|
|
|
$value = "'{$value}'"; |
355
|
|
|
} elseif (!preg_match('/^[a-zA-Z0-9_\.\-`]*$/', $value)) { |
356
|
|
|
$value = '``'; |
357
|
|
|
} |
358
|
|
|
} |
359
|
|
|
$clause .= " {$this->operator} {$value}"; |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
return $clause; |
363
|
|
|
} |
364
|
|
|
|
365
|
|
|
/** |
366
|
|
|
* Generate an LDAP filter from criteria |
367
|
|
|
* |
368
|
|
|
* @return string |
369
|
|
|
* @author Nathan Dial [email protected], improved by Pierre-Eric MENUET [email protected] |
370
|
|
|
*/ |
371
|
|
|
public function renderLdap() |
372
|
|
|
{ |
373
|
|
|
if ($this->operator === '>') { |
374
|
|
|
$this->operator = '>='; |
375
|
|
|
} |
376
|
|
|
if ($this->operator === '<') { |
377
|
|
|
$this->operator = '<='; |
378
|
|
|
} |
379
|
|
|
|
380
|
|
|
if ($this->operator === '!=' || $this->operator === '<>') { |
381
|
|
|
$operator = '='; |
382
|
|
|
$clause = '(!(' . $this->column . $operator . $this->value . '))'; |
383
|
|
|
} else { |
384
|
|
|
if ($this->operator === 'IN') { |
385
|
|
|
$newvalue = str_replace(array('(', ')'), '', $this->value); |
386
|
|
|
$tab = explode(',', $newvalue); |
387
|
|
|
$clause = ''; |
388
|
|
|
foreach ($tab as $uid) { |
389
|
|
|
$clause .= "({$this->column}={$uid})"; |
390
|
|
|
} |
391
|
|
|
$clause = '(|' . $clause . ')'; |
392
|
|
|
} else { |
393
|
|
|
$clause = '(' . $this->column . $this->operator . $this->value . ')'; |
394
|
|
|
} |
395
|
|
|
} |
396
|
|
|
|
397
|
|
|
return $clause; |
398
|
|
|
} |
399
|
|
|
|
400
|
|
|
/** |
401
|
|
|
* Make a SQL "WHERE" clause |
402
|
|
|
* |
403
|
|
|
* @return string |
404
|
|
|
*/ |
405
|
|
|
public function renderWhere() |
406
|
|
|
{ |
407
|
|
|
$cond = $this->render(); |
408
|
|
|
|
409
|
|
|
return empty($cond) ? '' : "WHERE {$cond}"; |
410
|
|
|
} |
411
|
|
|
} |
412
|
|
|
|