diff --git a/ajax/settings.php b/ajax/settings.php
--- a/ajax/settings.php
+++ b/ajax/settings.php
@@ -1,237 +1,260 @@
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE
* License as published by the Free Software Foundation; either
* version 3 of the License, or any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU AFFERO GENERAL PUBLIC LICENSE for more details.
*
* You should have received a copy of the GNU Affero General Public
* License along with this library. If not, see .
*
*/
/**
* This is the AJAX portion of the settings page.
*
* It can:
* - Verify the connection settings
* - Load autocomplete values for tables
* - Load autocomplete values for columns
* - Save settings for a given domain
* - Load settings for a given domain
*
* It always returns JSON encoded responses
*/
namespace OCA\user_sql;
// Init owncloud
// Check if we are a user
\OCP\User::checkAdminUser();
\OCP\JSON::checkAppEnabled('user_sql');
// CSRF checks
\OCP\JSON::callCheck();
$helper = new \OCA\user_sql\lib\Helper;
$l = \OC::$server->getL10N('user_sql');
$params = $helper -> getParameterArray();
$response = new \OCP\AppFramework\Http\JSONResponse();
// Check if the request is for us
if(isset($_POST['appname']) && ($_POST['appname'] === 'user_sql') && isset($_POST['function']) && isset($_POST['domain']))
{
$domain = $_POST['domain'];
switch($_POST['function'])
{
// Save the settings for the given domain to the database
case 'saveSettings':
- foreach($params as $param)
+ $parameters = array('host' => $_POST['sql_hostname'],
+ 'password' => $_POST['sql_password'],
+ 'user' => $_POST['sql_username'],
+ 'dbname' => $_POST['sql_database'],
+ 'tablePrefix' => ''
+ );
+
+ // Check if the table exists
+ if(!$helper->verifyTable($parameters, $_POST['sql_driver'], $_POST['sql_table']))
+ {
+ $response->setData(array('status' => 'error',
+ 'data' => array('message' => $l -> t('The selected SQL table '.$_POST['sql_table'].' does not exist!'))));
+ break;
+ }
+
+ // Retrieve all column settings
+ $columns = array();
+ foreach($params as $param)
+ {
+ if(strpos($param, 'col_') === 0)
{
- // Special handling for checkbox fields
- if(isset($_POST[$param]))
+ if(isset($_POST[$param]) && $_POST[$param] !== '')
+ $columns[] = $_POST[$param];
+ }
+ }
+
+ // Check if the columns exist
+ $status = $helper->verifyColumns($parameters, $_POST['sql_driver'], $_POST['sql_table'], $columns);
+ if($status !== true)
+ {
+ $response->setData(array('status' => 'error',
+ 'data' => array('message' => $l -> t('The selected SQL column(s) do(es) not exist: '.$status))));
+ break;
+ }
+
+ // If we reach this point, all settings have been verified
+ foreach($params as $param)
+ {
+ // Special handling for checkbox fields
+ if(isset($_POST[$param]))
+ {
+ if($param === 'set_strip_domain')
{
- if($param === 'set_strip_domain')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_strip_domain_'.$domain, 'true');
- }
- elseif($param === 'set_allow_pwchange')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_allow_pwchange_'.$domain, 'true');
- }
- elseif($param === 'set_active_invert')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_active_invert_'.$domain, 'true');
- }
- elseif($param === 'set_enable_gethome')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_enable_gethome_'.$domain, 'true');
- }
- else
- {
- \OC::$server->getConfig()->setAppValue('user_sql', $param.'_'.$domain, $_POST[$param]);
- }
- } else
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_strip_domain_'.$domain, 'true');
+ }
+ elseif($param === 'set_allow_pwchange')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_allow_pwchange_'.$domain, 'true');
+ }
+ elseif($param === 'set_active_invert')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_active_invert_'.$domain, 'true');
+ }
+ elseif($param === 'set_enable_gethome')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_enable_gethome_'.$domain, 'true');
+ }
+ else
{
- if($param === 'set_strip_domain')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_strip_domain_'.$domain, 'false');
- }
- elseif($param === 'set_allow_pwchange')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_allow_pwchange_'.$domain, 'false');
- }
- elseif($param === 'set_active_invert')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_active_invert_'.$domain, 'false');
- }
- elseif($param === 'set_enable_gethome')
- {
- \OC::$server->getConfig()->setAppValue('user_sql', 'set_enable_gethome_'.$domain, 'false');
- }
+ \OC::$server->getConfig()->setAppValue('user_sql', $param.'_'.$domain, $_POST[$param]);
+ }
+ } else
+ {
+ if($param === 'set_strip_domain')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_strip_domain_'.$domain, 'false');
+ }
+ elseif($param === 'set_allow_pwchange')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_allow_pwchange_'.$domain, 'false');
+ }
+ elseif($param === 'set_active_invert')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_active_invert_'.$domain, 'false');
+ }
+ elseif($param === 'set_enable_gethome')
+ {
+ \OC::$server->getConfig()->setAppValue('user_sql', 'set_enable_gethome_'.$domain, 'false');
}
}
- $response->setData(array('status' => 'success',
- 'data' => array('message' => $l -> t('Application settings successfully stored.'))));
+ }
+ $response->setData(array('status' => 'success',
+ 'data' => array('message' => $l -> t('Application settings successfully stored.'))));
break;
// Load the settings for a given domain
case 'loadSettingsForDomain':
$retArr = array();
foreach($params as $param)
{
$retArr[$param] = \OC::$server->getConfig()->getAppValue('user_sql', $param.'_'.$domain, '');
}
$response->setData(array('status' => 'success',
'settings' => $retArr));
break;
// Try to verify the database connection settings
case 'verifySettings':
$cm = new \OC\DB\ConnectionFactory();
if(!isset($_POST['sql_driver']))
{
$response->setData(array('status' => 'error',
'data' => array('message' => $l -> t('Error connecting to database: No driver specified.'))));
break;
}
if(($_POST['sql_hostname'] === '') || ($_POST['sql_database'] === ''))
{
$response->setData(array('status' => 'error',
'data' => array('message' => $l -> t('Error connecting to database: You must specify at least host and database'))));
break;
}
$parameters = array('host' => $_POST['sql_hostname'],
'password' => $_POST['sql_password'],
'user' => $_POST['sql_username'],
'dbname' => $_POST['sql_database'],
'tablePrefix' => ''
);
try {
$conn = $cm -> getConnection($_POST['sql_driver'], $parameters);
$response->setData(array('status' => 'success',
'data' => array('message' => $l -> t('Successfully connected to database'))));
}
catch(\Exception $e)
{
$response->setData(array('status' => 'error',
'data' => array('message' => $l -> t('Error connecting to database: ').$e->getMessage())));
}
break;
// Get the autocompletion values for a column
case 'getColumnAutocomplete':
- $cm = new \OC\DB\ConnectionFactory();
- $search = $_POST['request'];
- $table = $_POST['sql_table'];
+
+
$parameters = array('host' => $_POST['sql_hostname'],
'password' => $_POST['sql_password'],
'user' => $_POST['sql_username'],
'dbname' => $_POST['sql_database'],
'tablePrefix' => ''
);
- try {
- $conn = $cm -> getConnection($_POST['sql_driver'], $parameters);
- $platform = $conn -> getDatabasePlatform();
- $query = $platform -> getListTableColumnsSQL($table);
- $result = $conn -> executeQuery($query);
- $ret = array();
- while($row = $result -> fetch())
+
+ if($helper->verifyTable($parameters, $_POST['sql_driver'], $_POST['sql_table']))
+ $columns = $helper->getColumns($parameters, $_POST['sql_driver'], $_POST['sql_table']);
+ else
+ $columns = array();
+
+ $search = $_POST['request'];
+ $ret = array();
+
+ foreach($columns as $name)
+ {
+ if(($search === '') || ($search === 'search') || (strpos($name, $search) === 0))
{
- $name = $row['Field'];
- if(($search === '') || ($search === 'search') || (strpos($name, $search) === 0))
- {
- $ret[] = array('label' => $name,
- 'value' => $name);
- }
+ $ret[] = array('label' => $name,
+ 'value' => $name);
}
- $response -> setData($ret);
}
- catch(\Exception $e)
- {
- $response->setData(array());
- }
+ $response -> setData($ret);
break;
// Get the autocompletion values for a table
case 'getTableAutocomplete':
- $cm = new \OC\DB\ConnectionFactory();
- $search = $_POST['request'];
$parameters = array('host' => $_POST['sql_hostname'],
'password' => $_POST['sql_password'],
'user' => $_POST['sql_username'],
'dbname' => $_POST['sql_database'],
'tablePrefix' => ''
);
- try {
- $conn = $cm -> getConnection($_POST['sql_driver'], $parameters);
- $platform = $conn -> getDatabasePlatform();
- $query = $platform -> getListTablesSQL();
- $result = $conn -> executeQuery($query);
- $ret = array();
- while($row = $result -> fetch())
+
+ $tables = $helper->getTables($parameters, $_POST['sql_driver']);
+
+ $search = $_POST['request'];
+ $ret = array();
+ foreach($tables as $name)
+ {
+ if(($search === '') || ($search === 'search') || (strpos($name, $search) === 0))
{
- $name = $row['Tables_in_'.$_POST['sql_database']];
- if(($search === '') || ($search === 'search') || (strpos($name, $search) === 0))
- {
- $ret[] = array('label' => $name,
- 'value' => $name);
- }
+ $ret[] = array('label' => $name,
+ 'value' => $name);
}
- $response -> setData($ret);
}
- catch(\Exception $e)
- {
- $response->setData(array());
- }
+ $response -> setData($ret);
break;
}
} else
{
// If the request was not for us, set an error message
$response->setData(array('status' => 'error',
'data' => array('message' => $l -> t('Not submitted for us.'))));
}
// Return the JSON array
echo $response->render();
diff --git a/lib/helper.php b/lib/helper.php
--- a/lib/helper.php
+++ b/lib/helper.php
@@ -1,251 +1,348 @@
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE
* License as published by the Free Software Foundation; either
* version 3 of the License, or any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU AFFERO GENERAL PUBLIC LICENSE for more details.
*
* You should have received a copy of the GNU Affero General Public
* License along with this library. If not, see .
*
*/
namespace OCA\user_sql\lib;
class Helper {
protected $db;
protected $db_conn;
protected $settings;
/**
* The default constructor initializes some parameters
*/
public function __construct()
{
$this->db_conn = false;
}
/**
* Return an array with all supported parameters
* @return array Containing strings of the parameters
*/
public function getParameterArray()
{
$params = array(
'sql_hostname',
'sql_username',
'sql_password',
'sql_database',
'sql_table',
'sql_driver',
'col_username',
'col_password',
'col_active',
'col_displayname',
'col_email',
'col_gethome',
'set_active_invert',
'set_allow_pwchange',
'set_default_domain',
'set_strip_domain',
'set_crypt_type',
'set_mail_sync_mode',
'set_enable_gethome',
'set_gethome_mode',
'set_gethome'
);
return $params;
}
/**
* Load the settings for a given domain. If the domain is not found,
* the settings for 'default' are returned instead.
* @param string $domain The domain name
* @return array of settings
*/
public function loadSettingsForDomain($domain)
{
\OCP\Util::writeLog('OC_USER_SQL', "Trying to load settings for domain: " . $domain, \OCP\Util::DEBUG);
$settings = array();
$sql_host = \OC::$server->getConfig()->getAppValue('user_sql', 'sql_hostname_'.$domain, '');
if($sql_host === '')
{
$domain = 'default';
}
$params = $this -> getParameterArray();
foreach($params as $param)
{
$settings[$param] = \OC::$server->getConfig()->getAppValue('user_sql', $param.'_'.$domain, '');
}
\OCP\Util::writeLog('OC_USER_SQL', "Loaded settings for domain: " . $domain, \OCP\Util::DEBUG);
return $settings;
}
/**
* Run a given query type and return the results
* @param string $type The type of query to run
* @param array $params The parameter array of the query (i.e. the values to bind as key-value pairs)
* @param bool $execOnly Only execute the query, but don't fetch the results (optional, default = false)
* @param bool $fetchArray Fetch an array instead of a single row (optional, default=false)
* @param array $limits use the given limits for the query (optional, default = empty)
* @return mixed
*/
public function runQuery($type, $params, $execOnly = false, $fetchArray = false, $limits = array())
{
\OCP\Util::writeLog('OC_USER_SQL', "Entering runQuery for type: " . $type, \OCP\Util::DEBUG);
if(!$this -> db_conn)
return false;
switch($type)
{
case 'getHome':
$query = "SELECT ".$this->settings['col_gethome']." FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username']." = :uid";
break;
case 'getMail':
$query = "SELECT ".$this->settings['col_email']." FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username']." = :uid";
break;
case 'setMail':
$query = "UPDATE ".$this->settings['sql_table']." SET ".$this->settings['col_email']." = :currMail WHERE ".$this->settings['col_username']." = :uid";
break;
case 'getPass':
$query = "SELECT ".$this->settings['col_password']." FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username']." = :uid";
if($this -> settings['col_active'] !== '')
$query .= " AND " .($this -> settings['set_active_invert'] === 'true' ? "NOT " : "" ) . $this -> settings['col_active'];
break;
case 'setPass':
$query = "UPDATE ".$this->settings['sql_table']." SET ".$this->settings['col_password']." = :enc_password WHERE ".$this->settings['col_username'] ." = :uid";
break;
case 'getRedmineSalt':
$query = "SELECT salt FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username'] ." = :uid;";
break;
case 'countUsers':
$query = "SELECT COUNT(*) FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username'] ." LIKE :search";
if($this -> settings['col_active'] !== '')
$query .= " AND " .($this -> settings['set_active_invert'] === 'true' ? "NOT " : "" ) . $this -> settings['col_active'];
break;
case 'getUsers':
$query = "SELECT ".$this->settings['col_username']." FROM ".$this->settings['sql_table'];
$query .= " WHERE ".$this->settings['col_username']." LIKE :search";
if($this -> settings['col_active'] !== '')
$query .= " AND " .($this -> settings['set_active_invert'] === 'true' ? "NOT " : "" ) . $this -> settings['col_active'];
$query .= " ORDER BY ".$this->settings['col_username'];
break;
case 'userExists':
$query = "SELECT ".$this->settings['col_username']." FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username']." = :uid";
if($this -> settings['col_active'] !== '')
$query .= " AND " .($this -> settings['set_active_invert'] === 'true' ? "NOT " : "" ) . $this -> settings['col_active'];
break;
case 'getDisplayName':
$query = "SELECT ".$this->settings['col_displayname']." FROM ".$this->settings['sql_table']." WHERE ".$this->settings['col_username']." = :uid";
if($this -> settings['col_active'] !== '')
$query .= " AND " .($this -> settings['set_active_invert'] === 'true' ? "NOT " : "" ) . $this -> settings['col_active'];
break;
case 'mysqlEncryptSalt':
$query = "SELECT ENCRYPT(:pw, :salt);";
break;
case 'mysqlEncrypt':
$query = "SELECT ENCRYPT(:pw);";
break;
case 'mysqlPassword':
$query = "SELECT PASSWORD(:pw);";
break;
}
if(isset($limits['limit']) && $limits['limit'] !== null)
{
$limit = intval($limits['limit']);
$query .= " LIMIT ".$limit;
}
if(isset($limits['offset']) && $limits['offset'] !== null)
{
$offset = intval($limits['offset']);
$query .= " OFFSET ".$offset;
}
\OCP\Util::writeLog('OC_USER_SQL', "Preparing query: $query", \OCP\Util::DEBUG);
$result = $this -> db -> prepare($query);
foreach($params as $param => $value)
{
$result -> bindValue(":".$param, $value);
}
\OCP\Util::writeLog('OC_USER_SQL', "Executing query...", \OCP\Util::DEBUG);
if(!$result -> execute())
{
$err = $result -> errorInfo();
\OCP\Util::writeLog('OC_USER_SQL', "Query failed: " . $err[2], \OCP\Util::DEBUG);
return false;
}
if($execOnly === true)
{
return true;
}
\OCP\Util::writeLog('OC_USER_SQL', "Fetching result...", \OCP\Util::DEBUG);
if($fetchArray === true)
$row = $result -> fetchAll();
else
$row = $result -> fetch();
if(!$row)
{
return false;
}
return $row;
}
/**
* Connect to the database using ownCloud's DBAL
* @param array $settings The settings for the connection
* @return bool
*/
public function connectToDb($settings)
{
$this -> settings = $settings;
$cm = new \OC\DB\ConnectionFactory();
$parameters = array('host' => $this -> settings['sql_hostname'],
'password' => $this -> settings['sql_password'],
'user' => $this -> settings['sql_username'],
'dbname' => $this -> settings['sql_database'],
'tablePrefix' => ''
);
try
{
$this -> db = $cm -> getConnection($this -> settings['sql_driver'], $parameters);
$this -> db -> query("SET NAMES 'UTF8'");
$this -> db_conn = true;
return true;
}
catch (\Exception $e)
{
\OCP\Util::writeLog('OC_USER_SQL', 'Failed to connect to the database: ' . $e -> getMessage(), \OCP\Util::ERROR);
$this -> db_conn = false;
return false;
}
}
+
+ /**
+ * Check if all of the given columns exist
+ * @param array $parameters The connection parameters
+ * @param string $sql_driver The SQL driver to use
+ * @param string $table The table name to check
+ * @param array $cols The columns to check
+ * @param array True if found, otherwise false
+ */
+ public function verifyColumns($parameters, $sql_driver, $table, $cols)
+ {
+ $columns = $this->getColumns($parameters, $sql_driver, $table);
+ $res = true;
+ $err = '';
+ foreach($cols as $col)
+ {
+ if(!in_array($col, $columns, true))
+ {
+ $res = false;
+ $err .= $col.' ';
+ }
+ }
+ if($res)
+ return true;
+ else
+ return $err;
+ }
+
+ /**
+ * Check if a given table exists
+ * @param array $parameters The connection parameters
+ * @param string $sql_driver The SQL driver to use
+ * @param string $table The table name to check
+ * @param array True if found, otherwise false
+ */
+ public function verifyTable($parameters, $sql_driver, $table)
+ {
+ $tables = $this->getTables($parameters, $sql_driver);
+ return in_array($table, $tables, true);
+ }
+
+ /**
+ * Retrieve a list of tables for the given connection parameters
+ * @param array $parameters The connection parameters
+ * @param string $sql_driver The SQL driver to use
+ * @return array The found tables, empty if an error occured
+ */
+ public function getTables($parameters, $sql_driver)
+ {
+ $cm = new \OC\DB\ConnectionFactory();
+ try {
+ $conn = $cm -> getConnection($sql_driver, $parameters);
+ $platform = $conn -> getDatabasePlatform();
+ $query = $platform -> getListTablesSQL();
+ $result = $conn -> executeQuery($query);
+ $ret = array();
+ while($row = $result -> fetch())
+ {
+ $name = $row['Tables_in_'.$parameters['dbname']];
+ $ret[] = $name;
+ }
+ return $ret;
+ }
+ catch(\Exception $e)
+ {
+ return array();
+ }
+ }
+
+ /**
+ * Retrieve a list of columns for the given connection parameters
+ * @param array $parameters The connection parameters
+ * @param string $sql_driver The SQL driver to use
+ * @param string $table The SQL table to work with
+ * @return array The found column, empty if an error occured
+ */
+ public function getColumns($parameters, $sql_driver, $table)
+ {
+ $cm = new \OC\DB\ConnectionFactory();
+ try {
+ $conn = $cm -> getConnection($sql_driver, $parameters);
+ $platform = $conn -> getDatabasePlatform();
+ $query = $platform -> getListTableColumnsSQL($table);
+ $result = $conn -> executeQuery($query);
+ $ret = array();
+ while($row = $result -> fetch())
+ {
+ $name = $row['Field'];
+ $ret[] = $name;
+ }
+ return $ret;
+ }
+ catch(\Exception $e)
+ {
+ return array();
+ }
+ }
}