‪TYPO3CMS  9.5
•All Classes Namespaces Files Functions Variables Pages
QueryHelperTest.php
Go to the documentation of this file.
1 <?php
2 declare(strict_types = 1);
3 
4 namespace ‪TYPO3\CMS\Core\Tests\Unit\Database\Query;
5 
6 /*
7  * This file is part of the TYPO3 CMS project.
8  *
9  * It is free software; you can redistribute it and/or modify it under
10  * the terms of the GNU General Public License, either version 2
11  * of the License, or any later version.
12  *
13  * For the full copyright and license information, please read the
14  * LICENSE.txt file that was distributed with this source code.
15  *
16  * The TYPO3 project - inspiring people to share!
17  */
18 
19 use Prophecy\Argument;
20 use ‪TYPO3\CMS\Core\Database\Connection;
21 use ‪TYPO3\CMS\Core\Database\Query\QueryHelper;
22 use TYPO3\TestingFramework\Core\Unit\UnitTestCase;
23 
27 class ‪QueryHelperTest extends UnitTestCase
28 {
34  public function ‪stripLogicalOperatorPrefixDataProvider(): array
35  {
36  return [
37  'unprefixed input' => ['1=1', '1=1'],
38  'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
39  'AND' => ['AND 1=1', '1=1'],
40  'AND with leading space' => [' AND 1=1', '1=1'],
41  'AND with mixed whitespace' => [' AND 1<>1', '1<>1'],
42  'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
43  'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
44  'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
45  'OR' => ['OR 1=1', '1=1'],
46  'OR with leading space' => [' OR 1=1', '1=1'],
47  'OR with mixed whitespace' => [' OR 1<>1', '1<>1'],
48  'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
49  'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
50  'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
51  ];
52  }
53 
60  public function ‪stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql): void
61  {
62  $this->assertSame($expectedSql, ‪QueryHelper::stripLogicalOperatorPrefix($input));
63  }
64 
70  public function ‪parseOrderByDataProvider(): array
71  {
72  return [
73  'empty string' => [
74  '',
75  [],
76  ],
77  'single field' => [
78  'aField',
79  [
80  ['aField', null],
81  ],
82  ],
83  'single field with leading whitespace' => [
84  ' aField',
85  [
86  ['aField', null],
87  ],
88  ],
89  'prefixed single field' => [
90  'ORDER BY aField',
91  [
92  ['aField', null],
93  ],
94  ],
95  'prefixed single field with leading whitespace' => [
96  ' ORDER BY aField',
97  [
98  ['aField', null],
99  ],
100  ],
101  'single field with direction' => [
102  'aField DESC',
103  [
104  ['aField', 'DESC'],
105  ],
106  ],
107  'multiple fields' => [
108  'aField,anotherField, aThirdField',
109  [
110  ['aField', null],
111  ['anotherField', null],
112  ['aThirdField', null]
113  ],
114  ],
115  'multiple fields with direction' => [
116  'aField ASC,anotherField, aThirdField DESC',
117  [
118  ['aField', 'ASC'],
119  ['anotherField', null],
120  ['aThirdField', 'DESC']
121  ],
122  ],
123  'prefixed multiple fields with direction' => [
124  'ORDER BY aField ASC,anotherField, aThirdField DESC',
125  [
126  ['aField', 'ASC'],
127  ['anotherField', null],
128  ['aThirdField', 'DESC']
129  ],
130  ],
131  'with table prefix' => [
132  'ORDER BY be_groups.title',
133  [
134  ['be_groups.title', null]
135  ]
136  ],
137  ];
138  }
139 
146  public function ‪parseOrderByTest(string $input, array $expectedResult): void
147  {
148  $this->assertSame($expectedResult, ‪QueryHelper::parseOrderBy($input));
149  }
150 
156  public function ‪parseTableListDataProvider(): array
157  {
158  return [
159  'single table' => [
160  'aTable',
161  [
162  ['aTable', null],
163  ],
164  ],
165  'single table with leading whitespace' => [
166  ' aTable',
167  [
168  ['aTable', null],
169  ],
170  ],
171  'prefixed single table' => [
172  'FROM aTable',
173  [
174  ['aTable', null],
175  ],
176  ],
177  'prefixed single table with leading whitespace' => [
178  ' FROM aTable',
179  [
180  ['aTable', null],
181  ],
182  ],
183  'single table with alias' => [
184  'aTable a',
185  [
186  ['aTable', 'a'],
187  ],
188  ],
189  'multiple tables' => [
190  'aTable,anotherTable, aThirdTable',
191  [
192  ['aTable', null],
193  ['anotherTable', null],
194  ['aThirdTable', null]
195  ],
196  ],
197  'multiple tables with aliases' => [
198  'aTable a,anotherTable, aThirdTable AS c',
199  [
200  ['aTable', 'a'],
201  ['anotherTable', null],
202  ['aThirdTable', 'c']
203  ],
204  ],
205  'prefixed multiple tables with aliases' => [
206  'FROM aTable a,anotherTable, aThirdTable AS c',
207  [
208  ['aTable', 'a'],
209  ['anotherTable', null],
210  ['aThirdTable', 'c']
211  ],
212  ]
213  ];
214  }
215 
222  public function ‪parseTableListTest(string $input, array $expectedResult): void
223  {
224  $this->assertSame($expectedResult, ‪QueryHelper::parseTableList($input));
225  }
226 
232  public function ‪parseGroupByDataProvider(): array
233  {
234  return [
235  'single field' => [
236  'aField',
237  ['aField'],
238  ],
239  'single field with leading whitespace' => [
240  ' aField',
241  ['aField'],
242  ],
243  'prefixed single field' => [
244  'GROUP BY aField',
245  ['aField'],
246  ],
247  'prefixed single field with leading whitespace' => [
248  ' GROUP BY aField',
249  ['aField'],
250  ],
251  'multiple fields' => [
252  'aField,anotherField, aThirdField',
253  ['aField', 'anotherField', 'aThirdField']
254  ],
255  'prefixed multiple fields' => [
256  'GROUP BY aField,anotherField, aThirdField',
257  ['aField', 'anotherField', 'aThirdField']
258  ],
259  'with table prefix' => [
260  'GROUP BY be_groups.title',
261  ['be_groups.title']
262  ],
263  ];
264  }
265 
272  public function ‪parseGroupByTest(string $input, array $expectedResult): void
273  {
274  $this->assertSame($expectedResult, ‪QueryHelper::parseGroupBy($input));
275  }
276 
282  public function ‪parseJoinDataProvider(): array
283  {
284  return [
285  'unquoted tableName' => [
286  'aTable ON aTable.uid = anotherTable.uid_foreign',
287  [
288  'tableName' => 'aTable',
289  'tableAlias' => 'aTable',
290  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
291  ],
292  ],
293  'quoted tableName' => [
294  '`aTable` ON aTable.uid = anotherTable.uid_foreign',
295  [
296  'tableName' => 'aTable',
297  'tableAlias' => 'aTable',
298  'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
299  ],
300  ],
301  'quoted tableName with alias' => [
302  '`aTable` a ON a.uid = anotherTable.uid_foreign',
303  [
304  'tableName' => 'aTable',
305  'tableAlias' => 'a',
306  'joinCondition' => 'a.uid = anotherTable.uid_foreign'
307  ],
308  ],
309  'quoted tableName with quoted alias' => [
310  '`aTable` `a` ON a.uid = anotherTable.uid_foreign',
311  [
312  'tableName' => 'aTable',
313  'tableAlias' => 'a',
314  'joinCondition' => 'a.uid = anotherTable.uid_foreign'
315  ],
316  ],
317  'quoted tableName with AS alias' => [
318  '`aTable` AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
319  [
320  'tableName' => 'aTable',
321  'tableAlias' => 'anAlias',
322  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
323  ],
324  ],
325  'quoted tableName with AS quoted alias' => [
326  '`aTable` AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
327  [
328  'tableName' => 'aTable',
329  'tableAlias' => 'anAlias',
330  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
331  ],
332  ],
333  'unquoted tableName with AS quoted alias' => [
334  'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
335  [
336  'tableName' => 'aTable',
337  'tableAlias' => 'anAlias',
338  'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
339  ],
340  ],
341  ];
342  }
343 
350  public function ‪parseJoinSplitsStatement(string $input, array $expected): void
351  {
352  $this->assertSame($expected, ‪QueryHelper::parseJoin($input));
353  }
354 
360  public function ‪quoteDatabaseIdentifierDataProvider(): array
361  {
362  return [
363  'no marked identifiers' => [
364  'colPos=0',
365  'colPos=0',
366  ],
367  'single fieldname' => [
368  '{#colPos}=0',
369  '"colPos"=0',
370  ],
371  'tablename and fieldname' => [
372  '{#tt_content.colPos}=0',
373  '"tt_content"."colPos"=0',
374  ],
375  'multiple fieldnames' => [
376  '{#colPos}={#aField}',
377  '"colPos"="aField"',
378  ],
379  ];
380  }
381 
388  public function ‪quoteDatabaseIdentifiers(string $input, string $expected): void
389  {
390  $connectionProphet = $this->prophesize(Connection::class);
391  $connectionProphet->quoteIdentifier(Argument::cetera())->will(function (‪$args) {
392  $parts = array_map(
393  function ($identifier) {
394  return '"' . $identifier . '"';
395  },
396  explode('.', ‪$args[0])
397  );
398 
399  return implode('.', $parts);
400  });
401 
402  $this->assertSame($expected, ‪QueryHelper::quoteDatabaseIdentifiers($connectionProphet->reveal(), $input));
403  }
404 }
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseOrderBy
‪static array array[] parseOrderBy(string $input)
Definition: QueryHelper.php:42
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseJoinDataProvider
‪array parseJoinDataProvider()
Definition: QueryHelperTest.php:282
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\stripLogicalOperatorPrefixDataProvider
‪array stripLogicalOperatorPrefixDataProvider()
Definition: QueryHelperTest.php:34
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseOrderByTest
‪parseOrderByTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:146
‪$args
‪$args
Definition: checkIntegrityCsvFixtures.php:230
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseTableList
‪static array array[] parseTableList(string $input)
Definition: QueryHelper.php:70
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseJoin
‪static array parseJoin(string $input)
Definition: QueryHelper.php:113
‪TYPO3\CMS\Core\Database\Query\QueryHelper\parseGroupBy
‪static array string[] parseGroupBy(string $input)
Definition: QueryHelper.php:100
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseJoinSplitsStatement
‪parseJoinSplitsStatement(string $input, array $expected)
Definition: QueryHelperTest.php:350
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseOrderByDataProvider
‪array parseOrderByDataProvider()
Definition: QueryHelperTest.php:70
‪TYPO3\CMS\Core\Database\Query\QueryHelper\quoteDatabaseIdentifiers
‪static string quoteDatabaseIdentifiers(Connection $connection, string $sql)
Definition: QueryHelper.php:217
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest
Definition: QueryHelperTest.php:28
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\stripLogicalOperatorPrefixRemovesConstraintPrefixes
‪stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql)
Definition: QueryHelperTest.php:60
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseGroupByDataProvider
‪array parseGroupByDataProvider()
Definition: QueryHelperTest.php:232
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseTableListTest
‪parseTableListTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:222
‪TYPO3\CMS\Core\Database\Query\QueryHelper
Definition: QueryHelper.php:30
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\quoteDatabaseIdentifiers
‪quoteDatabaseIdentifiers(string $input, string $expected)
Definition: QueryHelperTest.php:388
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:31
‪TYPO3\CMS\Core\Database\Query\QueryHelper\stripLogicalOperatorPrefix
‪static string stripLogicalOperatorPrefix(string $constraint)
Definition: QueryHelper.php:163
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseTableListDataProvider
‪array parseTableListDataProvider()
Definition: QueryHelperTest.php:156
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\quoteDatabaseIdentifierDataProvider
‪array quoteDatabaseIdentifierDataProvider()
Definition: QueryHelperTest.php:360
‪TYPO3\CMS\Core\Tests\Unit\Database\Query
Definition: BulkInsertTest.php:3
‪TYPO3\CMS\Core\Tests\Unit\Database\Query\QueryHelperTest\parseGroupByTest
‪parseGroupByTest(string $input, array $expectedResult)
Definition: QueryHelperTest.php:272