TYPO3CMS  8
 All Classes Namespaces Files Functions Variables Pages
Unit/Database/DatabaseConnectionTest.php
Go to the documentation of this file.
1 <?php
2 namespace TYPO3\CMS\Core\Tests\Unit\Database;
3 
4 /*
5  * This file is part of the TYPO3 CMS project.
6  *
7  * It is free software; you can redistribute it and/or modify it under
8  * the terms of the GNU General Public License, either version 2
9  * of the License, or any later version.
10  *
11  * For the full copyright and license information, please read the
12  * LICENSE.txt file that was distributed with this source code.
13  *
14  * The TYPO3 project - inspiring people to share!
15  */
16 
21 
27 {
31  protected $subject;
35  protected $testTable = 'test_database_connection';
36 
40  protected $testField = 'test_field';
41 
45  protected $anotherTestField = 'another_test_field';
46 
52  protected function setUp()
53  {
54  parent::setUp();
55  $this->subject = $this->getAccessibleMock(DatabaseConnection::class, ['dummy'], [], '', false);
56  $this->subject->_set('databaseName', 'typo3_test');
57  }
58 
60  // Write/Read tests for charsets and binaries
62 
66  public function storedFullAsciiRangeCallsLinkObjectWithGivenData()
67  {
68  $binaryString = '';
69  for ($i = 0; $i < 256; $i++) {
70  $binaryString .= chr($i);
71  }
72 
74  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
75  $subject->_set('isConnected', true);
76  $subject
77  ->expects($this->any())
78  ->method('fullQuoteStr')
79  ->will($this->returnCallback(function ($data) {
80  return $data;
81  }));
82  $mysqliProphecy = $this->prophesize(\mysqli::class);
83  $mysqliProphecy->query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ({$binaryString})")
84  ->shouldBeCalled();
85  $subject->_set('link', $mysqliProphecy->reveal());
86 
87  $subject->exec_INSERTquery($this->testTable, [$this->testField => $binaryString]);
88  }
89 
94  public function storedGzipCompressedDataReturnsSameData()
95  {
96  $testStringWithBinary = gzcompress('sdfkljer4587');
97 
99  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
100  $subject->_set('isConnected', true);
101  $subject
102  ->expects($this->any())
103  ->method('fullQuoteStr')
104  ->will($this->returnCallback(function ($data) {
105  return $data;
106  }));
107  $mysqliProphecy = $this->prophesize(\mysqli::class);
108  $mysqliProphecy->query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ({$testStringWithBinary})")
109  ->shouldBeCalled();
110  $subject->_set('link', $mysqliProphecy->reveal());
111 
112  $subject->exec_INSERTquery($this->testTable, [$this->testField => $testStringWithBinary]);
113  }
114 
116  // Tests concerning listQuery
118 
123  public function listQueryWithIntegerCommaAsValue()
124  {
126  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['quoteStr'], [], '', false);
127  $subject->_set('isConnected', true);
128  $subject
129  ->expects($this->any())
130  ->method('quoteStr')
131  ->will($this->returnCallback(function ($data) {
132  return $data;
133  }));
134  // Note: 44 = ord(',')
135  $this->assertEquals($subject->listQuery('dummy', 44, 'table'), $subject->listQuery('dummy', '44', 'table'));
136  }
137 
141  public function listQueryThrowsExceptionIfValueContainsComma()
142  {
143  $this->expectException(\InvalidArgumentException::class);
144  $this->expectExceptionCode(1294585862);
145 
147  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['quoteStr'], [], '', false);
148  $subject->_set('isConnected', true);
149  $subject->listQuery('aField', 'foo,bar', 'aTable');
150  }
151 
153  // Tests concerning searchQuery
155 
161  public function searchQueryDataProvider()
162  {
163  return [
164  'One search word in one field' => [
165  '(pages.title LIKE \'%TYPO3%\')',
166  ['TYPO3'],
167  ['title'],
168  'pages',
169  'AND',
170  ],
171  'One search word with special chars (for like)' => [
172  '(pages.title LIKE \'%TYPO3\\_100\\%%\')',
173  ['TYPO3_100%'],
174  ['title'],
175  'pages',
176  'AND',
177  ],
178  'One search word in multiple fields' => [
179  "(pages.title LIKE '%TYPO3%' OR pages.keyword LIKE '%TYPO3%' OR pages.description LIKE '%TYPO3%')",
180  ['TYPO3'],
181  ['title', 'keyword', 'description'],
182  'pages',
183  'AND',
184  ],
185  'Multiple search words in one field with AND constraint' => [
186  "(pages.title LIKE '%TYPO3%') AND (pages.title LIKE '%is%') AND (pages.title LIKE '%great%')",
187  ['TYPO3', 'is', 'great'],
188  ['title'],
189  'pages',
190  'AND',
191  ],
192  'Multiple search words in one field with OR constraint' => [
193  "(pages.title LIKE '%TYPO3%') OR (pages.title LIKE '%is%') OR (pages.title LIKE '%great%')",
194  ['TYPO3', 'is', 'great'],
195  ['title'],
196  'pages',
197  'OR',
198  ],
199  'Multiple search words in multiple fields with AND constraint' => [
200  "(pages.title LIKE '%TYPO3%' OR pages.keywords LIKE '%TYPO3%' OR pages.description LIKE '%TYPO3%') " .
201  "AND (pages.title LIKE '%is%' OR pages.keywords LIKE '%is%' OR pages.description LIKE '%is%') " .
202  "AND (pages.title LIKE '%great%' OR pages.keywords LIKE '%great%' OR pages.description LIKE '%great%')",
203  ['TYPO3', 'is', 'great'],
204  ['title', 'keywords', 'description'],
205  'pages',
206  'AND',
207  ],
208  'Multiple search words in multiple fields with OR constraint' => [
209  "(pages.title LIKE '%TYPO3%' OR pages.keywords LIKE '%TYPO3%' OR pages.description LIKE '%TYPO3%') " .
210  "OR (pages.title LIKE '%is%' OR pages.keywords LIKE '%is%' OR pages.description LIKE '%is%') " .
211  "OR (pages.title LIKE '%great%' OR pages.keywords LIKE '%great%' OR pages.description LIKE '%great%')",
212  ['TYPO3', 'is', 'great'],
213  ['title', 'keywords', 'description'],
214  'pages',
215  'OR',
216  ],
217  ];
218  }
219 
229  public function searchQueryCreatesQuery($expectedResult, array $searchWords, array $fields, $table, $constraint)
230  {
232  $subject = $this->getMockBuilder(DatabaseConnection::class)
233  ->setMethods(['quoteStr'])
234  ->disableOriginalConstructor()
235  ->getMock();
236  $subject
237  ->expects($this->any())
238  ->method('quoteStr')
239  ->will($this->returnCallback(function ($data) {
240  return $data;
241  }));
242 
243  $this->assertSame($expectedResult, $subject->searchQuery($searchWords, $fields, $table, $constraint));
244  }
245 
247  // Tests concerning escapeStringForLikeComparison
249 
253  public function escapeStringForLikeComparison()
254  {
256  $subject = $this->getMockBuilder(DatabaseConnection::class)
257  ->setMethods(['dummy'])
258  ->disableOriginalConstructor()
259  ->getMock();
260  $this->assertEquals('foo\\_bar\\%', $subject->escapeStrForLike('foo_bar%', 'table'));
261  }
262 
264  // Tests concerning stripOrderByForOrderByKeyword
266 
274  {
275  return [
276  'single ORDER BY' => [
277  'ORDER BY name, tstamp',
278  'name, tstamp'
279  ],
280  'single ORDER BY in lower case' => [
281  'order by name, tstamp',
282  'name, tstamp'
283  ],
284  'ORDER BY with additional space behind' => [
285  'ORDER BY name, tstamp',
286  'name, tstamp'
287  ],
288  'ORDER BY without space between the words' => [
289  'ORDERBY name, tstamp',
290  'name, tstamp'
291  ],
292  'ORDER BY added twice' => [
293  'ORDER BY ORDER BY name, tstamp',
294  'name, tstamp'
295  ],
296  'ORDER BY added twice without spaces in the first occurrence' => [
297  'ORDERBY ORDER BY name, tstamp',
298  'name, tstamp',
299  ],
300  'ORDER BY added twice without spaces in the second occurrence' => [
301  'ORDER BYORDERBY name, tstamp',
302  'name, tstamp',
303  ],
304  'ORDER BY added twice without spaces' => [
305  'ORDERBYORDERBY name, tstamp',
306  'name, tstamp'
307  ],
308  'ORDER BY added twice without spaces afterwards' => [
309  'ORDERBYORDERBYname, tstamp',
310  'name, tstamp'
311  ],
312  ];
313  }
314 
322  public function stripOrderByForOrderByKeyword($orderByClause, $expectedResult)
323  {
325  $subject = $this->getMockBuilder(DatabaseConnection::class)
326  ->setMethods(['dummy'])
327  ->disableOriginalConstructor()
328  ->getMock();
329  $strippedQuery = $subject->stripOrderBy($orderByClause);
330  $this->assertEquals($expectedResult, $strippedQuery);
331  }
332 
334  // Tests concerning stripGroupByForGroupByKeyword
336 
344  {
345  return [
346  'single GROUP BY' => [
347  'GROUP BY name, tstamp',
348  'name, tstamp'
349  ],
350  'single GROUP BY in lower case' => [
351  'group by name, tstamp',
352  'name, tstamp'
353  ],
354  'GROUP BY with additional space behind' => [
355  'GROUP BY name, tstamp',
356  'name, tstamp'
357  ],
358  'GROUP BY without space between the words' => [
359  'GROUPBY name, tstamp',
360  'name, tstamp'
361  ],
362  'GROUP BY added twice' => [
363  'GROUP BY GROUP BY name, tstamp',
364  'name, tstamp'
365  ],
366  'GROUP BY added twice without spaces in the first occurrence' => [
367  'GROUPBY GROUP BY name, tstamp',
368  'name, tstamp',
369  ],
370  'GROUP BY added twice without spaces in the second occurrence' => [
371  'GROUP BYGROUPBY name, tstamp',
372  'name, tstamp',
373  ],
374  'GROUP BY added twice without spaces' => [
375  'GROUPBYGROUPBY name, tstamp',
376  'name, tstamp'
377  ],
378  'GROUP BY added twice without spaces afterwards' => [
379  'GROUPBYGROUPBYname, tstamp',
380  'name, tstamp'
381  ],
382  ];
383  }
384 
392  public function stripGroupByForGroupByKeyword($groupByClause, $expectedResult)
393  {
395  $subject = $this->getMockBuilder(DatabaseConnection::class)
396  ->setMethods(['dummy'])
397  ->disableOriginalConstructor()
398  ->getMock();
399  $strippedQuery = $subject->stripGroupBy($groupByClause);
400  $this->assertEquals($expectedResult, $strippedQuery);
401  }
402 
404  // Tests concerning stripOrderByForOrderByKeyword
406 
413  public function cleanIntArrayDataProvider()
414  {
415  return [
416  'simple array' => [
417  [1, 2, 3],
418  [1, 2, 3],
419  ],
420  'string array' => [
421  ['2', '4', '8'],
422  [2, 4, 8],
423  ],
424  'string array with letters #1' => [
425  ['3', '6letters', '12'],
426  [3, 6, 12],
427  ],
428  'string array with letters #2' => [
429  ['3', 'letters6', '12'],
430  [3, 0, 12],
431  ],
432  'string array with letters #3' => [
433  ['3', '6letters4', '12'],
434  [3, 6, 12],
435  ],
436  'associative array' => [
437  ['apples' => 3, 'bananas' => 4, 'kiwis' => 9],
438  ['apples' => 3, 'bananas' => 4, 'kiwis' => 9],
439  ],
440  'associative string array' => [
441  ['apples' => '1', 'bananas' => '5', 'kiwis' => '7'],
442  ['apples' => 1, 'bananas' => 5, 'kiwis' => 7],
443  ],
444  'associative string array with letters #1' => [
445  ['apples' => '1', 'bananas' => 'no5', 'kiwis' => '7'],
446  ['apples' => 1, 'bananas' => 0, 'kiwis' => 7],
447  ],
448  'associative string array with letters #2' => [
449  ['apples' => '1', 'bananas' => '5yes', 'kiwis' => '7'],
450  ['apples' => 1, 'bananas' => 5, 'kiwis' => 7],
451  ],
452  'associative string array with letters #3' => [
453  ['apples' => '1', 'bananas' => '5yes9', 'kiwis' => '7'],
454  ['apples' => 1, 'bananas' => 5, 'kiwis' => 7],
455  ],
456  'multidimensional associative array' => [
457  ['apples' => '1', 'bananas' => [3, 4], 'kiwis' => '7'],
458  // intval(array(...)) is 1
459  // But by specification "cleanIntArray" should only get used on one-dimensional arrays
460  ['apples' => 1, 'bananas' => 1, 'kiwis' => 7],
461  ],
462  ];
463  }
464 
472  public function cleanIntArray($exampleData, $expectedResult)
473  {
474  $sanitizedArray = $this->subject->cleanIntArray($exampleData);
475  $this->assertEquals($expectedResult, $sanitizedArray);
476  }
477 
484  {
485  $str = '234,-434,4.3,0, 1';
486  $result = $this->subject->cleanIntList($str);
487  $this->assertSame('234,-434,4,0,1', $result);
488  }
489 
493  public function sqlForSelectMmQuery()
494  {
495  $result = $this->subject->SELECT_mm_query(
496  '*',
497  'sys_category',
498  'sys_category_record_mm',
499  'tt_content',
500  'AND sys_category.uid = 1',
501  '',
502  'sys_category.title DESC'
503  );
504  $expected = 'SELECT * FROM sys_category,sys_category_record_mm,tt_content ' .
505  'WHERE sys_category.uid=sys_category_record_mm.uid_local ' .
506  'AND tt_content.uid=sys_category_record_mm.uid_foreign ' .
507  'AND sys_category.uid = 1 ORDER BY sys_category.title DESC';
508  $this->assertEquals($expected, $result);
509  }
510 
517  {
518  return [
519  'noQuote boolean false' => [
520  ['aField' => 'aValue', 'anotherField' => 'anotherValue'],
521  ['aField' => "'aValue'", 'anotherField' => "'anotherValue'"],
522  false,
523  ],
524  'noQuote boolean true' => [
525  ['aField' => 'aValue', 'anotherField' => 'anotherValue'],
526  ['aField' => 'aValue', 'anotherField' => 'anotherValue'],
527  true,
528  ],
529  'noQuote list of fields' => [
530  ['aField' => 'aValue', 'anotherField' => 'anotherValue'],
531  ['aField' => "'aValue'", 'anotherField' => 'anotherValue'],
532  'anotherField',
533  ],
534  'noQuote array of fields' => [
535  ['aField' => 'aValue', 'anotherField' => 'anotherValue'],
536  ['aField' => 'aValue', 'anotherField' => "'anotherValue'"],
537  ['aField'],
538  ],
539  ];
540  }
541 
549  public function noQuoteForFullQuoteArray(array $input, array $expected, $noQuote)
550  {
552  $subject = $this->getMockBuilder(DatabaseConnection::class)
553  ->setMethods(['fullQuoteStr'])
554  ->disableOriginalConstructor()
555  ->getMock();
556 
557  $subject
558  ->expects($this->any())
559  ->method('fullQuoteStr')
560  ->will($this->returnCallback(function ($data) {
561  return '\'' . (string)$data . '\'';
562  }));
563  $this->assertSame($expected, $subject->fullQuoteArray($input, 'aTable', $noQuote));
564  }
565 
569  public function sqlSelectDbReturnsTrue()
570  {
572  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['dummy'], [], '', false);
573  $subject->_set('isConnected', true);
574  $subject->_set('databaseName', $this->testTable);
575 
576  $mysqliProphecy = $this->prophesize(\mysqli::class);
577  $mysqliProphecy->select_db($this->testTable)->shouldBeCalled()->willReturn(true);
578  $subject->_set('link', $mysqliProphecy->reveal());
579 
580  $this->assertTrue($subject->sql_select_db());
581  }
582 
586  public function sqlSelectDbReturnsFalse()
587  {
588  $GLOBALS['TYPO3_CONF_VARS']['SYS']['systemLogLevel'] = GeneralUtility::SYSLOG_SEVERITY_WARNING;
589 
591  $subject = $this->getAccessibleMock(DatabaseConnection::class, ['sql_error'], [], '', false);
592  $subject->_set('isConnected', true);
593  $subject->_set('databaseName', $this->testTable);
594  $subject->expects($this->any())->method('sql_error')->will($this->returnValue(''));
595 
596  $mysqliProphecy = $this->prophesize(\mysqli::class);
597  $mysqliProphecy->select_db($this->testTable)->shouldBeCalled()->willReturn(false);
598  $subject->_set('link', $mysqliProphecy->reveal());
599 
600  $this->assertFalse($subject->sql_select_db());
601  }
602 
608  public function insertQueryCreateValidQuery()
609  {
610  $this->subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
611  $this->subject->expects($this->any())
612  ->method('fullQuoteStr')
613  ->will($this->returnCallback(function ($data) {
614  return '\'' . (string)$data . '\'';
615  }));
616 
617  $fieldValues = [$this->testField => 'Foo'];
618  $queryExpected = "INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('Foo')";
619  $queryGenerated = $this->subject->INSERTquery($this->testTable, $fieldValues);
620  $this->assertSame($queryExpected, $queryGenerated);
621  }
622 
629  {
630  $this->subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
631  $this->subject->expects($this->any())
632  ->method('fullQuoteStr')
633  ->will($this->returnCallback(function ($data) {
634  return '\'' . (string)$data . '\'';
635  }));
636  $fieldValues = [
637  $this->testField => 'Foo',
638  $this->anotherTestField => 'Bar',
639  ];
640  $queryExpected = "INSERT INTO {$this->testTable} ({$this->testField},{$this->anotherTestField}) " .
641  "VALUES ('Foo','Bar')";
642  $queryGenerated = $this->subject->INSERTquery($this->testTable, $fieldValues);
643  $this->assertSame($queryExpected, $queryGenerated);
644  }
645 
652  {
653  $this->subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
654  $this->subject->expects($this->any())
655  ->method('fullQuoteStr')
656  ->will($this->returnCallback(function ($data) {
657  return '\'' . (string)$data . '\'';
658  }));
660  $values = [
661  ['Foo', 100],
662  ['Bar', 200],
663  ['Baz', 300],
664  ];
665  $queryExpected = "INSERT INTO {$this->testTable} ({$this->testField}, {$this->anotherTestField}) " .
666  "VALUES ('Foo', '100'), ('Bar', '200'), ('Baz', '300')";
667  $queryGenerated = $this->subject->INSERTmultipleRows($this->testTable, $fields, $values);
668  $this->assertSame($queryExpected, $queryGenerated);
669  }
670 
676  public function updateQueryCreateValidQuery()
677  {
678  $this->subject = $this->getAccessibleMock(DatabaseConnection::class, ['fullQuoteStr'], [], '', false);
679  $this->subject->expects($this->any())
680  ->method('fullQuoteStr')
681  ->will($this->returnCallback(function ($data) {
682  return '\'' . (string)$data . '\'';
683  }));
684 
685  $fieldsValues = [$this->testField => 'aTestValue'];
686  $queryExpected = "UPDATE {$this->testTable} SET {$this->testField}='aTestValue' WHERE id=1";
687  $queryGenerated = $this->subject->UPDATEquery($this->testTable, 'id=1', $fieldsValues);
688  $this->assertSame($queryExpected, $queryGenerated);
689  }
690 
696  public function deleteQueryCreateValidQuery()
697  {
698  $queryExpected = "DELETE FROM {$this->testTable} WHERE id=1";
699  $queryGenerated = $this->subject->DELETEquery($this->testTable, 'id=1');
700  $this->assertSame($queryExpected, $queryGenerated);
701  }
702 
708  public function selectQueryCreateValidQuery()
709  {
710  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1";
711  $queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, 'id=1');
712  $this->assertSame($queryExpected, $queryGenerated);
713  }
714 
721  {
722  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable}";
723  $queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, '');
724  $this->assertSame($queryExpected, $queryGenerated);
725  }
726 
733  {
734  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1 GROUP BY id";
735  $queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, 'id=1', 'id');
736  $this->assertSame($queryExpected, $queryGenerated);
737  }
738 
745  {
746  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1 ORDER BY id";
747  $queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, 'id=1', '', 'id');
748  $this->assertSame($queryExpected, $queryGenerated);
749  }
750 
757  {
758  $queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, 'id=1', '', '', '1,2');
759  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1 LIMIT 1,2";
760  $this->assertSame($queryExpected, $queryGenerated);
761  }
762 
769  {
770  $queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1";
771  $queryGenerated = $this->subject->SELECTsubquery($this->testField, $this->testTable, 'id=1');
772  $this->assertSame($queryExpected, $queryGenerated);
773  }
774 
781  {
782  $queryExpected = "TRUNCATE TABLE {$this->testTable}";
783  $queryGenerated = $this->subject->TRUNCATEquery($this->testTable);
784  $this->assertSame($queryExpected, $queryGenerated);
785  }
786 }
if(TYPO3_MODE=== 'BE') $GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tsfebeuserauth.php']['frontendEditingController']['default']
getAccessibleMock($originalClassName, $methods=[], array $arguments=[], $mockClassName= '', $callOriginalConstructor=true, $callOriginalClone=true, $callAutoload=true)