[TASK] Provide QueryHelper to prepare SQL fragments for the QueryBuilder 36/47636/4
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Tue, 12 Apr 2016 20:52:16 +0000 (22:52 +0200)
committerGeorg Ringer <georg.ringer@gmail.com>
Wed, 13 Apr 2016 06:24:40 +0000 (08:24 +0200)
The QueryHelper deals with GROUP BY and ORDER BY strings coming from
sources where the data format is not strictly defined and the input
might be a string prefixes or otherwise unsuitable for immediate further
usage.

For GROUP BY strings the prefix is removed and the remainder of the
string is treated as a comma separated list of column names that get
parsed into an array.

For ORDER BY strings the prefix is removed and the remainder of the
string is treated as a list of comma separated column names, possibly
with a sort direction separated from the column name, that gets parsed
into an array.

For WHERE SQL fragments leading the leading logical operators "AND" and
"OR" get removed, no further modifications are performed on the string.

Resolves: #75559
Releases: master
Change-Id: I37016eda6597cab49b274f498188a8579dec7b2c
Reviewed-on: https://review.typo3.org/47636
Reviewed-by: Wouter Wolters <typo3@wouterwolters.nl>
Tested-by: Wouter Wolters <typo3@wouterwolters.nl>
Reviewed-by: Georg Ringer <georg.ringer@gmail.com>
Tested-by: Georg Ringer <georg.ringer@gmail.com>
typo3/sysext/core/Classes/Database/Query/QueryHelper.php [new file with mode: 0644]
typo3/sysext/core/Tests/Unit/Database/Query/QueryHelperTest.php [new file with mode: 0644]

