PHP Connection::quote方法代码示例

 /**
  * {@inheritDoc}
  */
 protected function doSetAll(array $values)
 {
     $this->runSQL('DELETE FROM `' . $this->tableName . '`');
     $rows = array();
     foreach ($values as $key => $value) {
         $this->runSQL('INSERT INTO ' . $this->tableName . ' (`key`, `value`) VALUES (' . $this->connection->quote($key) . ',' . $this->connection->quote(json_encode($value)) . ');');
     }
 }

 public function postUpdate()
 {
     $em = $this->container->get('doctrine.orm.entity_manager');
     $process = false;
     if (in_array('claro_forum_subject_temp', $this->conn->getSchemaManager()->listTableNames())) {
         $columns = $this->conn->getSchemaManager()->listTableColumns('claro_forum_subject_temp');
         foreach ($columns as $column) {
             if ($column->getName() === 'forum_id') {
                 $process = true;
                 break;
             }
         }
     }
     if ($process) {
         $this->log('restoring the subjects...');
         $forums = $em->getRepository('ClarolineForumBundle:Forum')->findAll();
         $sql = 'SELECT * FROM claro_forum_subject_temp WHERE forum_id = :forumId';
         $stmt = $this->conn->prepare($sql);
         foreach ($forums as $forum) {
             $category = new Category();
             $category->setName($forum->getResourceNode()->getName());
             $category->setForum($forum);
             $em->persist($category);
             $em->flush();
             $stmt->bindValue('forumId', $forum->getId());
             $stmt->execute();
             foreach ($stmt->fetchAll() as $rowsSubject) {
                 $this->conn->query("INSERT INTO claro_forum_subject VALUES (\n                        {$rowsSubject['id']},\n                        {$category->getId()},\n                        {$rowsSubject['user_id']},\n                        {$this->conn->quote($rowsSubject['title'])},\n                        '{$rowsSubject['created']}',\n                        '{$rowsSubject['updated']}',\n                        false\n                    )");
             }
         }
         $this->log('restoring the messages...');
         $this->conn->query('INSERT IGNORE INTO claro_forum_message SELECT * FROM claro_forum_message_temp');
         $this->conn->query('DROP TABLE claro_forum_message_temp');
         $this->conn->query('DROP TABLE claro_forum_subject_temp');
         $this->conn->query('DROP TABLE claro_forum_options');
     } else {
         $this->log('categories already added');
     }
     $widget = $em->getRepository('ClarolineCoreBundle:Widget\\Widget')->findBy(array('name' => 'claroline_forum_widget'));
     if (!$widget) {
         $this->log('adding the forum widget...');
         $plugin = $em->getRepository('ClarolineCoreBundle:Plugin')->findOneBy(array('vendorName' => 'Claroline', 'bundleName' => 'ForumBundle'));
         $widget = new Widget();
         $widget->setName('claroline_forum_widget');
         $widget->setDisplayableInDesktop(true);
         $widget->setDisplayableInWorkspace(true);
         $widget->setConfigurable(false);
         $widget->setExportable(false);
         $widget->setIcon('none');
         $widget->setPlugin($plugin);
         $em->persist($widget);
         $plugin->setHasOptions(true);
         $em->persist($widget);
         $em->flush();
     } else {
         $this->log('forum widget already added');
     }
 }

 public function generateSql($routeName, $type, array $state, array $title, array $description, array $alias, array $path, $parentRouteName = null, $template = null)
 {
     $sql = [];
     $currentDate = (new \DateTime())->format('y-m-d H:i:s');
     $sql[] = sprintf("SELECT @parentId:=id FROM `%s` WHERE `route_name` = '%s' LIMIT 1;", self::STRUCTURE_TABLE_NAME, $this->connection->quote($parentRouteName));
     $sql[] = sprintf("INSERT INTO `%s`\n            (\n                `parent_id`,\n                `created_at`,\n                `updated_at`,\n                `title`,\n                `description`,\n                `type`,\n                `alias`,\n                `path`,\n                `state`,\n                `template`,\n                `route_name`\n              )\n            VALUES (\n                @parentId,\n                '%s',\n                '%s',\n                '%s',\n                '%s',\n                '%s',\n                '%s',\n                '%s',\n                '%d',\n                '%s',\n                '%s'\n            )\n            ON DUPLICATE KEY UPDATE\n                `parent_id`=VALUES(`parent_id`),\n                `updated_at`=VALUES(`updated_at`),\n                `title`=VALUES(`title`),\n                `description`=VALUES(`description`),\n                `type`=VALUES(`type`),\n                `alias`=VALUES(`alias`),\n                `path`=VALUES(`path`),\n                `state`=VALUES(`state`),\n                `template`=VALUES(`template`)\n            ;", self::STRUCTURE_TABLE_NAME, $currentDate, $currentDate, $this->connection->quote($title[$this->defaultLocale]), $this->connection->quote($description[$this->defaultLocale]), $type, $this->connection->quote($alias[$this->defaultLocale]), $this->connection->quote($path[$this->defaultLocale]), (bool) $state[$this->defaultLocale] ? 1 : 0, $this->connection->quote($template), $this->connection->quote($routeName));
     $sql[] = sprintf("SELECT @structureId := id FROM `%s` WHERE route_name = '%s';", self::STRUCTURE_TABLE_NAME, $this->connection->quote($routeName));
     return implode("\n", $sql);
 }

 /**
  * Changes a value to a specific type.
  *
  * The original implementation of this method
  * had some vestiges of support for type options
  * (like a regular expression pattern for the regexp
  * type) . This seems to have long been unsupported
  * so now all bits of the type after <code>:</code>
  * are squashed.
  *
  * @param  mixed  $value value to bind
  * @param  string $type  type of value
  * @return mixed  modified value
  */
 public function getBindVarValue($value, $type)
 {
     if (false !== strpos($type, ':')) {
         $type = strstr($type, ':', true);
     }
     switch ($type) {
         case 'csv':
         case 'passthru':
             return $value;
             break;
         case 'floatval':
             return is_numeric($value) ? $value : 0;
             break;
         case 'integer':
             return (int) $value;
             break;
         case 'currency':
         case 'date':
         case 'string':
             return $this->conn->quote($value);
             break;
         case 'noquotestring':
             return $this->prepareInput($value);
         case 'regexp':
             return $this->prepareInput(preg_quote($value));
     }
     throw new RuntimeException(sprintf('Type %s does not exist', $type));
 }

 /**
  * Returns the SQL to check if a value is one in a set of
  * given values..
  *
  * in() accepts an arbitrary number of parameters. The first parameter
  * must always specify the value that should be matched against. Successive
  * parameters must contain a logical expression or an array with logical
  * expressions.  These expressions will be matched against the first
  * parameter.
  *
  * Example:
  * <code>
  * $q->select( '*' )->from( 'table' )
  *                  ->where( $q->expr->in( 'id', 1, 2, 3 ) );
  * </code>
  *
  * @throws \eZ\Publish\Core\Persistence\Database\QueryException if called with less than two
  *         parameters.
  * @throws \eZ\Publish\Core\Persistence\Database\QueryException if the 2nd parameter is an
  *         empty array.
  *
  * @param string $column the value that should be matched against
  * @param string|array(string) $... values that will be matched against $column
  *
  * @return string logical expression
  */
 public function in($column)
 {
     $args = func_get_args();
     if (count($args) < 2) {
         throw new QueryException('Expected two or more parameters to in()');
     }
     if (is_array($args[1])) {
         $values = array_values($args[1]);
     } else {
         $values = array_slice($args, 1);
     }
     // Special handling of sub selects to avoid double braces
     if (count($values) === 1 && $values[0] instanceof SubselectDoctrineQuery) {
         return "{$column} IN " . $values[0]->getQuery();
     }
     if (count($values) == 0) {
         throw new QueryException('At least one element is required as value.');
     }
     foreach ($values as $key => $value) {
         switch (true) {
             case $value instanceof SubselectDoctrineQuery:
                 $values[$key] = $value->getQuery();
                 break;
             case is_int($value):
             case is_float($value):
                 $values[$key] = (string) $value;
                 break;
             default:
                 $values[$key] = $this->connection->quote($value);
         }
     }
     return "{$column} IN ( " . implode(', ', $values) . ' )';
 }

 /**
  * Generates a single query builder from the provided keywords array.
  *
  * @param Keyword[] $keywords
  * @param $tables
  * @return QueryBuilder
  */
 private function buildQueryFromKeywords($keywords, $tables)
 {
     $keywordSelection = [];
     foreach ($keywords as $match) {
         $keywordSelection[] = 'SELECT ' . $match->getRelevance() . ' as relevance, ' . $this->connection->quote($match->getTerm()) . ' as term, ' . $match->getId() . ' as keywordID';
     }
     $keywordSelection = implode("\n             UNION ALL ", $keywordSelection);
     $tablesSql = [];
     foreach ($tables as $table) {
         $query = $this->connection->createQueryBuilder();
         $alias = 'st' . $table['tableID'];
         $query->select(['MAX(sf.relevance * sm.relevance) as relevance', 'sm.keywordID']);
         $query->from('(' . $keywordSelection . ')', 'sm');
         $query->innerJoin('sm', 's_search_index', 'si', 'sm.keywordID = si.keywordID');
         $query->innerJoin('si', 's_search_fields', 'sf', 'si.fieldID = sf.id AND sf.relevance != 0 AND sf.tableID = ' . $table['tableID']);
         $query->groupBy('articleID')->addGroupBy('sm.term')->addGroupBy('sf.id');
         if (!empty($table['referenz_table'])) {
             $query->addSelect($alias . '.articleID as articleID');
             $query->innerJoin('si', $table['referenz_table'], $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
         } elseif (!empty($table['foreign_key'])) {
             $query->addSelect($alias . '.id as articleID');
             $query->innerJoin('si', 's_articles', $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
         } else {
             $query->addSelect('si.elementID as articleID');
         }
         $tablesSql[] = $query->getSQL();
     }
     $tablesSql = "\n" . implode("\n     UNION ALL\n", $tablesSql);
     $subQuery = $this->connection->createQueryBuilder();
     $subQuery->select(['srd.articleID', 'SUM(srd.relevance) as relevance']);
     $subQuery->from("(" . $tablesSql . ')', 'srd')->groupBy('srd.articleID')->setMaxResults(5000);
     $query = $this->connection->createQueryBuilder();
     $query->from("(" . $subQuery->getSQL() . ')', 'sr')->innerJoin('sr', 's_articles', 'a', 'a.id = sr.articleID');
     return $query;
 }

 /**
  * Revert data to the previous version.
  */
 public function sqlDownData()
 {
     // Map columns to custom fields.
     $colmap = array();
     foreach ($this->fieldmap as $field => $col) {
         $colmap[$col] = $field;
     }
     // Test that the v2 columns actually exist.
     $existingCols = $this->conn->getSchemaManager()->listTableColumns($this->usersTable);
     $existingColnames = array_map(function ($col) {
         return $col->getName();
     }, $existingCols);
     foreach ($this->fieldmap as $col) {
         if (!in_array($col, $existingColnames)) {
             throw new \RuntimeException('Cannot migrate down because current schema is not v2. (Missing column "' . $this->usersTable . '.' . $col . '").');
         }
     }
     // Get user columns to revert back to custom fields.
     $userData = $this->conn->fetchAll('SELECT id AS user_id, ' . implode(', ', $this->fieldmap) . ' FROM ' . $this->conn->quoteIdentifier($this->usersTable));
     $queries = array();
     foreach ($userData as $row) {
         foreach ($this->fieldmap as $col) {
             if ($row[$col] !== null) {
                 $queries[] = 'INSERT INTO ' . $this->conn->quoteIdentifier($this->userCustomFieldsTable) . ' (user_id, attribute, value) VALUES' . ' (' . $this->conn->quote($row['user_id'], Type::INTEGER) . ', ' . $this->conn->quote($colmap[$col], Type::STRING) . ', ' . $this->conn->quote($row[$col], Type::STRING) . ')';
             }
         }
     }
     return $queries;
 }

 /**
  * @param mixed $value
  * @param Type  $type
  *
  * @return string
  */
 protected function quoteValue($value, Type $type)
 {
     // Don't quote numbers as some don't follow standards for casting
     if (is_scalar($value) && ctype_digit((string) $value)) {
         return (string) $value;
     }
     return $this->connection->quote($value, $type->getBindingType());
 }

 public function getRawSql()
 {
     $sql = $this->getSql();
     foreach ($this->qb->getParameters() as $key => $value) {
         $sql = str_replace(':' . $key, $this->connection->quote($value), $sql);
     }
     return $sql;
 }

 /**
  * Update object
  *
  * @param StorableInterface $obj
  * @param string            $type
  *
  * @return void
  */
 protected function update(StorableInterface $obj, $type)
 {
     $qb = $this->con->createQueryBuilder()->update($this->tables[$type])->where('id = :id')->setParameter('id', $obj->getId(), \PDO::PARAM_INT);
     foreach ($obj->getStorableData() as $name => $value) {
         $qb->set($name, $this->con->quote($value));
     }
     return $qb->execute();
 }

 public function lookupRange(&$escapedField, &$rawValue, &$negate)
 {
     if (!is_array($rawValue) || count($rawValue) !== 2) {
         throw new \InvalidArgumentException("value for RANGE lookup must me array with two elements");
     }
     $rawValue = Expr($this->db->quote($rawValue[0]) . ' AND ' . $this->db->quote($rawValue[1]));
     return 'BETWEEN %s';
 }

 /**
  * Creates a left join list for translations
  * on used query components
  *
  * @todo: make it cleaner
  * @return string
  */
 private function prepareTranslatedComponents()
 {
     $q = $this->getQuery();
     $locale = $q->getHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE);
     if (!$locale) {
         // use from listener
         $locale = $this->listener->getListenerLocale();
     }
     $defaultLocale = $this->listener->getDefaultLocale();
     if ($locale === $defaultLocale && !$this->listener->getPersistDefaultLocaleTranslation()) {
         // Skip preparation as there's no need to translate anything
         return;
     }
     $em = $this->getEntityManager();
     $ea = new TranslatableEventAdapter();
     $ea->setEntityManager($em);
     $joinStrategy = $q->getHint(TranslatableListener::HINT_INNER_JOIN) ? 'INNER' : 'LEFT';
     foreach ($this->translatedComponents as $dqlAlias => $comp) {
         $meta = $comp['metadata'];
         $config = $this->listener->getConfiguration($em, $meta->name);
         $transClass = $this->listener->getTranslationClass($ea, $meta->name);
         $transMeta = $em->getClassMetadata($transClass);
         $transTable = $transMeta->getQuotedTableName($this->platform);
         foreach ($config['fields'] as $field) {
             $compTblAlias = $this->walkIdentificationVariable($dqlAlias, $field);
             $tblAlias = $this->getSQLTableAlias('trans' . $compTblAlias . $field);
             $sql = " {$joinStrategy} JOIN " . $transTable . ' ' . $tblAlias;
             $sql .= ' ON ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('locale', $this->platform) . ' = ' . $this->conn->quote($locale);
             $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('field', $this->platform) . ' = ' . $this->conn->quote($field);
             $identifier = $meta->getSingleIdentifierFieldName();
             $idColName = $meta->getQuotedColumnName($identifier, $this->platform);
             if ($ea->usesPersonalTranslation($transClass)) {
                 $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getSingleAssociationJoinColumnName('object') . ' = ' . $compTblAlias . '.' . $idColName;
             } else {
                 $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('objectClass', $this->platform) . ' = ' . $this->conn->quote($config['useObjectClass']);
                 $mappingFK = $transMeta->getFieldMapping('foreignKey');
                 $mappingPK = $meta->getFieldMapping($identifier);
                 $fkColName = $this->getCastedForeignKey($compTblAlias . '.' . $idColName, $mappingFK['type'], $mappingPK['type']);
                 $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('foreignKey', $this->platform) . ' = ' . $fkColName;
             }
             isset($this->components[$dqlAlias]) ? $this->components[$dqlAlias] .= $sql : ($this->components[$dqlAlias] = $sql);
             $originalField = $compTblAlias . '.' . $meta->getQuotedColumnName($field, $this->platform);
             $substituteField = $tblAlias . '.' . $transMeta->getQuotedColumnName('content', $this->platform);
             // Treat translation as original field type
             $fieldMapping = $meta->getFieldMapping($field);
             if ($this->platform instanceof MySqlPlatform && in_array($fieldMapping["type"], array("decimal")) || !$this->platform instanceof MySqlPlatform && !in_array($fieldMapping["type"], array("datetime", "datetimetz", "date", "time"))) {
                 $type = Type::getType($fieldMapping["type"]);
                 $substituteField = 'CAST(' . $substituteField . ' AS ' . $type->getSQLDeclaration($fieldMapping, $this->platform) . ')';
             }
             // Fallback to original if was asked for
             if ($this->needsFallback() && (!isset($config['fallback'][$field]) || $config['fallback'][$field]) || !$this->needsFallback() && isset($config['fallback'][$field]) && $config['fallback'][$field]) {
                 $substituteField = 'COALESCE(' . $substituteField . ', ' . $originalField . ')';
             }
             $this->replacements[$originalField] = $substituteField;
         }
     }
 }

    /**
     * Constructs the SQL for retrieving the primary key of the given object
     * identity.
     *
     * @param ObjectIdentityInterface $oid
     *
     * @return string
     */
    protected function getSelectObjectIdentityIdSql(ObjectIdentityInterface $oid)
    {
        $query = <<<QUERY
            SELECT o.id
            FROM %s o
            INNER JOIN %s c ON c.id = o.class_id
            WHERE o.object_identifier = %s AND c.class_type = %s
QUERY;
        return sprintf($query, $this->options['oid_table_name'], $this->options['class_table_name'], $this->connection->quote((string) $oid->getIdentifier()), $this->connection->quote((string) $oid->getType()));
    }

 /**
  * {@inheritdoc}
  */
 public function startQuery($sql, array $params = null, array $types = null)
 {
     if ($params) {
         list($sql, $params, $types) = SQLParserUtils::expandListParameters($sql, $params, $types);
         $query = vsprintf(str_replace('?', "%s", $sql), call_user_func(function () use($params, $types) {
             $quotedParams = array();
             foreach ($params as $typeIndex => $value) {
                 $quotedParams[] = $this->connection->quote($value, $types[$typeIndex]);
             }
             return $quotedParams;
         }));
     } else {
         $query = $sql;
     }
     $this->lastQuery = $query;
     if ($this->outputQuery) {
         $this->output($query);
     }
 }

 /**
  * {@inheritdoc}
  */
 public function quote($value)
 {
     if (ConnectionDecoratorChain::isDecorate()) {
         return (new ConnectionDecoratorChain($this, $this->decorators))->quote($value);
     }
     try {
         return $this->conn->quote($value);
     } catch (\Exception $e) {
         throw $this->convertException($e);
     }
 }