‪TYPO3CMS  11.5
QueryHelper.php
Go to the documentation of this file.
1 <?php
2 
3 declare(strict_types=1);
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 
19 
22 
32 {
44  public static function ‪parseOrderBy(string $input): array
45  {
46  $input = preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
47  $orderExpressions = ‪GeneralUtility::trimExplode(',', $input, true);
48 
49  return array_map(
50  static function ($expression) {
51  $fieldNameOrderArray = ‪GeneralUtility::trimExplode(' ', $expression, true);
52  $fieldName = $fieldNameOrderArray[0] ?? null;
53  $order = $fieldNameOrderArray[1] ?? null;
54 
55  return [$fieldName, $order];
56  },
57  $orderExpressions
58  );
59  }
60 
72  public static function ‪parseTableList(string $input): array
73  {
74  $input = preg_replace('/^(?:FROM[[:space:]]+)+/i', '', trim($input)) ?: '';
75  $tableExpressions = ‪GeneralUtility::trimExplode(',', $input, true);
76 
77  return array_map(
78  static function ($expression) {
79  [$tableName, $as, $alias] = array_pad(‪GeneralUtility::trimExplode(' ', $expression, true), 3, null);
80 
81  if (!empty($as) && strtolower($as) === 'as' && !empty($alias)) {
82  return [$tableName, $alias];
83  }
84  if (!empty($as) && empty($alias)) {
85  return [$tableName, $as];
86  }
87  return [$tableName, null];
88  },
89  $tableExpressions
90  );
91  }
92 
102  public static function ‪parseGroupBy(string $input): array
103  {
104  $input = preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
105 
106  return ‪GeneralUtility::trimExplode(',', $input, true);
107  }
108 
115  public static function ‪parseJoin(string $input): array
116  {
117  $input = trim($input);
118  $quoteCharacter = ' ';
119  $matchQuotingStartCharacters = [
120  '`' => '`',
121  '"' => '"',
122  '[' => '[]',
123  ];
124 
125  // Check if the tableName is quoted
126  if ($matchQuotingStartCharacters[$input[0]] ?? false) {
127  $quoteCharacter .= $matchQuotingStartCharacters[$input[0]];
128  $input = substr($input, 1);
129  $tableName = strtok($input, $quoteCharacter);
130  } else {
131  $tableName = strtok($input, $quoteCharacter);
132  }
133 
134  $tableAlias = (string)strtok($quoteCharacter);
135  if (strtolower($tableAlias) === 'as') {
136  $tableAlias = (string)strtok($quoteCharacter);
137  // Skip the next token which must be ON
138  strtok(' ');
139  $joinCondition = strtok('');
140  } elseif (strtolower($tableAlias) === 'on') {
141  $tableAlias = null;
142  $joinCondition = strtok('');
143  } else {
144  // Skip the next token which must be ON
145  strtok(' ');
146  $joinCondition = strtok('');
147  }
148 
149  // Catch the edge case that the table name is unquoted and the
150  // table alias is actually quoted. This will not work in the case
151  // that the quoted table alias contains whitespace.
152  $firstCharacterOfTableAlias = $tableAlias[0] ?? null;
153  if ($matchQuotingStartCharacters[$firstCharacterOfTableAlias] ?? false) {
154  $tableAlias = substr((string)$tableAlias, 1, -1);
155  }
156 
157  $tableAlias = $tableAlias ?: $tableName;
158 
159  return ['tableName' => $tableName, 'tableAlias' => $tableAlias, 'joinCondition' => $joinCondition];
160  }
161 
171  public static function ‪stripLogicalOperatorPrefix(string $constraint): string
172  {
173  return preg_replace('/^(?:(AND|OR)[[:space:]]*)+/i', '', trim($constraint)) ?: '';
174  }
175 
183  public static function ‪getDateTimeFormats()
184  {
185  return [
186  'date' => [
187  'empty' => '0000-00-00',
188  'format' => 'Y-m-d',
189  'reset' => null,
190  ],
191  'datetime' => [
192  'empty' => '0000-00-00 00:00:00',
193  'format' => 'Y-m-d H:i:s',
194  'reset' => null,
195  ],
196  'time' => [
197  'empty' => '00:00:00',
198  'format' => 'H:i:s',
199  'reset' => 0,
200  ],
201  ];
202  }
203 
211  public static function ‪getDateTimeTypes()
212  {
213  return [
214  'date',
215  'datetime',
216  'time',
217  ];
218  }
219 
228  public static function ‪quoteDatabaseIdentifiers(‪Connection $connection, string $sql): string
229  {
230  if (str_contains($sql, '{#')) {
231  $sql = preg_replace_callback(
232  '/{#(?P<identifier>[^}]+)}/',
233  static function (array $matches) use ($connection) {
234  return $connection->‪quoteIdentifier($matches['identifier']);
235  },
236  $sql
237  );
238  }
239 
240  return $sql;
241  }
242 
259  public static function ‪implodeToIntQuotedValueList(array $values, ‪Connection $connection): string
260  {
261  if (empty($values)) {
262  return 'NULL';
263  }
264 
265  // Ensure values are all integer
266  $values = ‪GeneralUtility::intExplode(',', implode(',', $values));
267 
268  // Ensure all values are quoted as int for used dbms
269  array_walk($values, static function (&$value) use ($connection) {
270  $value = $connection->quote($value, ‪Connection::PARAM_INT);
271  });
272 
273  return implode(',', $values);
274  }
275 
292  public static function ‪implodeToStringQuotedValueList(array $values, ‪Connection $connection): string
293  {
294  if (empty($values)) {
295  return 'NULL';
296  }
297 
298  // Ensure values are all strings
299  $values = ‪GeneralUtility::trimExplode(',', implode(',', $values));
300 
301  // Ensure all values are quoted as string values for used dbmns
302  array_walk($values, static function (&$value) use ($connection) {
303  $value = $connection->quote($value, ‪Connection::PARAM_STR);
304  });
305 
306  return implode(',', $values);
307  }
308 }
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseOrderBy
‪static array array[] parseOrderBy(string $input)
Definition: QueryHelper.php:44
‪TYPO3\CMS\Core\Utility\GeneralUtility\trimExplode
‪static list< string > trimExplode($delim, $string, $removeEmptyValues=false, $limit=0)
Definition: GeneralUtility.php:999
‪TYPO3\CMS\Core\Database\Query\QueryHelper\getDateTimeFormats
‪static array getDateTimeFormats()
Definition: QueryHelper.php:183
‪TYPO3\CMS\Core\Database\Connection\PARAM_INT
‪const PARAM_INT
Definition: Connection.php:49
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseTableList
‪static array array[] parseTableList(string $input)
Definition: QueryHelper.php:72
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseJoin
‪static array parseJoin(string $input)
Definition: QueryHelper.php:115
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseGroupBy
‪static array string[] parseGroupBy(string $input)
Definition: QueryHelper.php:102
‪TYPO3\CMS\Core\Database\Connection\quoteIdentifier
‪string quoteIdentifier($identifier)
Definition: Connection.php:133
‪TYPO3\CMS\Core\Database\Query\QueryHelper\quoteDatabaseIdentifiers
‪static string quoteDatabaseIdentifiers(Connection $connection, string $sql)
Definition: QueryHelper.php:228
‪TYPO3\CMS\Core\Database\Connection\PARAM_STR
‪const PARAM_STR
Definition: Connection.php:54
‪TYPO3\CMS\Core\Database\Query\QueryHelper\implodeToIntQuotedValueList
‪static string implodeToIntQuotedValueList(array $values, Connection $connection)
Definition: QueryHelper.php:259
‪TYPO3\CMS\Core\Database\Query\QueryHelper
Definition: QueryHelper.php:32
‪TYPO3\CMS\Core\Database\Query\QueryHelper\getDateTimeTypes
‪static array getDateTimeTypes()
Definition: QueryHelper.php:211
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:38
‪TYPO3\CMS\Core\Database\Query\QueryHelper\implodeToStringQuotedValueList
‪static string implodeToStringQuotedValueList(array $values, Connection $connection)
Definition: QueryHelper.php:292
‪TYPO3\CMS\Core\Database\Query\QueryHelper\stripLogicalOperatorPrefix
‪static string stripLogicalOperatorPrefix(string $constraint)
Definition: QueryHelper.php:171
‪TYPO3\CMS\Core\Database\Query
Definition: BulkInsertQuery.php:18
‪TYPO3\CMS\Core\Utility\GeneralUtility\intExplode
‪static int[] intExplode($delimiter, $string, $removeEmptyValues=false, $limit=0)
Definition: GeneralUtility.php:927
‪TYPO3\CMS\Core\Utility\GeneralUtility
Definition: GeneralUtility.php:50