/**
* Build a condition to match a table name against a standard information_schema.
*
* MySQL uses databases like schemas rather than catalogs so when we build
* a condition to query the information_schema.tables, we set the default
* database as the schema unless specified otherwise, and exclude table_catalog
* from the condition criteria.
*/
protected function buildTableNameCondition($table_name, $operator = '=', $add_prefix = TRUE)
{
$table_info = $this->getPrefixInfo($table_name, $add_prefix);
$condition = new Condition('AND');
$condition->condition('table_schema', $table_info['database']);
$condition->condition('table_name', $table_info['table'], $operator);
return $condition;
}
/**
* {@inheritdoc}
*/
public function query()
{
$query = $this->select('file_managed', 'f')->fields('f')->orderBy('f.timestamp');
// Filter by scheme(s), if configured.
if (isset($this->configuration['scheme'])) {
$schemes = array();
// Accept either a single scheme, or a list.
foreach ((array) $this->configuration['scheme'] as $scheme) {
$schemes[] = rtrim($scheme) . '://';
}
$schemes = array_map([$this->getDatabase(), 'escapeLike'], $schemes);
// uri LIKE 'public://%' OR uri LIKE 'private://%'
$conditions = new Condition('OR');
foreach ($schemes as $scheme) {
$conditions->condition('uri', $scheme . '%', 'LIKE');
}
$query->condition($conditions);
}
return $query;
}
/**
* Creates a query condition from an array of node access grants.
*
* @param array $node_access_grants
* An array of grants, as returned by node_access_grants().
* @return \Drupal\Core\Database\Query\Condition
* A condition object to be passed to $query->condition().
*
* @see node_access_grants()
*/
protected static function buildGrantsQueryCondition(array $node_access_grants)
{
$grants = new Condition("OR");
foreach ($node_access_grants as $realm => $gids) {
if (!empty($gids)) {
$and = new Condition('AND');
$grants->condition($and->condition('gid', $gids, 'IN')->condition('realm', $realm));
}
}
return $grants;
}
/**
* Queries to find search results, and sets status messages.
*
* This method can assume that $this->isSearchExecutable() has already been
* checked and returned TRUE.
*
* @return \Drupal\Core\Database\StatementInterface|null
* Results from search query execute() method, or NULL if the search
* failed.
*/
protected function findResults()
{
$keys = $this->keywords;
// Build matching conditions.
$query = $this->database->select('search_index', 'i', array('target' => 'replica'))->extend('Drupal\\search\\SearchQuery')->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender');
$query->join('node_field_data', 'n', 'n.nid = i.sid');
$query->condition('n.status', 1)->addTag('node_access')->searchExpression($keys, $this->getPluginId());
// Handle advanced search filters in the f query string.
// \Drupal::request()->query->get('f') is an array that looks like this in
// the URL: ?f[]=type:page&f[]=term:27&f[]=term:13&f[]=langcode:en
// So $parameters['f'] looks like:
// array('type:page', 'term:27', 'term:13', 'langcode:en');
// We need to parse this out into query conditions, some of which go into
// the keywords string, and some of which are separate conditions.
$parameters = $this->getParameters();
if (!empty($parameters['f']) && is_array($parameters['f'])) {
$filters = array();
// Match any query value that is an expected option and a value
// separated by ':' like 'term:27'.
$pattern = '/^(' . implode('|', array_keys($this->advanced)) . '):([^ ]*)/i';
foreach ($parameters['f'] as $item) {
if (preg_match($pattern, $item, $m)) {
// Use the matched value as the array key to eliminate duplicates.
$filters[$m[1]][$m[2]] = $m[2];
}
}
// Now turn these into query conditions. This assumes that everything in
// $filters is a known type of advanced search.
foreach ($filters as $option => $matched) {
$info = $this->advanced[$option];
// Insert additional conditions. By default, all use the OR operator.
$operator = empty($info['operator']) ? 'OR' : $info['operator'];
$where = new Condition($operator);
foreach ($matched as $value) {
$where->condition($info['column'], $value);
}
$query->condition($where);
if (!empty($info['join'])) {
$query->join($info['join']['table'], $info['join']['alias'], $info['join']['condition']);
}
}
}
// Add the ranking expressions.
$this->addNodeRankings($query);
// Run the query.
$find = $query->fields('i', array('langcode'))->groupBy('i.langcode')->limit(10)->execute();
// Check query status and set messages if needed.
$status = $query->getStatus();
if ($status & SearchQuery::EXPRESSIONS_IGNORED) {
drupal_set_message($this->t('Your search used too many AND/OR expressions. Only the first @count terms were included in this search.', array('@count' => $this->searchSettings->get('and_or_limit'))), 'warning');
}
if ($status & SearchQuery::LOWER_CASE_OR) {
drupal_set_message($this->t('Search for either of the two terms with uppercase <strong>OR</strong>. For example, <strong>cats OR dogs</strong>.'), 'warning');
}
if ($status & SearchQuery::NO_POSITIVE_KEYWORDS) {
drupal_set_message($this->formatPlural($this->searchSettings->get('index.minimum_word_size'), 'You must include at least one keyword to match in the content, and punctuation is ignored.', 'You must include at least one keyword to match in the content. Keywords must be at least @count characters, and punctuation is ignored.'), 'warning');
}
return $find;
}
/**
* Build a condition to match a table name against a standard information_schema.
*
* The information_schema is a SQL standard that provides information about the
* database server and the databases, schemas, tables, columns and users within
* it. This makes information_schema a useful tool to use across the drupal
* database drivers and is used by a few different functions. The function below
* describes the conditions to be meet when querying information_schema.tables
* for drupal tables or information associated with drupal tables. Even though
* this is the standard method, not all databases follow standards and so this
* method should be overwritten by a database driver if the database provider
* uses alternate methods. Because information_schema.tables is used in a few
* different functions, a database driver will only need to override this function
* to make all the others work. For example see
* core/includes/databases/mysql/schema.inc.
*
* @param $table_name
* The name of the table in question.
* @param $operator
* The operator to apply on the 'table' part of the condition.
* @param $add_prefix
* Boolean to indicate whether the table name needs to be prefixed.
*
* @return \Drupal\Core\Database\Query\Condition
* A Condition object.
*/
protected function buildTableNameCondition($table_name, $operator = '=', $add_prefix = TRUE)
{
$info = $this->connection->getConnectionOptions();
// Retrieve the table name and schema
$table_info = $this->getPrefixInfo($table_name, $add_prefix);
$condition = new Condition('AND');
$condition->condition('table_catalog', $info['database']);
$condition->condition('table_schema', $table_info['schema']);
$condition->condition('table_name', $table_info['table'], $operator);
return $condition;
}
/**
* Implements Drupal\Core\Database\Query\ConditionInterface::condition().
*/
public function condition($field, $value = NULL, $operator = '=')
{
$this->condition->condition($field, $value, $operator);
return $this;
}
/**
* {@inheritdoc}
*/
public function havingCondition($field, $value = NULL, $operator = NULL)
{
$this->having->condition($field, $value, $operator);
return $this;
}
/**
* {@inheritdoc}
*/
public function countMails(array $conditions = array())
{
// Continue to support 'nid' as a condition.
if (!empty($conditions['nid'])) {
$conditions['entity_type'] = 'node';
$conditions['entity_id'] = $conditions['nid'];
unset($conditions['nid']);
}
// Add default status condition if not set.
if (!isset($conditions['status'])) {
$conditions['status'] = array(SpoolStorageInterface::STATUS_PENDING, SpoolStorageInterface::STATUS_IN_PROGRESS);
}
$query = $this->connection->select('simplenews_mail_spool');
// Add conditions.
foreach ($conditions as $field => $value) {
if ($field == 'status') {
if (!is_array($value)) {
$value = array($value);
}
$status_or = new Condition('OR');
foreach ($value as $status) {
// Do not count pending entries unless they are expired.
if ($status == SpoolStorageInterface::STATUS_IN_PROGRESS) {
$status_or->condition((new Condition('AND'))->condition('status', $status)->condition('timestamp', $this->getExpirationTime(), '<'));
} else {
$status_or->condition('status', $status);
}
}
$query->condition($status_or);
} else {
$query->condition($field, $value);
}
}
$query->addExpression('COUNT(*)', 'count');
return (int) $query->execute()->fetchField();
}
/**
* {@inheritdoc}
*/
public function preloadPathAlias($preloaded, $langcode)
{
$langcode_list = [$langcode, LanguageInterface::LANGCODE_NOT_SPECIFIED];
$select = $this->connection->select('url_alias')->fields('url_alias', ['source', 'alias']);
if (!empty($preloaded)) {
$conditions = new Condition('OR');
foreach ($preloaded as $preloaded_item) {
$conditions->condition('source', $this->connection->escapeLike($preloaded_item), 'LIKE');
}
$select->condition($conditions);
}
// Always get the language-specific alias before the language-neutral one.
// For example 'de' is less than 'und' so the order needs to be ASC, while
// 'xx-lolspeak' is more than 'und' so the order needs to be DESC. We also
// order by pid ASC so that fetchAllKeyed() returns the most recently
// created alias for each source. Subsequent queries using fetchField() must
// use pid DESC to have the same effect.
if ($langcode == LanguageInterface::LANGCODE_NOT_SPECIFIED) {
array_pop($langcode_list);
} elseif ($langcode < LanguageInterface::LANGCODE_NOT_SPECIFIED) {
$select->orderBy('langcode', 'ASC');
} else {
$select->orderBy('langcode', 'DESC');
}
$select->orderBy('pid', 'ASC');
$select->condition('langcode', $langcode_list, 'IN');
return $select->execute()->fetchAllKeyed();
}
/**
* Creates a database query condition for a given search filter.
*
* Used as a helper method in createDbQuery().
*
* @param \Drupal\search_api\Query\FilterInterface $filter
* The filter for which a condition should be created.
* @param array $fields
* Internal information about the index's fields.
* @param \Drupal\Core\Database\Query\SelectInterface $db_query
* The database query to which the condition will be added.
* @param \Drupal\search_api\IndexInterface $index
* The index we're searching on.
*
* @return \Drupal\Core\Database\Query\ConditionInterface|null
* The condition to set on the query, or NULL if none is necessary.
*
* @throws \Drupal\search_api\SearchApiException
* Thrown if an unknown field was used in the filter.
*/
protected function createFilterCondition(FilterInterface $filter, array $fields, SelectInterface $db_query, IndexInterface $index) {
$cond = new Condition($filter->getConjunction());
$db_info = $this->getIndexDbInfo($index);
$empty = TRUE;
// Store whether a JOIN already occurred for a field, so we don't JOIN
// repeatedly for OR filters.
$first_join = array();
// Store the table aliases for the fields in this condition group.
$tables = array();
foreach ($filter->getFilters() as $f) {
if (is_object($f)) {
$c = $this->createFilterCondition($f, $fields, $db_query, $index);
if ($c) {
$empty = FALSE;
$cond->condition($c);
}
}
else {
$empty = FALSE;
// We don't index the datasource explicitly, so this needs a bit of
// magic.
// @todo Index the datasource explicitly so this doesn't need magic.
if ($f[0] === 'search_api_datasource') {
$alias = $this->getTableAlias(array('table' => $db_info['index_table']), $db_query);
// @todo Stop recognizing "!=" as an operator.
$operator = ($f[2] == '<>' || $f[2] == '!=') ? 'NOT LIKE' : 'LIKE';
$prefix = Utility::createCombinedId($f[1], '');
$cond->condition($alias . '.item_id', $this->database->escapeLike($prefix) . '%', $operator);
continue;
}
if (!isset($fields[$f[0]])) {
throw new SearchApiException(new FormattableMarkup('Unknown field in filter clause: @field.', array('@field' => $f[0])));
}
$field = $fields[$f[0]];
// Fields have their own table, so we have to check for NULL values in
// a special way (i.e., check for missing entries in that table).
// @todo This can probably always use the denormalized table.
if ($f[1] === NULL) {
$query = $this->database->select($field['table'], 't')
->fields('t', array('item_id'));
$cond->condition('t.item_id', $query, $f[2] == '<>' || $f[2] == '!=' ? 'IN' : 'NOT IN');
continue;
}
if (Utility::isTextType($field['type'])) {
$keys = $this->prepareKeys($f[1]);
$query = $this->createKeysQuery($keys, array($f[0] => $field), $fields, $index);
// We don't need the score, so we remove it. The score might either be
// an expression or a field.
$query_expressions = &$query->getExpressions();
if ($query_expressions) {
$query_expressions = array();
}
else {
$query_fields = &$query->getFields();
unset($query_fields['score']);
unset($query_fields);
}
unset($query_expressions);
$cond->condition('t.item_id', $query, $f[2] == '<>' || $f[2] == '!=' ? 'NOT IN' : 'IN');
}
else {
$new_join = ($filter->getConjunction() == 'AND' || empty($first_join[$f[0]]));
if ($new_join || empty($tables[$f[0]])) {
$tables[$f[0]] = $this->getTableAlias($field, $db_query, $new_join);
$first_join[$f[0]] = TRUE;
}
$column = $tables[$f[0]] . '.' . 'value';
if ($f[1] !== NULL) {
$cond->condition($column, $f[1], $f[2]);
}
else {
$method = ($f[2] == '=') ? 'isNull' : 'isNotNull';
$cond->$method($column);
}
}
}
}
return $empty ? NULL : $cond;
}
/**
* {@inheritdoc}
*/
public function execute()
{
$results = array();
if (!$this->isSearchExecutable()) {
return $results;
}
$keys = $this->keywords;
// Build matching conditions.
$query = $this->database->select('search_index', 'i', array('target' => 'replica'))->extend('Drupal\\search\\SearchQuery')->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender');
$query->join('node_field_data', 'n', 'n.nid = i.sid');
$query->condition('n.status', 1)->addTag('node_access')->searchExpression($keys, $this->getPluginId());
// Handle advanced search filters in the f query string.
// \Drupal::request()->query->get('f') is an array that looks like this in
// the URL: ?f[]=type:page&f[]=term:27&f[]=term:13&f[]=langcode:en
// So $parameters['f'] looks like:
// array('type:page', 'term:27', 'term:13', 'langcode:en');
// We need to parse this out into query conditions, some of which go into
// the keywords string, and some of which are separate conditions.
$parameters = $this->getParameters();
if (!empty($parameters['f']) && is_array($parameters['f'])) {
$filters = array();
// Match any query value that is an expected option and a value
// separated by ':' like 'term:27'.
$pattern = '/^(' . implode('|', array_keys($this->advanced)) . '):([^ ]*)/i';
foreach ($parameters['f'] as $item) {
if (preg_match($pattern, $item, $m)) {
// Use the matched value as the array key to eliminate duplicates.
$filters[$m[1]][$m[2]] = $m[2];
}
}
// Now turn these into query conditions. This assumes that everything in
// $filters is a known type of advanced search.
foreach ($filters as $option => $matched) {
$info = $this->advanced[$option];
// Insert additional conditions. By default, all use the OR operator.
$operator = empty($info['operator']) ? 'OR' : $info['operator'];
$where = new Condition($operator);
foreach ($matched as $value) {
$where->condition($info['column'], $value);
}
$query->condition($where);
if (!empty($info['join'])) {
$query->join($info['join']['table'], $info['join']['alias'], $info['join']['condition']);
}
}
}
// Add the ranking expressions.
$this->addNodeRankings($query);
// Run the query and load results.
$find = $query->fields('i', array('langcode'))->groupBy('i.langcode')->limit(10)->execute();
// Check query status and set messages if needed.
$status = $query->getStatus();
if ($status & SearchQuery::EXPRESSIONS_IGNORED) {
drupal_set_message($this->t('Your search used too many AND/OR expressions. Only the first @count terms were included in this search.', array('@count' => $this->searchSettings->get('and_or_limit'))), 'warning');
}
if ($status & SearchQuery::LOWER_CASE_OR) {
drupal_set_message($this->t('Search for either of the two terms with uppercase <strong>OR</strong>. For example, <strong>cats OR dogs</strong>.'), 'warning');
}
if ($status & SearchQuery::NO_POSITIVE_KEYWORDS) {
drupal_set_message(\Drupal::translation()->formatPlural($this->searchSettings->get('index.minimum_word_size'), 'You must include at least one positive keyword with 1 character or more.', 'You must include at least one positive keyword with @count characters or more.'), 'warning');
}
$node_storage = $this->entityManager->getStorage('node');
$node_render = $this->entityManager->getViewBuilder('node');
foreach ($find as $item) {
// Render the node.
/** @var \Drupal\node\NodeInterface $node */
$node = $node_storage->load($item->sid)->getTranslation($item->langcode);
$build = $node_render->view($node, 'search_result', $item->langcode);
unset($build['#theme']);
// Fetch comment count for snippet.
$node->rendered = SafeMarkup::set(drupal_render($build) . ' ' . SafeMarkup::escape($this->moduleHandler->invoke('comment', 'node_update_index', array($node, $item->langcode))));
$extra = $this->moduleHandler->invokeAll('node_search_result', array($node, $item->langcode));
$language = language_load($item->langcode);
$username = array('#theme' => 'username', '#account' => $node->getOwner());
$results[] = array('link' => $node->url('canonical', array('absolute' => TRUE, 'language' => $language)), 'type' => String::checkPlain($this->entityManager->getStorage('node_type')->load($node->bundle())->label()), 'title' => $node->label(), 'user' => drupal_render($username), 'date' => $node->getChangedTime(), 'node' => $node, 'extra' => $extra, 'score' => $item->calculated_score, 'snippet' => search_excerpt($keys, $node->rendered, $item->langcode), 'langcode' => $node->language()->getId());
}
return $results;
}
/**
* @covers ::compile
*
* @expectedException \PHPUnit_Framework_Error
* @dataProvider providerTestCompileWithSqlInjectionForOperator
*/
public function testCompileWithSqlInjectionForOperator($operator)
{
$connection = $this->prophesize(Connection::class);
$connection->escapeField(Argument::any())->will(function ($args) {
return preg_replace('/[^A-Za-z0-9_.]+/', '', $args[0]);
});
$connection->mapConditionOperator(Argument::any())->willReturn(NULL);
$connection = $connection->reveal();
$query_placeholder = $this->prophesize(PlaceholderInterface::class);
$counter = 0;
$query_placeholder->nextPlaceholder()->will(function () use(&$counter) {
return $counter++;
});
$query_placeholder->uniqueIdentifier()->willReturn(4);
$query_placeholder = $query_placeholder->reveal();
$condition = new Condition('AND');
$condition->condition('name', 'value', $operator);
$condition->compile($connection, $query_placeholder);
}
/**
* Creates a database query condition for a given search filter.
*
* Used as a helper method in createDbQuery().
*
* @param \Drupal\search_api\Query\ConditionGroupInterface $conditions
* The conditions for which a condition should be created.
* @param array $fields
* Internal information about the index's fields.
* @param \Drupal\Core\Database\Query\SelectInterface $db_query
* The database query to which the condition will be added.
* @param \Drupal\search_api\IndexInterface $index
* The index we're searching on.
*
* @return \Drupal\Core\Database\Query\ConditionInterface|null
* The condition to set on the query, or NULL if none is necessary.
*
* @throws \Drupal\search_api\SearchApiException
* Thrown if an unknown field was used in the filter.
*/
protected function createDbCondition(ConditionGroupInterface $conditions, array $fields, SelectInterface $db_query, IndexInterface $index)
{
$db_condition = new Condition($conditions->getConjunction());
$db_info = $this->getIndexDbInfo($index);
// Store whether a JOIN already occurred for a field, so we don't JOIN
// repeatedly for OR filters.
$first_join = array();
// Store the table aliases for the fields in this condition group.
$tables = array();
foreach ($conditions->getConditions() as $condition) {
if ($condition instanceof ConditionGroupInterface) {
$sub_condition = $this->createDbCondition($condition, $fields, $db_query, $index);
if ($sub_condition) {
$db_condition->condition($sub_condition);
}
} else {
$field = $condition->getField();
$operator = $condition->getOperator();
$value = $condition->getValue();
$not_equals = $operator == '<>' || $operator == '!=';
// We don't index the datasource explicitly, so this needs a bit of
// magic.
// @todo Index the datasource explicitly so this doesn't need magic.
if ($field === 'search_api_datasource') {
if (empty($tables[NULL])) {
$table = array('table' => $db_info['index_table']);
$tables[NULL] = $this->getTableAlias($table, $db_query);
}
$operator = $not_equals ? 'NOT LIKE' : 'LIKE';
$prefix = Utility::createCombinedId($value, '');
$db_condition->condition($tables[NULL] . '.item_id', $this->database->escapeLike($prefix) . '%', $operator);
continue;
}
if (!isset($fields[$field])) {
throw new SearchApiException(new FormattableMarkup('Unknown field in filter clause: @field.', array('@field' => $field)));
}
$field_info = $fields[$field];
// For NULL values, we can just use the single-values table, since we
// only need to know if there's any value at all for that field.
if ($value === NULL || empty($field_info['multi-valued'])) {
if (empty($tables[NULL])) {
$table = array('table' => $db_info['index_table']);
$tables[NULL] = $this->getTableAlias($table, $db_query);
}
$column = $tables[NULL] . '.' . $field_info['column'];
if ($value === NULL) {
$method = $not_equals ? 'isNotNull' : 'isNull';
$db_condition->{$method}($column);
} else {
$db_condition->condition($column, $value, $operator);
}
continue;
}
if (Utility::isTextType($field_info['type'])) {
$keys = $this->prepareKeys($value);
if (!isset($keys)) {
continue;
}
$query = $this->createKeysQuery($keys, array($field => $field_info), $fields, $index);
// We only want the item IDs, so we use the keys query as a nested
// query.
$query = $this->database->select($query, 't')->fields('t', array('item_id'));
$db_condition->condition('t.item_id', $query, $not_equals ? 'NOT IN' : 'IN');
} else {
$new_join = $conditions->getConjunction() == 'AND' || empty($first_join[$field]);
if ($new_join || empty($tables[$field])) {
$tables[$field] = $this->getTableAlias($field_info, $db_query, $new_join);
$first_join[$field] = TRUE;
}
$column = $tables[$field] . '.' . 'value';
if ($not_equals) {
// The situation is more complicated for multi-valued fields, since
// we must make sure that results are excluded if ANY of the field's
// values equals the one given in this condition.
$query = $this->database->select($field_info['table'], 't')->fields('t', array('item_id'))->condition('value', $value);
$db_condition->condition('t.item_id', $query, 'NOT IN');
} else {
$db_condition->condition($column, $value, $operator);
}
}
}
}
return $db_condition->count() ? $db_condition : NULL;
}