4673b67bd6a192eaba91ce93113220dc23082ec0
[Packages/TYPO3.CMS.git] / typo3 / sysext / typo3db_legacy / Tests / Functional / Database / DatabaseConnectionTest.php
1 <?php
2 namespace TYPO3\CMS\Typo3DbLegacy\Tests\Functional\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 use TYPO3\CMS\Typo3DbLegacy\Database\DatabaseConnection;
17 use TYPO3\TestingFramework\Core\AccessibleObjectInterface;
18
19 /**
20 * Test case for \TYPO3\CMS\Core\Database\DatabaseConnection
21 */
22 class DatabaseConnectionTest extends \TYPO3\TestingFramework\Core\Functional\FunctionalTestCase
23 {
24 /**
25 * @var array
26 */
27 protected $coreExtensionsToLoad = ['typo3db_legacy'];
28
29 /**
30 * @var DatabaseConnection
31 */
32 protected $subject = null;
33
34 /**
35 * @var string
36 */
37 protected $testTable = 'test_database_connection';
38
39 /**
40 * @var string
41 */
42 protected $testField = 'test_field';
43
44 /**
45 * @var string
46 */
47 protected $anotherTestField = 'another_test_field';
48
49 /**
50 * Set the test up
51 */
52 protected function setUp()
53 {
54 parent::setUp();
55 $this->subject = $GLOBALS['TYPO3_DB'];
56 $this->subject->sql_query(
57 "CREATE TABLE {$this->testTable} (" .
58 ' id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,' .
59 " {$this->testField} MEDIUMBLOB," .
60 " {$this->anotherTestField} MEDIUMBLOB," .
61 ' PRIMARY KEY (id)' .
62 ') ENGINE=MyISAM DEFAULT CHARSET=utf8;'
63 );
64 }
65
66 /**
67 * Tear the test down
68 */
69 protected function tearDown()
70 {
71 $this->subject->sql_query("DROP TABLE {$this->testTable};");
72 unset($this->subject);
73 }
74
75 /**
76 * @test
77 *
78 * @group mysql
79 */
80 public function selectDbReturnsTrue()
81 {
82 $this->assertTrue($this->subject->sql_select_db());
83 }
84
85 /**
86 * @test
87 *
88 * @group mysql
89 */
90 public function selectDbReturnsFalse()
91 {
92 $this->expectException(\RuntimeException::class);
93 $this->expectExceptionCode(1270853883);
94 $this->expectExceptionMessage('TYPO3 Fatal Error: Cannot connect to the current database, "Foo"!');
95
96 $this->subject->setDatabaseName('Foo');
97 $this->assertFalse($this->subject->sql_select_db());
98 }
99
100 /**
101 * @test
102 *
103 * @group mysql
104 */
105 public function sqlAffectedRowsReturnsCorrectAmountOfRows()
106 {
107 $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
108 $this->assertEquals(1, $this->subject->sql_affected_rows());
109 }
110
111 /**
112 * @test
113 *
114 * @group mysql
115 */
116 public function sqlInsertIdReturnsCorrectId()
117 {
118 $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
119 $this->assertEquals(1, $this->subject->sql_insert_id());
120 }
121
122 /**
123 * @test
124 *
125 * @group mysql
126 */
127 public function noSqlError()
128 {
129 $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
130 $this->assertEquals('', $this->subject->sql_error());
131 }
132
133 /**
134 * @test
135 *
136 * @group mysql
137 */
138 public function sqlErrorWhenInsertIntoInexistentField()
139 {
140 $this->subject->exec_INSERTquery($this->testTable, ['test' => 'test']);
141 $this->assertEquals('Unknown column \'test\' in \'field list\'', $this->subject->sql_error());
142 }
143
144 /**
145 * @test
146 *
147 * @group mysql
148 */
149 public function noSqlErrorCode()
150 {
151 $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
152 $this->assertEquals(0, $this->subject->sql_errno());
153 }
154
155 /**
156 * @test
157 *
158 * @group mysql
159 */
160 public function sqlErrorNoWhenInsertIntoInexistentField()
161 {
162 $this->subject->exec_INSERTquery($this->testTable, ['test' => 'test']);
163 $this->assertEquals(1054, $this->subject->sql_errno());
164 }
165
166 /**
167 * @test
168 *
169 * @group mysql
170 */
171 public function sqlPconnectReturnsInstanceOfMySqli()
172 {
173 $this->assertInstanceOf('mysqli', $this->subject->sql_pconnect());
174 }
175
176 /**
177 * @test
178 *
179 * @group mysql
180 */
181 public function connectDbThrowsExeptionsWhenNoDatabaseIsGiven()
182 {
183 $this->expectException(\RuntimeException::class);
184 $this->expectExceptionCode(1270853882);
185
186 /** @var DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|AccessibleObjectInterface $subject */
187 $subject = $this->getAccessibleMock(DatabaseConnection::class, ['dummy'], [], '', false);
188 $subject->connectDB();
189 }
190
191 /**
192 * @test
193 *
194 * @group mysql
195 */
196 public function connectDbConnectsToDatabaseWithoutErrors()
197 {
198 $this->subject->connectDB();
199 $this->assertTrue($this->subject->isConnected());
200 }
201
202 /**
203 * @test
204 *
205 * @group mysql
206 */
207 public function disconnectIfConnectedDisconnects()
208 {
209 $this->assertTrue($this->subject->isConnected());
210 $this->subject->setDatabaseHost('127.0.0.1');
211 $this->assertFalse($this->subject->isConnected());
212 }
213
214 /**
215 * Data Provider for fullQuoteStrReturnsQuotedString()
216 *
217 * @see fullQuoteStrReturnsQuotedString()
218 *
219 * @return array
220 */
221 public function fullQuoteStrReturnsQuotedStringDataProvider()
222 {
223 return [
224 'NULL string with ReturnNull is allowed' => [
225 [null, true],
226 'NULL',
227 ],
228 'NULL string with ReturnNull is false' => [
229 [null, false],
230 "''",
231 ],
232 'Normal string' => [
233 ['Foo', false],
234 "'Foo'",
235 ],
236 'Single quoted string' => [
237 ["'Hello'", false],
238 "'\\'Hello\\''",
239 ],
240 'Double quoted string' => [
241 ['"Hello"', false],
242 "'\\\"Hello\\\"'",
243 ],
244 'String with internal single tick' => [
245 ['It\'s me', false],
246 "'It\\'s me'",
247 ],
248 'Slashes' => [
249 ['/var/log/syslog.log', false],
250 "'/var/log/syslog.log'",
251 ],
252 'Backslashes' => [
253 ['\\var\\log\\syslog.log', false],
254 "'\\\\var\\\\log\\\\syslog.log'",
255 ],
256 ];
257 }
258
259 /**
260 * @test
261 * @dataProvider fullQuoteStrReturnsQuotedStringDataProvider
262 *
263 * @param string $values
264 * @param string $expectedResult
265 *
266 * @group mysql
267 */
268 public function fullQuoteStrReturnsQuotedString($values, $expectedResult)
269 {
270 /** @var DatabaseConnection $subject */
271 $quotedStr = $this->subject->fullQuoteStr($values[0], 'tt_content', $values[1]);
272 $this->assertEquals($expectedResult, $quotedStr);
273 }
274
275 /**
276 * Data Provider for fullQuoteArrayQuotesArray()
277 *
278 * @see fullQuoteArrayQuotesArray()
279 *
280 * @return array
281 */
282 public function fullQuoteArrayQuotesArrayDataProvider()
283 {
284 return [
285 'NULL array with ReturnNull is allowed' => [
286 [
287 [null, null],
288 false,
289 true,
290 ],
291 ['NULL', 'NULL'],
292 ],
293
294 'NULL array with ReturnNull is false' => [
295 [
296 [null, null],
297 false,
298 false,
299 ],
300 ["''", "''"],
301 ],
302
303 'Strings in array' => [
304 [
305 ['Foo', 'Bar'],
306 false,
307 false,
308 ],
309 ["'Foo'", "'Bar'"],
310 ],
311
312 'Single quotes in array' => [
313 [
314 ["'Hello'"],
315 false,
316 false,
317 ],
318 ["'\\'Hello\\''"],
319 ],
320
321 'Double quotes in array' => [
322 [
323 ['"Hello"'],
324 false,
325 false,
326 ],
327 ["'\\\"Hello\\\"'"],
328 ],
329
330 'Slashes in array' => [
331 [
332 ['/var/log/syslog.log'],
333 false,
334 false,
335 ],
336 ["'/var/log/syslog.log'"],
337 ],
338
339 'Backslashes in array' => [
340 [
341 ['\var\log\syslog.log'],
342 false,
343 false,
344 ],
345 ["'\\\\var\\\\log\\\\syslog.log'"],
346 ],
347
348 'Strings with internal single tick' => [
349 [
350 ['Hey!', 'It\'s me'],
351 false,
352 false,
353 ],
354 ["'Hey!'", "'It\\'s me'"],
355 ],
356
357 'no quotes strings from array' => [
358 [
359 [
360 'First' => 'Hey!',
361 'Second' => 'It\'s me',
362 'Third' => 'O\' Reily',
363 ],
364 ['First', 'Third'],
365 false,
366 ],
367 ['First' => 'Hey!', 'Second' => "'It\\'s me'", 'Third' => "O' Reily"],
368 ],
369
370 'no quotes strings from string' => [
371 [
372 [
373 'First' => 'Hey!',
374 'Second' => 'It\'s me',
375 'Third' => 'O\' Reily',
376 ],
377 'First,Third',
378 false,
379 ],
380 ['First' => 'Hey!', 'Second' => "'It\\'s me'", 'Third' => "O' Reily"],
381 ],
382 ];
383 }
384
385 /**
386 * @test
387 * @dataProvider fullQuoteArrayQuotesArrayDataProvider
388 *
389 * @param string $values
390 * @param string $expectedResult
391 *
392 * @group mysql
393 */
394 public function fullQuoteArrayQuotesArray($values, $expectedResult)
395 {
396 $quotedResult = $this->subject->fullQuoteArray($values[0], $this->testTable, $values[1], $values[2]);
397 $this->assertSame($expectedResult, $quotedResult);
398 }
399
400 /**
401 * Data Provider for quoteStrQuotesDoubleQuotesCorrectly()
402 *
403 * @see quoteStrQuotesDoubleQuotesCorrectly()
404 *
405 * @return array
406 */
407 public function quoteStrQuotesCorrectlyDataProvider()
408 {
409 return [
410 'Double Quotes' => [
411 '"Hello"',
412 '\\"Hello\\"'
413 ],
414 'Single Quotes' => [
415 '\'Hello\'',
416 "\\'Hello\\'"
417 ],
418 'Slashes' => [
419 '/var/log/syslog.log',
420 '/var/log/syslog.log'
421 ],
422 'Literal Backslashes' => [
423 '\\var\\log\\syslog.log',
424 '\\\\var\\\\log\\\\syslog.log'
425 ],
426 'Fallback Literal Backslashes' => [
427 '\var\log\syslog.log',
428 '\\\\var\\\\log\\\\syslog.log'
429 ],
430 ];
431 }
432
433 /**
434 * @test
435 * @dataProvider quoteStrQuotesCorrectlyDataProvider
436 *
437 * @param string $string String to quote
438 * @param string $expectedResult Quoted string we expect
439 *
440 * @group mysql
441 */
442 public function quoteStrQuotesDoubleQuotesCorrectly($string, $expectedResult)
443 {
444 $quotedString = $this->subject->quoteStr($string, $this->testTable);
445 $this->assertSame($expectedResult, $quotedString);
446 }
447
448 /**
449 * @test
450 *
451 * @group mysql
452 */
453 public function adminQueryReturnsTrueForInsertQuery()
454 {
455 $this->assertTrue(
456 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
457 );
458 }
459
460 /**
461 * @test
462 *
463 * @group mysql
464 */
465 public function adminQueryReturnsTrueForUpdateQuery()
466 {
467 $this->assertTrue(
468 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
469 );
470 $id = $this->subject->sql_insert_id();
471 $this->assertTrue(
472 $this->subject->admin_query("UPDATE {$this->testTable} SET {$this->testField}='bar' WHERE id={$id}")
473 );
474 }
475
476 /**
477 * @test
478 *
479 * @group mysql
480 */
481 public function adminQueryReturnsTrueForDeleteQuery()
482 {
483 $this->assertTrue(
484 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
485 );
486 $id = $this->subject->sql_insert_id();
487 $this->assertTrue($this->subject->admin_query("DELETE FROM {$this->testTable} WHERE id={$id}"));
488 }
489
490 /**
491 * @test
492 *
493 * @group mysql
494 */
495 public function adminQueryReturnsResultForSelectQuery()
496 {
497 $this->assertTrue(
498 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
499 );
500 $res = $this->subject->admin_query("SELECT {$this->testField} FROM {$this->testTable}");
501 $this->assertInstanceOf('mysqli_result', $res);
502 $result = $res->fetch_assoc();
503 $this->assertEquals('foo', $result[$this->testField]);
504 }
505
506 /**
507 * @test
508 *
509 * @group mysql
510 */
511 public function adminGetCharsetsReturnsArrayWithCharsets()
512 {
513 $columnsRes = $this->subject->admin_query('SHOW CHARACTER SET');
514 $result = $this->subject->admin_get_charsets();
515 $this->assertEquals(count($result), $columnsRes->num_rows);
516
517 /** @var array $row */
518 while (($row = $columnsRes->fetch_assoc())) {
519 $this->assertArrayHasKey($row['Charset'], $result);
520 }
521 }
522
523 /**
524 * @test
525 *
526 * @group mysql
527 */
528 public function adminGetKeysReturnIndexKeysOfTable()
529 {
530 $result = $this->subject->admin_get_keys($this->testTable);
531 $this->assertEquals('id', $result[0]['Column_name']);
532 }
533
534 /**
535 * @test
536 *
537 * @group mysql
538 */
539 public function adminGetFieldsReturnFieldInformationsForTable()
540 {
541 $result = $this->subject->admin_get_fields($this->testTable);
542 $this->assertArrayHasKey('id', $result);
543 $this->assertArrayHasKey($this->testField, $result);
544 }
545
546 /**
547 * @test
548 *
549 * @group mysql
550 */
551 public function adminGetTablesReturnAllTablesFromDatabase()
552 {
553 $result = $this->subject->admin_get_tables();
554 $this->assertArrayHasKey('tt_content', $result);
555 $this->assertArrayHasKey('pages', $result);
556 }
557
558 /**
559 * @test
560 *
561 * @group mysql
562 */
563 public function adminGetDbsReturnsAllDatabases()
564 {
565 /** @noinspection SqlResolve */
566 $databases = $this->subject->admin_query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
567 $result = $this->subject->admin_get_dbs();
568 $this->assertSame(count($result), $databases->num_rows);
569
570 $i = 0;
571 while ($database = $databases->fetch_assoc()) {
572 $this->assertSame($database['SCHEMA_NAME'], $result[$i]);
573 $i++;
574 }
575 }
576
577 /**
578 * Data Provider for sqlNumRowsReturnsCorrectAmountOfRows()
579 *
580 * @see sqlNumRowsReturnsCorrectAmountOfRows()
581 *
582 * @return array
583 */
584 public function sqlNumRowsReturnsCorrectAmountOfRowsProvider()
585 {
586 $sql1 = "SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz'";
587 $sql2 = "SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz' OR {$this->testField}='bar'";
588 $sql3 = "SELECT * FROM {$this->testTable} WHERE {$this->testField} IN ('baz', 'bar', 'foo')";
589
590 return [
591 'One result' => [$sql1, 1],
592 'Two results' => [$sql2, 2],
593 'Three results' => [$sql3, 3],
594 ];
595 }
596
597 /**
598 * @test
599 * @dataProvider sqlNumRowsReturnsCorrectAmountOfRowsProvider
600 *
601 * @param string $sql
602 * @param string $expectedResult
603 *
604 * @group mysql
605 */
606 public function sqlNumRowsReturnsCorrectAmountOfRows($sql, $expectedResult)
607 {
608 $this->assertTrue(
609 $this->subject->admin_query(
610 "INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo'), ('bar'), ('baz')"
611 )
612 );
613
614 $res = $this->subject->admin_query($sql);
615 $numRows = $this->subject->sql_num_rows($res);
616 $this->assertSame($expectedResult, $numRows);
617 }
618
619 /**
620 * @test
621 *
622 * @group mysql
623 */
624 public function sqlNumRowsReturnsFalse()
625 {
626 $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE test='baz'");
627 $numRows = $this->subject->sql_num_rows($res);
628 $this->assertFalse($numRows);
629 }
630
631 /**
632 * Prepares the test table for the fetch* Tests
633 */
634 protected function prepareTableForFetchTests()
635 {
636 $this->assertTrue(
637 $this->subject->sql_query(
638 "ALTER TABLE {$this->testTable} " .
639 'ADD name mediumblob, ' .
640 'ADD deleted int, ' .
641 'ADD street varchar(100), ' .
642 'ADD city varchar(50), ' .
643 'ADD country varchar(100)'
644 )
645 );
646
647 $this->assertTrue(
648 $this->subject->admin_query(
649 "INSERT INTO {$this->testTable} (name,street,city,country,deleted) VALUES " .
650 "('Mr. Smith','Oakland Road','Los Angeles','USA',0)," .
651 "('Ms. Smith','Oakland Road','Los Angeles','USA',0)," .
652 "('Alice im Wunderland','Große Straße','Königreich der Herzen','Wunderland',0)," .
653 "('Agent Smith','Unbekannt','Unbekannt','Matrix',1)"
654 )
655 );
656 }
657
658 /**
659 * @test
660 *
661 * @group mysql
662 */
663 public function sqlFetchAssocReturnsAssocArray()
664 {
665 $this->prepareTableForFetchTests();
666
667 $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} ORDER BY id");
668 $expectedResult = [
669 [
670 'id' => '1',
671 $this->testField => null,
672 $this->anotherTestField => null,
673 'name' => 'Mr. Smith',
674 'deleted' => '0',
675 'street' => 'Oakland Road',
676 'city' => 'Los Angeles',
677 'country' => 'USA',
678 ],
679 [
680 'id' => '2',
681 $this->testField => null,
682 $this->anotherTestField => null,
683 'name' => 'Ms. Smith',
684 'deleted' => '0',
685 'street' => 'Oakland Road',
686 'city' => 'Los Angeles',
687 'country' => 'USA',
688 ],
689 [
690 'id' => '3',
691 $this->testField => null,
692 $this->anotherTestField => null,
693 'name' => 'Alice im Wunderland',
694 'deleted' => '0',
695 'street' => 'Große Straße',
696 'city' => 'Königreich der Herzen',
697 'country' => 'Wunderland',
698 ],
699 [
700 'id' => '4',
701 $this->testField => null,
702 $this->anotherTestField => null,
703 'name' => 'Agent Smith',
704 'deleted' => '1',
705 'street' => 'Unbekannt',
706 'city' => 'Unbekannt',
707 'country' => 'Matrix',
708 ],
709 ];
710 $i = 0;
711 while ($row = $this->subject->sql_fetch_assoc($res)) {
712 $this->assertSame($expectedResult[$i], $row);
713 $i++;
714 }
715 }
716
717 /**
718 * @test
719 *
720 * @group mysql
721 */
722 public function sqlFetchRowReturnsNumericArray()
723 {
724 $this->prepareTableForFetchTests();
725 $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} ORDER BY id");
726 $expectedResult = [
727 ['1', null, null, 'Mr. Smith', '0', 'Oakland Road', 'Los Angeles', 'USA'],
728 ['2', null, null, 'Ms. Smith', '0', 'Oakland Road', 'Los Angeles', 'USA'],
729 ['3', null, null, 'Alice im Wunderland', '0', 'Große Straße', 'Königreich der Herzen', 'Wunderland'],
730 ['4', null, null, 'Agent Smith', '1', 'Unbekannt', 'Unbekannt', 'Matrix'],
731 ];
732 $i = 0;
733 while ($row = $this->subject->sql_fetch_row($res)) {
734 $this->assertSame($expectedResult[$i], $row);
735 $i++;
736 }
737 }
738
739 /**
740 * @test
741 *
742 * @group mysql
743 */
744 public function sqlFreeResultReturnsFalseOnFailure()
745 {
746 $this->assertTrue(
747 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('baz')")
748 );
749 $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE {$this->testField}=baz");
750 $this->assertFalse($this->subject->sql_free_result($res));
751 }
752
753 /**
754 * @test
755 *
756 * @group mysql
757 */
758 public function sqlFreeResultReturnsTrueOnSuccess()
759 {
760 $this->assertTrue(
761 $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('baz')")
762 );
763 $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz'");
764 $this->assertTrue($this->subject->sql_free_result($res));
765 }
766 }