diff --git a/typo3/sysext/core/Classes/Database/Query/QueryHelper.php b/typo3/sysext/core/Classes/Database/Query/QueryHelper.php
new file mode 100644 (file)
index 0000000..09c0cac
--- /dev/null
@@ -0,0 +1,85 @@
+<?php
+declare (strict_types = 1);
+namespace TYPO3\CMS\Core\Database\Query;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
+/**
+ * Contains misc helper methods to build syntactically valid SQL queries.
+ * Most helper functions are required to deal with legacy data where the
+ * format of the input is not strict enough to reliably use the SQL parts
+ * in queries directly.
+ *
+ * @internal
+ */
+class QueryHelper
+{
+    /**
+     * Takes an input, possibly prefixed with ORDER BY, and explodes it into
+     * and array of arrays where each item consists of a fieldName and a order
+     * direction.
+     *
+     * Each of the resulting fieldName/direction pairs can be used passed into
+     * QueryBuilder::orderBy() so sort a query result set.
+     *
+     * @param string $input eg . "ORDER BY title, uid
+     * @return array|array[] Array of arrays containing fieldName/direction pairs
+     */
+    public static function parseOrderBy(string $input): array
+    {
+        $input = preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
+        $orderExpressions = GeneralUtility::trimExplode(',', $input, true);
+
+        return array_map(
+            function ($expression) {
+                list($fieldName, $order) = GeneralUtility::trimExplode(' ', $expression, true);
+
+                return [$fieldName, $order];
+            },
+            $orderExpressions
+        );
+    }
+
+    /**
+     * Removes the prefix "GROUP BY" from the input string.
+     *
+     * This function should be used when you can't guarantee that the string
+     * that you want to use as a GROUP BY fragment is not prefixed.
+     *
+     * @param string $input eg. "GROUP BY title, uid
+     * @return array|string[] column names to group by
+     */
+    public static function parseGroupBy(string $input): array
+    {
+        $input = preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
+
+        return GeneralUtility::trimExplode(',', $input, true);
+    }
+
+    /**
+     * Removes the prefixes AND/OR from the input string.
+     *
+     * This function should be used when you can't guarantee that the string
+     * that you want to use as a WHERE fragment is not prefixed.
+     *
+     * @param string $constraint The where part fragment with a possible leading AND or OR operator
+     * @return string The modified where part without leading operator
+     */
+    public static function stripLogicalOperatorPrefix(string $constraint): string
+    {
+        return preg_replace('/^(?:(AND|OR)[[:space:]]*)+/i', '', trim($constraint)) ?: '';
+    }
+}
diff --git a/typo3/sysext/core/Tests/Unit/Database/Query/QueryHelperTest.php b/typo3/sysext/core/Tests/Unit/Database/Query/QueryHelperTest.php
new file mode 100644 (file)
index 0000000..2185d70
--- /dev/null
@@ -0,0 +1,194 @@
+<?php
+declare (strict_types = 1);
+namespace TYPO3\CMS\Core\Tests\Unit\Database\Query;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
+use TYPO3\CMS\Core\Tests\UnitTestCase;
+
+/**
+ * Query helper test
+ */
+class QueryHelperTest extends UnitTestCase
+{
+    /**
+     * Test cases for stripping of leading logical operators in where constraints.
+     *
+     * @return array
+     */
+    public function stripLogicalOperatorPrefixDataProvider(): array
+    {
+        return [
+            'unprefixed input' => ['1=1', '1=1'],
+            'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
+            'AND' => ['AND 1=1', '1=1'],
+            'AND with leading space' => ['     AND 1=1', '1=1'],
+            'AND with mixed whitespace' => ['   AND 1<>1', '1<>1'],
+            'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
+            'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
+            'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
+            'OR' => ['OR 1=1', '1=1'],
+            'OR with leading space' => ['      OR 1=1', '1=1'],
+            'OR with mixed whitespace' => ['    OR 1<>1', '1<>1'],
+            'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
+            'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
+            'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
+        ];
+    }
+
+    /**
+     * @test
+     * @dataProvider stripLogicalOperatorPrefixDataProvider
+     * @param string $input
+     * @param string $expectedSql
+     */
+    public function stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql)
+    {
+        $this->assertSame($expectedSql, QueryHelper::stripLogicalOperatorPrefix($input));
+    }
+
+    /**
+     * Test cases for parsing ORDER BY SQL fragments
+     *
+     * @return array
+     */
+    public function parseOrderByDataProvider(): array
+    {
+        return [
+            'single field' => [
+                'aField',
+                [
+                    ['aField', null],
+                ],
+            ],
+            'single field with leading whitespace' => [
+                ' aField',
+                [
+                    ['aField', null],
+                ],
+            ],
+            'prefixed single field' => [
+                'ORDER BY aField',
+                [
+                    ['aField', null],
+                ],
+            ],
+            'prefixed single field with leading whitespace' => [
+                ' ORDER BY aField',
+                [
+                    ['aField', null],
+                ],
+            ],
+            'single field with direction' => [
+                'aField DESC',
+                [
+                    ['aField', 'DESC'],
+                ],
+            ],
+            'multiple fields' => [
+                'aField,anotherField, aThirdField',
+                [
+                    ['aField', null],
+                    ['anotherField', null],
+                    ['aThirdField', null]
+                ],
+            ],
+            'multiple fields with direction' => [
+                'aField ASC,anotherField, aThirdField DESC',
+                [
+                    ['aField', 'ASC'],
+                    ['anotherField', null],
+                    ['aThirdField', 'DESC']
+                ],
+            ],
+            'prefixed multiple fields with direction' => [
+                'ORDER BY aField ASC,anotherField, aThirdField DESC',
+                [
+                    ['aField', 'ASC'],
+                    ['anotherField', null],
+                    ['aThirdField', 'DESC']
+                ],
+            ],
+            'with table prefix' => [
+                'ORDER BY be_groups.title',
+                [
+                    ['be_groups.title', null]
+                ]
+            ],
+        ];
+    }
+
+    /**
+     * @test
+     * @dataProvider parseOrderByDataProvider
+     * @param string $input
+     * @param array $expectedResult
+     */
+    public function parseOrderByTest(string $input, array $expectedResult)
+    {
+        $this->assertSame($expectedResult, QueryHelper::parseOrderBy($input));
+    }
+
+    /**
+     * Test cases for parsing ORDER BY SQL fragments
+     *
+     * @return array
+     */
+    public function parseGroupByDataProvider(): array
+    {
+        return [
+            'single field' => [
+                'aField',
+                ['aField'],
+            ],
+            'single field with leading whitespace' => [
+                ' aField',
+                ['aField'],
+            ],
+            'prefixed single field' => [
+                'GROUP BY aField',
+                ['aField'],
+            ],
+            'prefixed single field with leading whitespace' => [
+                ' GROUP BY aField',
+                ['aField'],
+            ],
+            'multiple fields' => [
+                'aField,anotherField, aThirdField',
+                ['aField', 'anotherField', 'aThirdField']
+            ],
+            'prefixed multiple fields' => [
+                'GROUP BY aField,anotherField, aThirdField',
+                ['aField', 'anotherField', 'aThirdField']
+            ],
+            'with table prefix' => [
+                'GROUP BY be_groups.title',
+                ['be_groups.title']
+            ],
+        ];
+    }
+
+    /**
+     * @test
+     * @dataProvider parseGroupByDataProvider
+     * @param string $input
+     * @param array $expectedResult
+     */
+    public function parseGroupByTest(string $input, array $expectedResult)
+    {
+        $this->assertSame($expectedResult, QueryHelper::parseGroupBy($input));
+    }
+
+}