<?php
namespace Doctrine\Tests\DBAL;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\SQLParserUtils;
/**
* @group DBAL-78
* @group DDC-1372
*/
class SQLParserUtilsTest extends \Doctrine\Tests\DbalTestCase
{
public function dataGetPlaceholderPositions()
{
return array(
// none
array('SELECT * FROM Foo', true, array()),
array('SELECT * FROM Foo', false, array()),
// Positionals
array('SELECT ?', true, array(7)),
array('SELECT * FROM Foo WHERE bar IN (?, ?, ?)', true, array(32, 35, 38)),
array('SELECT ? FROM ?', true, array(7, 14)),
array('SELECT "?" FROM foo', true, array()),
array("SELECT '?' FROM foo", true, array()),
array("SELECT `?` FROM foo", true, array()), // Ticket DBAL-552
array("SELECT [?] FROM foo", true, array()),
array("SELECT 'Doctrine\DBAL?' FROM foo", true, array()), // Ticket DBAL-558
array('SELECT "Doctrine\DBAL?" FROM foo', true, array()), // Ticket DBAL-558
array('SELECT `Doctrine\DBAL?` FROM foo', true, array()), // Ticket DBAL-558
array('SELECT [Doctrine\DBAL?] FROM foo', true, array()), // Ticket DBAL-558
array('SELECT "?" FROM foo WHERE bar = ?', true, array(32)),
array("SELECT '?' FROM foo WHERE bar = ?", true, array(32)),
array("SELECT `?` FROM foo WHERE bar = ?", true, array(32)), // Ticket DBAL-552
array("SELECT [?] FROM foo WHERE bar = ?", true, array(32)),
array('SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', true, array(56)), // Ticket GH-2295
array("SELECT 'Doctrine\DBAL?' FROM foo WHERE bar = ?", true, array(45)), // Ticket DBAL-558
array('SELECT "Doctrine\DBAL?" FROM foo WHERE bar = ?', true, array(45)), // Ticket DBAL-558
array('SELECT `Doctrine\DBAL?` FROM foo WHERE bar = ?', true, array(45)), // Ticket DBAL-558
array('SELECT [Doctrine\DBAL?] FROM foo WHERE bar = ?', true, array(45)), // Ticket DBAL-558
array("SELECT * FROM FOO WHERE bar = 'it\\'s a trap? \\\\' OR bar = ?\nAND baz = \"\\\"quote\\\" me on it? \\\\\" OR baz = ?", true, array(58, 104)),
array('SELECT * FROM foo WHERE foo = ? AND bar = ?', true, array(1 => 42, 0 => 30)), // explicit keys
// named
array('SELECT :foo FROM :bar', false, array(7 => 'foo', 17 => 'bar')),
array('SELECT * FROM Foo WHERE bar IN (:name1, :name2)', false, array(32 => 'name1', 40 => 'name2')),
array('SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', false, array(37 => 'name1', 45 => 'name2')),
array("SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", false, array(37 => 'name1', 45 => 'name2')),
array('SELECT :foo_id', false, array(7 => 'foo_id')), // Ticket DBAL-231
array('SELECT @rank := 1', false, array()), // Ticket DBAL-398
array('SELECT @rank := 1 AS rank, :foo AS foo FROM :bar', false, array(27 => 'foo', 44 => 'bar')), // Ticket DBAL-398
array('SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date', false, array(30 => 'start_date', 52 => 'start_date')), // Ticket GH-113
array('SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date', false, array(46 => 'start_date', 68 => 'start_date')), // Ticket GH-259
array('SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1', false, array(57 => 'param1')), // Ticket DBAL-552
array('SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1', false, array(57 => 'param1')), // Ticket DBAL-552
array('SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])', false, array(56 => 'foo')), // Ticket GH-2295
array(
<<<'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, array(74 => 'a_param1', 91 => 'a_param2', 190 => 'a_param3')
),
array("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, array(121 => 'condition_0', 174 => 'condition_1')),
array('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, array(121 => 'condition_0', 174 => 'condition_1')),
array('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, array(121 => 'condition_0', 174 => 'condition_1')),
array('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, array(121 => 'condition_0', 174 => 'condition_1')),
array('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, array(121 => 'condition_0', 174 => 'condition_1')),
);
}
/**
* @dataProvider dataGetPlaceholderPositions
*/
public function testGetPlaceholderPositions($query, $isPositional, $expectedParamPos)
{
$actualParamPos = SQLParserUtils::getPlaceholderPositions($query, $isPositional);
self::assertEquals($expectedParamPos, $actualParamPos);
}
public function dataExpandListParameters()
{
return array(
// Positional: Very simple with one needle
array(
"SELECT * FROM Foo WHERE foo IN (?)",
array(array(1, 2, 3)),
array(Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
array(1, 2, 3),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Positional: One non-list before d one after list-needle
array(
"SELECT * FROM Foo WHERE foo = ? AND bar IN (?)",
array("string", array(1, 2, 3)),
array(ParameterType::STRING, Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
array("string", 1, 2, 3),
array(ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Positional: One non-list after list-needle
array(
"SELECT * FROM Foo WHERE bar IN (?) AND baz = ?",
array(array(1, 2, 3), "foo"),
array(Connection::PARAM_INT_ARRAY, ParameterType::STRING),
'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
array(1, 2, 3, "foo"),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING)
),
// Positional: One non-list before and one after list-needle
array(
"SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?",
array(1, array(1, 2, 3), 4),
array(ParameterType::INTEGER, Connection::PARAM_INT_ARRAY, ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
array(1, 1, 2, 3, 4),
array(
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
)
),
// Positional: Two lists
array(
"SELECT * FROM Foo WHERE foo IN (?, ?)",
array(array(1, 2, 3), array(4, 5)),
array(Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
array(1, 2, 3, 4, 5),
array(
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
)
),
// Positional: Empty "integer" array DDC-1978
array(
"SELECT * FROM Foo WHERE foo IN (?)",
array(array()),
array(Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (NULL)',
array(),
array()
),
// Positional: Empty "str" array DDC-1978
array(
"SELECT * FROM Foo WHERE foo IN (?)",
array(array()),
array(Connection::PARAM_STR_ARRAY),
'SELECT * FROM Foo WHERE foo IN (NULL)',
array(),
array()
),
// Positional: explicit keys for params and types
array(
"SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?",
array(1 => 'bar', 2 => 'baz', 0 => 1),
array(2 => ParameterType::STRING, 1 => ParameterType::STRING),
'SELECT * FROM Foo WHERE foo = ? AND bar = ? AND baz = ?',
array(1 => 'bar', 0 => 1, 2 => 'baz'),
array(1 => ParameterType::STRING, 2 => ParameterType::STRING),
),
// Positional: explicit keys for array params and array types
array(
"SELECT * FROM Foo WHERE foo IN (?) AND bar IN (?) AND baz = ?",
array(1 => array('bar1', 'bar2'), 2 => true, 0 => array(1, 2, 3)),
array(2 => ParameterType::BOOLEAN, 1 => Connection::PARAM_STR_ARRAY, 0 => Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND bar IN (?, ?) AND baz = ?',
array(1, 2, 3, 'bar1', 'bar2', true),
array(
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
array(
"SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ? AND foo IN (?)",
array(1 => 1, 2 => array(1, 2, 3), 3 => 4, 4 => array(5, 6)),
array(
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 (?, ?)',
array(1, 1, 2, 3, 4, 5, 6),
array(
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
ParameterType::INTEGER,
)
),
// Named parameters : Very simple with param int
array(
"SELECT * FROM Foo WHERE foo = :foo",
array('foo'=>1),
array('foo' => ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ?',
array(1),
array(ParameterType::INTEGER),
),
// Named parameters : Very simple with param int and string
array(
"SELECT * FROM Foo WHERE foo = :foo AND bar = :bar",
array('bar'=>'Some String','foo'=>1),
array('foo' => ParameterType::INTEGER, 'bar' => ParameterType::STRING),
'SELECT * FROM Foo WHERE foo = ? AND bar = ?',
array(1,'Some String'),
array(ParameterType::INTEGER, ParameterType::STRING)
),
// Named parameters : Very simple with one needle
array(
"SELECT * FROM Foo WHERE foo IN (:foo)",
array('foo'=>array(1, 2, 3)),
array('foo'=>Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
array(1, 2, 3),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER),
),
// Named parameters: One non-list before d one after list-needle
array(
"SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)",
array('foo'=>"string", 'bar'=>array(1, 2, 3)),
array('foo' => ParameterType::STRING, 'bar' => Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
array("string", 1, 2, 3),
array(ParameterType::STRING, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Named parameters: One non-list after list-needle
array(
"SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz",
array('bar'=>array(1, 2, 3), 'baz'=>"foo"),
array('bar'=>Connection::PARAM_INT_ARRAY, 'baz'=>ParameterType::STRING),
'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
array(1, 2, 3, "foo"),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING)
),
// Named parameters: One non-list before and one after list-needle
array(
"SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz",
array('bar'=>array(1, 2, 3),'foo'=>1, 'baz'=>4),
array('bar'=>Connection::PARAM_INT_ARRAY, 'foo'=>ParameterType::INTEGER, 'baz'=>ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
array(1, 1, 2, 3, 4),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Named parameters: Two lists
array(
"SELECT * FROM Foo WHERE foo IN (:a, :b)",
array('b'=>array(4, 5),'a'=>array(1, 2, 3)),
array('a'=>Connection::PARAM_INT_ARRAY, 'b'=>Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
array(1, 2, 3, 4, 5),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Named parameters : With the same name arg type string
array(
"SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg",
array('arg'=>"Some String"),
array('arg'=>ParameterType::STRING),
'SELECT * FROM Foo WHERE foo <> ? AND bar = ?',
array("Some String","Some String"),
array(ParameterType::STRING,ParameterType::STRING,)
),
// Named parameters : With the same name arg
array(
"SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)",
array('arg'=>array(1, 2, 3)),
array('arg'=>Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND NOT bar IN (?, ?, ?)',
array(1, 2, 3, 1, 2, 3),
array(ParameterType::INTEGER,ParameterType::INTEGER, ParameterType::INTEGER,ParameterType::INTEGER,ParameterType::INTEGER, ParameterType::INTEGER)
),
// Named parameters : Same name, other name in between DBAL-299
array(
"SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)",
array('foo'=>2,'bar'=>3),
array('foo'=>ParameterType::INTEGER,'bar'=>ParameterType::INTEGER),
'SELECT * FROM Foo WHERE (? = 2) AND (? = 3) AND (? = 2)',
array(2, 3, 2),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::INTEGER)
),
// Named parameters : Empty "integer" array DDC-1978
array(
"SELECT * FROM Foo WHERE foo IN (:foo)",
array('foo'=>array()),
array('foo'=>Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (NULL)',
array(),
array()
),
// Named parameters : Two empty "str" array DDC-1978
array(
"SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)",
array('foo'=>array(), 'bar'=>array()),
array('foo'=>Connection::PARAM_STR_ARRAY, 'bar'=>Connection::PARAM_STR_ARRAY),
'SELECT * FROM Foo WHERE foo IN (NULL) OR bar IN (NULL)',
array(),
array()
),
array(
"SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar OR baz = :baz",
array('foo' => array(1, 2), 'bar' => 'bar', 'baz' => 'baz'),
array('foo' => Connection::PARAM_INT_ARRAY, 'baz' => 'string'),
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ? OR baz = ?',
array(1, 2, 'bar', 'baz'),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING, 'string')
),
array(
"SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar",
array('foo' => array(1, 2), 'bar' => 'bar'),
array('foo' => Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
array(1, 2, 'bar'),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING)
),
// Params/types with colons
array(
"SELECT * FROM Foo WHERE foo = :foo OR bar = :bar",
array(':foo' => 'foo', ':bar' => 'bar'),
array(':foo' => ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
array('foo', 'bar'),
array(ParameterType::INTEGER, ParameterType::STRING)
),
array(
"SELECT * FROM Foo WHERE foo = :foo OR bar = :bar",
array(':foo' => 'foo', ':bar' => 'bar'),
array(':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ? OR bar = ?',
array('foo', 'bar'),
array(ParameterType::INTEGER, ParameterType::INTEGER)
),
array(
"SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar",
array(':foo' => array(1, 2), ':bar' => 'bar'),
array('foo' => Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
array(1, 2, 'bar'),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING)
),
array(
"SELECT * FROM Foo WHERE foo IN (:foo) OR bar = :bar",
array('foo' => array(1, 2), 'bar' => 'bar'),
array(':foo' => Connection::PARAM_INT_ARRAY),
'SELECT * FROM Foo WHERE foo IN (?, ?) OR bar = ?',
array(1, 2, 'bar'),
array(ParameterType::INTEGER, ParameterType::INTEGER, ParameterType::STRING)
),
// DBAL-522 - null valued parameters are not considered
array(
'INSERT INTO Foo (foo, bar) values (:foo, :bar)',
array('foo' => 1, 'bar' => null),
array(':foo' => ParameterType::INTEGER, ':bar' => ParameterType::NULL),
'INSERT INTO Foo (foo, bar) values (?, ?)',
array(1, null),
array(ParameterType::INTEGER, ParameterType::NULL)
),
array(
'INSERT INTO Foo (foo, bar) values (?, ?)',
array(1, null),
array(ParameterType::INTEGER, ParameterType::NULL),
'INSERT INTO Foo (foo, bar) values (?, ?)',
array(1, null),
array(ParameterType::INTEGER, ParameterType::NULL)
),
// DBAL-1205 - Escaped single quotes SQL- and C-Style
array(
"SELECT * FROM Foo WHERE foo = :foo||''':not_a_param''\\'' OR bar = ''':not_a_param''\\'':bar",
array(':foo' => 1, ':bar' => 2),
array(':foo' => ParameterType::INTEGER, 'bar' => ParameterType::INTEGER),
'SELECT * FROM Foo WHERE foo = ?||\'\'\':not_a_param\'\'\\\'\' OR bar = \'\'\':not_a_param\'\'\\\'\'?',
array(1, 2),
array(ParameterType::INTEGER, ParameterType::INTEGER)
),
);
}
/**
* @dataProvider dataExpandListParameters
*/
public function testExpandListParameters($q, $p, $t, $expectedQuery, $expectedParams, $expectedTypes)
{
list($query, $params, $types) = SQLParserUtils::expandListParameters($q, $p, $t);
self::assertEquals($expectedQuery, $query, "Query was not rewritten correctly.");
self::assertEquals($expectedParams, $params, "Params dont match");
self::assertEquals($expectedTypes, $types, "Types dont match");
}
public function dataQueryWithMissingParameters()
{
return array(
array(
"SELECT * FROM foo WHERE bar = :param",
array('other' => 'val'),
array(),
),
array(
"SELECT * FROM foo WHERE bar = :param",
array(),
array(),
),
array(
"SELECT * FROM foo WHERE bar = :param",
array(),
array('param' => Connection::PARAM_INT_ARRAY),
),
array(
"SELECT * FROM foo WHERE bar = :param",
array(),
array(':param' => Connection::PARAM_INT_ARRAY),
),
array(
"SELECT * FROM foo WHERE bar = :param",
array(),
array('bar' => Connection::PARAM_INT_ARRAY),
),
array(
"SELECT * FROM foo WHERE bar = :param",
array('bar' => 'value'),
array('bar' => Connection::PARAM_INT_ARRAY),
),
);
}
/**
* @dataProvider dataQueryWithMissingParameters
*/
public function testExceptionIsThrownForMissingParam($query, $params, $types = array())
{
$this->expectException(
'Doctrine\DBAL\SQLParserUtilsException',
'Value for :param not found in params array. Params array key should be "param"'
);
SQLParserUtils::expandListParameters($query, $params, $types);
}
}