SelectUtilTrait::getWhereCondition() - Code Metrics - Inspection of "Function refactoring." - lagdo/jaxon-dbadmin - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 1a0bc1...893379 )

by Thierry

created 2021-12-24 21:54 UTC

SelectUtilTrait::getWhereCondition() B

↳ Parent: SelectUtilTrait

Complexity

Conditions	8
Paths	8

Size

Total Lines	25
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	8
eloc	17
c	1
b	0
f	0
nc	8
nop	2
dl	0
loc	25
rs	8.4444

<?php

namespace Lagdo\DbAdmin\Db\Traits;

use Lagdo\DbAdmin\Driver\Entity\IndexEntity;
use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;

use function intval;
use function implode;
use function strtoupper;
use function in_array;
use function array_map;
use function preg_match;
use function preg_replace;
use function preg_match_all;

trait SelectUtilTrait
{
    /**
     * @param TableFieldEntity $field Single field from fields()
     * @param string $value
     * @param string $function
     *
     * @return string
     */
    abstract protected function getUnconvertedFieldValue(TableFieldEntity $field, string $value, string $function = ''): string;

    /**
     * Filter length value including enums
     *
     * @param string $length
     *
     * @return string
     */
    public function processLength(string $length): string
    {
        if (!$length) {
            return '';
        }
        $enumLength = $this->driver->enumLength();
        if (preg_match("~^\\s*\\(?\\s*$enumLength(?:\\s*,\\s*$enumLength)*+\\s*\\)?\\s*\$~", $length) &&
            preg_match_all("~$enumLength~", $length, $matches)) {
            return '(' . implode(',', $matches[0]) . ')';
        }
        return preg_replace('~^[0-9].*~', '(\0)', preg_replace('~[^-0-9,+()[\]]~', '', $length));
    }

    /**
     * Process limit box in select
     *
     * @return int
     */
    public function processSelectLimit(): int
    {
        return isset($this->input->values['limit']) ? intval($this->input->values['limit']) : 50;
    }

    /**
     * Process length box in select
     *
     * @return int
     */
    public function processSelectLength(): int
    {
        return isset($this->input->values['text_length']) ? intval($this->input->values['text_length']) : 100;
    }

    /**
     * Process order box in select
     *
     * @return array
     */
    public function processSelectOrder(): array
    {
        $expressions = [];
        foreach ((array) $this->input->values['order'] as $key => $value) {
            if ($value !== '') {
                $regexp = '~^((COUNT\(DISTINCT |[A-Z0-9_]+\()(`(?:[^`]|``)+`|"(?:[^"]|"")+")\)|COUNT\(\*\))$~';
                $expression = $value;
                if (preg_match($regexp, $expression) === false) {
                    $expression = $this->driver->escapeId($expression);
                }
                if (isset($this->input->values['desc'][$key])) {
                    $expression .= ' DESC';
                }
                $expressions[] = $expression;
            }
        }
        return $expressions;
    }

    /**
     * Process extras in select form
     *
     * @param array $where AND conditions
     * @param array $foreignKeys
     *
     * @return bool
     */
    // public function processSelectEmail(array $where, array $foreignKeys)
    // {
    //     return false;
    // }

    /**
     * Apply SQL function
     *
     * @param string $function
     * @param string $column escaped column identifier
     *
     * @return string
     */
    public function applySqlFunction(string $function, string $column): string
    {
        if (!$function) {
            return $column;
        }
        if ($function === 'unixepoch') {
            return "DATETIME($column, '$function')";
        }
        if ($function === 'count distinct') {
            return "COUNT(DISTINCT $column)";
        }
        return strtoupper($function) . "($column)";
    }

    /**
     * @param array $value
     *
     * @return bool
     */
    private function colHasValidValue(array $value): bool
    {
        return $value['fun'] === 'count' ||
            ($value['col'] !== '' && (!$value['fun'] ||
                in_array($value['fun'], $this->driver->functions()) ||
                in_array($value['fun'], $this->driver->grouping())));
    }

    /**
     * Process columns box in select
     *
     * @return array
     */
    public function processSelectColumns(): array
    {
        $select = []; // select expressions, empty for *
        $group = []; // expressions without aggregation - will be used for GROUP BY if an aggregation function is used
        foreach ((array) $this->input->values['columns'] as $key => $value) {
            if ($this->colHasValidValue($value)) {
                $fields = '*';
                if ($value['col'] !== '') {
                    $fields = $this->driver->escapeId($value['col']);
                }
                $select[$key] = $this->applySqlFunction($value['fun'], $fields);
                if (!in_array($value['fun'], $this->driver->grouping())) {
                    $group[] = $select[$key];
                }
            }
        }
        return [$select, $group];
    }

