View Single Post
  #13  
Old 8th December 2010, 13:04
smartcms smartcms is offline
Junior Member
 
Join Date: Dec 2010
Posts: 1
Thanks: 0
Thanked 2 Times in 1 Post
Default

Thanks necromncr for your plugin. It was just what we were looking for.

However I think we had the same problem as madmucho, and we found the following problems:
- The rcmail->db class doesn't seem to like the parameters ( ? ) in the query.
- It's trying to update the identities table using identity_id = $rcmail->user->data['user_id']
- The latest version of ISPConfig 2 seems to be using the user_email field insterad of the user_emailalias field

So we updated ispconfig_addons.php like this:
Code:
<?php

/**                                                                                                                                                                                                            
 * ISPConfig v2.x integration for Roundcube 0.4.2 plugin                                                                                                                                                       
 *                                                                                                                                                                                                             
 * Adds ability to authenticate against ISPConfig 2.x user database.                                                                                                                                           
 *                                                                                                                                                                                                             
 * To use you must have config.inc.php file following settings:                                                                                                                                                
 * isp_db_name - ISPConfig database name                                                                                                                                                                       
 * isp_db_user - user with access to only selected fields in selected tables                                                                                                                                   
 * isp_db_pass - password for that user                                                                                                                                                                        
 *                                                                                                                                                                                                             
 * Code is tested however there is no waranty for it. Use at your own risk. Feedback is welcome!                                                                                                               
 *                                                                                                                                                                                                             
 * @version 0.1                                                                                                                                                                                                
 * @author Janez Dolinar s.p., <info@tuksi.net>                                                                                                                                                                
 */   
class ispconfig_addons extends rcube_plugin
{  
  function init()
  {
    $this->add_hook('user_create', array($this, 'usercreate'));
    $this->add_hook('login_after', array($this, 'loginafter'));

    $this->load_config();
  }
                                                                                                                                                                                                               
  function loginafter($query) {                                                                                                                                                                                
    $rcmail = rcmail::get_instance();                                                                                                                                                                          
                                                                                                                                                                                                               
    if (isset($_SESSION['userFullName'])) {                                                                                                                                                                    
      $sql = "UPDATE identities SET name='" . $_SESSION['userFullName'] . "', email='" . $_SESSION['userEmail'] . "' WHERE user_id = " . $rcmail->user->data['user_id'];                                                                                                                                    
                                                                                                                                                                                                               
      $rcmail->db->query($sql);                                                                                           
                                                                                                                                                                                                               
      unset($_SESSION['userFullName']);                                                                                                                                                                        
      unset($_SESSION['userEmail']);                                                                                                                                                                           
    }                                                                                                                                                                                                          
    return $query;
  }
  
  
  function usercreate($args)
  {
    $rcmail = rcmail::get_instance();
    if (($rcmail->config->get('isp_db_name')=='') || ($rcmail->config->get('isp_db_user')=='') || ($rcmail->config->get('isp_db_pass')=='')) {
      return $args;
    } else {
      $sql = "SELECT iiu.user_username, iiu.user_email, iiu.user_name, iid.web_domain FROM isp_isp_user iiu, isp_dep id, isp_isp_web iid "
            ."WHERE iiu.user_username = '%s'  AND iiu.doc_id = id.child_doc_id  AND id.child_doctype_id = 1014  AND iid.doc_id = id.parent_doc_id";
            
      $mysql = new MySQLi('localhost', $rcmail->config->get('isp_db_user'), $rcmail->config->get('isp_db_pass'), $rcmail->config->get('isp_db_name'));
      
      if ($mysql->connect_error) return false;
      $result = $mysql->query(sprintf($sql,addslashes($args['user'])));

      if ($result) {
        $row = $result->fetch_object();
        $userEmail = explode("\n",str_replace("\r","",$row->user_email));
        $args['alias'] = $userEmail[0].'@'.$row->web_domain;
        $_SESSION['userFullName'] = $row->user_name;
        $_SESSION['userEmail'] = $userEmail[0].'@'.$row->web_domain;
        $result->close();
      }
      
      $mysql->close();
    }
  
    return $args;
  }

}
?>

Remember to update the permissions of your roundcube MySQL user to grant select on the user_email field instead of the user_emailalias field.

We also had to change the following parameters in the roundcube config main.inc.php:
Code:
$rcmail_config['identities_level'] = 3;
before we had
Code:
$rcmail_config['identities_level'] = 1;
Hope this helps someone!

/ Andreas & Niklas
smartcms.se
Reply With Quote