auth/db/tests/db_test.php

   1 <?php
   2 // This file is part of Moodle - http://moodle.org/
   3 //
   4 // Moodle is free software: you can redistribute it and/or modify
   5 // it under the terms of the GNU General Public License as published by
   6 // the Free Software Foundation, either version 3 of the License, or
   7 // (at your option) any later version.
   8 //
   9 // Moodle is distributed in the hope that it will be useful,
  10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 // GNU General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU General Public License
  15 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 /**
  18  * External database auth sync tests, this also tests adodb drivers
  19  * that are matching our four supported Moodle database drivers.
  20  *
  21  * @package    auth_db
  22  * @category   phpunit
  23  * @copyright  2012 Petr Skoda {@link http://skodak.org}
  24  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  25  */
  26
  27 defined('MOODLE_INTERNAL') || die();
  28
  29
  30 class auth_db_testcase extends advanced_testcase {
  31     /** @var string Original error log */
  32     protected $oldlog;
  33
  34     protected function init_auth_database() {
  35         global $DB, $CFG;
  36         require_once("$CFG->dirroot/auth/db/auth.php");
  37
  38         // Discard error logs from AdoDB.
  39         $this->oldlog = ini_get('error_log');
  40         ini_set('error_log', "$CFG->dataroot/testlog.log");
  41
  42         $dbman = $DB->get_manager();
  43
  44         set_config('extencoding', 'utf-8', 'auth/db');
  45
  46         set_config('host', $CFG->dbhost, 'auth/db');
  47         set_config('user', $CFG->dbuser, 'auth/db');
  48         set_config('pass', $CFG->dbpass, 'auth/db');
  49         set_config('name', $CFG->dbname, 'auth/db');
  50
  51         if (!empty($CFG->dboptions['dbport'])) {
  52             set_config('host', $CFG->dbhost.':'.$CFG->dboptions['dbport'], 'auth/db');
  53         }
  54
  55         switch ($DB->get_dbfamily()) {
  56
  57             case 'mysql':
  58                 set_config('type', 'mysqli', 'auth/db');
  59                 set_config('setupsql', "SET NAMES 'UTF-8'", 'auth/db');
  60                 set_config('sybasequoting', '0', 'auth/db');
  61                 if (!empty($CFG->dboptions['dbsocket'])) {
  62                     $dbsocket = $CFG->dboptions['dbsocket'];
  63                     if ((strpos($dbsocket, '/') === false and strpos($dbsocket, '\\') === false)) {
  64                         $dbsocket = ini_get('mysqli.default_socket');
  65                     }
  66                     set_config('type', 'mysqli://'.rawurlencode($CFG->dbuser).':'.rawurlencode($CFG->dbpass).'@'.rawurlencode($CFG->dbhost).'/'.rawurlencode($CFG->dbname).'?socket='.rawurlencode($dbsocket), 'auth/db');
  67                 }
  68                 break;
  69
  70             case 'oracle':
  71                 set_config('type', 'oci8po', 'auth/db');
  72                 set_config('sybasequoting', '1', 'auth/db');
  73                 break;
  74
  75             case 'postgres':
  76                 set_config('type', 'postgres7', 'auth/db');
  77                 $setupsql = "SET NAMES 'UTF-8'";
  78                 if (!empty($CFG->dboptions['dbschema'])) {
  79                     $setupsql .= "; SET search_path = '".$CFG->dboptions['dbschema']."'";
  80                 }
  81                 set_config('setupsql', $setupsql, 'auth/db');
  82                 set_config('sybasequoting', '0', 'auth/db');
  83                 if (!empty($CFG->dboptions['dbsocket']) and ($CFG->dbhost === 'localhost' or $CFG->dbhost === '127.0.0.1')) {
  84                     if (strpos($CFG->dboptions['dbsocket'], '/') !== false) {
  85                         $socket = $CFG->dboptions['dbsocket'];
  86                         if (!empty($CFG->dboptions['dbport'])) {
  87                             $socket .= ':' . $CFG->dboptions['dbport'];
  88                         }
  89                         set_config('host', $socket, 'auth/db');
  90                     } else {
  91                         set_config('host', '', 'auth/db');
  92                     }
  93                 }
  94                 break;
  95
  96             case 'mssql':
  97                 if (get_class($DB) == 'mssql_native_moodle_database') {
  98                     set_config('type', 'mssql_n', 'auth/db');
  99                 } else {
 100                     set_config('type', 'mssqlnative', 'auth/db');
 101                 }
 102                 set_config('sybasequoting', '1', 'auth/db');
 103                 break;
 104
 105             default:
 106                 throw new exception('Unknown database family ' . $DB->get_dbfamily());
 107         }
 108
 109         $table = new xmldb_table('auth_db_users');
 110         $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 111         $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null);
 112         $table->add_field('pass', XMLDB_TYPE_CHAR, '255', null, null, null);
 113         $table->add_field('email', XMLDB_TYPE_CHAR, '255', null, null, null);
 114         $table->add_field('firstname', XMLDB_TYPE_CHAR, '255', null, null, null);
 115         $table->add_field('lastname', XMLDB_TYPE_CHAR, '255', null, null, null);
 116         $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 117         if ($dbman->table_exists($table)) {
 118             $dbman->drop_table($table);
 119         }
 120         $dbman->create_table($table);
 121         set_config('table', $CFG->prefix.'auth_db_users', 'auth/db');
 122         set_config('fielduser', 'name', 'auth/db');
 123         set_config('fieldpass', 'pass', 'auth/db');
 124         set_config('field_map_lastname', 'lastname', 'auth/db');
 125         set_config('field_updatelocal_lastname', 'oncreate', 'auth/db');
 126         set_config('field_lock_lastname', 'unlocked', 'auth/db');
 127         // Setu up field mappings.
 128
 129         set_config('field_map_email', 'email', 'auth/db');
 130         set_config('field_updatelocal_email', 'oncreate', 'auth/db');
 131         set_config('field_updateremote_email', '0', 'auth/db');
 132         set_config('field_lock_email', 'unlocked', 'auth/db');
 133
 134         // Init the rest of settings.
 135         set_config('passtype', 'plaintext', 'auth/db');
 136         set_config('changepasswordurl', '', 'auth/db');
 137         set_config('debugauthdb', 0, 'auth/db');
 138         set_config('removeuser', AUTH_REMOVEUSER_KEEP, 'auth/db');
 139     }
 140
 141     protected function cleanup_auth_database() {
 142         global $DB;
 143
 144         $dbman = $DB->get_manager();
 145         $table = new xmldb_table('auth_db_users');
 146         $dbman->drop_table($table);
 147
 148         ini_set('error_log', $this->oldlog);
 149     }
 150
 151     public function test_plugin() {
 152         global $DB, $CFG;
 153
 154         $this->resetAfterTest(true);
 155
 156         // NOTE: It is strongly discouraged to create new tables in advanced_testcase classes,
 157         //       but there is no other simple way to test ext database enrol sync, so let's
 158         //       disable transactions are try to cleanup after the tests.
 159
 160         $this->preventResetByRollback();
 161
 162         $this->init_auth_database();
 163
 164         /** @var auth_plugin_db $auth */
 165         $auth = get_auth_plugin('db');
 166
 167         $authdb = $auth->db_init();
 168
 169
 170         // Test adodb may access the table.
 171
 172         $user1 = (object)array('name'=>'u1', 'pass'=>'heslo', 'email'=>'u1@example.com');
 173         $user1->id = $DB->insert_record('auth_db_users', $user1);
 174
 175
 176         $sql = "SELECT * FROM {$auth->config->table}";
 177         $rs = $authdb->Execute($sql);
 178         $this->assertInstanceOf('ADORecordSet', $rs);
 179         $this->assertFalse($rs->EOF);
 180         $fields = $rs->FetchRow();
 181         $this->assertTrue(is_array($fields));
 182         $this->assertTrue($rs->EOF);
 183         $rs->Close();
 184
 185         $authdb->Close();
 186
 187
 188         // Test bulk user account creation.
 189
 190         $user2 = (object)array('name'=>'u2', 'pass'=>'heslo', 'email'=>'u2@example.com');
 191         $user2->id = $DB->insert_record('auth_db_users', $user2);
 192
 193         $user3 = (object)array('name'=>'admin', 'pass'=>'heslo', 'email'=>'admin@example.com'); // Should be skipped.
 194         $user3->id = $DB->insert_record('auth_db_users', $user3);
 195
 196         $this->assertCount(2, $DB->get_records('user'));
 197
 198         $trace = new null_progress_trace();
 199         $auth->sync_users($trace, false);
 200
 201         $this->assertEquals(4, $DB->count_records('user'));
 202         $u1 = $DB->get_record('user', array('username'=>$user1->name, 'auth'=>'db'));
 203         $this->assertSame($user1->email, $u1->email);
 204         $u2 = $DB->get_record('user', array('username'=>$user2->name, 'auth'=>'db'));
 205         $this->assertSame($user2->email, $u2->email);
 206         $admin = $DB->get_record('user', array('username'=>'admin', 'auth'=>'manual'));
 207         $this->assertNotEmpty($admin);
 208
 209
 210         // Test sync updates.
 211
 212         $user2b = clone($user2);
 213         $user2b->email = 'u2b@example.com';
 214         $DB->update_record('auth_db_users', $user2b);
 215
 216         $auth->sync_users($trace, false);
 217         $this->assertEquals(4, $DB->count_records('user'));
 218         $u2 = $DB->get_record('user', array('username'=>$user2->name));
 219         $this->assertSame($user2->email, $u2->email);
 220
 221         $auth->sync_users($trace, true);
 222         $this->assertEquals(4, $DB->count_records('user'));
 223         $u2 = $DB->get_record('user', array('username'=>$user2->name));
 224         $this->assertSame($user2->email, $u2->email);
 225
 226         set_config('field_updatelocal_email', 'onlogin', 'auth/db');
 227         $auth->config->field_updatelocal_email = 'onlogin';
 228
 229         $auth->sync_users($trace, false);
 230         $this->assertEquals(4, $DB->count_records('user'));
 231         $u2 = $DB->get_record('user', array('username'=>$user2->name));
 232         $this->assertSame($user2->email, $u2->email);
 233
 234         $auth->sync_users($trace, true);
 235         $this->assertEquals(4, $DB->count_records('user'));
 236         $u2 = $DB->get_record('user', array('username'=>$user2->name));
 237         $this->assertSame($user2b->email, $u2->email);
 238
 239
 240         // Test sync deletes and suspends.
 241
 242         $DB->delete_records('auth_db_users', array('id'=>$user2->id));
 243         $this->assertCount(2, $DB->get_records('auth_db_users'));
 244         unset($user2);
 245         unset($user2b);
 246
 247         $auth->sync_users($trace, false);
 248         $this->assertEquals(4, $DB->count_records('user'));
 249         $this->assertEquals(0, $DB->count_records('user', array('deleted'=>1)));
 250         $this->assertEquals(0, $DB->count_records('user', array('suspended'=>1)));
 251
 252         set_config('removeuser', AUTH_REMOVEUSER_SUSPEND, 'auth/db');
 253         $auth->config->removeuser = AUTH_REMOVEUSER_SUSPEND;
 254
 255         $auth->sync_users($trace, false);
 256         $this->assertEquals(4, $DB->count_records('user'));
 257         $this->assertEquals(0, $DB->count_records('user', array('deleted'=>1)));
 258         $this->assertEquals(1, $DB->count_records('user', array('suspended'=>1)));
 259
 260         $user2 = (object)array('name'=>'u2', 'pass'=>'heslo', 'email'=>'u2@example.com');
 261         $user2->id = $DB->insert_record('auth_db_users', $user2);
 262
 263         $auth->sync_users($trace, false);
 264         $this->assertEquals(4, $DB->count_records('user'));
 265         $this->assertEquals(0, $DB->count_records('user', array('deleted'=>1)));
 266         $this->assertEquals(0, $DB->count_records('user', array('suspended'=>1)));
 267
 268         $DB->delete_records('auth_db_users', array('id'=>$user2->id));
 269
 270         set_config('removeuser', AUTH_REMOVEUSER_FULLDELETE, 'auth/db');
 271         $auth->config->removeuser = AUTH_REMOVEUSER_FULLDELETE;
 272
 273         $auth->sync_users($trace, false);
 274         $this->assertEquals(4, $DB->count_records('user'));
 275         $this->assertEquals(1, $DB->count_records('user', array('deleted'=>1)));
 276         $this->assertEquals(0, $DB->count_records('user', array('suspended'=>1)));
 277
 278         $user2 = (object)array('name'=>'u2', 'pass'=>'heslo', 'email'=>'u2@example.com');
 279         $user2->id = $DB->insert_record('auth_db_users', $user2);
 280
 281         $auth->sync_users($trace, false);
 282         $this->assertEquals(5, $DB->count_records('user'));
 283         $this->assertEquals(1, $DB->count_records('user', array('deleted'=>1)));
 284         $this->assertEquals(0, $DB->count_records('user', array('suspended'=>1)));
 285
 286
 287         // Test user_login().
 288
 289         $user3 = (object)array('name'=>'u3', 'pass'=>'heslo', 'email'=>'u3@example.com');
 290         $user3->id = $DB->insert_record('auth_db_users', $user3);
 291
 292         $this->assertFalse($auth->user_login('u4', 'heslo'));
 293         $this->assertTrue($auth->user_login('u1', 'heslo'));
 294
 295         $this->assertFalse($DB->record_exists('user', array('username'=>'u3', 'auth'=>'db')));
 296         $this->assertTrue($auth->user_login('u3', 'heslo'));
 297         $this->assertFalse($DB->record_exists('user', array('username'=>'u3', 'auth'=>'db')));
 298
 299         set_config('passtype', 'md5', 'auth/db');
 300         $auth->config->passtype = 'md5';
 301         $user3->pass = md5('heslo');
 302         $DB->update_record('auth_db_users', $user3);
 303         $this->assertTrue($auth->user_login('u3', 'heslo'));
 304
 305         set_config('passtype', 'sh1', 'auth/db');
 306         $auth->config->passtype = 'sha1';
 307         $user3->pass = sha1('heslo');
 308         $DB->update_record('auth_db_users', $user3);
 309         $this->assertTrue($auth->user_login('u3', 'heslo'));
 310
 311         require_once($CFG->libdir.'/password_compat/lib/password.php');
 312         set_config('passtype', 'saltedcrypt', 'auth/db');
 313         $auth->config->passtype = 'saltedcrypt';
 314         $user3->pass = password_hash('heslo', PASSWORD_BCRYPT);
 315         $DB->update_record('auth_db_users', $user3);
 316         $this->assertTrue($auth->user_login('u3', 'heslo'));
 317
 318         set_config('passtype', 'internal', 'auth/db');
 319         $auth->config->passtype = 'internal';
 320         create_user_record('u3', 'heslo', 'db');
 321         $this->assertTrue($auth->user_login('u3', 'heslo'));
 322
 323
 324         $DB->delete_records('auth_db_users', array('id'=>$user3->id));
 325
 326         set_config('removeuser', AUTH_REMOVEUSER_KEEP, 'auth/db');
 327         $auth->config->removeuser = AUTH_REMOVEUSER_KEEP;
 328         $this->assertTrue($auth->user_login('u3', 'heslo'));
 329
 330         set_config('removeuser', AUTH_REMOVEUSER_SUSPEND, 'auth/db');
 331         $auth->config->removeuser = AUTH_REMOVEUSER_SUSPEND;
 332         $this->assertFalse($auth->user_login('u3', 'heslo'));
 333
 334         set_config('removeuser', AUTH_REMOVEUSER_FULLDELETE, 'auth/db');
 335         $auth->config->removeuser = AUTH_REMOVEUSER_FULLDELETE;
 336         $this->assertFalse($auth->user_login('u3', 'heslo'));
 337
 338         set_config('passtype', 'sh1', 'auth/db');
 339         $auth->config->passtype = 'sha1';
 340         $this->assertFalse($auth->user_login('u3', 'heslo'));
 341
 342
 343         // Test login create and update.
 344
 345         $user4 = (object)array('name'=>'u4', 'pass'=>'heslo', 'email'=>'u4@example.com');
 346         $user4->id = $DB->insert_record('auth_db_users', $user4);
 347
 348         set_config('passtype', 'plaintext', 'auth/db');
 349         $auth->config->passtype = 'plaintext';
 350
 351         $iuser4 = create_user_record('u4', 'heslo', 'db');
 352         $this->assertNotEmpty($iuser4);
 353         $this->assertSame($user4->name, $iuser4->username);
 354         $this->assertSame($user4->email, $iuser4->email);
 355         $this->assertSame('db', $iuser4->auth);
 356         $this->assertSame($CFG->mnet_localhost_id, $iuser4->mnethostid);
 357
 358         $user4b = clone($user4);
 359         $user4b->email = 'u4b@example.com';
 360         $DB->update_record('auth_db_users', $user4b);
 361
 362         set_config('field_updatelocal_email', 'oncreate', 'auth/db');
 363         $auth->config->field_updatelocal_email = 'oncreate';
 364
 365         update_user_record('u4');
 366         $iuser4 = $DB->get_record('user', array('id'=>$iuser4->id));
 367         $this->assertSame($user4->email, $iuser4->email);
 368
 369         set_config('field_updatelocal_email', 'onlogin', 'auth/db');
 370         $auth->config->field_updatelocal_email = 'onlogin';
 371
 372         update_user_record('u4');
 373         $iuser4 = $DB->get_record('user', array('id'=>$iuser4->id));
 374         $this->assertSame($user4b->email, $iuser4->email);
 375
 376
 377         // Test user_exists()
 378
 379         $this->assertTrue($auth->user_exists('u1'));
 380         $this->assertTrue($auth->user_exists('admin'));
 381         $this->assertFalse($auth->user_exists('u3'));
 382         $this->assertTrue($auth->user_exists('u4'));
 383
 384         $this->cleanup_auth_database();
 385     }
 386
 387     /**
 388      * Testing the function _colonscope() from ADOdb.
 389      */
 390     public function test_adodb_colonscope() {
 391         global $CFG;
 392         require_once($CFG->libdir.'/adodb/adodb.inc.php');
 393         require_once($CFG->libdir.'/adodb/drivers/adodb-odbc.inc.php');
 394         require_once($CFG->libdir.'/adodb/drivers/adodb-db2ora.inc.php');
 395
 396         $this->resetAfterTest(false);
 397
 398         $sql = "select * from table WHERE column=:1 AND anothercolumn > :0";
 399         $arr = array('b', 1);
 400         list($sqlout, $arrout) = _colonscope($sql,$arr);
 401         $this->assertEquals("select * from table WHERE column=? AND anothercolumn > ?", $sqlout);
 402         $this->assertEquals(array(1, 'b'), $arrout);
 403     }
 404
 405     /**
 406      * Testing the clean_data() method.
 407      */
 408     public function test_clean_data() {
 409         global $DB;
 410
 411         $this->resetAfterTest(false);
 412         $this->preventResetByRollback();
 413         $this->init_auth_database();
 414         $auth = get_auth_plugin('db');
 415         $auth->db_init();
 416
 417         // Create users on external table.
 418         $extdbuser1 = (object)array('name'=>'u1', 'pass'=>'heslo', 'email'=>'u1@example.com');
 419         $extdbuser1->id = $DB->insert_record('auth_db_users', $extdbuser1);
 420
 421         // User with malicious data on the name (won't be imported).
 422         $extdbuser2 = (object)array('name'=>'user<script>alert(1);</script>xss', 'pass'=>'heslo', 'email'=>'xssuser@example.com');
 423         $extdbuser2->id = $DB->insert_record('auth_db_users', $extdbuser2);
 424
 425         $extdbuser3 = (object)array('name'=>'u3', 'pass'=>'heslo', 'email'=>'u3@example.com',
 426                 'lastname' => 'user<script>alert(1);</script>xss');
 427         $extdbuser3->id = $DB->insert_record('auth_db_users', $extdbuser3);
 428         $trace = new null_progress_trace();
 429
 430         // Let's test user sync make sure still works as expected..
 431         $auth->sync_users($trace, true);
 432         $this->assertDebuggingCalled("The property 'lastname' has invalid data and has been cleaned.");
 433         // User with correct data, should be equal to external db.
 434         $user1 = $DB->get_record('user', array('email'=> $extdbuser1->email, 'auth'=>'db'));
 435         $this->assertEquals($extdbuser1->name, $user1->username);
 436         $this->assertEquals($extdbuser1->email, $user1->email);
 437
 438         // Get the user on moodle user table.
 439         $user2 = $DB->get_record('user', array('email'=> $extdbuser2->email, 'auth'=>'db'));
 440         $user3 = $DB->get_record('user', array('email'=> $extdbuser3->email, 'auth'=>'db'));
 441
 442         $this->assertEmpty($user2);
 443         $this->assertEquals($extdbuser3->name, $user3->username);
 444         $this->assertEquals('useralert(1);xss', $user3->lastname);
 445
 446         $this->cleanup_auth_database();
 447     }
 448 }