3 namespace Drupal\KernelTests\Core\Database;
6 * Tests Drupal's extended prepared statement syntax..
10 class QueryTest extends DatabaseTestBase {
13 * Tests that we can pass an array of values directly in the query.
15 public function testArraySubstitution() {
16 $names = db_query('SELECT name FROM {test} WHERE age IN ( :ages[] ) ORDER BY age', [':ages[]' => [25, 26, 27]])->fetchAll();
17 $this->assertEqual(count($names), 3, 'Correct number of names returned');
19 $names = db_query('SELECT name FROM {test} WHERE age IN ( :ages[] ) ORDER BY age', [':ages[]' => [25]])->fetchAll();
20 $this->assertEqual(count($names), 1, 'Correct number of names returned');
24 * Tests that we can not pass a scalar value when an array is expected.
26 public function testScalarSubstitution() {
28 $names = db_query('SELECT name FROM {test} WHERE age IN ( :ages[] ) ORDER BY age', [':ages[]' => 25])->fetchAll();
29 $this->fail('Array placeholder with scalar argument should result in an exception.');
31 catch (\InvalidArgumentException $e) {
32 $this->pass('Array placeholder with scalar argument should result in an exception.');
38 * Tests SQL injection via database query array arguments.
40 public function testArrayArgumentsSQLInjection() {
41 // Attempt SQL injection and verify that it does not work.
43 "1 ;INSERT INTO {test} (name) VALUES ('test12345678'); -- " => '',
47 db_query("SELECT * FROM {test} WHERE name = :name", [':name' => $condition])->fetchObject();
48 $this->fail('SQL injection attempt via array arguments should result in a database exception.');
50 catch (\InvalidArgumentException $e) {
51 $this->pass('SQL injection attempt via array arguments should result in a database exception.');
54 // Test that the insert query that was used in the SQL injection attempt did
55 // not result in a row being inserted in the database.
56 $result = db_select('test')
57 ->condition('name', 'test12345678')
61 $this->assertFalse($result, 'SQL injection attempt did not result in a row being inserted in the database table.');
65 * Tests SQL injection via condition operator.
67 public function testConditionOperatorArgumentsSQLInjection() {
68 $injection = "IS NOT NULL) ;INSERT INTO {test} (name) VALUES ('test12345678'); -- ";
70 // Convert errors to exceptions for testing purposes below.
71 set_error_handler(function ($severity, $message, $filename, $lineno) {
72 throw new \ErrorException($message, 0, $severity, $filename, $lineno);
75 $result = db_select('test', 't')
77 ->condition('name', 1, $injection)
79 $this->fail('Should not be able to attempt SQL injection via condition operator.');
81 catch (\ErrorException $e) {
82 $this->pass('SQL injection attempt via condition arguments should result in a database exception.');
85 // Test that the insert query that was used in the SQL injection attempt did
86 // not result in a row being inserted in the database.
87 $result = db_select('test')
88 ->condition('name', 'test12345678')
92 $this->assertFalse($result, 'SQL injection attempt did not result in a row being inserted in the database table.');
94 // Attempt SQLi via union query with no unsafe characters.
95 $this->enableModules(['user']);
96 $this->installEntitySchema('user');
98 ->fields(['name' => '123456'])
100 $injection = "= 1 UNION ALL SELECT password FROM user WHERE uid =";
103 $result = db_select('test', 't')
104 ->fields('t', ['name', 'name'])
105 ->condition('name', 1, $injection)
107 $this->fail('Should not be able to attempt SQL injection via operator.');
109 catch (\ErrorException $e) {
110 $this->pass('SQL injection attempt via condition arguments should result in a database exception.');
113 // Attempt SQLi via union query - uppercase tablename.
114 db_insert('TEST_UPPERCASE')
115 ->fields(['name' => 'secrets'])
117 $injection = "IS NOT NULL) UNION ALL SELECT name FROM {TEST_UPPERCASE} -- ";
120 $result = db_select('test', 't')
121 ->fields('t', ['name'])
122 ->condition('name', 1, $injection)
124 $this->fail('Should not be able to attempt SQL injection via operator.');
126 catch (\ErrorException $e) {
127 $this->pass('SQL injection attempt via condition arguments should result in a database exception.');
129 restore_error_handler();
133 * Tests numeric query parameter expansion in expressions.
135 * @see \Drupal\Core\Database\Driver\sqlite\Statement::getStatement()
136 * @see http://bugs.php.net/bug.php?id=45259
138 public function testNumericExpressionSubstitution() {
139 $count = db_query('SELECT COUNT(*) >= 3 FROM {test}')->fetchField();
140 $this->assertEqual((bool) $count, TRUE);
142 $count = db_query('SELECT COUNT(*) >= :count FROM {test}', [
145 $this->assertEqual((bool) $count, TRUE);