PreparedStatementTest.php

Go to the documentation of this file.

<?php
namespace TYPO3\CMS\Core\Tests\Unit\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\Database\DatabaseConnection;
use TYPO3\CMS\Core\Database\PreparedStatement;

class PreparedStatementTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
{
    protected $databaseStub;

    protected function setUp()
    {
        $this->databaseStub = $this->setUpAndReturnDatabaseStub();
    }

    // Utility functions

    private function setUpAndReturnDatabaseStub()
    {
        $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
            DatabaseConnection::class,
            ['prepare_PREPAREDquery'],
            [],
            '',
            false,
            false
        );

        return $GLOBALS['TYPO3_DB'];
    }

    private function createPreparedStatement($query)
    {
        return new PreparedStatement($query, 'pages');
    }

    // Tests for the utility functions

    public function setUpAndReturnDatabaseStubReturnsMockObjectOfDatabaseConnection()
    {
        $this->assertTrue($this->setUpAndReturnDatabaseStub() instanceof DatabaseConnection);
    }

    public function createPreparedStatementReturnsInstanceOfPreparedStatementClass()
    {
        $this->assertTrue($this->createPreparedStatement('dummy') instanceof PreparedStatement);
    }

    // Tests for \TYPO3\CMS\Core\Database\PreparedStatement

    public function parametersAndQueriesDataProvider()
    {
        return [
            'one named integer parameter' => [
                'SELECT * FROM pages WHERE pid=:pid',
                [':pid' => 1],
                'SELECT * FROM pages WHERE pid=?'
            ],
            'one unnamed integer parameter' => [
                'SELECT * FROM pages WHERE pid=?',
                [1],
                'SELECT * FROM pages WHERE pid=?'
            ],
            'one named integer parameter is replaced multiple times' => [
                'SELECT * FROM pages WHERE pid=:pid OR uid=:pid',
                [':pid' => 1],
                'SELECT * FROM pages WHERE pid=? OR uid=?'
            ],
            'two named integer parameters are replaced' => [
                'SELECT * FROM pages WHERE pid=:pid OR uid=:uid',
                [':pid' => 1, ':uid' => 10],
                'SELECT * FROM pages WHERE pid=? OR uid=?'
            ],
            'two unnamed integer parameters are replaced' => [
                'SELECT * FROM pages WHERE pid=? OR uid=?',
                [1, 1],
                'SELECT * FROM pages WHERE pid=? OR uid=?'
            ],
        ];
    }

    public function parametersAreReplacedByQuestionMarkInQueryByCallingExecute($query, $parameters, $expectedResult)
    {
        $statement = $this->createPreparedStatement($query);
        $this->databaseStub->expects($this->any())
            ->method('prepare_PREPAREDquery')
            ->with($this->equalTo($expectedResult));
        $statement->execute($parameters);
    }

    public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult)
    {
        $statement = $this->createPreparedStatement($query);
        $this->databaseStub->expects($this->any())
            ->method('prepare_PREPAREDquery')
            ->with($this->equalTo($expectedResult));
        $statement->bindValues($parameters);
        $statement->execute();
    }

    public function invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider()
    {
        return [
            'integer passed with param type NULL' => [
                1,
                PreparedStatement::PARAM_NULL,
                1282489834
            ],
            'string passed with param type NULL' => [
                '1',
                PreparedStatement::PARAM_NULL,
                1282489834
            ],
            'bool passed with param type NULL' => [
                true,
                PreparedStatement::PARAM_NULL,
                1282489834
            ],
            'NULL passed with param type INT' => [
                null,
                PreparedStatement::PARAM_INT,
                1281868686
            ],
            'string passed with param type INT' => [
                '1',
                PreparedStatement::PARAM_INT,
                1281868686
            ],
            'bool passed with param type INT' => [
                true,
                PreparedStatement::PARAM_INT,
                1281868686
            ],
            'NULL passed with param type BOOL' => [
                null,
                PreparedStatement::PARAM_BOOL,
                1281868687
            ],
            'string passed with param type BOOL' => [
                '1',
                PreparedStatement::PARAM_BOOL,
                1281868687
            ],
            'integer passed with param type BOOL' => [
                1,
                PreparedStatement::PARAM_BOOL,
                1281868687
            ]
        ];
    }

    public function invalidParameterTypesPassedToBindValueThrowsException($parameter, $type, $exceptionCode)
    {
        $this->expectException(\InvalidArgumentException::class);
        $this->expectExceptionCode($exceptionCode);

        $statement = $this->createPreparedStatement('');
        $statement->bindValue(1, $parameter, $type);
    }

    public function passingInvalidMarkersThrowsExceptionDataProvider()
    {
        return [
            'using other prefix than colon' => [
                'SELECT * FROM pages WHERE pid=#pid',
                ['#pid' => 1]
            ],
            'using non alphanumerical character' => [
                'SELECT * FROM pages WHERE title=:stra≠e',
                [':stra≠e' => 1]
            ],
            'no colon used' => [
                'SELECT * FROM pages WHERE pid=pid',
                ['pid' => 1]
            ],
            'colon at the end' => [
                'SELECT * FROM pages WHERE pid=pid:',
                ['pid:' => 1]
            ],
            'colon without alphanumerical character' => [
                'SELECT * FROM pages WHERE pid=:',
                [':' => 1]
            ]
        ];
    }

    public function passingInvalidMarkersThrowsException($query, $parameters)
    {
        $this->expectException(\InvalidArgumentException::class);
        $this->expectExceptionCode(1395055513);

        $statement = $this->createPreparedStatement($query);
        $statement->bindValues($parameters);
    }
}