/** * Returns SQL appropriate for relationship joins and wheres * * @todo add support for multiple relationships and guids. * * @param string $column Column name the GUID should be checked against. * Provide in table.column format. * @param string $relationship Type of the relationship * @param int $relationship_guid Entity GUID to check * @param bool $inverse_relationship Is $relationship_guid the target of the relationship? * * @return mixed * @access private */ public function getEntityRelationshipWhereSql($column, $relationship = null, $relationship_guid = null, $inverse_relationship = false) { if ($relationship == null && $relationship_guid == null) { return ''; } $wheres = array(); $joins = array(); $group_by = ''; if ($inverse_relationship) { $joins[] = "JOIN {$this->db->getTablePrefix()}entity_relationships r on r.guid_one = {$column}"; } else { $joins[] = "JOIN {$this->db->getTablePrefix()}entity_relationships r on r.guid_two = {$column}"; } if ($relationship) { $wheres[] = "r.relationship = '" . $this->db->sanitizeString($relationship) . "'"; } if ($relationship_guid) { if ($inverse_relationship) { $wheres[] = "r.guid_two = '{$relationship_guid}'"; } else { $wheres[] = "r.guid_one = '{$relationship_guid}'"; } } else { // See #5775. Queries that do not include a relationship_guid must be grouped by entity table alias, // otherwise the result set is not unique $group_by = $column; } if ($where_str = implode(' AND ', $wheres)) { return array('wheres' => array("({$where_str})"), 'joins' => $joins, 'group_by' => $group_by); } return ''; }
/** * Deletes a private setting for an entity. * * @param int $entity_guid The Entity GUID * @param string $name The name of the setting * @return bool */ function remove($entity_guid, $name) { $entity_guid = (int) $entity_guid; $entity = $this->entities->get($entity_guid); if (!$entity instanceof \ElggEntity) { return false; } $name = $this->db->sanitizeString($name); return $this->db->deleteData("DELETE FROM {$this->table}\n\t\t\tWHERE name = '{$name}'\n\t\t\tAND entity_guid = {$entity_guid}"); }
/** * Set the value for a datalist element. * * Plugin authors should use elgg_save_config() and pass null for the site GUID. * * @warning Names should be selected so as not to collide with the names for the * site config. * * @warning Values set here are not available in $CONFIG until next page load. * * @param string $name The name of the datalist * @param string $value The new value * * @return bool * @access private */ function set($name, $value) { $name = trim($name); if (!$this->validateName($name)) { return false; } $escaped_name = $this->db->sanitizeString($name); $escaped_value = $this->db->sanitizeString($value); $success = $this->db->insertData("INSERT INTO {$this->table}" . " SET name = '{$escaped_name}', value = '{$escaped_value}'" . " ON DUPLICATE KEY UPDATE value = '{$escaped_value}'"); $this->cache->put($name, $value); return $success !== false; }
/** * Set the value for a datalist element. * * Plugin authors should use elgg_save_config() and pass null for the site GUID. * * @warning Names should be selected so as not to collide with the names for the * site config. * * @warning Values set here are not available in $CONFIG until next page load. * * @param string $name The name of the datalist * @param string $value The new value * * @return bool * @access private */ function set($name, $value) { $name = trim($name); // cannot store anything longer than 255 characters in db, so catch before we set if (elgg_strlen($name) > 255) { $this->logger->error("The name length for configuration variables cannot be greater than 255"); return false; } $escaped_name = $this->db->sanitizeString($name); $escaped_value = $this->db->sanitizeString($value); $success = $this->db->insertData("INSERT INTO {$this->table}" . " SET name = '{$escaped_name}', value = '{$escaped_value}'" . " ON DUPLICATE KEY UPDATE value = '{$escaped_value}'"); $this->cache->put($name, $value); return $success !== false; }
/** * Add a metastring. * * @warning You should not call this directly. Use elgg_get_metastring_id(). * * @param string $string The value to be normalized * @return int The identifier for this string */ function add($string) { $escaped_string = $this->db->sanitizeString(trim($string)); return $this->db->insertData("INSERT INTO {$this->getTableName()} (string) VALUES ('{$escaped_string}')"); }
/** * Returns metadata name and value SQL where for entities. * NB: $names and $values are not paired. Use $pairs for this. * Pairs default to '=' operand. * * This function is reused for annotations because the tables are * exactly the same. * * @param string $e_table Entities table name * @param string $n_table Normalized metastrings table name (Where entities, * values, and names are joined. annotations / metadata) * @param array|null $names Array of names * @param array|null $values Array of values * @param array|null $pairs Array of names / values / operands * @param string $pair_operator ("AND" or "OR") Operator to use to join the where clauses for pairs * @param bool $case_sensitive Case sensitive metadata names? * @param array|null $order_by_metadata Array of names / direction * @param array|null $owner_guids Array of owner GUIDs * * @return false|array False on fail, array('joins', 'wheres') * @access private */ function getEntityMetadataWhereSql($e_table, $n_table, $names = null, $values = null, $pairs = null, $pair_operator = 'AND', $case_sensitive = true, $order_by_metadata = null, $owner_guids = null) { // short circuit if nothing requested // 0 is a valid (if not ill-conceived) metadata name. // 0 is also a valid metadata value for false, null, or 0 // 0 is also a valid(ish) owner_guid if (!$names && $names !== 0 && (!$values && $values !== 0) && (!$pairs && $pairs !== 0) && (!$owner_guids && $owner_guids !== 0) && !$order_by_metadata) { return ''; } // join counter for incremental joins. $i = 1; // binary forces byte-to-byte comparision of strings, making // it case- and diacritical-mark- sensitive. // only supported on values. $binary = $case_sensitive ? ' BINARY ' : ''; $access = _elgg_get_access_where_sql(array('table_alias' => 'n_table')); $return = array('joins' => array(), 'wheres' => array(), 'orders' => array()); // will always want to join these tables if pulling metastrings. $return['joins'][] = "JOIN {$this->db->getTablePrefix()}{$n_table} n_table on\n\t\t\t{$e_table}.guid = n_table.entity_guid"; $wheres = array(); // get names wheres and joins $names_where = ''; if ($names !== null) { if (!is_array($names)) { $names = array($names); } $sanitised_names = array(); foreach ($names as $name) { // normalise to 0. if (!$name) { $name = '0'; } $sanitised_names[] = '\'' . $this->db->sanitizeString($name) . '\''; } if ($names_str = implode(',', $sanitised_names)) { $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msn on n_table.name_id = msn.id"; $names_where = "(msn.string IN ({$names_str}))"; } } // get values wheres and joins $values_where = ''; if ($values !== null) { if (!is_array($values)) { $values = array($values); } $sanitised_values = array(); foreach ($values as $value) { // normalize to 0 if (!$value) { $value = 0; } $sanitised_values[] = '\'' . $this->db->sanitizeString($value) . '\''; } if ($values_str = implode(',', $sanitised_values)) { $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msv on n_table.value_id = msv.id"; $values_where = "({$binary}msv.string IN ({$values_str}))"; } } if ($names_where && $values_where) { $wheres[] = "({$names_where} AND {$values_where} AND {$access})"; } elseif ($names_where) { $wheres[] = "({$names_where} AND {$access})"; } elseif ($values_where) { $wheres[] = "({$values_where} AND {$access})"; } // add pairs // pairs must be in arrays. if (is_array($pairs)) { // check if this is an array of pairs or just a single pair. if (isset($pairs['name']) || isset($pairs['value'])) { $pairs = array($pairs); } $pair_wheres = array(); // @todo when the pairs are > 3 should probably split the query up to // denormalize the strings table. foreach ($pairs as $index => $pair) { // @todo move this elsewhere? // support shortcut 'n' => 'v' method. if (!is_array($pair)) { $pair = array('name' => $index, 'value' => $pair); } // must have at least a name and value if (!isset($pair['name']) || !isset($pair['value'])) { // @todo should probably return false. continue; } // case sensitivity can be specified per pair. // default to higher level setting. if (isset($pair['case_sensitive'])) { $pair_binary = $pair['case_sensitive'] ? ' BINARY ' : ''; } else { $pair_binary = $binary; } if (isset($pair['operand'])) { $operand = $this->db->sanitizeString($pair['operand']); } else { $operand = ' = '; } // for comparing $trimmed_operand = trim(strtolower($operand)); $access = _elgg_get_access_where_sql(array('table_alias' => "n_table{$i}")); // certain operands can't work well with strings that can be interpreted as numbers // for direct comparisons like IN, =, != we treat them as strings // gt/lt comparisons need to stay unencapsulated because strings '5' > '15' // see https://github.com/Elgg/Elgg/issues/7009 $num_safe_operands = array('>', '<', '>=', '<='); $num_test_operand = trim(strtoupper($operand)); if (is_numeric($pair['value']) && in_array($num_test_operand, $num_safe_operands)) { $value = $this->db->sanitizeString($pair['value']); } else { if (is_bool($pair['value'])) { $value = (int) $pair['value']; } else { if (is_array($pair['value'])) { $values_array = array(); foreach ($pair['value'] as $pair_value) { if (is_numeric($pair_value) && !in_array($num_test_operand, $num_safe_operands)) { $values_array[] = $this->db->sanitizeString($pair_value); } else { $values_array[] = "'" . $this->db->sanitizeString($pair_value) . "'"; } } if ($values_array) { $value = '(' . implode(', ', $values_array) . ')'; } // @todo allow support for non IN operands with array of values. // will have to do more silly joins. $operand = 'IN'; } else { if ($trimmed_operand == 'in') { $value = "({$pair['value']})"; } else { $value = "'" . $this->db->sanitizeString($pair['value']) . "'"; } } } } $name = $this->db->sanitizeString($pair['name']); // @todo The multiple joins are only needed when the operator is AND $return['joins'][] = "JOIN {$this->db->getTablePrefix()}{$n_table} n_table{$i}\n\t\t\t\t\ton {$e_table}.guid = n_table{$i}.entity_guid"; $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msn{$i}\n\t\t\t\t\ton n_table{$i}.name_id = msn{$i}.id"; $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msv{$i}\n\t\t\t\t\ton n_table{$i}.value_id = msv{$i}.id"; $pair_wheres[] = "(msn{$i}.string = '{$name}' AND {$pair_binary}msv{$i}.string\n\t\t\t\t\t{$operand} {$value} AND {$access})"; $i++; } if ($where = implode(" {$pair_operator} ", $pair_wheres)) { $wheres[] = "({$where})"; } } // add owner_guids if ($owner_guids) { if (is_array($owner_guids)) { $sanitised = array_map('sanitise_int', $owner_guids); $owner_str = implode(',', $sanitised); } else { $owner_str = (int) $owner_guids; } $wheres[] = "(n_table.owner_guid IN ({$owner_str}))"; } if ($where = implode(' AND ', $wheres)) { $return['wheres'][] = "({$where})"; } if (is_array($order_by_metadata)) { if (count($order_by_metadata) > 0 && !isset($order_by_metadata[0])) { // singleton, so fix $order_by_metadata = array($order_by_metadata); } foreach ($order_by_metadata as $order_by) { if (is_array($order_by) && isset($order_by['name'])) { $name = $this->db->sanitizeString($order_by['name']); if (isset($order_by['direction'])) { $direction = $this->db->sanitizeString($order_by['direction']); } else { $direction = 'ASC'; } $return['joins'][] = "JOIN {$this->db->getTablePrefix()}{$n_table} n_table{$i}\n\t\t\t\t\t\ton {$e_table}.guid = n_table{$i}.entity_guid"; $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msn{$i}\n\t\t\t\t\t\ton n_table{$i}.name_id = msn{$i}.id"; $return['joins'][] = "JOIN {$this->metastringsTable->getTableName()} msv{$i}\n\t\t\t\t\t\ton n_table{$i}.value_id = msv{$i}.id"; $access = _elgg_get_access_where_sql(array('table_alias' => "n_table{$i}")); $return['wheres'][] = "(msn{$i}.string = '{$name}' AND {$access})"; if (isset($order_by['as']) && $order_by['as'] == 'integer') { $return['orders'][] = "CAST(msv{$i}.string AS SIGNED) {$direction}"; } else { $return['orders'][] = "msv{$i}.string {$direction}"; } $i++; } } } return $return; }
/** * Returns private setting name and value SQL where/join clauses for entities * * @param string $table Entities table name * @param array|null $names Array of names * @param array|null $values Array of values * @param array|null $pairs Array of names / values / operands * @param string $pair_operator Operator for joining pairs where clauses * @param string $name_prefix A string to prefix all names with * @return array */ private function getWhereSql($table, $names = null, $values = null, $pairs = null, $pair_operator = 'AND', $name_prefix = '') { // @todo short circuit test $return = array('joins' => array(), 'wheres' => array()); $return['joins'][] = "JOIN {$this->table} ps on\n\t\t\t{$table}.guid = ps.entity_guid"; $wheres = array(); // get names wheres $names_where = ''; if ($names !== null) { if (!is_array($names)) { $names = array($names); } $sanitised_names = array(); foreach ($names as $name) { $name = $name_prefix . $name; $sanitised_names[] = '\'' . $this->db->sanitizeString($name) . '\''; } $names_str = implode(',', $sanitised_names); if ($names_str) { $names_where = "(ps.name IN ({$names_str}))"; } } // get values wheres $values_where = ''; if ($values !== null) { if (!is_array($values)) { $values = array($values); } $sanitised_values = array(); foreach ($values as $value) { // normalize to 0 if (!$value) { $value = 0; } $sanitised_values[] = '\'' . $this->db->sanitizeString($value) . '\''; } $values_str = implode(',', $sanitised_values); if ($values_str) { $values_where = "(ps.value IN ({$values_str}))"; } } if ($names_where && $values_where) { $wheres[] = "({$names_where} AND {$values_where})"; } elseif ($names_where) { $wheres[] = "({$names_where})"; } elseif ($values_where) { $wheres[] = "({$values_where})"; } // add pairs which must be in arrays. if (is_array($pairs)) { // join counter for incremental joins in pairs $i = 1; // check if this is an array of pairs or just a single pair. if (isset($pairs['name']) || isset($pairs['value'])) { $pairs = array($pairs); } $pair_wheres = array(); foreach ($pairs as $index => $pair) { // @todo move this elsewhere? // support shortcut 'n' => 'v' method. if (!is_array($pair)) { $pair = array('name' => $index, 'value' => $pair); } // must have at least a name and value if (!isset($pair['name']) || !isset($pair['value'])) { // @todo should probably return false. continue; } if (isset($pair['operand'])) { $operand = $this->db->sanitizeString($pair['operand']); } else { $operand = ' = '; } // for comparing $trimmed_operand = trim(strtolower($operand)); // if the value is an int, don't quote it because str '15' < str '5' // if the operand is IN don't quote it because quoting should be done already. if (is_numeric($pair['value'])) { $value = $this->db->sanitizeString($pair['value']); } else { if (is_array($pair['value'])) { $values_array = array(); foreach ($pair['value'] as $pair_value) { if (is_numeric($pair_value)) { $values_array[] = $this->db->sanitizeString($pair_value); } else { $values_array[] = "'" . $this->db->sanitizeString($pair_value) . "'"; } } if ($values_array) { $value = '(' . implode(', ', $values_array) . ')'; } // @todo allow support for non IN operands with array of values. // will have to do more silly joins. $operand = 'IN'; } else { if ($trimmed_operand == 'in') { $value = "({$pair['value']})"; } else { $value = "'" . $this->db->sanitizeString($pair['value']) . "'"; } } } $name = $this->db->sanitizeString($name_prefix . $pair['name']); // @todo The multiple joins are only needed when the operator is AND $return['joins'][] = "JOIN {$this->table} ps{$i}\n\t\t\t\t\ton {$table}.guid = ps{$i}.entity_guid"; $pair_wheres[] = "(ps{$i}.name = '{$name}' AND ps{$i}.value\n\t\t\t\t\t{$operand} {$value})"; $i++; } $where = implode(" {$pair_operator} ", $pair_wheres); if ($where) { $wheres[] = "({$where})"; } } $where = implode(' AND ', $wheres); if ($where) { $return['wheres'][] = "({$where})"; } return $return; }
/** * {@inheritdoc} */ public function sanitizeString($value) { return $this->db->sanitizeString($value); }