Passed
Push — main ( 1a0bc1...893379 )
by Thierry
01:55
created

SelectUtilTrait::getMatchExpression()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 10
Code Lines 7

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 0 Features 0
Metric Value
cc 2
eloc 7
c 1
b 0
f 0
nc 2
nop 2
dl 0
loc 10
rs 10
1
<?php
2
3
namespace Lagdo\DbAdmin\Db\Traits;
4
5
use Lagdo\DbAdmin\Driver\Entity\IndexEntity;
6
use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;
7
8
use function intval;
9
use function implode;
10
use function strtoupper;
11
use function in_array;
12
use function array_map;
13
use function preg_match;
14
use function preg_replace;
15
use function preg_match_all;
16
17
trait SelectUtilTrait
18
{
19
    /**
20
     * @param TableFieldEntity $field Single field from fields()
21
     * @param string $value
22
     * @param string $function
23
     *
24
     * @return string
25
     */
26
    abstract protected function getUnconvertedFieldValue(TableFieldEntity $field, string $value, string $function = ''): string;
27
28
    /**
29
     * Filter length value including enums
30
     *
31
     * @param string $length
32
     *
33
     * @return string
34
     */
35
    public function processLength(string $length): string
36
    {
37
        if (!$length) {
38
            return '';
39
        }
40
        $enumLength = $this->driver->enumLength();
41
        if (preg_match("~^\\s*\\(?\\s*$enumLength(?:\\s*,\\s*$enumLength)*+\\s*\\)?\\s*\$~", $length) &&
42
            preg_match_all("~$enumLength~", $length, $matches)) {
43
            return '(' . implode(',', $matches[0]) . ')';
44
        }
45
        return preg_replace('~^[0-9].*~', '(\0)', preg_replace('~[^-0-9,+()[\]]~', '', $length));
46
    }
47
48
    /**
49
     * Process limit box in select
50
     *
51
     * @return int
52
     */
53
    public function processSelectLimit(): int
54
    {
55
        return isset($this->input->values['limit']) ? intval($this->input->values['limit']) : 50;
56
    }
57
58
    /**
59
     * Process length box in select
60
     *
61
     * @return int
62
     */
63
    public function processSelectLength(): int
64
    {
65
        return isset($this->input->values['text_length']) ? intval($this->input->values['text_length']) : 100;
66
    }
67
68
    /**
69
     * Process order box in select
70
     *
71
     * @return array
72
     */
73
    public function processSelectOrder(): array
74
    {
75
        $expressions = [];
76
        foreach ((array) $this->input->values['order'] as $key => $value) {
77
            if ($value !== '') {
78
                $regexp = '~^((COUNT\(DISTINCT |[A-Z0-9_]+\()(`(?:[^`]|``)+`|"(?:[^"]|"")+")\)|COUNT\(\*\))$~';
79
                $expression = $value;
80
                if (preg_match($regexp, $expression) === false) {
81
                    $expression = $this->driver->escapeId($expression);
82
                }
83
                if (isset($this->input->values['desc'][$key])) {
84
                    $expression .= ' DESC';
85
                }
86
                $expressions[] = $expression;
87
            }
88
        }
89
        return $expressions;
90
    }
91
92
    /**
93
     * Process extras in select form
94
     *
95
     * @param array $where AND conditions
96
     * @param array $foreignKeys
97
     *
98
     * @return bool
99
     */
100
    // public function processSelectEmail(array $where, array $foreignKeys)
101
    // {
102
    //     return false;
103
    // }
104
105
    /**
106
     * Apply SQL function
107
     *
108
     * @param string $function
109
     * @param string $column escaped column identifier
110
     *
111
     * @return string
112
     */
113
    public function applySqlFunction(string $function, string $column): string
114
    {
115
        if (!$function) {
116
            return $column;
117
        }
118
        if ($function === 'unixepoch') {
119
            return "DATETIME($column, '$function')";
120
        }
121
        if ($function === 'count distinct') {
122
            return "COUNT(DISTINCT $column)";
123
        }
124
        return strtoupper($function) . "($column)";
125
    }
126
127
    /**
128
     * @param array $value
129
     *
130
     * @return bool
131
     */
132
    private function colHasValidValue(array $value): bool
133
    {
134
        return $value['fun'] === 'count' ||
135
            ($value['col'] !== '' && (!$value['fun'] ||
136
                in_array($value['fun'], $this->driver->functions()) ||
137
                in_array($value['fun'], $this->driver->grouping())));
138
    }
139
140
    /**
141
     * Process columns box in select
142
     *
143
     * @return array
144
     */
145
    public function processSelectColumns(): array
146
    {
147
        $select = []; // select expressions, empty for *
148
        $group = []; // expressions without aggregation - will be used for GROUP BY if an aggregation function is used
149
        foreach ((array) $this->input->values['columns'] as $key => $value) {
150
            if ($this->colHasValidValue($value)) {
151
                $fields = '*';
152
                if ($value['col'] !== '') {
153
                    $fields = $this->driver->escapeId($value['col']);
154
                }
155
                $select[$key] = $this->applySqlFunction($value['fun'], $fields);
156
                if (!in_array($value['fun'], $this->driver->grouping())) {
157
                    $group[] = $select[$key];
158
                }
159
            }
160
        }
161
        return [$select, $group];
162
    }
163
164
    /**
165
     * @param array $value
166
     * @param array $fields
167
     *
168
     * @return string
169
     */
170
    private function getWhereCondition(array $value, array $fields): string
171
    {
172
        $op = $value['op'];
173
        $val = $value['val'];
174
        $col = $value['col'];
175
        if (preg_match('~IN$~', $op)) {
176
            $in = $this->processLength($val);
177
            return " $op " . ($in !== '' ? $in : '(NULL)');
178
        }
179
        if ($op === 'SQL') {
180
            return ' ' . $val; // SQL injection
181
        }
182
        if ($op === 'LIKE %%') {
183
            return ' LIKE ' . $this->getUnconvertedFieldValue($fields[$col], "%$val%");
184
        }
185
        if ($op === 'ILIKE %%') {
186
            return ' ILIKE ' . $this->getUnconvertedFieldValue($fields[$col], "%$val%");
187
        }
188
        if ($op === 'FIND_IN_SET') {
189
            return ')';
190
        }
191
        if (!preg_match('~NULL$~', $op)) {
192
            return " $op " . $this->getUnconvertedFieldValue($fields[$col], $val);
193
        }
194
        return " $op";
195
    }
196
197
    /**
198
     * @param TableFieldEntity $field
199
     * @param array $value
200
     *
201
     * @return bool
202
     */
203
    private function selectFieldIsValid(TableFieldEntity $field, array $value): bool
204
    {
205
        $op = $value['op'];
206
        $val = $value['val'];
207
        $in = preg_match('~IN$~', $op) ? ',' : '';
208
        return (preg_match('~^[-\d.' . $in . ']+$~', $val) ||
209
                !preg_match('~' . $this->driver->numberRegex() . '|bit~', $field->type)) &&
210
            (!preg_match("~[\x80-\xFF]~", $val) ||
211
                preg_match('~char|text|enum|set~', $field->type)) &&
212
            (!preg_match('~date|timestamp~', $field->type) ||
213
                preg_match('~^\d+-\d+-\d+~', $val));
214
    }
215
216
    /**
217
     * @param array $value
218
     * @param array $fields
219
     *
220
     * @return string
221
     */
222
    private function getSelectExpression(array $value, array $fields): string
223
    {
224
        $op = $value['op'];
225
        $col = $value['col'];
226
        $prefix = '';
227
        if ($op === 'FIND_IN_SET') {
228
            $prefix = $op .'(' . $this->driver->quote($value['val']) . ', ';
229
        }
230
        $condition = $this->getWhereCondition($value, $fields);
231
        if ($col !== '') {
232
            return $prefix . $this->driver->convertSearch($this->driver->escapeId($col),
233
                $value, $fields[$col]) . $condition;
234
        }
235
        // find anywhere
236
        $clauses = [];
237
        foreach ($fields as $name => $field) {
238
            if ($this->selectFieldIsValid($field, $value)) {
239
                $clauses[] = $prefix . $this->driver->convertSearch($this->driver->escapeId($name),
240
                    $value, $field) . $condition;
241
            }
242
        }
243
        if (empty($clauses)) {
244
            return '1 = 0';
245
        }
246
        return '(' . implode(' OR ', $clauses) . ')';
247
    }
248
249
    /**
250
     * @param IndexEntity $index
251
     * @param int $i
252
     *
253
     * @return string
254
     */
255
    private function getMatchExpression(IndexEntity $index, int $i): string
256
    {
257
        $columns = array_map(function ($column) {
258
            return $this->driver->escapeId($column);
259
        }, $index->columns);
260
        $match = $this->driver->quote($this->input->values['fulltext'][$i]);
261
        if (isset($this->input->values['boolean'][$i])) {
262
            $match .= ' IN BOOLEAN MODE';
263
        }
264
        return 'MATCH (' . implode(', ', $columns) . ') AGAINST (' . $match . ')';
265
    }
266
267
    /**
268
     * Process search box in select
269
     *
270
     * @param array $fields
271
     * @param array $indexes
272
     *
273
     * @return array
274
     */
275
    public function processSelectWhere(array $fields, array $indexes): array
276
    {
277
        $expressions = [];
278
        foreach ($indexes as $i => $index) {
279
            if ($index->type === 'FULLTEXT' && $this->input->values['fulltext'][$i] !== '') {
280
                $expressions[] = $this->getMatchExpression($index, $i);
281
            }
282
        }
283
        foreach ((array) $this->input->values['where'] as $key => $value) {
284
            if (($value['col'] !== '' ||  $value['val'] !== '') &&
285
                in_array($value['op'], $this->driver->operators())) {
286
                $expressions[] = $this->getSelectExpression($value, $fields);
287
            }
288
        }
289
        return $expressions;
290
    }
291
}
292