SQLParserUtilsTest::dataExpandListParameters() - Code Metrics - Inspection of "Manually merge 2.10.x into 2.11.x" - doctrine/dbal - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 2.11.x (#3897)

by Sergei

created 2020-03-16 20:27 UTC

SQLParserUtilsTest::dataExpandListParameters() B

↳ Parent: SQLParserUtilsTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	313
Code Lines	231

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	231
c	1
b	0
f	0
dl	0
loc	313
rs	8
cc	1
nc	1
nop	0

How to fix Long Method

<?php

namespace Doctrine\Tests\DBAL;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\SQLParserUtils;
use Doctrine\DBAL\SQLParserUtilsException;
use Doctrine\Tests\DbalTestCase;

/**
 * @group DBAL-78
 * @group DDC-1372
 */
class SQLParserUtilsTest extends DbalTestCase
{
    /**
     * @return mixed[][]
     */
    public static function dataGetPlaceholderPositions() : iterable
    {
        return [
            // none
            ['SELECT * FROM Foo', true, []],
            ['SELECT * FROM Foo', false, []],

            // Positionals
            ['SELECT ?', true, [7]],
            ['SELECT * FROM Foo WHERE bar IN (?, ?, ?)', true, [32, 35, 38]],
            ['SELECT ? FROM ?', true, [7, 14]],
            ['SELECT "?" FROM foo', true, []],
            ["SELECT '?' FROM foo", true, []],
            ['SELECT `?` FROM foo', true, []], // Ticket DBAL-552
            ['SELECT [?] FROM foo', true, []],
            ["SELECT 'Doctrine\DBAL?' FROM foo", true, []], // Ticket DBAL-558
            ['SELECT "Doctrine\DBAL?" FROM foo', true, []], // Ticket DBAL-558
            ['SELECT `Doctrine\DBAL?` FROM foo', true, []], // Ticket DBAL-558
            ['SELECT [Doctrine\DBAL?] FROM foo', true, []], // Ticket DBAL-558
            ['SELECT "?" FROM foo WHERE bar = ?', true, [32]],
            ["SELECT '?' FROM foo WHERE bar = ?", true, [32]],
            ['SELECT `?` FROM foo WHERE bar = ?', true, [32]], // Ticket DBAL-552
            ['SELECT [?] FROM foo WHERE bar = ?', true, [32]],
            ['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', true, [56]], // Ticket GH-2295
            ["SELECT 'Doctrine\DBAL?' FROM foo WHERE bar = ?", true, [45]], // Ticket DBAL-558
            ['SELECT "Doctrine\DBAL?" FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
            ['SELECT `Doctrine\DBAL?` FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
            ['SELECT [Doctrine\DBAL?] FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
            ["SELECT * FROM FOO WHERE bar = 'it\\'s a trap? \\\\' OR bar = ?\nAND baz = \"\\\"quote\\\" me on it? \\\\\" OR baz = ?", true, [58, 104]],
            ['SELECT * FROM foo WHERE foo = ? AND bar = ?', true, [1 => 42, 0 => 30]], // explicit keys

            // named
            ['SELECT :foo FROM :bar', false, [7 => 'foo', 17 => 'bar']],
            ['SELECT * FROM Foo WHERE bar IN (:name1, :name2)', false, [32 => 'name1', 40 => 'name2']],
            ['SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', false, [37 => 'name1', 45 => 'name2']],
            ["SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", false, [37 => 'name1', 45 => 'name2']],
            ['SELECT :foo_id', false, [7 => 'foo_id']], // Ticket DBAL-231
            ['SELECT @rank := 1', false, []], // Ticket DBAL-398
            ['SELECT @rank := 1 AS rank, :foo AS foo FROM :bar', false, [27 => 'foo', 44 => 'bar']], // Ticket DBAL-398
            ['SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [30 => 'start_date', 52 => 'start_date']], // Ticket GH-113
            ['SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [46 => 'start_date', 68 => 'start_date']], // Ticket GH-259
            ['SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1', false, [57 => 'param1']], // Ticket DBAL-552
            ['SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1', false, [57 => 'param1']], // Ticket DBAL-552
            ['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])', false, [56 => 'foo']], // Ticket GH-2295
            ['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[:foo])', false, [56 => 'foo']],
            ['SELECT table.field1, ARRAY[\'3\'] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']', false, [73 => 'foo']],
            ['SELECT table.field1, ARRAY[\'3\']::integer[] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']::integer[]', false, [84 => 'foo']],
            ['SELECT table.field1, ARRAY[:foo] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']', false, [27 => 'foo', 74 => 'bar']],
            ['SELECT table.field1, ARRAY[:foo]::integer[] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']::integer[]', false, [27 => 'foo', 85 => 'bar']],
            [
                <<<'SQLDATA'
SELECT * FROM foo WHERE 
bar = ':not_a_param1 ''":not_a_param2"'''
OR bar=:a_param1
OR bar=:a_param2||':not_a_param3'
OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6'
OR bar=''
OR bar=:a_param3
SQLDATA
                ,
                false,
                [
                    74 => 'a_param1',
                    91 => 'a_param2',
                    190 => 'a_param3',
                ],
            ],
            ["SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE '\\\\') AND (data.description LIKE :condition_1 ESCAPE '\\\\') ORDER BY id ASC", false, [121 => 'condition_0', 174 => 'condition_1']],
            ['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE "\\\\") ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
            ['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE \'\\\\\') ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
            ['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE `\\\\`) AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
            ['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE \'\\\\\') AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
            ["SELECT * FROM Foo WHERE (foo.bar LIKE :condition_0 ESCAPE '\') AND (foo.baz = :condition_1) AND (foo.bak LIKE :condition_2 ESCAPE '\')", false, [38 => 'condition_0', 78 => 'condition_1', 110 => 'condition_2']],
        ];
    }

    /**
     * @param int[] $expectedParamPos
     *
     * @dataProvider dataGetPlaceholderPositions
     */
    public function testGetPlaceholderPositions(string $query, bool $isPositional, array $expectedParamPos) : void
    {
        $actualParamPos = SQLParserUtils::getPlaceholderPositions($query, $isPositional);
        self::assertEquals($expectedParamPos, $actualParamPos);
    }

    /**
     * @return mixed[][]
     */
    public static function dataExpandListParameters() : iterable
    {
        return [
            'Positional: Very simple with one needle' => [
                'SELECT * FROM Foo WHERE foo IN (?)',
                [[1, 2, 3]],
                [Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
                [1, 2, 3],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            'Positional: One non-list before d one after list-needle' => [
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?)',
                ['string', [1, 2, 3]],
                [ParameterType::STRING, Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
                ['string', 1, 2, 3],
                [ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            'Positional: One non-list after list-needle' => [
                'SELECT * FROM Foo WHERE bar IN (?) AND baz = ?',
                [[1, 2, 3], 'foo'],
                [Connection::PARAM_INT_ARRAY, ParameterType::STRING],
                'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
                [1, 2, 3, 'foo'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
            ],
            'Positional: One non-list before and one after list-needle' => [
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?',
                [1, [1, 2, 3], 4],
                [ParameterType::INTEGER, Connection::PARAM_INT_ARRAY, ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
                [1, 1, 2, 3, 4],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Positional: Two lists' => [
                'SELECT * FROM Foo WHERE foo IN (?, ?)',
                [[1, 2, 3], [4, 5]],
                [Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
                [1, 2, 3, 4, 5],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Positional: Empty "integer" array (DDC-1978)' => [
                'SELECT * FROM Foo WHERE foo IN (?)',
                [[]],
                [Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (NULL)',
                [],
                [],
            ],
            'Positional: Empty "str" array (DDC-1978)' => [
                'SELECT * FROM Foo WHERE foo IN (?)',
                [[]],
                [Connection::PARAM_STR_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (NULL)',
                [],
                [],
            ],
            'Positional: explicit keys for params and types' => [
                'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
                [1 => 'bar', 2 => 'baz', 0 => 1],
                [2 => ParameterType::STRING, 1 => ParameterType::STRING],
                'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
                [1 => 'bar', 0 => 1, 2 => 'baz'],
                [1 => ParameterType::STRING, 2 => ParameterType::STRING, 0 => null],
            ],
            'Positional: explicit keys for array params and array types' => [
                'SELECT * FROM Foo WHERE foo IN (?) AND bar IN (?) AND baz = ?',
                [1 => ['bar1', 'bar2'], 2 => true, 0 => [1, 2, 3]],
                [2 => ParameterType::BOOLEAN, 1 => Connection::PARAM_STR_ARRAY, 0 => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND bar IN (?, ?) AND baz = ?',
                [1, 2, 3, 'bar1', 'bar2', true],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::STRING,
                    ParameterType::STRING,
                    ParameterType::BOOLEAN,
                ],
            ],
            'Positional starts from 1: One non-list before and one after list-needle' => [
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ? AND foo IN (?)',
                [1 => 1, 2 => [1, 2, 3], 3 => 4, 4 => [5, 6]],
                [
                    1 => ParameterType::INTEGER,
                    2 => Connection::PARAM_INT_ARRAY,
                    3 => ParameterType::INTEGER,
                    4 => Connection::PARAM_INT_ARRAY,
                ],
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ? AND foo IN (?, ?)',
                [1, 1, 2, 3, 4, 5, 6],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Named: Very simple with param int' => [
                'SELECT * FROM Foo WHERE foo = :foo',
                ['foo' => 1],
                ['foo' => ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE foo = ?',
                [1],
                [ParameterType::INTEGER],
            ],
            'Named: Very simple with param int and string' => [
                'SELECT * FROM Foo WHERE foo = :foo AND bar = :bar',
                ['bar' => 'Some String','foo' => 1],
                ['foo' => ParameterType::INTEGER, 'bar' => ParameterType::STRING],
                'SELECT * FROM Foo WHERE foo = ? AND bar = ?',
                [1,'Some String'],
                [ParameterType::INTEGER, ParameterType::STRING],
            ],
            'Named: Very simple with one needle' => [
                'SELECT * FROM Foo WHERE foo IN (:foo)',
                ['foo' => [1, 2, 3]],
                ['foo' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
                [1, 2, 3],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            'Named: One non-list before d one after list-needle' => [
                'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)',
                ['foo' => 'string', 'bar' => [1, 2, 3]],
                ['foo' => ParameterType::STRING, 'bar' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
                ['string', 1, 2, 3],
                [ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            'Named: One non-list after list-needle' => [
                'SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz',
                ['bar' => [1, 2, 3], 'baz' => 'foo'],
                ['bar' => Connection::PARAM_INT_ARRAY, 'baz' => ParameterType::STRING],
                'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
                [1, 2, 3, 'foo'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
            ],
            'Named: One non-list before and one after list-needle' => [
                'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz',
                ['bar' => [1, 2, 3],'foo' => 1, 'baz' => 4],
                [
                    'bar' => Connection::PARAM_INT_ARRAY,
                    'foo' => ParameterType::INTEGER,
                    'baz' => ParameterType::INTEGER,
                ],
                'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
                [1, 1, 2, 3, 4],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Named: Two lists' => [
                'SELECT * FROM Foo WHERE foo IN (:a, :b)',
                ['b' => [4, 5],'a' => [1, 2, 3]],
                ['a' => Connection::PARAM_INT_ARRAY, 'b' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
                [1, 2, 3, 4, 5],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Named: With the same name arg type string' => [
                'SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg',
                ['arg' => 'Some String'],
                ['arg' => ParameterType::STRING],
                'SELECT * FROM Foo WHERE foo <> ? AND bar = ?',
                ['Some String','Some String'],
                [ParameterType::STRING,ParameterType::STRING],
            ],
            'Named: With the same name arg' => [
                'SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)',
                ['arg' => [1, 2, 3]],
                ['arg' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND NOT bar IN (?, ?, ?)',
                [1, 2, 3, 1, 2, 3],
                [
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                    ParameterType::INTEGER,
                ],
            ],
            'Named: Same name, other name in between (DBAL-299)' => [
                'SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)',
                ['foo' => 2,'bar' => 3],
                ['foo' => ParameterType::INTEGER,'bar' => ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE (? = 2) AND (? = 3) AND (? = 2)',
                [2, 3, 2],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            'Named: Empty "integer" array (DDC-1978)' => [
                'SELECT * FROM Foo WHERE foo IN (:foo)',
                ['foo' => []],
                ['foo' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (NULL)',
                [],
                [],
            ],
            'Named: Two empty "str" array (DDC-1978)' => [
                'SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)',
                ['foo' => [], 'bar' => []],
                ['foo' => Connection::PARAM_STR_ARRAY, 'bar' => Connection::PARAM_STR_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (NULL) OR bar IN (NULL)',
                [],
                [],
            ],
            [
                'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar OR baz = :baz',
                ['foo' => [1, 2], 'bar' => 'bar', 'baz' => 'baz'],
                ['foo' => Connection::PARAM_INT_ARRAY, 'baz' => 'string'],
                'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ? OR baz = ?',
                [1, 2, 'bar', 'baz'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING, 'string'],
            ],
            [
                'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
                ['foo' => [1, 2], 'bar' => 'bar'],
                ['foo' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
                [1, 2, 'bar'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
            ],
            'Params/types with colons' => [
                'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
                [':foo' => 'foo', ':bar' => 'bar'],
                [':foo' => ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
                ['foo', 'bar'],
                [ParameterType::INTEGER, ParameterType::STRING],
            ],
            [
                'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
                [':foo' => 'foo', ':bar' => 'bar'],
                [':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
                ['foo', 'bar'],
                [ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            [
                'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
                [':foo' => [1, 2], ':bar' => 'bar'],
                ['foo' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
                [1, 2, 'bar'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
            ],
            [
                'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
                ['foo' => [1, 2], 'bar' => 'bar'],
                [':foo' => Connection::PARAM_INT_ARRAY],
                'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
                [1, 2, 'bar'],
                [ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
            ],
            'Null valued parameters (DBAL-522)' => [
                'INSERT INTO Foo (foo, bar) values (:foo, :bar)',
                ['foo' => 1, 'bar' => null],
                [':foo' => ParameterType::INTEGER, ':bar' => ParameterType::NULL],
                'INSERT INTO Foo (foo, bar) values (?, ?)',
                [1, null],
                [ParameterType::INTEGER, ParameterType::NULL],
            ],
            [
                'INSERT INTO Foo (foo, bar) values (?, ?)',
                [1, null],
                [ParameterType::INTEGER, ParameterType::NULL],
                'INSERT INTO Foo (foo, bar) values (?, ?)',
                [1, null],
                [ParameterType::INTEGER, ParameterType::NULL],
            ],
            'Escaped single quotes SQL- and C-Style (DBAL-1205)' => [
                "SELECT * FROM Foo WHERE foo = :foo||''':not_a_param''\\'' OR bar = ''':not_a_param''\\'':bar",
                [':foo' => 1, ':bar' => 2],
                [':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
                'SELECT * FROM Foo WHERE foo = ?||\'\'\':not_a_param\'\'\\\'\' OR bar = \'\'\':not_a_param\'\'\\\'\'?',
                [1, 2],
                [ParameterType::INTEGER, ParameterType::INTEGER],
            ],
            [
                'SELECT NULL FROM dummy WHERE ? IN (?)',
                ['foo', ['bar', 'baz']],
                [1 => Connection::PARAM_STR_ARRAY],
                'SELECT NULL FROM dummy WHERE ? IN (?, ?)',
                ['foo', 'bar', 'baz'],
                [null, ParameterType::STRING, ParameterType::STRING],
            ],
        ];
    }

    /**
     * @param mixed[] $params
     * @param mixed[] $types
     * @param mixed[] $expectedParams
     * @param mixed[] $expectedTypes
     *
     * @dataProvider dataExpandListParameters
     */
    public function testExpandListParameters(
        string $query,
        array $params,
        array $types,
        string $expectedQuery,
        array $expectedParams,
        array $expectedTypes
    ) : void {
        [$query, $params, $types] = SQLParserUtils::expandListParameters($query, $params, $types);

        self::assertEquals($expectedQuery, $query, 'Query was not rewritten correctly.');
        self::assertEquals($expectedParams, $params, 'Params dont match');
        self::assertEquals($expectedTypes, $types, 'Types dont match');
    }

    /**
     * @return mixed[][]
     */
    public static function dataQueryWithMissingParameters() : iterable
    {
        return [
            [
                'SELECT * FROM foo WHERE bar = :param',
                ['other' => 'val'],
                [],
            ],
            [
                'SELECT * FROM foo WHERE bar = :param',
                [],
                [],
            ],
            [
                'SELECT * FROM foo WHERE bar = :param',
                [],
                ['param' => Connection::PARAM_INT_ARRAY],
            ],
            [
                'SELECT * FROM foo WHERE bar = :param',
                [],
                [':param' => Connection::PARAM_INT_ARRAY],
            ],
            [
                'SELECT * FROM foo WHERE bar = :param',
                [],
                ['bar' => Connection::PARAM_INT_ARRAY],
            ],
            [
                'SELECT * FROM foo WHERE bar = :param',
                ['bar' => 'value'],
                ['bar' => Connection::PARAM_INT_ARRAY],
            ],
        ];
    }

    /**
     * @param mixed[] $params
     * @param mixed[] $types
     *
     * @dataProvider dataQueryWithMissingParameters
     */
    public function testExceptionIsThrownForMissingParam(string $query, array $params, array $types = []) : void
    {
        $this->expectException(SQLParserUtilsException::class);
        $this->expectExceptionMessage('Value for :param not found in params array. Params array key should be "param"');

        SQLParserUtils::expandListParameters($query, $params, $types);
    }
}


1			<?php
2
3			namespace Doctrine\Tests\DBAL;
4
5			use Doctrine\DBAL\Connection;
6			use Doctrine\DBAL\ParameterType;
7			use Doctrine\DBAL\SQLParserUtils;
8			use Doctrine\DBAL\SQLParserUtilsException;
9			use Doctrine\Tests\DbalTestCase;
10
11			/**
12			* @group DBAL-78
13			* @group DDC-1372
14			*/
15			class SQLParserUtilsTest extends DbalTestCase
16			{
17			/**
18			* @return mixed[][]
19			*/
20			public static function dataGetPlaceholderPositions() : iterable
21			{
22			return [
23			// none
24			['SELECT * FROM Foo', true, []],
25			['SELECT * FROM Foo', false, []],
26
27			// Positionals
28			['SELECT ?', true, [7]],
29			['SELECT * FROM Foo WHERE bar IN (?, ?, ?)', true, [32, 35, 38]],
30			['SELECT ? FROM ?', true, [7, 14]],
31			['SELECT "?" FROM foo', true, []],
32			["SELECT '?' FROM foo", true, []],
33			['SELECT `?` FROM foo', true, []], // Ticket DBAL-552
34			['SELECT [?] FROM foo', true, []],
35			["SELECT 'Doctrine\DBAL?' FROM foo", true, []], // Ticket DBAL-558
36			['SELECT "Doctrine\DBAL?" FROM foo', true, []], // Ticket DBAL-558
37			['SELECT `Doctrine\DBAL?` FROM foo', true, []], // Ticket DBAL-558
38			['SELECT [Doctrine\DBAL?] FROM foo', true, []], // Ticket DBAL-558
39			['SELECT "?" FROM foo WHERE bar = ?', true, [32]],
40			["SELECT '?' FROM foo WHERE bar = ?", true, [32]],
41			['SELECT `?` FROM foo WHERE bar = ?', true, [32]], // Ticket DBAL-552
42			['SELECT [?] FROM foo WHERE bar = ?', true, [32]],
43			['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', true, [56]], // Ticket GH-2295
44			["SELECT 'Doctrine\DBAL?' FROM foo WHERE bar = ?", true, [45]], // Ticket DBAL-558
45			['SELECT "Doctrine\DBAL?" FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
46			['SELECT `Doctrine\DBAL?` FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
47			['SELECT [Doctrine\DBAL?] FROM foo WHERE bar = ?', true, [45]], // Ticket DBAL-558
48			["SELECT * FROM FOO WHERE bar = 'it\\'s a trap? \\\\' OR bar = ?\nAND baz = \"\\\"quote\\\" me on it? \\\\\" OR baz = ?", true, [58, 104]],
49			['SELECT * FROM foo WHERE foo = ? AND bar = ?', true, [1 => 42, 0 => 30]], // explicit keys
50
51			// named
52			['SELECT :foo FROM :bar', false, [7 => 'foo', 17 => 'bar']],
53			['SELECT * FROM Foo WHERE bar IN (:name1, :name2)', false, [32 => 'name1', 40 => 'name2']],
54			['SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', false, [37 => 'name1', 45 => 'name2']],
55			["SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", false, [37 => 'name1', 45 => 'name2']],
56			['SELECT :foo_id', false, [7 => 'foo_id']], // Ticket DBAL-231
57			['SELECT @rank := 1', false, []], // Ticket DBAL-398
58			['SELECT @rank := 1 AS rank, :foo AS foo FROM :bar', false, [27 => 'foo', 44 => 'bar']], // Ticket DBAL-398
59			['SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [30 => 'start_date', 52 => 'start_date']], // Ticket GH-113
60			['SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date', false, [46 => 'start_date', 68 => 'start_date']], // Ticket GH-259
61			['SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1', false, [57 => 'param1']], // Ticket DBAL-552
62			['SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1', false, [57 => 'param1']], // Ticket DBAL-552
63			['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])', false, [56 => 'foo']], // Ticket GH-2295
64			['SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[:foo])', false, [56 => 'foo']],
65			['SELECT table.field1, ARRAY[\'3\'] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']', false, [73 => 'foo']],
66			['SELECT table.field1, ARRAY[\'3\']::integer[] FROM schema.table table WHERE table.f1 = :foo AND ARRAY[\'3\']::integer[]', false, [84 => 'foo']],
67			['SELECT table.field1, ARRAY[:foo] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']', false, [27 => 'foo', 74 => 'bar']],
68			['SELECT table.field1, ARRAY[:foo]::integer[] FROM schema.table table WHERE table.f1 = :bar AND ARRAY[\'3\']::integer[]', false, [27 => 'foo', 85 => 'bar']],
69			[
70			<<<'SQLDATA'
71			SELECT * FROM foo WHERE
72			bar = ':not_a_param1 ''":not_a_param2"'''
73			OR bar=:a_param1
74			OR bar=:a_param2\|\|':not_a_param3'
75			OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6'
76			OR bar=''
77			OR bar=:a_param3
78			SQLDATA
79			,
80			false,
81			[
82			74 => 'a_param1',
83			91 => 'a_param2',
84			190 => 'a_param3',
85			],
86			],
87			["SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE '\\\\') AND (data.description LIKE :condition_1 ESCAPE '\\\\') ORDER BY id ASC", false, [121 => 'condition_0', 174 => 'condition_1']],
88			['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE "\\\\") ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
89			['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE \'\\\\\') ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
90			['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE `\\\\`) AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
91			['SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE \'\\\\\') AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', false, [121 => 'condition_0', 174 => 'condition_1']],
92			["SELECT * FROM Foo WHERE (foo.bar LIKE :condition_0 ESCAPE '\') AND (foo.baz = :condition_1) AND (foo.bak LIKE :condition_2 ESCAPE '\')", false, [38 => 'condition_0', 78 => 'condition_1', 110 => 'condition_2']],
93			];
94			}
95
96			/**
97			* @param int[] $expectedParamPos
98			*
99			* @dataProvider dataGetPlaceholderPositions
100			*/
101			public function testGetPlaceholderPositions(string $query, bool $isPositional, array $expectedParamPos) : void
102			{
103			$actualParamPos = SQLParserUtils::getPlaceholderPositions($query, $isPositional);
104			self::assertEquals($expectedParamPos, $actualParamPos);
105			}
106
107			/**
108			* @return mixed[][]
109			*/
110			public static function dataExpandListParameters() : iterable
111			{
112			return [
113			'Positional: Very simple with one needle' => [
114			'SELECT * FROM Foo WHERE foo IN (?)',
115			[[1, 2, 3]],
116			[Connection::PARAM_INT_ARRAY],
117			'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
118			[1, 2, 3],
119			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
120			],
121			'Positional: One non-list before d one after list-needle' => [
122			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?)',
123			['string', [1, 2, 3]],
124			[ParameterType::STRING, Connection::PARAM_INT_ARRAY],
125			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
126			['string', 1, 2, 3],
127			[ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
128			],
129			'Positional: One non-list after list-needle' => [
130			'SELECT * FROM Foo WHERE bar IN (?) AND baz = ?',
131			[[1, 2, 3], 'foo'],
132			[Connection::PARAM_INT_ARRAY, ParameterType::STRING],
133			'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
134			[1, 2, 3, 'foo'],
135			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
136			],
137			'Positional: One non-list before and one after list-needle' => [
138			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?',
139			[1, [1, 2, 3], 4],
140			[ParameterType::INTEGER, Connection::PARAM_INT_ARRAY, ParameterType::INTEGER],
141			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
142			[1, 1, 2, 3, 4],
143			[
144			ParameterType::INTEGER,
145			ParameterType::INTEGER,
146			ParameterType::INTEGER,
147			ParameterType::INTEGER,
148			ParameterType::INTEGER,
149			],
150			],
151			'Positional: Two lists' => [
152			'SELECT * FROM Foo WHERE foo IN (?, ?)',
153			[[1, 2, 3], [4, 5]],
154			[Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY],
155			'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
156			[1, 2, 3, 4, 5],
157			[
158			ParameterType::INTEGER,
159			ParameterType::INTEGER,
160			ParameterType::INTEGER,
161			ParameterType::INTEGER,
162			ParameterType::INTEGER,
163			],
164			],
165			'Positional: Empty "integer" array (DDC-1978)' => [
166			'SELECT * FROM Foo WHERE foo IN (?)',
167			[[]],
168			[Connection::PARAM_INT_ARRAY],
169			'SELECT * FROM Foo WHERE foo IN (NULL)',
170			[],
171			[],
172			],
173			'Positional: Empty "str" array (DDC-1978)' => [
174			'SELECT * FROM Foo WHERE foo IN (?)',
175			[[]],
176			[Connection::PARAM_STR_ARRAY],
177			'SELECT * FROM Foo WHERE foo IN (NULL)',
178			[],
179			[],
180			],
181			'Positional: explicit keys for params and types' => [
182			'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
183			[1 => 'bar', 2 => 'baz', 0 => 1],
184			[2 => ParameterType::STRING, 1 => ParameterType::STRING],
185			'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
186			[1 => 'bar', 0 => 1, 2 => 'baz'],
187			[1 => ParameterType::STRING, 2 => ParameterType::STRING, 0 => null],
188			],
189			'Positional: explicit keys for array params and array types' => [
190			'SELECT * FROM Foo WHERE foo IN (?) AND bar IN (?) AND baz = ?',
191			[1 => ['bar1', 'bar2'], 2 => true, 0 => [1, 2, 3]],
192			[2 => ParameterType::BOOLEAN, 1 => Connection::PARAM_STR_ARRAY, 0 => Connection::PARAM_INT_ARRAY],
193			'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND bar IN (?, ?) AND baz = ?',
194			[1, 2, 3, 'bar1', 'bar2', true],
195			[
196			ParameterType::INTEGER,
197			ParameterType::INTEGER,
198			ParameterType::INTEGER,
199			ParameterType::STRING,
200			ParameterType::STRING,
201			ParameterType::BOOLEAN,
202			],
203			],
204			'Positional starts from 1: One non-list before and one after list-needle' => [
205			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ? AND foo IN (?)',
206			[1 => 1, 2 => [1, 2, 3], 3 => 4, 4 => [5, 6]],
207			[
208			1 => ParameterType::INTEGER,
209			2 => Connection::PARAM_INT_ARRAY,
210			3 => ParameterType::INTEGER,
211			4 => Connection::PARAM_INT_ARRAY,
212			],
213			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ? AND foo IN (?, ?)',
214			[1, 1, 2, 3, 4, 5, 6],
215			[
216			ParameterType::INTEGER,
217			ParameterType::INTEGER,
218			ParameterType::INTEGER,
219			ParameterType::INTEGER,
220			ParameterType::INTEGER,
221			ParameterType::INTEGER,
222			ParameterType::INTEGER,
223			],
224			],
225			'Named: Very simple with param int' => [
226			'SELECT * FROM Foo WHERE foo = :foo',
227			['foo' => 1],
228			['foo' => ParameterType::INTEGER],
229			'SELECT * FROM Foo WHERE foo = ?',
230			[1],
231			[ParameterType::INTEGER],
232			],
233			'Named: Very simple with param int and string' => [
234			'SELECT * FROM Foo WHERE foo = :foo AND bar = :bar',
235			['bar' => 'Some String','foo' => 1],
236			['foo' => ParameterType::INTEGER, 'bar' => ParameterType::STRING],
237			'SELECT * FROM Foo WHERE foo = ? AND bar = ?',
238			[1,'Some String'],
239			[ParameterType::INTEGER, ParameterType::STRING],
240			],
241			'Named: Very simple with one needle' => [
242			'SELECT * FROM Foo WHERE foo IN (:foo)',
243			['foo' => [1, 2, 3]],
244			['foo' => Connection::PARAM_INT_ARRAY],
245			'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
246			[1, 2, 3],
247			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
248			],
249			'Named: One non-list before d one after list-needle' => [
250			'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)',
251			['foo' => 'string', 'bar' => [1, 2, 3]],
252			['foo' => ParameterType::STRING, 'bar' => Connection::PARAM_INT_ARRAY],
253			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
254			['string', 1, 2, 3],
255			[ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
256			],
257			'Named: One non-list after list-needle' => [
258			'SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz',
259			['bar' => [1, 2, 3], 'baz' => 'foo'],
260			['bar' => Connection::PARAM_INT_ARRAY, 'baz' => ParameterType::STRING],
261			'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
262			[1, 2, 3, 'foo'],
263			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
264			],
265			'Named: One non-list before and one after list-needle' => [
266			'SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz',
267			['bar' => [1, 2, 3],'foo' => 1, 'baz' => 4],
268			[
269			'bar' => Connection::PARAM_INT_ARRAY,
270			'foo' => ParameterType::INTEGER,
271			'baz' => ParameterType::INTEGER,
272			],
273			'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
274			[1, 1, 2, 3, 4],
275			[
276			ParameterType::INTEGER,
277			ParameterType::INTEGER,
278			ParameterType::INTEGER,
279			ParameterType::INTEGER,
280			ParameterType::INTEGER,
281			],
282			],
283			'Named: Two lists' => [
284			'SELECT * FROM Foo WHERE foo IN (:a, :b)',
285			['b' => [4, 5],'a' => [1, 2, 3]],
286			['a' => Connection::PARAM_INT_ARRAY, 'b' => Connection::PARAM_INT_ARRAY],
287			'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
288			[1, 2, 3, 4, 5],
289			[
290			ParameterType::INTEGER,
291			ParameterType::INTEGER,
292			ParameterType::INTEGER,
293			ParameterType::INTEGER,
294			ParameterType::INTEGER,
295			],
296			],
297			'Named: With the same name arg type string' => [
298			'SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg',
299			['arg' => 'Some String'],
300			['arg' => ParameterType::STRING],
301			'SELECT * FROM Foo WHERE foo <> ? AND bar = ?',
302			['Some String','Some String'],
303			[ParameterType::STRING,ParameterType::STRING],
304			],
305			'Named: With the same name arg' => [
306			'SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)',
307			['arg' => [1, 2, 3]],
308			['arg' => Connection::PARAM_INT_ARRAY],
309			'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND NOT bar IN (?, ?, ?)',
310			[1, 2, 3, 1, 2, 3],
311			[
312			ParameterType::INTEGER,
313			ParameterType::INTEGER,
314			ParameterType::INTEGER,
315			ParameterType::INTEGER,
316			ParameterType::INTEGER,
317			ParameterType::INTEGER,
318			],
319			],
320			'Named: Same name, other name in between (DBAL-299)' => [
321			'SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)',
322			['foo' => 2,'bar' => 3],
323			['foo' => ParameterType::INTEGER,'bar' => ParameterType::INTEGER],
324			'SELECT * FROM Foo WHERE (? = 2) AND (? = 3) AND (? = 2)',
325			[2, 3, 2],
326			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER],
327			],
328			'Named: Empty "integer" array (DDC-1978)' => [
329			'SELECT * FROM Foo WHERE foo IN (:foo)',
330			['foo' => []],
331			['foo' => Connection::PARAM_INT_ARRAY],
332			'SELECT * FROM Foo WHERE foo IN (NULL)',
333			[],
334			[],
335			],
336			'Named: Two empty "str" array (DDC-1978)' => [
337			'SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)',
338			['foo' => [], 'bar' => []],
339			['foo' => Connection::PARAM_STR_ARRAY, 'bar' => Connection::PARAM_STR_ARRAY],
340			'SELECT * FROM Foo WHERE foo IN (NULL) OR bar IN (NULL)',
341			[],
342			[],
343			],
344			[
345			'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar OR baz = :baz',
346			['foo' => [1, 2], 'bar' => 'bar', 'baz' => 'baz'],
347			['foo' => Connection::PARAM_INT_ARRAY, 'baz' => 'string'],
348			'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ? OR baz = ?',
349			[1, 2, 'bar', 'baz'],
350			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING, 'string'],
351			],
352			[
353			'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
354			['foo' => [1, 2], 'bar' => 'bar'],
355			['foo' => Connection::PARAM_INT_ARRAY],
356			'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
357			[1, 2, 'bar'],
358			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
359			],
360			'Params/types with colons' => [
361			'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
362			[':foo' => 'foo', ':bar' => 'bar'],
363			[':foo' => ParameterType::INTEGER],
364			'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
365			['foo', 'bar'],
366			[ParameterType::INTEGER, ParameterType::STRING],
367			],
368			[
369			'SELECT * FROM Foo WHERE foo = :foo OR bar = :bar',
370			[':foo' => 'foo', ':bar' => 'bar'],
371			[':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
372			'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
373			['foo', 'bar'],
374			[ParameterType::INTEGER, ParameterType::INTEGER],
375			],
376			[
377			'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
378			[':foo' => [1, 2], ':bar' => 'bar'],
379			['foo' => Connection::PARAM_INT_ARRAY],
380			'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
381			[1, 2, 'bar'],
382			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
383			],
384			[
385			'SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar',
386			['foo' => [1, 2], 'bar' => 'bar'],
387			[':foo' => Connection::PARAM_INT_ARRAY],
388			'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
389			[1, 2, 'bar'],
390			[ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING],
391			],
392			'Null valued parameters (DBAL-522)' => [
393			'INSERT INTO Foo (foo, bar) values (:foo, :bar)',
394			['foo' => 1, 'bar' => null],
395			[':foo' => ParameterType::INTEGER, ':bar' => ParameterType::NULL],
396			'INSERT INTO Foo (foo, bar) values (?, ?)',
397			[1, null],
398			[ParameterType::INTEGER, ParameterType::NULL],
399			],
400			[
401			'INSERT INTO Foo (foo, bar) values (?, ?)',
402			[1, null],
403			[ParameterType::INTEGER, ParameterType::NULL],
404			'INSERT INTO Foo (foo, bar) values (?, ?)',
405			[1, null],
406			[ParameterType::INTEGER, ParameterType::NULL],
407			],
408			'Escaped single quotes SQL- and C-Style (DBAL-1205)' => [
409			"SELECT * FROM Foo WHERE foo = :foo\|\|''':not_a_param''\\'' OR bar = ''':not_a_param''\\'':bar",
410			[':foo' => 1, ':bar' => 2],
411			[':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER],
412			'SELECT * FROM Foo WHERE foo = ?\|\|\'\'\':not_a_param\'\'\\\'\' OR bar = \'\'\':not_a_param\'\'\\\'\'?',
413			[1, 2],
414			[ParameterType::INTEGER, ParameterType::INTEGER],
415			],
416			[
417			'SELECT NULL FROM dummy WHERE ? IN (?)',
418			['foo', ['bar', 'baz']],
419			[1 => Connection::PARAM_STR_ARRAY],
420			'SELECT NULL FROM dummy WHERE ? IN (?, ?)',
421			['foo', 'bar', 'baz'],
422			[null, ParameterType::STRING, ParameterType::STRING],
423			],
424			];
425			}
426
427			/**
428			* @param mixed[] $params
429			* @param mixed[] $types
430			* @param mixed[] $expectedParams
431			* @param mixed[] $expectedTypes
432			*
433			* @dataProvider dataExpandListParameters
434			*/
435			public function testExpandListParameters(
436			string $query,
437			array $params,
438			array $types,
439			string $expectedQuery,
440			array $expectedParams,
441			array $expectedTypes
442			) : void {
443			[$query, $params, $types] = SQLParserUtils::expandListParameters($query, $params, $types);
444
445			self::assertEquals($expectedQuery, $query, 'Query was not rewritten correctly.');
446			self::assertEquals($expectedParams, $params, 'Params dont match');
447			self::assertEquals($expectedTypes, $types, 'Types dont match');
448			}
449
450			/**
451			* @return mixed[][]
452			*/
453			public static function dataQueryWithMissingParameters() : iterable
454			{
455			return [
456			[
457			'SELECT * FROM foo WHERE bar = :param',
458			['other' => 'val'],
459			[],
460			],
461			[
462			'SELECT * FROM foo WHERE bar = :param',
463			[],
464			[],
465			],
466			[
467			'SELECT * FROM foo WHERE bar = :param',
468			[],
469			['param' => Connection::PARAM_INT_ARRAY],
470			],
471			[
472			'SELECT * FROM foo WHERE bar = :param',
473			[],
474			[':param' => Connection::PARAM_INT_ARRAY],
475			],
476			[
477			'SELECT * FROM foo WHERE bar = :param',
478			[],
479			['bar' => Connection::PARAM_INT_ARRAY],
480			],
481			[
482			'SELECT * FROM foo WHERE bar = :param',
483			['bar' => 'value'],
484			['bar' => Connection::PARAM_INT_ARRAY],
485			],
486			];
487			}
488
489			/**
490			* @param mixed[] $params
491			* @param mixed[] $types
492			*
493			* @dataProvider dataQueryWithMissingParameters
494			*/
495			public function testExceptionIsThrownForMissingParam(string $query, array $params, array $types = []) : void
496			{
497			$this->expectException(SQLParserUtilsException::class);
498			$this->expectExceptionMessage('Value for :param not found in params array. Params array key should be "param"');
499
500			SQLParserUtils::expandListParameters($query, $params, $types);
501			}
502			}
503

doctrine / dbal

Pull Request — 2.11.x (#3897)

SQLParserUtilsTest::dataExpandListParameters() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like