PHP Zend_Db_Adapter_Abstract::quote方法代码示例

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