db1dc7960284d6cf955c5073448bf257a6e7b101
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / Query / QueryHelperTest.php
1 <?php
2 declare(strict_types = 1);
3 namespace TYPO3\CMS\Core\Tests\Unit\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 Prophecy\Argument;
19 use TYPO3\CMS\Core\Database\Connection;
20 use TYPO3\CMS\Core\Database\Query\QueryHelper;
21
22 /**
23 * Query helper test
24 */
25 class QueryHelperTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
26 {
27 /**
28 * Subject is not notice free, disable E_NOTICES
29 */
30 protected static $suppressNotices = true;
31
32 /**
33 * Test cases for stripping of leading logical operators in where constraints.
34 *
35 * @return array
36 */
37 public function stripLogicalOperatorPrefixDataProvider(): array
38 {
39 return [
40 'unprefixed input' => ['1=1', '1=1'],
41 'leading/trailing whitespace is removed' => [' 1=1 ', '1=1'],
42 'AND' => ['AND 1=1', '1=1'],
43 'AND with leading space' => [' AND 1=1', '1=1'],
44 'AND with mixed whitespace' => [' AND 1<>1', '1<>1'],
45 'AND with opening bracket' => ['AND (1=1)', '(1=1)'],
46 'AND without whitespace before bracket' => ['AND(1=1)', '(1=1)'],
47 'AND within input' => ['1=1 AND 2=2', '1=1 AND 2=2'],
48 'OR' => ['OR 1=1', '1=1'],
49 'OR with leading space' => [' OR 1=1', '1=1'],
50 'OR with mixed whitespace' => [' OR 1<>1', '1<>1'],
51 'OR with opening bracket' => ['OR (1=1)', '(1=1)'],
52 'OR without whitespace before bracket' => ['OR(1=1)', '(1=1)'],
53 'OR within input' => ['1=1 OR 2=2', '1=1 OR 2=2'],
54 ];
55 }
56
57 /**
58 * @test
59 * @dataProvider stripLogicalOperatorPrefixDataProvider
60 * @param string $input
61 * @param string $expectedSql
62 */
63 public function stripLogicalOperatorPrefixRemovesConstraintPrefixes(string $input, string $expectedSql)
64 {
65 $this->assertSame($expectedSql, QueryHelper::stripLogicalOperatorPrefix($input));
66 }
67
68 /**
69 * Test cases for parsing ORDER BY SQL fragments
70 *
71 * @return array
72 */
73 public function parseOrderByDataProvider(): array
74 {
75 return [
76 'empty string' => [
77 '',
78 [],
79 ],
80 'single field' => [
81 'aField',
82 [
83 ['aField', null],
84 ],
85 ],
86 'single field with leading whitespace' => [
87 ' aField',
88 [
89 ['aField', null],
90 ],
91 ],
92 'prefixed single field' => [
93 'ORDER BY aField',
94 [
95 ['aField', null],
96 ],
97 ],
98 'prefixed single field with leading whitespace' => [
99 ' ORDER BY aField',
100 [
101 ['aField', null],
102 ],
103 ],
104 'single field with direction' => [
105 'aField DESC',
106 [
107 ['aField', 'DESC'],
108 ],
109 ],
110 'multiple fields' => [
111 'aField,anotherField, aThirdField',
112 [
113 ['aField', null],
114 ['anotherField', null],
115 ['aThirdField', null]
116 ],
117 ],
118 'multiple fields with direction' => [
119 'aField ASC,anotherField, aThirdField DESC',
120 [
121 ['aField', 'ASC'],
122 ['anotherField', null],
123 ['aThirdField', 'DESC']
124 ],
125 ],
126 'prefixed multiple fields with direction' => [
127 'ORDER BY aField ASC,anotherField, aThirdField DESC',
128 [
129 ['aField', 'ASC'],
130 ['anotherField', null],
131 ['aThirdField', 'DESC']
132 ],
133 ],
134 'with table prefix' => [
135 'ORDER BY be_groups.title',
136 [
137 ['be_groups.title', null]
138 ]
139 ],
140 ];
141 }
142
143 /**
144 * @test
145 * @dataProvider parseOrderByDataProvider
146 * @param string $input
147 * @param array $expectedResult
148 */
149 public function parseOrderByTest(string $input, array $expectedResult)
150 {
151 $this->assertSame($expectedResult, QueryHelper::parseOrderBy($input));
152 }
153
154 /**
155 * Test cases for parsing FROM tableList SQL fragments
156 *
157 * @return array
158 */
159 public function parseTableListDataProvider(): array
160 {
161 return [
162 'single table' => [
163 'aTable',
164 [
165 ['aTable', null],
166 ],
167 ],
168 'single table with leading whitespace' => [
169 ' aTable',
170 [
171 ['aTable', null],
172 ],
173 ],
174 'prefixed single table' => [
175 'FROM aTable',
176 [
177 ['aTable', null],
178 ],
179 ],
180 'prefixed single table with leading whitespace' => [
181 ' FROM aTable',
182 [
183 ['aTable', null],
184 ],
185 ],
186 'single table with alias' => [
187 'aTable a',
188 [
189 ['aTable', 'a'],
190 ],
191 ],
192 'multiple tables' => [
193 'aTable,anotherTable, aThirdTable',
194 [
195 ['aTable', null],
196 ['anotherTable', null],
197 ['aThirdTable', null]
198 ],
199 ],
200 'multiple tables with aliases' => [
201 'aTable a,anotherTable, aThirdTable AS c',
202 [
203 ['aTable', 'a'],
204 ['anotherTable', null],
205 ['aThirdTable', 'c']
206 ],
207 ],
208 'prefixed multiple tables with aliases' => [
209 'FROM aTable a,anotherTable, aThirdTable AS c',
210 [
211 ['aTable', 'a'],
212 ['anotherTable', null],
213 ['aThirdTable', 'c']
214 ],
215 ]
216 ];
217 }
218
219 /**
220 * @test
221 * @dataProvider parseTableListDataProvider
222 * @param string $input
223 * @param array $expectedResult
224 */
225 public function parseTableListTest(string $input, array $expectedResult)
226 {
227 $this->assertSame($expectedResult, QueryHelper::parseTableList($input));
228 }
229
230 /**
231 * Test cases for parsing ORDER BY SQL fragments
232 *
233 * @return array
234 */
235 public function parseGroupByDataProvider(): array
236 {
237 return [
238 'single field' => [
239 'aField',
240 ['aField'],
241 ],
242 'single field with leading whitespace' => [
243 ' aField',
244 ['aField'],
245 ],
246 'prefixed single field' => [
247 'GROUP BY aField',
248 ['aField'],
249 ],
250 'prefixed single field with leading whitespace' => [
251 ' GROUP BY aField',
252 ['aField'],
253 ],
254 'multiple fields' => [
255 'aField,anotherField, aThirdField',
256 ['aField', 'anotherField', 'aThirdField']
257 ],
258 'prefixed multiple fields' => [
259 'GROUP BY aField,anotherField, aThirdField',
260 ['aField', 'anotherField', 'aThirdField']
261 ],
262 'with table prefix' => [
263 'GROUP BY be_groups.title',
264 ['be_groups.title']
265 ],
266 ];
267 }
268
269 /**
270 * @test
271 * @dataProvider parseGroupByDataProvider
272 * @param string $input
273 * @param array $expectedResult
274 */
275 public function parseGroupByTest(string $input, array $expectedResult)
276 {
277 $this->assertSame($expectedResult, QueryHelper::parseGroupBy($input));
278 }
279
280 /**
281 * Test cases for parsing JOIN fragments into table name, alias and conditions
282 *
283 * @return array
284 */
285 public function parseJoinDataProvider(): array
286 {
287 return [
288 'unquoted tableName' => [
289 'aTable ON aTable.uid = anotherTable.uid_foreign',
290 [
291 'tableName' => 'aTable',
292 'tableAlias' => 'aTable',
293 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
294 ],
295 ],
296 'quoted tableName' => [
297 '`aTable` ON aTable.uid = anotherTable.uid_foreign',
298 [
299 'tableName' => 'aTable',
300 'tableAlias' => 'aTable',
301 'joinCondition' => 'aTable.uid = anotherTable.uid_foreign'
302 ],
303 ],
304 'quoted tableName with alias' => [
305 '`aTable` a ON a.uid = anotherTable.uid_foreign',
306 [
307 'tableName' => 'aTable',
308 'tableAlias' => 'a',
309 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
310 ],
311 ],
312 'quoted tableName with quoted alias' => [
313 '`aTable` `a` ON a.uid = anotherTable.uid_foreign',
314 [
315 'tableName' => 'aTable',
316 'tableAlias' => 'a',
317 'joinCondition' => 'a.uid = anotherTable.uid_foreign'
318 ],
319 ],
320 'quoted tableName with AS alias' => [
321 '`aTable` AS anAlias ON anAlias.uid = anotherTable.uid_foreign',
322 [
323 'tableName' => 'aTable',
324 'tableAlias' => 'anAlias',
325 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
326 ],
327 ],
328 'quoted tableName with AS quoted alias' => [
329 '`aTable` AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
330 [
331 'tableName' => 'aTable',
332 'tableAlias' => 'anAlias',
333 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
334 ],
335 ],
336 'unquoted tableName with AS quoted alias' => [
337 'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign',
338 [
339 'tableName' => 'aTable',
340 'tableAlias' => 'anAlias',
341 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
342 ],
343 ],
344 ];
345 }
346
347 /**
348 * @test
349 * @dataProvider parseJoinDataProvider
350 * @param string $input
351 * @param array $expected
352 */
353 public function parseJoinSplitsStatement(string $input, array $expected)
354 {
355 $this->assertSame($expected, QueryHelper::parseJoin($input));
356 }
357
358 /**
359 * Test cases for quoting column/table name identifiers in SQL fragments
360 *
361 * @return array
362 */
363 public function quoteDatabaseIdentifierDataProvider(): array
364 {
365 return [
366 'no marked identifiers' => [
367 'colPos=0',
368 'colPos=0',
369 ],
370 'single fieldname' => [
371 '{#colPos}=0',
372 '"colPos"=0',
373 ],
374 'tablename and fieldname' => [
375 '{#tt_content.colPos}=0',
376 '"tt_content"."colPos"=0',
377 ],
378 'multiple fieldnames' => [
379 '{#colPos}={#aField}',
380 '"colPos"="aField"',
381 ],
382 ];
383 }
384
385 /**
386 * @test
387 * @dataProvider quoteDatabaseIdentifierDataProvider
388 * @param string $input
389 * @param string $expected
390 */
391 public function quoteDatabaseIdentifiers(string $input, string $expected)
392 {
393 $connectionProphet = $this->prophesize(Connection::class);
394 $connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
395 $parts = array_map(
396 function ($identifier) {
397 return '"' . $identifier . '"';
398 },
399 explode('.', $args[0])
400 );
401
402 return implode('.', $parts);
403 });
404
405 $this->assertSame($expected, QueryHelper::quoteDatabaseIdentifiers($connectionProphet->reveal(), $input));
406 }
407 }