Sanitizes a string for use in a query
| public sanitizeString ( string $value ) : string | ||
| $value | string | Value to escape |
| return | string | |
/**
* 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);
}
