[Summary view] [Print] [Text view]

   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          set_config('passtype', 'saltedcrypt', 'auth/db');
 312          $auth->config->passtype = 'saltedcrypt';
 313          $user3->pass = password_hash('heslo', PASSWORD_BCRYPT);
 314          $DB->update_record('auth_db_users', $user3);
 315          $this->assertTrue($auth->user_login('u3', 'heslo'));
 316  
 317          set_config('passtype', 'internal', 'auth/db');
 318          $auth->config->passtype = 'internal';
 319          create_user_record('u3', 'heslo', 'db');
 320          $this->assertTrue($auth->user_login('u3', 'heslo'));
 321  
 322  
 323          $DB->delete_records('auth_db_users', array('id'=>$user3->id));
 324  
 325          set_config('removeuser', AUTH_REMOVEUSER_KEEP, 'auth/db');
 326          $auth->config->removeuser = AUTH_REMOVEUSER_KEEP;
 327          $this->assertTrue($auth->user_login('u3', 'heslo'));
 328  
 329          set_config('removeuser', AUTH_REMOVEUSER_SUSPEND, 'auth/db');
 330          $auth->config->removeuser = AUTH_REMOVEUSER_SUSPEND;
 331          $this->assertFalse($auth->user_login('u3', 'heslo'));
 332  
 333          set_config('removeuser', AUTH_REMOVEUSER_FULLDELETE, 'auth/db');
 334          $auth->config->removeuser = AUTH_REMOVEUSER_FULLDELETE;
 335          $this->assertFalse($auth->user_login('u3', 'heslo'));
 336  
 337          set_config('passtype', 'sh1', 'auth/db');
 338          $auth->config->passtype = 'sha1';
 339          $this->assertFalse($auth->user_login('u3', 'heslo'));
 340  
 341  
 342          // Test login create and update.
 343  
 344          $user4 = (object)array('name'=>'u4', 'pass'=>'heslo', 'email'=>'u4@example.com');
 345          $user4->id = $DB->insert_record('auth_db_users', $user4);
 346  
 347          set_config('passtype', 'plaintext', 'auth/db');
 348          $auth->config->passtype = 'plaintext';
 349  
 350          $iuser4 = create_user_record('u4', 'heslo', 'db');
 351          $this->assertNotEmpty($iuser4);
 352          $this->assertSame($user4->name, $iuser4->username);
 353          $this->assertSame($user4->email, $iuser4->email);
 354          $this->assertSame('db', $iuser4->auth);
 355          $this->assertSame($CFG->mnet_localhost_id, $iuser4->mnethostid);
 356  
 357          $user4b = clone($user4);
 358          $user4b->email = 'u4b@example.com';
 359          $DB->update_record('auth_db_users', $user4b);
 360  
 361          set_config('field_updatelocal_email', 'oncreate', 'auth/db');
 362          $auth->config->field_updatelocal_email = 'oncreate';
 363  
 364          update_user_record('u4');
 365          $iuser4 = $DB->get_record('user', array('id'=>$iuser4->id));
 366          $this->assertSame($user4->email, $iuser4->email);
 367  
 368          set_config('field_updatelocal_email', 'onlogin', 'auth/db');
 369          $auth->config->field_updatelocal_email = 'onlogin';
 370  
 371          update_user_record('u4');
 372          $iuser4 = $DB->get_record('user', array('id'=>$iuser4->id));
 373          $this->assertSame($user4b->email, $iuser4->email);
 374  
 375  
 376          // Test user_exists()
 377  
 378          $this->assertTrue($auth->user_exists('u1'));
 379          $this->assertTrue($auth->user_exists('admin'));
 380          $this->assertFalse($auth->user_exists('u3'));
 381          $this->assertTrue($auth->user_exists('u4'));
 382  
 383          $this->cleanup_auth_database();
 384      }
 385  
 386      /**
 387       * Testing the function _colonscope() from ADOdb.
 388       */
 389      public function test_adodb_colonscope() {
 390          global $CFG;
 391          require_once($CFG->libdir.'/adodb/adodb.inc.php');
 392          require_once($CFG->libdir.'/adodb/drivers/adodb-odbc.inc.php');
 393          require_once($CFG->libdir.'/adodb/drivers/adodb-db2ora.inc.php');
 394  
 395          $this->resetAfterTest(false);
 396  
 397          $sql = "select * from table WHERE column=:1 AND anothercolumn > :0";
 398          $arr = array('b', 1);
 399          list($sqlout, $arrout) = _colonscope($sql,$arr);
 400          $this->assertEquals("select * from table WHERE column=? AND anothercolumn > ?", $sqlout);
 401          $this->assertEquals(array(1, 'b'), $arrout);
 402      }
 403  
 404      /**
 405       * Testing the clean_data() method.
 406       */
 407      public function test_clean_data() {
 408          global $DB;
 409  
 410          $this->resetAfterTest(false);
 411          $this->preventResetByRollback();
 412          $this->init_auth_database();
 413          $auth = get_auth_plugin('db');
 414          $auth->db_init();
 415  
 416          // Create users on external table.
 417          $extdbuser1 = (object)array('name'=>'u1', 'pass'=>'heslo', 'email'=>'u1@example.com');
 418          $extdbuser1->id = $DB->insert_record('auth_db_users', $extdbuser1);
 419  
 420          // User with malicious data on the name (won't be imported).
 421          $extdbuser2 = (object)array('name'=>'user<script>alert(1);</script>xss', 'pass'=>'heslo', 'email'=>'xssuser@example.com');
 422          $extdbuser2->id = $DB->insert_record('auth_db_users', $extdbuser2);
 423  
 424          $extdbuser3 = (object)array('name'=>'u3', 'pass'=>'heslo', 'email'=>'u3@example.com',
 425                  'lastname' => 'user<script>alert(1);</script>xss');
 426          $extdbuser3->id = $DB->insert_record('auth_db_users', $extdbuser3);
 427          $trace = new null_progress_trace();
 428  
 429          // Let's test user sync make sure still works as expected..
 430          $auth->sync_users($trace, true);
 431          $this->assertDebuggingCalled("The property 'lastname' has invalid data and has been cleaned.");
 432          // User with correct data, should be equal to external db.
 433          $user1 = $DB->get_record('user', array('email'=> $extdbuser1->email, 'auth'=>'db'));
 434          $this->assertEquals($extdbuser1->name, $user1->username);
 435          $this->assertEquals($extdbuser1->email, $user1->email);
 436  
 437          // Get the user on moodle user table.
 438          $user2 = $DB->get_record('user', array('email'=> $extdbuser2->email, 'auth'=>'db'));
 439          $user3 = $DB->get_record('user', array('email'=> $extdbuser3->email, 'auth'=>'db'));
 440  
 441          $this->assertEmpty($user2);
 442          $this->assertEquals($extdbuser3->name, $user3->username);
 443          $this->assertEquals('useralert(1);xss', $user3->lastname);
 444  
 445          $this->cleanup_auth_database();
 446      }
 447  }