/** * 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']); }