/** * Get field search SQL request (used by class CMS_object_search) * * @param integer $fieldID : this field id in object (aka $this->_field->getID()) * @param mixed $value : the value to search * @param string $operator : additionnal search operator * @param string $where : where clauses to add to SQL * @param boolean $public : values are public or edited ? (default is edited) * @return string : the SQL request * @access public */ function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false) { $supportedOperator = array('<', '<=', '>', '>='); if ($operator && !in_array($operator, $supportedOperator)) { $this->_raiseError(get_class($this) . " : getFieldSearchSQL : unkown search operator : " . $operator . ", use default search instead"); $operator = false; } if (!$operator) { return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public); } $statusSuffix = $public ? "_public" : "_edited"; $sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_integer" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand value " . $operator . " '" . SensitiveIO::sanitizeSQLString($value) . "'\n\t\t\t\t{$where}"; return $sql; }
/** * Get field search SQL request (used by class CMS_object_search) * * @param integer $fieldID : this field id in object (aka $this->_field->getID()) * @param mixed $value : the value to search * @param string $operator : additionnal search operator * @param string $where : where clauses to add to SQL * @param boolean $public : values are public or edited ? (default is edited) * @return string : the SQL request * @access public */ function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false) { $supportedOperator = array('like', '!=', '=', 'any', 'all', 'phrase', 'beginswith'); $supportedOperatorForArray = array('in', 'not in', 'any', 'all'); // No operator : use default search if (!$operator) { return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public); } // Check supported operators if ($operator && !in_array($operator, array_merge($supportedOperator, $supportedOperatorForArray))) { $this->raiseError("Unknown search operator : " . $operator . ", use default search instead"); $operator = false; } // Check operators for array value if (is_array($value) && $operator && !in_array($operator, $supportedOperatorForArray)) { $this->raiseError("Can't use this operator : " . $operator . " with an array value, return empty sql"); return ''; } $statusSuffix = $public ? "_public" : "_edited"; $cleanedWords = array(); if (is_array($value)) { if ($operator == 'any' || $operator == 'all') { // in this case, we do a specific cleanup foreach ($value as $i => $val) { $cleanedWords[] = str_replace(array('%', '_'), array('\\%', '\\_'), $val); } } else { foreach ($value as $i => $val) { $value[$i] = "'" . SensitiveIO::sanitizeSQLString($val) . "'"; } $value = '(' . implode(',', $value) . ')'; } } elseif (strtolower($value) == 'null') { $value = "''"; } else { if ($operator == 'any' || $operator == 'all') { $words = array(); $words = array_map("trim", array_unique(explode(" ", $value))); foreach ($words as $aWord) { if ($aWord && $aWord != '' && io::strlen($aWord) >= 3) { $aWord = str_replace(array('%', '_'), array('\\%', '\\_'), $aWord); $cleanedWords[] = $aWord; } } } elseif ($operator != 'phrase' && $operator != 'beginswith') { // we keep this for backward compatibility, where the user can specify his search with % at the beginning / end $value = "'" . SensitiveIO::sanitizeSQLString($value) . "'"; } } $whereClause = ''; switch ($operator) { case 'any': $whereClause .= '('; //then add keywords $count = '0'; foreach ($cleanedWords as $aWord) { $whereClause .= $count ? ' or ' : ''; $count++; $whereClause .= "value like '%" . $aWord . "%'"; if (htmlentities($aWord) != $aWord) { $whereClause .= " or value like '%" . htmlentities($aWord) . "%'"; } } $whereClause .= ')'; break; case 'all': $whereClause .= '('; //then add keywords $count = '0'; foreach ($cleanedWords as $aWord) { $whereClause .= $count ? ' and ' : ''; $count++; if (htmlentities($aWord) != $aWord) { $whereClause .= "(value like '%" . $aWord . "%' or value like '%" . htmlentities($aWord) . "%')"; } else { $whereClause .= "value like '%" . $aWord . "%'"; } } $whereClause .= ')'; break; case 'phrase': $value = str_replace(array('%', '_'), array('\\%', '\\_'), trim($value)); if (htmlentities($value) != $value) { $whereClause .= "(value like '%" . $value . "%' or value like '%" . htmlentities($value) . "%')"; } else { $whereClause .= "value like '%" . $value . "%'"; } break; case 'beginswith': $value = str_replace(array('%', '_'), array('\\%', '\\_'), trim($value)); if (htmlentities($value) != $value) { $whereClause .= "(value like '" . $value . "%' or value like '" . htmlentities($value) . "%')"; } else { $whereClause .= "value like '" . $value . "%'"; } break; default: $whereClause .= " value " . $operator . " " . $value; break; } $sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_text" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand " . $whereClause . "\n\t\t\t\t{$where}"; return $sql; }
/** * Get field search SQL request (used by class CMS_object_search) * * @param integer $fieldID : this field id in object (aka $this->_field->getID()) * @param mixed $value : the value to search * @param string $operator : additionnal search operator * @param string $where : where clauses to add to SQL * @param boolean $public : values are public or edited ? (default is edited) * @return string : the SQL request * @access public */ function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false) { $supportedOperator = array('>=', '<=', '>', '<', '>= or null', '<= or null', '> or null', '< or null', '>= and not null', '<= and not null', '> and not null', '< and not null', 'beginswith'); if ($operator && !in_array($operator, $supportedOperator)) { $this->raiseError("Unknown search operator : " . $operator . ", use default search instead"); $operator = false; } if (!$operator) { return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public); } // canBeNull $operators = explode('or', $operator); $operator = trim($operators[0]); $canBeNull = isset($operators[1]) ? ' or value is NULL' : ''; // cantBeNull $operators = explode('and', $operator); $operator = trim($operators[0]); $cantBeNull = isset($operators[1]) ? ' and value is not NULL and value != \'0000-00-00\' and value != \'0000-00-00 00:00:00\'' : ''; $statusSuffix = $public ? "_public" : "_edited"; $whereClause = ''; if ($operator == 'beginswith') { global $cms_language; $dateFormat = $cms_language->getDateFormat(); $dateFormatSql = str_replace(array('D', 'M', 'n', 'jS', 'd', 'j', 'u', 'H', 'h', 'g', 'i', 'z', 'G', 'g', 'F', 'm', 'A', 's', 's', 'W', 'l', 'w', 'Y', 'y'), array('%a', '%b', '%c', '%D', '%d', '%e', '%f', '%H', '%h', '%I', '%i', '%j', '%k', '%l', '%M', '%m', '%p', '%S', '%s', '%u', '%W', '%w', '%Y', '%y'), $dateFormat); $whereClause = "(DATE_FORMAT(value,'" . $dateFormatSql . "') like '" . SensitiveIO::sanitizeSQLString($value) . "%')"; } else { $whereClause = "(value " . $operator . " '" . SensitiveIO::sanitizeSQLString($value) . "'" . $canBeNull . $cantBeNull . ")"; } $sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_date" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand " . $whereClause . "\n\t\t\t\t{$where}"; return $sql; }