    /**
     * @param array $value
     * @param array $fields
     *
     * @return string
     */
    private function getWhereCondition(array $value, array $fields): string
    {
        $op = $value['op'];
        $val = $value['val'];
        $col = $value['col'];
        if (preg_match('~IN$~', $op)) {
            $in = $this->processLength($val);
            return " $op " . ($in !== '' ? $in : '(NULL)');
        }
        if ($op === 'SQL') {
            return ' ' . $val; // SQL injection
        }
        if ($op === 'LIKE %%') {
            return ' LIKE ' . $this->getUnconvertedFieldValue($fields[$col], "%$val%");
        }
        if ($op === 'ILIKE %%') {
            return ' ILIKE ' . $this->getUnconvertedFieldValue($fields[$col], "%$val%");
        }
        if ($op === 'FIND_IN_SET') {
            return ')';
        }
        if (!preg_match('~NULL$~', $op)) {
            return " $op " . $this->getUnconvertedFieldValue($fields[$col], $val);
        }
        return " $op";
    }

    /**
     * @param TableFieldEntity $field
     * @param array $value
     *
     * @return bool
     */
    private function selectFieldIsValid(TableFieldEntity $field, array $value): bool
    {
        $op = $value['op'];
        $val = $value['val'];
        $in = preg_match('~IN$~', $op) ? ',' : '';
        return (preg_match('~^[-\d.' . $in . ']+$~', $val) ||
                !preg_match('~' . $this->driver->numberRegex() . '|bit~', $field->type)) &&
            (!preg_match("~[\x80-\xFF]~", $val) ||
                preg_match('~char|text|enum|set~', $field->type)) &&
            (!preg_match('~date|timestamp~', $field->type) ||
                preg_match('~^\d+-\d+-\d+~', $val));
    }

    /**
     * @param array $value
     * @param array $fields
     *
     * @return string
     */
    private function getSelectExpression(array $value, array $fields): string
    {
        $op = $value['op'];
        $col = $value['col'];
        $prefix = '';
        if ($op === 'FIND_IN_SET') {
            $prefix = $op .'(' . $this->driver->quote($value['val']) . ', ';
        }
        $condition = $this->getWhereCondition($value, $fields);
        if ($col !== '') {
            return $prefix . $this->driver->convertSearch($this->driver->escapeId($col),
                $value, $fields[$col]) . $condition;
        }
        // find anywhere
        $clauses = [];
        foreach ($fields as $name => $field) {
            if ($this->selectFieldIsValid($field, $value)) {
                $clauses[] = $prefix . $this->driver->convertSearch($this->driver->escapeId($name),
                    $value, $field) . $condition;
            }
        }
        if (empty($clauses)) {
            return '1 = 0';
        }
        return '(' . implode(' OR ', $clauses) . ')';
    }

    /**
     * @param IndexEntity $index
     * @param int $i
     *
     * @return string
     */
    private function getMatchExpression(IndexEntity $index, int $i): string
    {
        $columns = array_map(function ($column) {
            return $this->driver->escapeId($column);
        }, $index->columns);
        $match = $this->driver->quote($this->input->values['fulltext'][$i]);
        if (isset($this->input->values['boolean'][$i])) {
            $match .= ' IN BOOLEAN MODE';
        }
        return 'MATCH (' . implode(', ', $columns) . ') AGAINST (' . $match . ')';
    }

    /**
     * Process search box in select
     *
     * @param array $fields
     * @param array $indexes
     *
     * @return array
     */
    public function processSelectWhere(array $fields, array $indexes): array
    {
        $expressions = [];
        foreach ($indexes as $i => $index) {
            if ($index->type === 'FULLTEXT' && $this->input->values['fulltext'][$i] !== '') {
                $expressions[] = $this->getMatchExpression($index, $i);
            }
        }
        foreach ((array) $this->input->values['where'] as $key => $value) {
            if (($value['col'] !== '' ||  $value['val'] !== '') &&
                in_array($value['op'], $this->driver->operators())) {
                $expressions[] = $this->getSelectExpression($value, $fields);
            }
        }
        return $expressions;
    }
}


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

lagdo / jaxon-dbadmin

Push — main ( 1a0bc1...893379 )

SelectUtilTrait::getWhereCondition() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like