DatabaseConnectionTest.php

Go to the documentation of this file.

<?php
namespace TYPO3\CMS\Core\Tests\Functional\Database;

/*
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */
use TYPO3\CMS\Core\Core\Bootstrap;
use TYPO3\CMS\Core\Database\DatabaseConnection;
use TYPO3\TestingFramework\Core\AccessibleObjectInterface;

class DatabaseConnectionTest extends \TYPO3\TestingFramework\Core\Functional\FunctionalTestCase
{
    protected $subject = null;

    protected $testTable = 'test_database_connection';

    protected $testField = 'test_field';

    protected $anotherTestField = 'another_test_field';

    protected function setUp()
    {
        parent::setUp();
        Bootstrap::getInstance()->initializeTypo3DbGlobal();
        $this->subject = $GLOBALS['TYPO3_DB'];
        $this->subject->sql_query(
            "CREATE TABLE {$this->testTable} (" .
            '   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,' .
            "   {$this->testField} MEDIUMBLOB," .
            "   {$this->anotherTestField} MEDIUMBLOB," .
            '   PRIMARY KEY (id)' .
            ') ENGINE=MyISAM DEFAULT CHARSET=utf8;'
        );
    }

    protected function tearDown()
    {
        $this->subject->sql_query("DROP TABLE {$this->testTable};");
        unset($this->subject);
    }

    public function selectDbReturnsTrue()
    {
        $this->assertTrue($this->subject->sql_select_db());
    }

    public function selectDbReturnsFalse()
    {
        $this->expectException(\RuntimeException::class);
        $this->expectExceptionCode(1270853883);
        $this->expectExceptionMessage('TYPO3 Fatal Error: Cannot connect to the current database, "Foo"!');

        $this->subject->setDatabaseName('Foo');
        $this->assertFalse($this->subject->sql_select_db());
    }

    public function sqlAffectedRowsReturnsCorrectAmountOfRows()
    {
        $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
        $this->assertEquals(1, $this->subject->sql_affected_rows());
    }

    public function sqlInsertIdReturnsCorrectId()
    {
        $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
        $this->assertEquals(1, $this->subject->sql_insert_id());
    }

    public function noSqlError()
    {
        $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
        $this->assertEquals('', $this->subject->sql_error());
    }

    public function sqlErrorWhenInsertIntoInexistentField()
    {
        $this->subject->exec_INSERTquery($this->testTable, ['test' => 'test']);
        $this->assertEquals('Unknown column \'test\' in \'field list\'', $this->subject->sql_error());
    }

    public function noSqlErrorCode()
    {
        $this->subject->exec_INSERTquery($this->testTable, [$this->testField => 'test']);
        $this->assertEquals(0, $this->subject->sql_errno());
    }

    public function sqlErrorNoWhenInsertIntoInexistentField()
    {
        $this->subject->exec_INSERTquery($this->testTable, ['test' => 'test']);
        $this->assertEquals(1054, $this->subject->sql_errno());
    }

    public function sqlPconnectReturnsInstanceOfMySqli()
    {
        $this->assertInstanceOf('mysqli', $this->subject->sql_pconnect());
    }

    public function connectDbThrowsExeptionsWhenNoDatabaseIsGiven()
    {
        $this->expectException(\RuntimeException::class);
        $this->expectExceptionCode(1270853882);

        $subject = $this->getAccessibleMock(DatabaseConnection::class, ['dummy'], [], '', false);
        $subject->connectDB();
    }

    public function connectDbConnectsToDatabaseWithoutErrors()
    {
        $this->subject->connectDB();
        $this->assertTrue($this->subject->isConnected());
    }

    public function disconnectIfConnectedDisconnects()
    {
        $this->assertTrue($this->subject->isConnected());
        $env = getenv('typo3DatabaseHost');
        $databaseHost = is_string($env) ? trim($env) : '127.0.0.1';
        $this->subject->setDatabaseHost($databaseHost);
        $this->assertFalse($this->subject->isConnected());
    }

