- added letsencrypt arguments to exec call to avoid need of ini file - adde...

Marius Cramer

2015-12-08 873244e795356d77426cbff4224f7b8a55a6835f

 server/plugins-available/mysql_clientdb_plugin.inc.php

@@ -83,7 +83,14 @@
      if(!is_array($host_list)) $host_list = explode(',', $host_list);

      $success = true;

      if(!preg_match('/\*[A-F0-9]{40}$/', $database_password)) {
            $result = $link->query("SELECT PASSWORD('" . $link->escape_string($database_password) . "') as `crypted`");
            if($result) {
                  $row = $result->fetch_assoc();
                  $database_password = $row['crypted'];
                  $result->free();
            }
      }
      // loop through hostlist
      foreach($host_list as $db_host) {
         $db_host = trim($db_host);
@@ -94,7 +101,8 @@
         $valid = true;
         if($db_host == '%' || $db_host == 'localhost') {
            $valid = true;
         } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
//         } elseif(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $db_host)) {
         } elseif(preg_match("/^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/", $db_host)) {
            $groups = explode('.', $db_host);
            foreach($groups as $group){
               if($group<0 or $group>255)
@@ -107,10 +115,10 @@
         if($valid == false) continue;

         if($action == 'GRANT') {
            if(!$link->query("GRANT " . ($user_read_only ? "SELECT" : "ALL") . " ON ".$link->escape_string($database_name).".* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
            $app->log("GRANT " . ($user_read_only ? "SELECT" : "ALL") . " ON ".$link->escape_string($database_name).".* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."'; success? " . ($success ? 'yes' : 'no'), LOGLEVEL_DEBUG);
            if(!$link->query("GRANT " . ($user_read_only ? "SELECT" : "ALL") . " ON `".$link->escape_string($database_name)."`.* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
            $app->log("GRANT " . ($user_read_only ? "SELECT" : "ALL") . " ON `".$link->escape_string($database_name)."`.* TO '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."'; success? " . ($success ? 'yes' : 'no'), LOGLEVEL_DEBUG);
         } elseif($action == 'REVOKE') {
            if(!$link->query("REVOKE ALL PRIVILEGES ON ".$link->escape_string($database_name).".* FROM '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
            if(!$link->query("REVOKE ALL PRIVILEGES ON `".$link->escape_string($database_name)."`.* FROM '".$link->escape_string($database_user)."'@'$db_host' IDENTIFIED BY PASSWORD '".$link->escape_string($database_password)."';")) $success = false;
         } elseif($action == 'DROP') {
            if(!$link->query("DROP USER '".$link->escape_string($database_user)."'@'$db_host';")) $success = false;
         } elseif($action == 'RENAME') {
@@ -129,7 +137,7 @@
      // set to all hosts if none given
      if(trim($host_list) == '') $host_list = '%';

      $db_user_databases = $app->db->queryAllRecords("SELECT * FROM web_database WHERE (database_user_id = ".$user_id." OR database_ro_user_id = ".$user_id.") AND active = 'y' AND database_id != ".$database_id);
      $db_user_databases = $app->db->queryAllRecords("SELECT * FROM web_database WHERE (database_user_id = ? OR database_ro_user_id = ?) AND active = 'y' AND database_id != ?", $user_id, $user_id, $database_id);
      $db_user_host_list = array();
      if(is_array($db_user_databases) && !empty($db_user_databases)){
         foreach($db_user_databases as $db_user_database){
@@ -188,7 +196,7 @@
         }

         //* Create the new database
         if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table)) {
         if ($link->query('CREATE DATABASE `'.$link->escape_string($data['new']['database_name']).'`'.$query_charset_table)) {
            $app->log('Created MySQL database: '.$data['new']['database_name'], LOGLEVEL_DEBUG);
         } else {
            $app->log('Unable to create the database: '.$link->error, LOGLEVEL_WARNING);
@@ -198,9 +206,8 @@
         if($data['new']['active'] == 'y') {

            // get the users for this database
            $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");

            $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_ro_user_id']) . "'");
            $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']);
            $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_ro_user_id']);

            $host_list = '';
            if($data['new']['remote_access'] == 'y') {
@@ -246,11 +253,11 @@
         }

         // get the users for this database
         $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_user_id']) . "'");
         $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
         $db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_user_id']);
         $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_user_id']);

         $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['new']['database_ro_user_id']) . "'");
         $old_db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_ro_user_id']) . "'");
         $db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['new']['database_ro_user_id']);
         $old_db_ro_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_ro_user_id']);

         $host_list = '';
         if($data['new']['remote_access'] == 'y') {
@@ -269,8 +276,125 @@
         if($old_host_list != '') $old_host_list .= ',';
         $old_host_list .= 'localhost';

         //* rename database
         if ( $data['new']['database_name'] !=  $data['old']['database_name'] ) {
            $old_name = $link->escape_string($data['old']['database_name']);
            $new_name = $link->escape_string($data['new']['database_name']);
            $timestamp = time();

            $tables = $link->query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema='".$old_name."' AND TABLE_TYPE='BASE TABLE'");
            if ($tables->num_rows > 0) {
               while ($row = $tables->fetch_assoc()) {
                  $tables_array[] = $row['TABLE_NAME'];
               }

               //* save triggers, routines and events
               $triggers = $link->query("SHOW TRIGGERS FROM ".$old_name);
               if ($triggers->num_rows > 0) {
                  while ($row = $triggers->fetch_assoc()) {
                     $triggers_array[] = $row;
                  }
                  $app->log('Dumping triggers from '.$old_name, LOGLEVEL_DEBUG);
                  $command = "mysqldump -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$old_name." -d -t -R -E > ".$timestamp.$old_name.'.triggers';
                  exec($command, $out, $ret);
                  $app->system->chmod($timestamp.$old_name.'.triggers', 0600);
                  if ($ret != 0) {
                     unset($triggers_array);
                     $app->system->unlink($timestamp.$old_name.'.triggers');
                     $app->log('Unable to dump triggers from '.$old_name, LOGLEVEL_ERROR);
                  }
                  unset($out);
               }

               //* save views
               $views = $link->query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema='".$old_name."' and TABLE_TYPE='VIEW'");
               if ($views->num_rows > 0) {
                  while ($row = $views->fetch_assoc()) {
                     $views_array[] = $row;
                  }
                  foreach ($views_array as $_views) {
                     $temp[] = $_views['TABLE_NAME'];
                  }
                  $app->log('Dumping views from '.$old_name, LOGLEVEL_DEBUG);
                  $temp_views = implode(' ', $temp);
                  $command = "mysqldump -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$old_name." ".$temp_views." > ".$timestamp.$old_name.'.views';
                  exec($command, $out, $ret);
                  $app->system->chmod($timestamp.$old_name.'.views', 0600);
                  if ($ret != 0) {
                     unset($views_array);
                     $app->system->unlink($timestamp.$old_name.'.views');
                     $app->log('Unable to dump views from '.$old_name, LOGLEVEL_ERROR);
                  }
                  unset($out);
                  unset($temp);
                  unset($temp_views);
               }

               //* create new database
               $this->db_insert($event_name, $data);

               $link->query("show databases like '".$new_name."'");
               if ($link) {
                  //* rename tables
                  foreach ($tables_array as $table) {
                     $table = $link->escape_string($table);
                     $sql = "RENAME TABLE ".$old_name.".".$table." TO ".$new_name.".".$table;
                     $link->query($sql);
                     $app->log($sql, LOGLEVEL_DEBUG);
                     if(!$link) {
                        $app->log($sql." failed", LOGLEVEL_ERROR);
                     }
                  }

                  //* drop old triggers
                  if (@is_array($triggers_array)) {
                     foreach($triggers_array as $trigger) {
                        $_trigger = $link->escape_string($trigger['Trigger']);
                        $sql = "DROP TRIGGER ".$old_name.".".$_trigger;
                        $link->query($sql);
                        $app->log($sql, LOGLEVEL_DEBUG);
                        unset($_trigger);
                     }
                     //* update triggers, routines and events
                     $command = "mysql -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$new_name." < ".$timestamp.$old_name.'.triggers';
                     exec($command, $out, $ret);
                     if ($ret != 0) {
                        $app->log('Unable to import triggers for '.$new_name, LOGLEVEL_ERROR);
                     } else {
                        $app->system->unlink($timestamp.$old_name.'.triggers');
                     }
                  }

                  //* loading views
                  if (@is_array($views_array)) {
                     $command = "mysql -h ".escapeshellarg($clientdb_host)." -u ".escapeshellarg($clientdb_user)." -p".escapeshellarg($clientdb_password)." ".$new_name." < ".$timestamp.$old_name.'.views';
                     exec($command, $out, $ret);
                     if ($ret != 0) {
                        $app->log('Unable to import views for '.$new_name, LOGLEVEL_ERROR);
                     } else {
                        $app->system->unlink($timestamp.$old_name.'.views');
                     }
                  }

                  //* drop old database
                  $this->db_delete($event_name, $data);
               } else {
                  $app->log('Connection to new databse '.$new_name.' failed', LOGLEVEL_ERROR);
                  if (@is_array($triggers_array)) {
                     $app->system->unlink($timestamp.$old_name.'.triggers');
                  }
                  if (@is_array($views_array)) {
                     $app->system->unlink($timestamp.$old_name.'.views');
                  }
               }

            } else { //* SELECT TABLE_NAME error
               $app->log('Unable to rename database '.$old_name.' to '.$new_name, LOGLEVEL_ERROR);
            }
         }

         // Create the database user if database was disabled before
         if($data['new']['active'] == 'y' && $data['old']['active'] == 'n') {
         if($data['new']['active'] == 'y') {
            if($db_user) {
               if($db_user['database_user'] == 'root') $app->log('User root not allowed for Client databases', LOGLEVEL_WARNING);
               else $this->process_host_list('GRANT', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $host_list, $link);
@@ -293,6 +417,7 @@
                     //$this->process_host_list('DROP', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
                     //$this->process_host_list('REVOKE', $data['new']['database_name'], $db_user['database_user'], $db_user['database_password'], $old_host_list, $link);
                  }

               }
               if($old_db_ro_user && $data['old']['database_user_id'] != $data['old']['database_ro_user_id']) {
                  if($old_db_ro_user['database_user'] == 'root'){
@@ -477,20 +602,20 @@
         $old_host_list .= 'localhost';

         if($data['old']['database_user_id']) {
            $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
            $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_user_id']);
            $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_user_id'], $old_host_list);
            if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
            if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
         }
         if($data['old']['database_ro_user_id']) {
            $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '" . intval($data['old']['database_ro_user_id']) . "'");
            $old_db_user = $app->db->queryOneRecord("SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = ?", $data['old']['database_ro_user_id']);
            $drop_or_revoke_user = $this->drop_or_revoke_user($data['old']['database_id'], $data['old']['database_ro_user_id'], $old_host_list);
            if($drop_or_revoke_user['drop_hosts'] != '') $this->process_host_list('DROP', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['drop_hosts'], $link);
            if($drop_or_revoke_user['revoke_hosts'] != '') $this->process_host_list('REVOKE', $data['old']['database_name'], $old_db_user['database_user'], $old_db_user['database_password'], $drop_or_revoke_user['revoke_hosts'], $link);
         }


         if($link->query('DROP DATABASE '.$link->escape_string($data['old']['database_name']))) {
         if($link->query('DROP DATABASE `'.$link->escape_string($data['old']['database_name'].'`'))) {
            $app->log('Dropping MySQL database: '.$data['old']['database_name'], LOGLEVEL_DEBUG);
         } else {
            $app->log('Error while dropping MySQL database: '.$data['old']['database_name'].' '.$link->error, LOGLEVEL_WARNING);
@@ -532,7 +657,8 @@

      $host_list = array('localhost');
      // get all databases this user was active for
      $db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = '" . intval($data['old']['database_user_id']) . "'");
      $user_id = intval($data['old']['database_user_id']);
      $db_list = $app->db->queryAllRecords("SELECT `remote_access`, `remote_ips` FROM `web_database` WHERE `database_user_id` = ? OR database_ro_user_id = ?", $user_id, $user_id);;
      if(count($db_list) < 1) return; // nothing to do on this server for this db user

      foreach($db_list as $database) {