‪TYPO3CMS  ‪main
QueryHelperTest.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 
20 use PHPUnit\Framework\Attributes\DataProvider;
21 use PHPUnit\Framework\Attributes\Test;
24 use TYPO3\TestingFramework\Core\Unit\UnitTestCase;
25 
26 final class ‪QueryHelperTest extends UnitTestCase
27 {
31  public static function ‪stripLogicalOperatorPrefixDataProvider(): array
32  {
33  return [
34  'unprefixed input' => ['1=1', '1=1'],
35  'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
36  'AND' => ['AND 1=1', '1=1'],
37  'AND with leading space' => [' AND 1=1', '1=1'],
38  'AND with mixed whitespace' => [' AND 1<>1', '1<>1'],
39  'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
40  'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
41  'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
42  'OR' => ['OR 1=1', '1=1'],
43  'OR with leading space' => [' OR 1=1', '1=1'],
44  'OR with mixed whitespace' => [' OR 1<>1', '1<>1'],
45  'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
46  'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
47  'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
48  ];
49  }
50 
51  #[DataProvider('stripLogicalOperatorPrefixDataProvider')]
52  #[Test]
53  public function ‪stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql): void
54  {
55  self::assertSame($expectedSql, ‪QueryHelper::stripLogicalOperatorPrefix($input));
56  }
57 
61  public static function ‪parseOrderByDataProvider(): array
62  {
63  return [
64  'empty string' => [
65  '',
66  [],
67  ],
68  'single field' => [
69  'aField',
70  [
71  ['aField', null],
72  ],
73  ],
74  'single field with leading whitespace' => [
75  ' aField',
76  [
77  ['aField', null],
78  ],
79  ],
80  'prefixed single field' => [
81  'ORDER BY aField',
82  [
83  ['aField', null],
84  ],
85  ],
86  'prefixed single field with leading whitespace' => [
87  ' ORDER BY aField',
88  [
89  ['aField', null],
90  ],
91  ],
92  'single field with direction' => [
93  'aField DESC',
94  [
95  ['aField', 'DESC'],
96  ],
97  ],
98  'multiple fields' => [
99  'aField,anotherField, aThirdField',
100  [
101  ['aField', null],
102  ['anotherField', null],
103  ['aThirdField', null],
104  ],
105  ],
106  'multiple fields with direction' => [
107  'aField ASC,anotherField, aThirdField DESC',
108  [
109  ['aField', 'ASC'],
110  ['anotherField', null],
111  ['aThirdField', 'DESC'],
112  ],
113  ],
114  'prefixed multiple fields with direction' => [
115  'ORDER BY aField ASC,anotherField, aThirdField DESC',
116  [
117  ['aField', 'ASC'],
118  ['anotherField', null],
119  ['aThirdField', 'DESC'],
120  ],
121  ],
122  'with table prefix' => [
123  'ORDER BY be_groups.title',
124  [
125  ['be_groups.title', null],
126  ],
127  ],
128  ];
129  }
130 
131  #[DataProvider('parseOrderByDataProvider')]
132  #[Test]
133  public function ‪parseOrderByTest(string $input, array $expectedResult): void
134  {
135  self::assertSame($expectedResult, ‪QueryHelper::parseOrderBy($input));
136  }
137 
141  public static function ‪parseTableListDataProvider(): array
142  {
143  return [
144  'single table' => [
145  'aTable',
146  [
147  ['aTable', null],
148  ],
149  ],
150  'single table with leading whitespace' => [
151  ' aTable',
152  [
153  ['aTable', null],
154  ],
155  ],
156  'prefixed single table' => [
157  'FROM aTable',
158  [
159  ['aTable', null],
160  ],
161  ],
162  'prefixed single table with leading whitespace' => [
163  ' FROM aTable',
164  [
165  ['aTable', null],
166  ],
167  ],
168  'single table with alias' => [
169  'aTable a',
170  [
171  ['aTable', 'a'],
172  ],
173  ],
174  'multiple tables' => [
175  'aTable,anotherTable, aThirdTable',
176  [
177  ['aTable', null],
178  ['anotherTable', null],
179  ['aThirdTable', null],
180  ],
181  ],
182  'multiple tables with aliases' => [
183  'aTable a,anotherTable, aThirdTable AS c',
184  [
185  ['aTable', 'a'],
186  ['anotherTable', null],
187  ['aThirdTable', 'c'],
188  ],
189  ],
190  'prefixed multiple tables with aliases' => [
191  'FROM aTable a,anotherTable, aThirdTable AS c',
192  [
193  ['aTable', 'a'],
194  ['anotherTable', null],
195  ['aThirdTable', 'c'],
196  ],
197  ],
198  ];
199  }
200 
201  #[DataProvider('parseTableListDataProvider')]
202  #[Test]
203  public function ‪parseTableListTest(string $input, array $expectedResult): void
204  {
205  self::assertSame($expectedResult, ‪QueryHelper::parseTableList($input));
206  }
207 
211  public static function ‪parseGroupByDataProvider(): array
212  {
213  return [
214  'single field' => [
215  'aField',
216  ['aField'],
217  ],
218  'single field with leading whitespace' => [
219  ' aField',
220  ['aField'],
221  ],
222  'prefixed single field' => [
223  'GROUP BY aField',
224  ['aField'],
225  ],
226  'prefixed single field with leading whitespace' => [
227  ' GROUP BY aField',
228  ['aField'],
229  ],
230  'multiple fields' => [
231  'aField,anotherField, aThirdField',
232  ['aField', 'anotherField', 'aThirdField'],
233  ],
234  'prefixed multiple fields' => [
235  'GROUP BY aField,anotherField, aThirdField',
236  ['aField', 'anotherField', 'aThirdField'],
237  ],
238  'with table prefix' => [
239  'GROUP BY be_groups.title',
240  ['be_groups.title'],
241  ],
242  ];
243  }
244 
245  #[DataProvider('parseGroupByDataProvider')]
246  #[Test]
247  public function ‪parseGroupByTest(string $input, array $expectedResult): void
248  {
249  self::assertSame($expectedResult, ‪QueryHelper::parseGroupBy($input));
250  }
251 
255  public static function ‪parseJoinDataProvider(): array
256  {
257  return [
258  'unquoted tableName' => [
259  'aTable ON aTable.uid = anotherTable.uid_foreign',
260  [
261  'tableName' => 'aTable',
262  'tableAlias' => 'aTable',
263  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign',
264  ],
265  ],
266  'backtick quoted tableName' => [
267  '`aTable` ON aTable.uid = anotherTable.uid_foreign',
268  [
269  'tableName' => 'aTable',
270  'tableAlias' => 'aTable',
271  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign',
272  ],
273  ],
274  'backtick quoted tableName with alias' => [
275  '`aTable` a ON a.uid = anotherTable.uid_foreign',
276  [
277  'tableName' => 'aTable',
278  'tableAlias' => 'a',
279  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
280  ],
281  ],
282  'backtick quoted tableName with backtick quoted alias' => [
283  '`aTable` `a` ON a.uid = anotherTable.uid_foreign',
284  [
285  'tableName' => 'aTable',
286  'tableAlias' => 'a',
287  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
288  ],
289  ],
290  'backtick quoted tableName with AS alias' => [
291  '`aTable` AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
292  [
293  'tableName' => 'aTable',
294  'tableAlias' => 'anAlias',
295  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
296  ],
297  ],
298  'backtick quoted tableName with AS backtick quoted alias' => [
299  '`aTable` AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
300  [
301  'tableName' => 'aTable',
302  'tableAlias' => 'anAlias',
303  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
304  ],
305  ],
306  'backtick unquoted tableName with AS backtick quoted alias' => [
307  'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
308  [
309  'tableName' => 'aTable',
310  'tableAlias' => 'anAlias',
311  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
312  ],
313  ],
314  'quoted tableName' => [
315  '"aTable" ON aTable.uid = anotherTable.uid_foreign',
316  [
317  'tableName' => 'aTable',
318  'tableAlias' => 'aTable',
319  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign',
320  ],
321  ],
322  'quoted tableName with alias' => [
323  '"aTable" a ON a.uid = anotherTable.uid_foreign',
324  [
325  'tableName' => 'aTable',
326  'tableAlias' => 'a',
327  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
328  ],
329  ],
330  'quoted tableName with quoted alias' => [
331  '"aTable" "a" ON a.uid = anotherTable.uid_foreign',
332  [
333  'tableName' => 'aTable',
334  'tableAlias' => 'a',
335  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
336  ],
337  ],
338  'quoted tableName with AS alias' => [
339  '"aTable" AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
340  [
341  'tableName' => 'aTable',
342  'tableAlias' => 'anAlias',
343  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
344  ],
345  ],
346  'quoted tableName with AS quoted alias' => [
347  '"aTable" AS "anAlias" ON anAlias.uid = anotherTable.uid_foreign',
348  [
349  'tableName' => 'aTable',
350  'tableAlias' => 'anAlias',
351  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
352  ],
353  ],
354  'unquoted tableName with AS quoted alias' => [
355  'aTable AS "anAlias" ON anAlias.uid = anotherTable.uid_foreign',
356  [
357  'tableName' => 'aTable',
358  'tableAlias' => 'anAlias',
359  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
360  ],
361  ],
362  'square-bracket quoted tableName' => [
363  '[aTable] ON aTable.uid = anotherTable.uid_foreign',
364  [
365  'tableName' => 'aTable',
366  'tableAlias' => 'aTable',
367  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign',
368  ],
369  ],
370  'square-bracket quoted tableName with alias' => [
371  '[aTable] a ON a.uid = anotherTable.uid_foreign',
372  [
373  'tableName' => 'aTable',
374  'tableAlias' => 'a',
375  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
376  ],
377  ],
378  'square-bracket quoted tableName with square-bracket quoted alias' => [
379  '[aTable] [a] ON a.uid = anotherTable.uid_foreign',
380  [
381  'tableName' => 'aTable',
382  'tableAlias' => 'a',
383  'joinCondition' => 'a.uid = anotherTable.uid_foreign',
384  ],
385  ],
386  'square-bracket quoted tableName with AS alias' => [
387  '[aTable] AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
388  [
389  'tableName' => 'aTable',
390  'tableAlias' => 'anAlias',
391  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
392  ],
393  ],
394  'square-bracket quoted tableName with AS square-bracket quoted alias' => [
395  '[aTable] AS [anAlias] ON anAlias.uid = anotherTable.uid_foreign',
396  [
397  'tableName' => 'aTable',
398  'tableAlias' => 'anAlias',
399  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
400  ],
401  ],
402  'unquoted tableName with AS square-bracket quoted alias' => [
403  'aTable AS [anAlias] ON anAlias.uid = anotherTable.uid_foreign',
404  [
405  'tableName' => 'aTable',
406  'tableAlias' => 'anAlias',
407  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign',
408  ],
409  ],
410  ];
411  }
412 
413  #[DataProvider('parseJoinDataProvider')]
414  #[Test]
415  public function ‪parseJoinSplitsStatement(string $input, array $expected): void
416  {
417  self::assertSame($expected, ‪QueryHelper::parseJoin($input));
418  }
419 
423  public static function ‪quoteDatabaseIdentifierDataProvider(): array
424  {
425  return [
426  'no marked identifiers' => [
427  'colPos=0',
428  'colPos=0',
429  ],
430  'single fieldname' => [
431  '{#colPos}=0',
432  '"colPos"=0',
433  ],
434  'tablename and fieldname' => [
435  '{#tt_content.colPos}=0',
436  '"tt_content"."colPos"=0',
437  ],
438  'multiple fieldnames' => [
439  '{#colPos}={#aField}',
440  '"colPos"="aField"',
441  ],
442  ];
443  }
444 
445  #[DataProvider('quoteDatabaseIdentifierDataProvider')]
446  #[Test]
447  public function ‪quoteDatabaseIdentifiers(string $input, string $expected): void
448  {
449  $connectionMock = $this->createMock(Connection::class);
450  $connectionMock->method('quoteIdentifier')->willReturnCallback(static function (string ‪$identifier): string {
451  $parts = array_map(
452  static function (string ‪$identifier): string {
453  return '"' . ‪$identifier . '"';
454  },
455  explode('.', ‪$identifier)
456  );
457 
458  return implode('.', $parts);
459  });
460 
461  self::assertSame($expected, ‪QueryHelper::quoteDatabaseIdentifiers($connectionMock, $input));
462  }
463 }
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseOrderByDataProvider
‪static parseOrderByDataProvider()
Definition: QueryHelperTest.php:61
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseOrderBy
‪static array array[] parseOrderBy(string $input)
Definition: QueryHelper.php:44
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseOrderByTest
‪parseOrderByTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:133
‪TYPO3\CMS\Core\Database\Query\QueryHelper\quoteDatabaseIdentifiers
‪static quoteDatabaseIdentifiers(Connection $connection, string $sql)
Definition: QueryHelper.php:224
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseTableList
‪static array array[] parseTableList(string $input)
Definition: QueryHelper.php:72
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseJoinDataProvider
‪static parseJoinDataProvider()
Definition: QueryHelperTest.php:255
‪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\Tests\Unit\Database\Query\QueryHelperTest\parseJoinSplitsStatement
‪parseJoinSplitsStatement(string $input, array $expected)
Definition: QueryHelperTest.php:415
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest
Definition: QueryHelperTest.php:27
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\stripLogicalOperatorPrefixRemovesConstraintPrefixes
‪stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql)
Definition: QueryHelperTest.php:53
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\stripLogicalOperatorPrefixDataProvider
‪static stripLogicalOperatorPrefixDataProvider()
Definition: QueryHelperTest.php:31
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseTableListTest
‪parseTableListTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:203
‪TYPO3\CMS\Core\Database\Query\QueryHelper
Definition: QueryHelper.php:32
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\quoteDatabaseIdentifiers
‪quoteDatabaseIdentifiers(string $input, string $expected)
Definition: QueryHelperTest.php:447
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseTableListDataProvider
‪static parseTableListDataProvider()
Definition: QueryHelperTest.php:141
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:41
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseGroupByDataProvider
‪static parseGroupByDataProvider()
Definition: QueryHelperTest.php:211
‪TYPO3\CMS\Core\Database\Query\QueryHelper\stripLogicalOperatorPrefix
‪static string stripLogicalOperatorPrefix(string $constraint)
Definition: QueryHelper.php:171
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\quoteDatabaseIdentifierDataProvider
‪static quoteDatabaseIdentifierDataProvider()
Definition: QueryHelperTest.php:423
‪TYPO3\CMS\Core\Tests\Unit\Database\Query
Definition: BulkInsertTest.php:18
‪TYPO3\CMS\Webhooks\Message\$identifier
‪identifier readonly string $identifier
Definition: FileAddedMessage.php:37
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseGroupByTest
‪parseGroupByTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:247