/**
* Apply filter
* @param Zend_Db_Adapter_Abstract $db
* @param Db_Select | Zend_Db_Select $sql
* @throws Exception
*/
public function applyTo(Zend_Db_Adapter_Abstract $db, $sql)
{
if (!$sql instanceof Db_Select && !$sql instanceof Zend_Db_Select) {
throw new Exception('Db_Select_Filter::applyTo $sql must be instance of Db_Select/Zend_Db_Select');
}
$quotedField = $db->quoteIdentifier($this->field);
switch ($this->type) {
case self::LT:
case self::GT:
case self::EQ:
case self::GT_EQ:
case self::LT_EQ:
case self::LIKE:
case self::NOT:
case self::NOT_LIKE:
$sql->where($quotedField . ' ' . $this->type . ' ?', $this->value);
break;
case self::IN:
case self::NOT_IN:
$sql->where($quotedField . ' ' . $this->type . ' (?)', $this->value);
break;
case self::NOT_NULL:
case self::IS_NULL:
$sql->where($quotedField . ' ' . $this->type);
break;
case self::BETWEEN:
case self::NOT_BETWEEN:
$sql->where($quotedField . ' ' . $this->type . ' ' . $db->quote($this->value[0]) . ' AND ' . $db->quote($this->value[1]));
break;
}
}
public function loadByName(Mage_Directory_Model_Region $region, $regionName, $countryId)
{
$locale = $this->_read->quote(Mage::app()->getLocale()->getLocaleCode());
$select = $this->_read->select()->from(array('region' => $this->_regionTable))->where('region.country_id=?', $countryId)->where('region.default_name=?', $regionName)->join(array('rname' => $this->_regionNameTable), 'rname.region_id=region.region_id AND rname.locale=' . $locale, array('name'));
$region->setData($this->_read->fetchRow($select));
return $this;
}
/**
* Return array of organizations that are a child of the given parentId
*
* @param int $parentId
* @return array
*/
public function getChildOrganisations($parentId = null)
{
if (is_null($parentId)) {
return array();
}
$organizations = $this->db->fetchPairs('SELECT gor_id_organization, gor_name FROM gems__organizations WHERE gor_active=1 AND gor_has_login=1 AND (gor_accessible_by LIKE ' . $this->db->quote('%:' . $parentId . ':%') . ' OR gor_id_organization = ' . $this->db->quote($parentId) . ') ORDER BY gor_name');
natsort($organizations);
return $organizations;
}
/**
* Overrule to implement snippet specific filtering and sorting.
*
* @param \MUtil_Model_ModelAbstract $model
*/
protected function processFilterAndSort(\MUtil_Model_ModelAbstract $model)
{
parent::processFilterAndSort($model);
$appId = $this->request->getParam(\Gems_Model::APPOINTMENT_ID);
if ($appId) {
$appKeyPrefix = $this->db->quote(FieldsDefinition::makeKey(FieldMaintenanceModel::APPOINTMENTS_NAME, ''));
$appSource = $this->db->quote(\Gems_Tracker_Engine_StepEngineAbstract::APPOINTMENT_TABLE);
$or[] = $this->db->quoteInto("gro_valid_after_source = {$appSource} AND\n (gto_id_respondent_track, gro_valid_after_field) IN\n (SELECT gr2t2a_id_respondent_track, CONCAT({$appKeyPrefix}, gr2t2a_id_app_field)\n FROM gems__respondent2track2appointment\n WHERE gr2t2a_id_appointment = ?)", $appId);
$or[] = $this->db->quoteInto("gro_valid_for_source = {$appSource} AND\n (gto_id_respondent_track, gro_valid_for_field) IN\n (SELECT gr2t2a_id_respondent_track, CONCAT({$appKeyPrefix}, gr2t2a_id_app_field)\n FROM gems__respondent2track2appointment\n WHERE gr2t2a_id_appointment = ?)", $appId);
}
$model->addFilter(array('(' . implode(') OR (', $or) . ')'));
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// XF CUSTOM: this function has problems. The regex isn't accurate and the
// accurate regex "{$q}([^\\\\{$q}]+|{$q}{$q}|\\\\.)*{$q}" has issues with
// extremely limited stack sizes.
return '';
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove quoted identifiers
if (!empty($d)) {
$rx = "{$d}{$d}|{$d}.*?(?<!(((?<![{$d}\\\\]){$d})|((?<!\\\\)\\\\))){$d}(?!{$d})";
$sql = preg_replace("/{$rx}/s", '', $sql);
}
// remove quoted values
if (!empty($q)) {
$rx = "{$q}{$q}|{$q}.*?(?<!(((?<![{$q}\\\\]){$q})|((?<!\\\\)\\\\))){$q}(?!{$q})";
$sql = preg_replace("/{$rx}/s", '', $sql);
}
return $sql;
}
/**
* Render and return the given filter expression
*
* @param Filter $filter
*
* @return string
*/
protected function renderFilterExpression(Filter $filter)
{
$column = $filter->getColumn();
$sign = $filter->getSign();
$value = $filter->getExpression();
if (is_array($value)) {
if ($sign === '=') {
return $column . ' IN (' . $this->dbAdapter->quote($value) . ')';
} elseif ($sign === '!=') {
return $column . ' NOT IN (' . $this->dbAdapter->quote($value) . ')';
}
throw new ProgrammingError('Unable to render array expressions with operators other than equal or not equal');
} elseif ($sign === '=' && strpos($value, '*') !== false) {
if ($value === '*') {
// We'll ignore such filters as it prevents index usage and because "*" means anything, anything means
// all whereas all means that whether we use a filter to match anything or no filter at all makes no
// difference, except for performance reasons...
return '';
}
return $column . ' LIKE ' . $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value));
} elseif ($sign === '!=' && strpos($value, '*') !== false) {
if ($value === '*') {
// We'll ignore such filters as it prevents index usage and because "*" means nothing, so whether we're
// using a real column with a valid comparison here or just an expression which cannot be evaluated to
// true makes no difference, except for performance reasons...
return $this->dbAdapter->quote(0);
}
return $column . ' NOT LIKE ' . $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value));
} else {
return $column . ' ' . $sign . ' ' . $this->dbAdapter->quote($value);
}
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->_adapter->quote($q);
$qe = substr($qe, 1, 2);
$qe = preg_quote($qe);
$escapeChar = substr($qe, 0, 1);
// remove 'foo\'bar'
if (!empty($q)) {
$escapeChar = preg_quote($escapeChar);
// this segfaults only after 65,000 characters instead of 9,000
$sql = preg_replace("/{$q}([^{$q}{$escapeChar}]*|({$qe})*)*{$q}/s", '', $sql);
}
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/\"(\\\\\"|[^\"])*\"/Us", '', $sql);
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->_adapter->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = preg_quote($de);
// Note: $de and $d where never used..., now they are:
$sql = preg_replace("/{$d}({$de}|\\\\{2}|[^{$d}])*{$d}/Us", '', $sql);
return $sql;
}
/**
* Render and return the given filter expression
*
* @param Filter $filter
*
* @return string
*/
protected function renderFilterExpression(Filter $filter)
{
$column = $filter->getColumn();
$sign = $filter->getSign();
$value = $filter->getExpression();
if (is_array($value)) {
if ($sign === '=') {
return $column . ' IN (' . $this->dbAdapter->quote($value) . ')';
} elseif ($sign === '!=') {
return sprintf('(%1$s NOT IN (%2$s) OR %1$s IS NULL)', $column, $this->dbAdapter->quote($value));
}
throw new ProgrammingError('Unable to render array expressions with operators other than equal or not equal');
} elseif ($sign === '=' && strpos($value, '*') !== false) {
if ($value === '*') {
// We'll ignore such filters as it prevents index usage and because "*" means anything, so whether we're
// using a real column with a valid comparison here or just an expression which can only be evaluated to
// true makes no difference, except for performance reasons...
return new Zend_Db_Expr('TRUE');
}
return $column . ' LIKE ' . $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value));
} elseif ($sign === '!=' && strpos($value, '*') !== false) {
if ($value === '*') {
// We'll ignore such filters as it prevents index usage and because "*" means nothing, so whether we're
// using a real column with a valid comparison here or just an expression which cannot be evaluated to
// true makes no difference, except for performance reasons...
return new Zend_Db_Expr('FALSE');
}
return sprintf('(%1$s NOT LIKE %2$s OR %1$s IS NULL)', $column, $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value)));
} elseif ($sign === '!=') {
return sprintf('(%1$s != %2$s OR %1$s IS NULL)', $column, $this->dbAdapter->quote($value));
} else {
return sprintf('%s %s %s', $column, $sign, $this->dbAdapter->quote($value));
}
}
/**
* Applies the given array of where statements to the given select
*/
public function applyWhereToSelect($where, Zend_Db_Select $select)
{
foreach ($where as $field => $value) {
if ($value instanceof Zend_Db_Expr && is_int($field)) {
$select->where($value);
} else {
if (is_string($field) && is_array($value)) {
// we have an in clause
$in = '';
$sep = '';
foreach ($value as $val) {
$in .= $sep . $this->proxied->quote($val);
$sep = ',';
}
$fieldVal = new Zend_Db_Expr($field . ' in (' . $in . ')');
$select->where($fieldVal);
} else {
if (strpos(mb_strtolower($field), 'or ') === 0) {
$field = substr($field, 3);
$select->orWhere($field . ' ?', $value);
} else {
$select->where($field . ' ?', $value);
}
}
}
}
return $select;
}
/**
* Load the data when the cache is empty.
*
* @param mixed $id
* @return array The array of data values
*/
protected function loadData($id)
{
if (\Gems_User_UserLoader::SYSTEM_NO_ORG === $id) {
$data = false;
} else {
try {
$sql = "SELECT * FROM gems__organizations WHERE gor_id_organization = ? LIMIT 1";
$data = $this->db->fetchRow($sql, intval($id));
} catch (\Exception $e) {
$data = false;
}
}
if ($data) {
try {
$dbOrgId = $this->db->quote($id, \Zend_Db::INT_TYPE);
$sql = "SELECT gor_id_organization, gor_name\n FROM gems__organizations\n WHERE gor_active = 1 AND\n (\n gor_id_organization = {$dbOrgId} OR\n gor_accessible_by LIKE '%:{$dbOrgId}:%'\n )\n ORDER BY gor_name";
$data['can_access'] = $this->db->fetchPairs($sql);
natsort($data['can_access']);
} catch (\Exception $e) {
$data['can_access'] = array();
}
// \MUtil_Echo::track($sql, $data['can_access']);
if (array_key_exists('gor_url_base', $data) && ($baseUrls = explode(' ', $data['gor_url_base']))) {
$data['base_url'] = reset($baseUrls);
}
} else {
$data = $this->_noOrganization;
$data['gor_id_organization'] = $id;
}
return $data;
}
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->_adapter->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = str_replace('\\', '\\\\', $de);
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->_adapter->quote($q);
$qe = substr($qe, 1, 2);
$qe = str_replace('\\', '\\\\', $qe);
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/{$q}({$qe}|\\\\{2}|[^{$q}])*{$q}/", '', $sql);
// remove 'foo\'bar'
if (!empty($q)) {
$sql = preg_replace("/{$q}({$qe}|[^{$q}])*{$q}/", '', $sql);
}
return $sql;
}
/**
* Generates an array of SQL insert statements that
* will save the current
*
* @param array $resources
* @access public
* @return string
*/
public function generateInserts(array $resources)
{
$quotedName = $this->_db->quoteIdentifier('name');
$quotedDescription = $this->_db->quoteIdentifier('description');
$quotedFlagsTable = $this->_db->quoteIdentifier('flags');
$insertResourceTemplate = sprintf('INSERT IGNORE INTO %s (%s, %s) VALUES (?, ?);', $quotedFlagsTable, $quotedName, $quotedDescription);
$selectResourceTemplate = sprintf('SET @flag_id := (SELECT id FROM %s WHERE %s = ?);', $quotedFlagsTable, $quotedName);
$insertPrivilegeTemplate = '(@flag_id, %s, %s)';
$inserts = array();
foreach ($resources as $resource) {
// ready the insert resource query
$insertResourceSql = $this->_db->quoteInto($insertResourceTemplate, $resource['name'], NULL, 1);
$insertResourceSql = $this->_db->quoteInto($insertResourceSql, $resource['description'], NULL, 1);
// ready the select resource query
$selectResourceSql = $this->_db->quoteInto($selectResourceTemplate, $resource['name']);
// ready the insert privilege query
$insertPrivilegeSql = sprintf('INSERT IGNORE INTO %s (%s, %s, %s) VALUES ', $this->_db->quoteIdentifier('privileges'), $this->_db->quoteIdentifier('flag_id'), $quotedName, $quotedDescription);
$insertPrivilegeSqlParts = array();
foreach ($resource['methods'] as $method) {
$insertPrivilegeSqlParts[] = sprintf($insertPrivilegeTemplate, $this->_db->quote($method['name']), $this->_db->quote($method['description']));
}
$inserts[] = $insertResourceSql . PHP_EOL . $selectResourceSql . PHP_EOL . $insertPrivilegeSql . PHP_EOL . "\t" . implode(',' . PHP_EOL . "\t", $insertPrivilegeSqlParts) . ';' . PHP_EOL;
}
return $inserts;
}
/**
* Remove the unanswered tokens for inactive rounds.
*
* @param \Gems_Tracker_RespondentTrack $respTrack The respondent track to check
* @param int $userId Id of the user who takes the action (for logging)
* @return int The number of tokens changed by this code
*/
protected function removeInactiveRounds(\Gems_Tracker_RespondentTrack $respTrack, $userId)
{
$qTrackId = $this->db->quote($this->_trackId);
$qRespTrackId = $this->db->quote($respTrack->getRespondentTrackId());
$orgId = $this->db->quote($respTrack->getOrganizationId());
$where = "gto_start_time IS NULL AND\n gto_id_respondent_track = {$qRespTrackId} AND\n gto_id_round != 0 AND\n gto_id_round IN (SELECT gro_id_round\n FROM gems__rounds\n WHERE (gro_active = 0 OR gro_organizations NOT LIKE CONCAT('%|',{$orgId},'|%')) AND\n gro_id_track = {$qTrackId})";
return $this->db->delete('gems__tokens', $where);
}
/**
* returns where statement for fulltext search index
*
* @param $fields
* @param $searchstring
*/
public function buildFulltextSearchWhere($fields, $searchstring)
{
$columnNames = array();
foreach ($fields as $c) {
$columnNames[] = $this->db->quoteIdentifier($c);
}
return 'MATCH (' . implode(",", $columnNames) . ') AGAINST (' . $this->db->quote($searchstring) . ' IN BOOLEAN MODE)';
}
/**
* Get the status keys for active agenda items as a quoted db query string for use in "x IN (?)"
*
* @return \Zend_Db_Expr
*/
public function getStatusKeysInactiveDbQuoted()
{
$codes = array();
foreach ($this->getStatusCodesInactive() as $key => $label) {
$codes[] = $this->db->quote($key);
}
return new \Zend_Db_Expr(implode(", ", $codes));
}
/**
* Queries the minecraft server specified and returns an array with information on the server
* @param Zend_Db_Adapter_Abstract $db
* @param $minecraftServer
*/
public function queryMinecraftServer($db, $minecraftServer)
{
$status = array();
switch ($minecraftServer['query_type']) {
case 'full_status':
$query = new HeroDev_MinecraftStatus_Helper_GS4QueryHelper();
try {
$query->connect($minecraftServer['address'], $minecraftServer['query_port']);
$status = $query->getLongStatus();
if (isset($status['playerList'])) {
//Try to generate a profile link for every player from their minecraft username. We do this now to minimize queries later on.
foreach ($status['playerList'] as $key => $player) {
$status['playerList'][$key] = array('username' => $player, 'profileLink' => self::getUserHref($player));
}
}
$status = array_merge($status, array("online" => 1));
} catch (GS4QueryException $e) {
$status = array("online" => 0, "error" => $e->getMessage());
}
break;
case 'short_status':
$query = new HeroDev_MinecraftStatus_Helper_GS4QueryHelper();
try {
$query->connect($minecraftServer['address'], $minecraftServer['query_port']);
$status = $query->getShortStatus();
$status = array_merge($status, array("online" => 1));
} catch (GS4QueryException $e) {
$status = array("online" => 0, "error" => $e->getMessage());
}
break;
case 'serverlistping':
$query = new HeroDev_MinecraftStatus_Helper_ServerListPingHelper();
try {
$status = $query->pingServer($minecraftServer['address'], $minecraftServer['query_port']);
$status = array_merge($status, array("online" => 1));
} catch (ServerListPingException $e) {
$status = array("online" => 0, "error" => $e->getMessage());
}
break;
}
//Update the status data
$db->update('xf_herodev_minecraft_server', array('query_data' => serialize($status)), 'minecraft_server_id = ' . $db->quote($minecraftServer['minecraft_server_id']));
//Update the last query time
$db->update('xf_herodev_minecraft_server', array('last_query_date' => XenForo_Application::$time), 'minecraft_server_id = ' . $db->quote($minecraftServer['minecraft_server_id']));
return $status;
}
/**
* Joins SQL text and bound params into a string.
* This emulates SQL parameters by quoting the values directly into the
* SQL string.
*
* @return string
*/
protected function _joinSql()
{
$sql = $this->_sqlSplit;
foreach ($this->_bindParam as $key => $val) {
$position = $key * 2 + 1;
$sql[$position] = $this->_adapter->quote($val);
}
return implode('', $sql);
}
/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
/*
* CASE WHEN `status` = 'enabled' THEN (CASE WHEN NOW() > `expires_at` THEN 'expired'
* WHEN (`login_failures` > 5 AND `last_login_failure_at` + INTERVAL 15 MINUTE > NOW())
* THEN 'blocked' ELSE 'enabled' END) ELSE 'disabled' END
*/
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (';
$statusSQL .= 'CASE WHEN ' . Tinebase_Backend_Sql_Command::setDate($this->_db, 'NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' WHEN (' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . " > {$this->_maxLoginFailures} AND " . Tinebase_Backend_Sql_Command::setDate($this->_db, $this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure'])) . " + INTERVAL '{$this->_blockTime}' MINUTE > " . Tinebase_Backend_Sql_Command::setDate($this->_db, 'NOW()') . ") THEN 'blocked'" . ' ELSE ' . $this->_db->quote('enabled') . ' END) ELSE ' . $this->_db->quote('disabled') . ' END ';
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility'))->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id'));
return $select;
}
/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
$interval = $this->_dbCommand->getDynamicInterval('SECOND', '1', 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 5 THEN 60 ELSE POWER(2, ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ') END');
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 0 AND ' . $this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure']) . ' + ' . $interval . ' > NOW()) THEN ' . $this->_db->quote('blocked') . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' ELSE ' . $this->_db->quote('disabled') . ' END';
$fields = array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility', 'NOW()');
// modlog fields have been added later
if ($this->_userTableHasModlogFields()) {
$fields = array_merge($fields, array('created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq'));
}
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', $fields)->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id'));
return $select;
}
/**
*
* @param array $description
* @param string $newColumnName
*/
protected function _alterTable(array $description, $newColumnName = null)
{
if (!$newColumnName) {
$newColumnName = $description['COLUMN_NAME'];
}
$this->_db->query('
ALTER TABLE ' . $description['TABLE_NAME'] . '
CHANGE ' . $description['COLUMN_NAME'] . ' ' . $newColumnName . '
' . $description['DATA_TYPE'] . ($description['LENGTH'] ? '(' . $description['LENGTH'] . ')' : '') . '
' . ($description['NULLABLE'] ? 'NULL' : 'NOT NULL') . '
DEFAULT ' . $this->_db->quote($description['DEFAULT']) . '
' . ($description['UNSIGNED'] ? 'UNSIGNED' : '') . '
');
}
/**
*
* @param string $orgId
* @param string $userId
* @return boolean
*/
private function _clearFailure($orgId, $userId)
{
if (!$orgId || !$userId) {
return false;
}
$table = 'md_user';
$bind = array('login_retry' => 0, 'unlock_time' => null);
$where = 'org_id = ' . $this->_db->quote($orgId) . ' AND ' . 'user_id = ' . $this->_db->quote($userId);
try {
$this->_db->update($table, $bind, $where);
} catch (Zend_Db_Exception $e) {
return false;
}
return true;
}
/**
* Render and return the given filter expression
*
* @param Filter $filter
*
* @return string
*/
protected function renderFilterExpression(Filter $filter)
{
$column = $filter->getColumn();
$sign = $filter->getSign();
$value = $filter->getExpression();
if (is_array($value) && $sign === '=') {
// TODO: Should we support this? Doesn't work for blub*
return $column . ' IN (' . $this->dbAdapter->quote($value) . ')';
} elseif ($sign === '=' && strpos($value, '*') !== false) {
return $column . ' LIKE ' . $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value));
} elseif ($sign === '!=' && strpos($value, '*') !== false) {
return $column . ' NOT LIKE ' . $this->dbAdapter->quote(preg_replace('~\\*~', '%', $value));
} else {
return $column . ' ' . $sign . ' ' . $this->dbAdapter->quote($value);
}
}
protected function _bootstrap(array $config)
{
if ($this->_sourceDb) {
// already run
return;
}
@set_time_limit(0);
$this->_config = $config;
$this->_sourceDb = Zend_Db::factory('mysqli', array('host' => $config['db']['host'], 'port' => $config['db']['port'], 'username' => $config['db']['username'], 'password' => $config['db']['password'], 'dbname' => $config['db']['dbname'], 'charset' => 'utf8'));
$this->_prefix = preg_replace('/[^a-z0-9_]/i', '', $config['db']['prefix']);
$this->_defaultLang = $this->_sourceDb->fetchOne("\n\t\t\tSELECT config_value\n\t\t\tFROM " . $this->_prefix . "config\n\t\t\tWHERE config_name = 'default_lang'\n\t\t");
$this->_defaultLangId = $this->_sourceDb->fetchOne('
SELECT lang_id
FROM ' . $this->_prefix . 'lang
WHERE lang_iso = ' . $this->_sourceDb->quote($this->_defaultLang));
}
protected function escapeForSql($value)
{
// bindParam? bindValue?
if (is_array($value)) {
$ret = array();
foreach ($value as $val) {
$ret[] = $this->escapeForSql($val);
}
return implode(', ', $ret);
} else {
//if (preg_match('/^\d+$/', $value)) {
// return $value;
//} else {
return $this->db->quote($value);
//}
}
}
public function saveSessionToSource($sessionId, $isUpdate)
{
if ($this->_cache) {
// same behavior on insert and updated
$this->_cache->save(serialize($this->_session), $this->_getSessionCacheName($sessionId), array(), $this->_config['lifetime']);
} else {
if ($isUpdate) {
// db update
$data = array('expiry_date' => XenForo_Application::$time + $this->_config['lifetime']);
if ($this->_dataChanged) {
$data['session_data'] = serialize($this->_session);
}
$this->_db->update($this->_config['table'], $data, 'session_id = ' . $this->_db->quote($sessionId));
} else {
// db insert
$this->_db->insert($this->_config['table'], array('session_id' => $sessionId, 'session_data' => serialize($this->_session), 'expiry_date' => XenForo_Application::$time + $this->_config['lifetime']));
}
}
}
/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
/*
* CASE WHEN `status` = 'enabled' THEN (CASE WHEN DATE(NOW()) > `expires_at` THEN 'expired'
* WHEN ( `login_failures` > 5 AND DATE(`last_login_failure_at`) + INTERVAL '15' MINUTE > DATE(NOW())) THEN 'blocked'
* ELSE 'enabled' END) WHEN `status` = 'expired' THEN 'expired' ELSE 'disabled' END
*/
$maxLoginFailures = Tinebase_Config::getInstance()->get(Tinebase_Config::MAX_LOGIN_FAILURES, 5);
if ($maxLoginFailures > 0) {
$loginFailuresCondition = 'WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . " > {$maxLoginFailures} AND " . $this->_dbCommand->setDate($this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure'])) . " + INTERVAL '{$this->_blockTime}' MINUTE > " . $this->_dbCommand->setDate('NOW()') . ") THEN 'blocked'";
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' User blocking disabled.');
}
$loginFailuresCondition = '';
}
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' ' . $loginFailuresCondition . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' ELSE ' . $this->_db->quote('disabled') . ' END';
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility', 'created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq'))->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id'));
return $select;
}
/**
* Internal function for creating the where clause
*
* @param string $condition
* @param string $value optional
* @param string $type optional
* @param boolean $bool true = AND, false = OR
* @return string clause
*/
protected function _where($condition, $value = null, $type = null, $bool = true)
{
if (is_array($value)) {
$count = substr_count($condition, '?');
foreach ($value as $key => $token) {
if (is_numeric($key)) {
if ($count > 0) {
$condition = $this->_adapter->quoteInto($condition, $token, null, 1);
} else {
$condition = $this->_adapter->quoteInto($condition, $token, $type);
}
--$count;
} else {
if ($key[0] !== ":") {
$key = ":" . $key;
}
if (strpos($condition, $key) === false) {
throw new Zend_Db_Select_Exception("Invalid token '{$key}' given");
}
$condition = str_replace($key, $this->_adapter->quote($token), $condition);
}
}
} else {
if ($value !== null) {
$condition = $this->_adapter->quoteInto($condition, $value, $type);
}
}
$cond = "";
if ($this->_parts[self::WHERE]) {
if ($bool === true) {
$cond = "AND ";
} else {
$cond = "OR ";
}
}
$condition = $cond . "({$condition})";
return $condition;
}
/**
* @return Zend_Auth_Result
*/
public function authenticate()
{
do {
// 验证串超时
if (abs(time() - $this->_timeStamp) > 1800) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'timeout';
break;
}
// 验证串有效性
if (md5($this->_account . $this->_orgId . $this->_timeStamp . $this->_authKey) != $this->_auth) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'invalid';
break;
}
$orgId = $this->_orgId;
// 读取图度组织信息
$sql = "SELECT org_id AS orgid, ts_id AS tsid, expire_date AS expiredate, status AS orgstatus, 'PASSPORT' AS admin_type, " . "3 AS admintype, " . $this->_db->quote('ACCOUNT^' . $this->_account) . " AS userid, '{$orgId}.tudu.com' AS domainname, 1 AS ispassport, " . $this->_db->quote($this->_account) . ' AS truename ' . "FROM md_organization " . "WHERE org_id = " . $this->_db->quote($orgId);
//echo $sql;exit();
$row = $this->_db->fetchRow($sql);
if (!$row) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE_IDENTITY_NOT_FOUND;
$this->_resultInfo['message'][] = 'not found';
break;
}
if (!empty($row['expiredate']) && strtotime($row['expiredate']) < time()) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'expired';
break;
}
$row['truename'] = $this->_account;
$this->_identity = $row;
$this->_resultInfo['code'] = Zend_Auth_Result::SUCCESS;
$this->_resultInfo['message'][] = 'success';
} while (false);
return new Zend_Auth_Result($this->_resultInfo['code'], $this->_identity, $this->_resultInfo['message']);
}
/**
* Insert multiple rows (not safe but fast)
* @param array $data
* @param integer $chunkSize
* @return boolean
*/
public function multiInsert($data, $chunkSize = 300)
{
if (empty($data)) {
return true;
}
$chunks = array_chunk($data, $chunkSize);
$keys = array_keys($data[key($data)]);
foreach ($keys as &$key) {
$key = $this->_db->quoteIdentifier($key);
}
unset($key);
$keys = implode(',', $keys);
foreach ($chunks as $rowset) {
foreach ($rowset as &$row) {
foreach ($row as &$colValue) {
if (is_bool($colValue)) {
$colValue = intval($colValue);
} elseif (is_null($colValue)) {
$colValue = 'NULL';
} else {
$colValue = $this->_db->quote($colValue);
}
}
unset($colValue);
$row = implode(',', $row);
}
unset($row);
$sql = 'INSERT INTO ' . $this->table() . ' (' . $keys . ') ' . "\n" . ' VALUES ' . "\n" . '(' . implode(')' . "\n" . ',(', array_values($rowset)) . ') ' . "\n" . '';
try {
$this->_db->query($sql);
} catch (Exception $e) {
$this->logError('multiInsert: ' . $e->getMessage());
return false;
}
}
return true;
}
/**
* @return Zend_Auth_Result
*/
public function authenticate()
{
$sql = "SELECT u.org_id AS orgid, u.user_id AS userid, ui.true_name AS truename, " . "ui.password, u.status, u.expire_date AS expiredate, o.ts_id AS tsid, " . "a.admin_level AS adminlevel, a.admin_type AS admintype, o.status AS orgstatus , ud.skin " . "FROM md_user u " . "LEFT JOIN md_organization o ON u.org_id = o.org_id " . "LEFT JOIN md_user_info ui ON u.org_id = ui.org_id AND u.user_id = ui.user_id " . "LEFT JOIN md_user_data ud ON u.org_id = ud.org_id AND u.user_id = ud.user_id " . "INNER JOIN md_site_admin a ON u.user_id = a.user_id " . "WHERE u.user_id = " . $this->_db->quote($this->_userId) . " " . "AND u.org_id = " . $this->_db->quote($this->_orgId);
$row = $this->_db->fetchRow($sql);
do {
if (!$row) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE_IDENTITY_NOT_FOUND;
$this->_resultInfo['message'][] = 'not found';
break;
}
if (!empty($row['expiredate']) && strtotime($row['expiredate']) < time()) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'expired';
break;
}
if ($row['orgstatus'] == 1) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'org forbid';
break;
}
if ($row['status'] == 0) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'forbid';
break;
}
if (md5($this->_password) != $row['password']) {
$this->_resultInfo['code'] = Zend_Auth_Result::FAILURE;
$this->_resultInfo['message'][] = 'failure';
break;
}
$row['address'] = $row['userid'] . '@' . $row['domainname'];
$this->setIdentity($row);
$this->_resultInfo['code'] = Zend_Auth_Result::SUCCESS;
$this->_resultInfo['message'][] = 'success';
} while (false);
return new Zend_Auth_Result($this->_resultInfo['code'], $this->_identity, $this->_resultInfo['message']);
}