    public function fullQuoteStrReturnsQuotedStringDataProvider()
    {
        return [
            'NULL string with ReturnNull is allowed' => [
                [null, true],
                'NULL',
            ],
            'NULL string with ReturnNull is false' => [
                [null, false],
                "''",
            ],
            'Normal string' => [
                ['Foo', false],
                "'Foo'",
            ],
            'Single quoted string' => [
                ["'Hello'", false],
                "'\\'Hello\\''",
            ],
            'Double quoted string' => [
                ['"Hello"', false],
                "'\\\"Hello\\\"'",
            ],
            'String with internal single tick' => [
                ['It\'s me', false],
                "'It\\'s me'",
            ],
            'Slashes' => [
                ['/var/log/syslog.log', false],
                "'/var/log/syslog.log'",
            ],
            'Backslashes' => [
                ['\\var\\log\\syslog.log', false],
                "'\\\\var\\\\log\\\\syslog.log'",
            ],
        ];
    }

    public function fullQuoteStrReturnsQuotedString($values, $expectedResult)
    {
        $quotedStr = $this->subject->fullQuoteStr($values[0], 'tt_content', $values[1]);
        $this->assertEquals($expectedResult, $quotedStr);
    }

    public function fullQuoteArrayQuotesArrayDataProvider()
    {
        return [
            'NULL array with ReturnNull is allowed' => [
                [
                    [null, null],
                    false,
                    true,
                ],
                ['NULL', 'NULL'],
            ],

            'NULL array with ReturnNull is false' => [
                [
                    [null, null],
                    false,
                    false,
                ],
                ["''", "''"],
            ],

            'Strings in array' => [
                [
                    ['Foo', 'Bar'],
                    false,
                    false,
                ],
                ["'Foo'", "'Bar'"],
            ],

            'Single quotes in array' => [
                [
                    ["'Hello'"],
                    false,
                    false,
                ],
                ["'\\'Hello\\''"],
            ],

            'Double quotes in array' => [
                [
                    ['"Hello"'],
                    false,
                    false,
                ],
                ["'\\\"Hello\\\"'"],
            ],

            'Slashes in array' => [
                [
                    ['/var/log/syslog.log'],
                    false,
                    false,
                ],
                ["'/var/log/syslog.log'"],
            ],

            'Backslashes in array' => [
                [
                    ['\var\log\syslog.log'],
                    false,
                    false,
                ],
                ["'\\\\var\\\\log\\\\syslog.log'"],
            ],

            'Strings with internal single tick' => [
                [
                    ['Hey!', 'It\'s me'],
                    false,
                    false,
                ],
                ["'Hey!'", "'It\\'s me'"],
            ],

            'no quotes strings from array' => [
                [
                    [
                        'First' => 'Hey!',
                        'Second' => 'It\'s me',
                        'Third' => 'O\' Reily',
                    ],
                    ['First', 'Third'],
                    false,
                ],
                ['First' => 'Hey!', 'Second' => "'It\\'s me'", 'Third' => "O' Reily"],
            ],

            'no quotes strings from string' => [
                [
                    [
                        'First' => 'Hey!',
                        'Second' => 'It\'s me',
                        'Third' => 'O\' Reily',
                    ],
                    'First,Third',
                    false,
                ],
                ['First' => 'Hey!', 'Second' => "'It\\'s me'", 'Third' => "O' Reily"],
            ],
        ];
    }

    public function fullQuoteArrayQuotesArray($values, $expectedResult)
    {
        $quotedResult = $this->subject->fullQuoteArray($values[0], $this->testTable, $values[1], $values[2]);
        $this->assertSame($expectedResult, $quotedResult);
    }

