function NamedPlaceholderConverterTest::statementsWithParametersProvider

Data for testParse.

File

core/modules/mysqli/tests/src/Unit/NamedPlaceholderConverterTest.php, line 38

Class

NamedPlaceholderConverterTest

Tests \Drupal\mysqli\Driver\Database\mysqli\NamedPlaceholderConverter.

Namespace

Code

public static function statementsWithParametersProvider() : iterable {
  (yield [
    'SELECT ?',
    [
      'foo',
    ],
    'SELECT ?',
    [
      'foo',
    ],
  ]);
  (yield [
    'SELECT * FROM Foo WHERE bar IN (?, ?, ?)',
    [
      'baz',
      'qux',
      'fred',
    ],
    'SELECT * FROM Foo WHERE bar IN (?, ?, ?)',
    [
      'baz',
      'qux',
      'fred',
    ],
  ]);
  (yield [
    'SELECT ? FROM ?',
    [
      'baz',
      'qux',
    ],
    'SELECT ? FROM ?',
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    'SELECT "?" FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT "?" FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    "SELECT '?' FROM foo WHERE bar = ?",
    [
      'baz',
    ],
    "SELECT '?' FROM foo WHERE bar = ?",
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT `?` FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT `?` FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT [?] FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT [?] FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])',
    [
      'baz',
    ],
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])',
    [
      'baz',
    ],
  ]);
  (yield [
    "SELECT 'foo-bar?' FROM foo WHERE bar = ?",
    [
      'baz',
    ],
    "SELECT 'foo-bar?' FROM foo WHERE bar = ?",
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT "foo-bar?" FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT "foo-bar?" FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT `foo-bar?` FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT `foo-bar?` FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT [foo-bar?] FROM foo WHERE bar = ?',
    [
      'baz',
    ],
    'SELECT [foo-bar?] FROM foo WHERE bar = ?',
    [
      'baz',
    ],
  ]);
  (yield [
    'SELECT :foo FROM :bar',
    [
      ':foo' => 'baz',
      ':bar' => 'qux',
    ],
    'SELECT ? FROM ?',
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    'SELECT * FROM Foo WHERE bar IN (:name1, :name2)',
    [
      ':name1' => 'baz',
      ':name2' => 'qux',
    ],
    'SELECT * FROM Foo WHERE bar IN (?, ?)',
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    'SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)',
    [
      ':name1' => 'baz',
      ':name2' => 'qux',
    ],
    'SELECT ":foo" FROM Foo WHERE bar IN (?, ?)',
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    "SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)",
    [
      ':name1' => 'baz',
      ':name2' => 'qux',
    ],
    "SELECT ':foo' FROM Foo WHERE bar IN (?, ?)",
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    'SELECT :foo_id',
    [
      ':foo_id' => 'bar',
    ],
    'SELECT ?',
    [
      'bar',
    ],
  ]);
  (yield [
    'SELECT @rank := 1 AS rank, :foo AS foo FROM :bar',
    [
      ':foo' => 'baz',
      ':bar' => 'qux',
    ],
    'SELECT @rank := 1 AS rank, ? AS foo FROM ?',
    [
      'baz',
      'qux',
    ],
  ]);
  (yield [
    'SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date',
    [
      ':start_date' => 'qux',
    ],
    'SELECT * FROM Foo WHERE bar > ? AND baz > ?',
    [
      'qux',
      'qux',
    ],
  ]);
  (yield [
    'SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date',
    [
      ':start_date' => 'qux',
    ],
    'SELECT foo::date as date FROM Foo WHERE bar > ? AND baz > ?',
    [
      'qux',
      'qux',
    ],
  ]);
  (yield [
    'SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1',
    [
      ':param1' => 'qux',
    ],
    'SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= ?',
    [
      'qux',
    ],
  ]);
  (yield [
    'SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1',
    [
      ':param1' => 'qux',
    ],
    'SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= ?',
    [
      'qux',
    ],
  ]);
  (yield [
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])',
    [
      ':foo' => 'qux',
    ],
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])',
    [
      'qux',
    ],
  ]);
  (yield [
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[:foo])',
    [
      ':foo' => 'qux',
    ],
    'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[?])',
    [
      'qux',
    ],
  ]);
  (yield [
    "SELECT table.column1, ARRAY['3'] FROM schema.table table WHERE table.f1 = :foo AND ARRAY['3']",
    [
      ':foo' => 'qux',
    ],
    "SELECT table.column1, ARRAY['3'] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']",
    [
      'qux',
    ],
  ]);
  (yield [
    "SELECT table.column1, ARRAY['3']::integer[] FROM schema.table table WHERE table.f1 = :foo AND ARRAY['3']::integer[]",
    [
      ':foo' => 'qux',
    ],
    "SELECT table.column1, ARRAY['3']::integer[] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']::integer[]",
    [
      'qux',
    ],
  ]);
  (yield [
    "SELECT table.column1, ARRAY[:foo] FROM schema.table table WHERE table.f1 = :bar AND ARRAY['3']",
    [
      ':foo' => 'qux',
      ':bar' => 'git',
    ],
    "SELECT table.column1, ARRAY[?] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']",
    [
      'qux',
      'git',
    ],
  ]);
  (yield [
    'SELECT table.column1, ARRAY[:foo]::integer[] FROM schema.table table' . " WHERE table.f1 = :bar AND ARRAY['3']::integer[]",
    [
      ':foo' => 'qux',
      ':bar' => 'git',
    ],
    'SELECT table.column1, ARRAY[?]::integer[] FROM schema.table table' . " WHERE table.f1 = ? AND ARRAY['3']::integer[]",
    [
      'qux',
      'git',
    ],
  ]);
  (yield 'Parameter array with placeholder keys missing starting colon' => [
    'SELECT table.column1, ARRAY[:foo]::integer[] FROM schema.table table' . " WHERE table.f1 = :bar AND ARRAY['3']::integer[]",
    [
      'foo' => 'qux',
      'bar' => 'git',
    ],
    'SELECT table.column1, ARRAY[?]::integer[] FROM schema.table table' . " WHERE table.f1 = ? AND ARRAY['3']::integer[]",
    [
      'qux',
      'git',
    ],
  ]);
  (yield 'Quotes inside literals escaped by doubling' => [
    <<<'SQL'
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
SQL
,
    [
      ':a_param1' => 'qux',
      ':a_param2' => 'git',
      ':a_param3' => 'foo',
    ],
    <<<'SQL'
SELECT * FROM foo
WHERE bar = ':not_a_param1 ''":not_a_param2"'''
   OR bar=?
   OR bar=?||':not_a_param3'
   OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6'
   OR bar=''
   OR bar=?
SQL
,
    [
      'qux',
      'git',
      'foo',
    ],
  ]);
  (yield [
    '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',
    [
      ':condition_0' => 'qux',
      ':condition_1' => 'git',
    ],
    '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 ? ESCAPE \'\\\\\') AND (data.description LIKE ? ESCAPE \'\\\\\') ORDER BY id ASC',
    [
      'qux',
      'git',
    ],
  ]);
  (yield [
    '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',
    [
      ':condition_0' => 'qux',
      ':condition_1' => 'git',
    ],
    '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 ? ESCAPE "\\\\") AND (data.description LIKE ? ESCAPE "\\\\") ORDER BY id ASC',
    [
      'qux',
      'git',
    ],
  ]);
  (yield 'Combined single and double quotes' => [
    <<<'SQL'
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
SQL
,
    [
      ':condition_0' => 'qux',
      ':condition_1' => 'git',
    ],
    <<<'SQL'
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 ? ESCAPE "\\")
   AND (data.description LIKE ? ESCAPE '\\') ORDER BY id ASC
SQL
,
    [
      'qux',
      'git',
    ],
  ]);
  (yield [
    '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',
    [
      ':condition_0' => 'qux',
      ':condition_1' => 'git',
    ],
    '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 ? ESCAPE `\\\\`) AND (data.description LIKE ? ESCAPE `\\\\`) ORDER BY id ASC',
    [
      'qux',
      'git',
    ],
  ]);
  (yield 'Combined single quotes and backticks' => [
    <<<'SQL'
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
SQL
,
    [
      ':condition_0' => 'qux',
      ':condition_1' => 'git',
    ],
    <<<'SQL'
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 ? ESCAPE '\\')
   AND (data.description LIKE ? ESCAPE `\\`) ORDER BY id ASC
SQL
,
    [
      'qux',
      'git',
    ],
  ]);
  (yield '? placeholders inside comments' => [
    <<<'SQL'
/*
 * test placeholder ?
 */
SELECT dummy as "dummy?"
  FROM DUAL
 WHERE '?' = '?'
-- AND dummy <> ?
   AND dummy = ?
SQL
,
    [
      'baz',
    ],
    <<<'SQL'
/*
 * test placeholder ?
 */
SELECT dummy as "dummy?"
  FROM DUAL
 WHERE '?' = '?'
-- AND dummy <> ?
   AND dummy = ?
SQL
,
    [
      'baz',
    ],
  ]);
  (yield 'Named placeholders inside comments' => [
    <<<'SQL'
/*
 * test :placeholder
 */
SELECT dummy as "dummy?"
  FROM DUAL
 WHERE '?' = '?'
-- AND dummy <> :dummy
   AND dummy = :key
SQL
,
    [
      ':key' => 'baz',
    ],
    <<<'SQL'
/*
 * test :placeholder
 */
SELECT dummy as "dummy?"
  FROM DUAL
 WHERE '?' = '?'
-- AND dummy <> :dummy
   AND dummy = ?
SQL
,
    [
      'baz',
    ],
  ]);
  (yield 'Escaped question' => [
    <<<'SQL'
SELECT '{"a":null}'::jsonb ?? :key
SQL
,
    [
      ':key' => 'qux',
    ],
    <<<'SQL'
SELECT '{"a":null}'::jsonb ?? ?
SQL
,
    [
      'qux',
    ],
  ]);
}