[TASK] Update php-cs-fixer to 2.5.0
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Query / QueryHelper.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Query;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use TYPO3\CMS\Core\Database\Connection;
19 use TYPO3\CMS\Core\Utility\GeneralUtility;
20
21 /**
22 * Contains misc helper methods to build syntactically valid SQL queries.
23 * Most helper functions are required to deal with legacy data where the
24 * format of the input is not strict enough to reliably use the SQL parts
25 * in queries directly.
26 *
27 * @internal
28 */
29 class QueryHelper
30 {
31 /**
32 * Takes an input, possibly prefixed with ORDER BY, and explodes it into
33 * and array of arrays where each item consists of a fieldName and a order
34 * direction.
35 *
36 * Each of the resulting fieldName/direction pairs can be used passed into
37 * QueryBuilder::orderBy() so sort a query result set.
38 *
39 * @param string $input eg . "ORDER BY title, uid
40 * @return array|array[] Array of arrays containing fieldName/direction pairs
41 */
42 public static function parseOrderBy(string $input): array
43 {
44 $input = preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
45 $orderExpressions = GeneralUtility::trimExplode(',', $input, true);
46
47 return array_map(
48 function ($expression) {
49 list($fieldName, $order) = GeneralUtility::trimExplode(' ', $expression, true);
50
51 return [$fieldName, $order];
52 },
53 $orderExpressions
54 );
55 }
56
57 /**
58 * Takes an input, possibly prefixed with FROM, and explodes it into
59 * and array of arrays where each item consists of a tableName and an
60 * optional alias name.
61 *
62 * Each of the resulting pairs can be used with QueryBuilder::from()
63 * to select from one or more tables.
64 *
65 * @param string $input eg . "FROM aTable, anotherTable AS b, aThirdTable c"
66 * @return array|array[] Array of arrays containing tableName/alias pairs
67 */
68 public static function parseTableList(string $input): array
69 {
70 $input = preg_replace('/^(?:FROM[[:space:]]+)+/i', '', trim($input)) ?: '';
71 $tableExpressions = GeneralUtility::trimExplode(',', $input, true);
72
73 return array_map(
74 function ($expression) {
75 list($tableName, $as, $alias) = GeneralUtility::trimExplode(' ', $expression, true);
76
77 if (!empty($as) && strtolower($as) === 'as' && !empty($alias)) {
78 return [$tableName, $alias];
79 }
80 if (!empty($as) && empty($alias)) {
81 return [$tableName, $as];
82 }
83 return [$tableName, null];
84 },
85 $tableExpressions
86 );
87 }
88
89 /**
90 * Removes the prefix "GROUP BY" from the input string.
91 *
92 * This function should be used when you can't guarantee that the string
93 * that you want to use as a GROUP BY fragment is not prefixed.
94 *
95 * @param string $input eg. "GROUP BY title, uid
96 * @return array|string[] column names to group by
97 */
98 public static function parseGroupBy(string $input): array
99 {
100 $input = preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
101
102 return GeneralUtility::trimExplode(',', $input, true);
103 }
104
105 /**
106 * Split a JOIN SQL fragment into table name, alias and join conditions.
107 *
108 * @param string $input eg. "JOIN tableName AS a ON a.uid = anotherTable.uid_foreign"
109 * @return array assoc array consisting of the keys tableName, tableAlias and joinCondition
110 */
111 public static function parseJoin(string $input): array
112 {
113 $input = trim($input);
114 $quoteCharacter = ' ';
115 // Check if the tableName is quoted
116 if ($input[0] === '`' || $input[0] === '"') {
117 $quoteCharacter .= $input[0];
118 $input = substr($input, 1);
119 $tableName = strtok($input, $quoteCharacter);
120 } else {
121 $tableName = strtok($input, $quoteCharacter);
122 }
123
124 $tableAlias = strtok($quoteCharacter);
125 if (strtolower($tableAlias) === 'as') {
126 $tableAlias = strtok($quoteCharacter);
127 // Skip the next token which must be ON
128 strtok(' ');
129 $joinCondition = strtok('');
130 } elseif (strtolower($tableAlias) === 'on') {
131 $tableAlias = null;
132 $joinCondition = strtok('');
133 } else {
134 // Skip the next token which must be ON
135 strtok(' ');
136 $joinCondition = strtok('');
137 }
138
139 // Catch the edge case that the table name is unquoted and the
140 // table alias is actually quoted. This will not work in the case
141 // that the quoted table alias contains whitespace.
142 if ($tableAlias[0] === '`' || $tableAlias[0] === '"') {
143 $tableAlias = substr($tableAlias, 1, -1);
144 }
145
146 $tableAlias = $tableAlias ?: $tableName;
147
148 return ['tableName' => $tableName, 'tableAlias' => $tableAlias, 'joinCondition' => $joinCondition];
149 }
150
151 /**
152 * Removes the prefixes AND/OR from the input string.
153 *
154 * This function should be used when you can't guarantee that the string
155 * that you want to use as a WHERE fragment is not prefixed.
156 *
157 * @param string $constraint The where part fragment with a possible leading AND or OR operator
158 * @return string The modified where part without leading operator
159 */
160 public static function stripLogicalOperatorPrefix(string $constraint): string
161 {
162 return preg_replace('/^(?:(AND|OR)[[:space:]]*)+/i', '', trim($constraint)) ?: '';
163 }
164
165 /**
166 * Returns the date and time formats compatible with the given database.
167 *
168 * This simple method should probably be deprecated and removed later.
169 *
170 * @return array
171 */
172 public static function getDateTimeFormats()
173 {
174 return [
175 'date' => [
176 'empty' => '0000-00-00',
177 'format' => 'Y-m-d'
178 ],
179 'datetime' => [
180 'empty' => '0000-00-00 00:00:00',
181 'format' => 'Y-m-d H:i:s'
182 ]
183 ];
184 }
185
186 /**
187 * Quote database table/column names indicated by {#identifier} markup in a SQL fragment string.
188 * This is an intermediate step to make SQL fragments in Typoscript and TCA database agnostic.
189 *
190 * @param \TYPO3\CMS\Core\Database\Connection $connection
191 * @param string $sql
192 * @return string
193 */
194 public static function quoteDatabaseIdentifiers(Connection $connection, string $sql): string
195 {
196 if (strpos($sql, '{#') !== false) {
197 $sql = preg_replace_callback(
198 '/{#(?P<identifier>[^}]+)}/',
199 function (array $matches) use ($connection) {
200 return $connection->quoteIdentifier($matches['identifier']);
201 },
202 $sql
203 );
204 }
205
206 return $sql;
207 }
208 }