    public function quoteStrQuotesCorrectlyDataProvider()
    {
        return [
            'Double Quotes' => [
                '"Hello"',
                '\\"Hello\\"'
            ],
            'Single Quotes' => [
                '\'Hello\'',
                "\\'Hello\\'"
            ],
            'Slashes' => [
                '/var/log/syslog.log',
                '/var/log/syslog.log'
            ],
            'Literal Backslashes' => [
                '\\var\\log\\syslog.log',
                '\\\\var\\\\log\\\\syslog.log'
            ],
            'Fallback Literal Backslashes' => [
                '\var\log\syslog.log',
                '\\\\var\\\\log\\\\syslog.log'
            ],
        ];
    }

    public function quoteStrQuotesDoubleQuotesCorrectly($string, $expectedResult)
    {
        $quotedString = $this->subject->quoteStr($string, $this->testTable);
        $this->assertSame($expectedResult, $quotedString);
    }

    public function adminQueryReturnsTrueForInsertQuery()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
        );
    }

    public function adminQueryReturnsTrueForUpdateQuery()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
        );
        $id = $this->subject->sql_insert_id();
        $this->assertTrue(
            $this->subject->admin_query("UPDATE {$this->testTable} SET {$this->testField}='bar' WHERE id={$id}")
        );
    }

    public function adminQueryReturnsTrueForDeleteQuery()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
        );
        $id = $this->subject->sql_insert_id();
        $this->assertTrue($this->subject->admin_query("DELETE FROM {$this->testTable} WHERE id={$id}"));
    }

    public function adminQueryReturnsResultForSelectQuery()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo')")
        );
        $res = $this->subject->admin_query("SELECT {$this->testField} FROM {$this->testTable}");
        $this->assertInstanceOf('mysqli_result', $res);
        $result = $res->fetch_assoc();
        $this->assertEquals('foo', $result[$this->testField]);
    }

    public function adminGetCharsetsReturnsArrayWithCharsets()
    {
        $columnsRes = $this->subject->admin_query('SHOW CHARACTER SET');
        $result = $this->subject->admin_get_charsets();
        $this->assertEquals(count($result), $columnsRes->num_rows);

        while (($row = $columnsRes->fetch_assoc())) {
            $this->assertArrayHasKey($row['Charset'], $result);
        }
    }

    public function adminGetKeysReturnIndexKeysOfTable()
    {
        $result = $this->subject->admin_get_keys($this->testTable);
        $this->assertEquals('id', $result[0]['Column_name']);
    }

    public function adminGetFieldsReturnFieldInformationsForTable()
    {
        $result = $this->subject->admin_get_fields($this->testTable);
        $this->assertArrayHasKey('id', $result);
        $this->assertArrayHasKey($this->testField, $result);
    }

    public function adminGetTablesReturnAllTablesFromDatabase()
    {
        $result = $this->subject->admin_get_tables();
        $this->assertArrayHasKey('tt_content', $result);
        $this->assertArrayHasKey('pages', $result);
    }

    public function adminGetDbsReturnsAllDatabases()
    {
        $databases = $this->subject->admin_query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
        $result = $this->subject->admin_get_dbs();
        $this->assertSame(count($result), $databases->num_rows);

        $i = 0;
        while ($database = $databases->fetch_assoc()) {
            $this->assertSame($database['SCHEMA_NAME'], $result[$i]);
            $i++;
        }
    }

    public function sqlNumRowsReturnsCorrectAmountOfRowsProvider()
    {
        $sql1 = "SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz'";
        $sql2 = "SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz' OR {$this->testField}='bar'";
        $sql3 = "SELECT * FROM {$this->testTable} WHERE {$this->testField} IN ('baz', 'bar', 'foo')";

        return [
            'One result' => [$sql1, 1],
            'Two results' => [$sql2, 2],
            'Three results' => [$sql3, 3],
        ];
    }

    public function sqlNumRowsReturnsCorrectAmountOfRows($sql, $expectedResult)
    {
        $this->assertTrue(
            $this->subject->admin_query(
                "INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('foo'), ('bar'), ('baz')"
            )
        );

        $res = $this->subject->admin_query($sql);
        $numRows = $this->subject->sql_num_rows($res);
        $this->assertSame($expectedResult, $numRows);
    }

    public function sqlNumRowsReturnsFalse()
    {
        $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE test='baz'");
        $numRows = $this->subject->sql_num_rows($res);
        $this->assertFalse($numRows);
    }

    protected function prepareTableForFetchTests()
    {
        $this->assertTrue(
            $this->subject->sql_query(
                "ALTER TABLE {$this->testTable} " .
                'ADD name mediumblob, ' .
                'ADD deleted int, ' .
                'ADD street varchar(100), ' .
                'ADD city varchar(50), ' .
                'ADD country varchar(100)'
            )
        );

        $this->assertTrue(
            $this->subject->admin_query(
                "INSERT INTO {$this->testTable} (name,street,city,country,deleted) VALUES " .
                "('Mr. Smith','Oakland Road','Los Angeles','USA',0)," .
                "('Ms. Smith','Oakland Road','Los Angeles','USA',0)," .
                "('Alice im Wunderland','Große Straße','Königreich der Herzen','Wunderland',0)," .
                "('Agent Smith','Unbekannt','Unbekannt','Matrix',1)"
            )
        );
    }

    public function sqlFetchAssocReturnsAssocArray()
    {
        $this->prepareTableForFetchTests();

        $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} ORDER BY id");
        $expectedResult = [
            [
                'id' => '1',
                $this->testField => null,
                $this->anotherTestField => null,
                'name' => 'Mr. Smith',
                'deleted' => '0',
                'street' => 'Oakland Road',
                'city' => 'Los Angeles',
                'country' => 'USA',
            ],
            [
                'id' => '2',
                $this->testField => null,
                $this->anotherTestField => null,
                'name' => 'Ms. Smith',
                'deleted' => '0',
                'street' => 'Oakland Road',
                'city' => 'Los Angeles',
                'country' => 'USA',
            ],
            [
                'id' => '3',
                $this->testField => null,
                $this->anotherTestField => null,
                'name' => 'Alice im Wunderland',
                'deleted' => '0',
                'street' => 'Große Straße',
                'city' => 'Königreich der Herzen',
                'country' => 'Wunderland',
            ],
            [
                'id' => '4',
                $this->testField => null,
                $this->anotherTestField => null,
                'name' => 'Agent Smith',
                'deleted' => '1',
                'street' => 'Unbekannt',
                'city' => 'Unbekannt',
                'country' => 'Matrix',
            ],
        ];
        $i = 0;
        while ($row = $this->subject->sql_fetch_assoc($res)) {
            $this->assertSame($expectedResult[$i], $row);
            $i++;
        }
    }

    public function sqlFetchRowReturnsNumericArray()
    {
        $this->prepareTableForFetchTests();
        $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} ORDER BY id");
        $expectedResult = [
            ['1', null, null, 'Mr. Smith', '0', 'Oakland Road', 'Los Angeles', 'USA'],
            ['2', null, null, 'Ms. Smith', '0', 'Oakland Road', 'Los Angeles', 'USA'],
            ['3', null, null, 'Alice im Wunderland', '0', 'Große Straße', 'Königreich der Herzen', 'Wunderland'],
            ['4', null, null, 'Agent Smith', '1', 'Unbekannt', 'Unbekannt', 'Matrix'],
        ];
        $i = 0;
        while ($row = $this->subject->sql_fetch_row($res)) {
            $this->assertSame($expectedResult[$i], $row);
            $i++;
        }
    }

    public function sqlFreeResultReturnsFalseOnFailure()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('baz')")
        );
        $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE {$this->testField}=baz");
        $this->assertFalse($this->subject->sql_free_result($res));
    }

    public function sqlFreeResultReturnsTrueOnSuccess()
    {
        $this->assertTrue(
            $this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('baz')")
        );
        $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} WHERE {$this->testField}='baz'");
        $this->assertTrue($this->subject->sql_free_result($res));
    }
}