| 1 | <?php |
| 2 | /* |
| 3 | +--------------------------------------------------------------------+ |
| 4 | | Copyright CiviCRM LLC. All rights reserved. | |
| 5 | | | |
| 6 | | This work is published under the GNU AGPLv3 license with some | |
| 7 | | permitted exceptions and without any warranty. For full license | |
| 8 | | and copyright information, see https://civicrm.org/licensing | |
| 9 | +--------------------------------------------------------------------+ |
| 10 | */ |
| 11 | namespace Civi\API; |
| 12 | |
| 13 | use Civi\API\Exception\UnauthorizedException; |
| 14 | |
| 15 | /** |
| 16 | * Query builder for civicrm_api_basic_get. |
| 17 | * |
| 18 | * Fetches an entity based on specified params for the "where" clause, |
| 19 | * return properties for the "select" clause, |
| 20 | * as well as limit and order. |
| 21 | * |
| 22 | * Automatically joins on custom fields to return or filter by them. |
| 23 | * |
| 24 | * Supports an additional sql fragment which the calling api can provide. |
| 25 | * |
| 26 | * @package Civi\API |
| 27 | */ |
| 28 | abstract class SelectQuery { |
| 29 | |
| 30 | const |
| 31 | MAX_JOINS = 4, |
| 32 | MAIN_TABLE_ALIAS = 'a'; |
| 33 | |
| 34 | /** |
| 35 | * @var string |
| 36 | */ |
| 37 | protected $entity; |
| 38 | public $select = []; |
| 39 | public $where = []; |
| 40 | public $orderBy = []; |
| 41 | public $limit; |
| 42 | public $offset; |
| 43 | /** |
| 44 | * @var array |
| 45 | */ |
| 46 | protected $selectFields = []; |
| 47 | /** |
| 48 | * @var bool |
| 49 | */ |
| 50 | public $isFillUniqueFields = FALSE; |
| 51 | /** |
| 52 | * @var \CRM_Utils_SQL_Select |
| 53 | */ |
| 54 | protected $query; |
| 55 | /** |
| 56 | * @var array |
| 57 | */ |
| 58 | protected $joins = []; |
| 59 | /** |
| 60 | * @var array |
| 61 | */ |
| 62 | protected $apiFieldSpec; |
| 63 | /** |
| 64 | * @var array |
| 65 | */ |
| 66 | protected $entityFieldNames = []; |
| 67 | /** |
| 68 | * @var array |
| 69 | */ |
| 70 | protected $aclFields = []; |
| 71 | /** |
| 72 | * @var string|bool |
| 73 | */ |
| 74 | protected $checkPermissions; |
| 75 | |
| 76 | protected $apiVersion; |
| 77 | |
| 78 | /** |
| 79 | * @param string $entity |
| 80 | * @param bool $checkPermissions |
| 81 | */ |
| 82 | public function __construct($entity, $checkPermissions) { |
| 83 | $this->entity = $entity; |
| 84 | require_once 'api/v3/utils.php'; |
| 85 | $baoName = _civicrm_api3_get_BAO($entity); |
| 86 | $bao = new $baoName(); |
| 87 | |
| 88 | $this->entityFieldNames = array_column($baoName::fields(), 'name'); |
| 89 | $this->apiFieldSpec = $this->getFields(); |
| 90 | |
| 91 | $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS); |
| 92 | |
| 93 | // Add ACLs first to avoid redundant subclauses |
| 94 | $this->checkPermissions = $checkPermissions; |
| 95 | $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); |
| 96 | } |
| 97 | |
| 98 | /** |
| 99 | * Build & execute the query and return results array |
| 100 | * |
| 101 | * @return array|int |
| 102 | * @throws \API_Exception |
| 103 | * @throws \CRM_Core_Exception |
| 104 | * @throws \Exception |
| 105 | */ |
| 106 | public function run() { |
| 107 | $this->buildSelectFields(); |
| 108 | |
| 109 | $this->buildWhereClause(); |
| 110 | if (in_array('count_rows', $this->select)) { |
| 111 | $this->query->select("count(*) as c"); |
| 112 | } |
| 113 | else { |
| 114 | foreach ($this->selectFields as $column => $alias) { |
| 115 | $this->query->select("$column as `$alias`"); |
| 116 | } |
| 117 | // Order by |
| 118 | $this->buildOrderBy(); |
| 119 | } |
| 120 | |
| 121 | // Limit |
| 122 | if (!empty($this->limit) || !empty($this->offset)) { |
| 123 | $this->query->limit($this->limit, $this->offset); |
| 124 | } |
| 125 | |
| 126 | $result_entities = []; |
| 127 | $result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL()); |
| 128 | |
| 129 | while ($result_dao->fetch()) { |
| 130 | if (in_array('count_rows', $this->select)) { |
| 131 | return (int) $result_dao->c; |
| 132 | } |
| 133 | $result_entities[$result_dao->id] = []; |
| 134 | foreach ($this->selectFields as $column => $alias) { |
| 135 | $returnName = $alias; |
| 136 | $alias = str_replace('.', '_', $alias); |
| 137 | if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) { |
| 138 | $result_entities[$result_dao->id][$returnName] = $result_dao->$alias; |
| 139 | } |
| 140 | // Backward compatibility on fields names. |
| 141 | if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) { |
| 142 | $result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->$alias; |
| 143 | } |
| 144 | foreach ($this->apiFieldSpec as $returnName => $spec) { |
| 145 | if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) { |
| 146 | $result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']]; |
| 147 | } |
| 148 | } |
| 149 | }; |
| 150 | } |
| 151 | return $result_entities; |
| 152 | } |
| 153 | |
| 154 | /** |
| 155 | * @param \CRM_Utils_SQL_Select $sqlFragment |
| 156 | * @return SelectQuery |
| 157 | */ |
| 158 | public function merge($sqlFragment) { |
| 159 | $this->query->merge($sqlFragment); |
| 160 | return $this; |
| 161 | } |
| 162 | |
| 163 | /** |
| 164 | * Joins onto an fk field |
| 165 | * |
| 166 | * Adds one or more joins to the query to make this field available for use in a clause. |
| 167 | * |
| 168 | * Enforces permissions at the api level and by appending the acl clause for that entity to the join. |
| 169 | * |
| 170 | * @param $fkFieldName |
| 171 | * @param $side |
| 172 | * |
| 173 | * @return array|null |
| 174 | * Returns the table and field name for adding this field to a SELECT or WHERE clause |
| 175 | * @throws \API_Exception |
| 176 | * @throws \Civi\API\Exception\UnauthorizedException |
| 177 | */ |
| 178 | protected function addFkField($fkFieldName, $side) { |
| 179 | $stack = explode('.', $fkFieldName); |
| 180 | if (count($stack) < 2) { |
| 181 | return NULL; |
| 182 | } |
| 183 | $prev = self::MAIN_TABLE_ALIAS; |
| 184 | foreach ($stack as $depth => $fieldName) { |
| 185 | // Setup variables then skip the first level |
| 186 | if (!$depth) { |
| 187 | $fk = $fieldName; |
| 188 | // We only join on core fields |
| 189 | // @TODO: Custom contact ref fields could be supported too |
| 190 | if (!in_array($fk, $this->entityFieldNames)) { |
| 191 | return NULL; |
| 192 | } |
| 193 | $fkField = &$this->apiFieldSpec[$fk]; |
| 194 | continue; |
| 195 | } |
| 196 | // More than 4 joins deep seems excessive - DOS attack? |
| 197 | if ($depth > self::MAX_JOINS) { |
| 198 | throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName"); |
| 199 | } |
| 200 | $subStack = array_slice($stack, 0, $depth); |
| 201 | $this->getJoinInfo($fkField, $subStack); |
| 202 | if (!isset($fkField['FKApiName']) || !isset($fkField['FKClassName'])) { |
| 203 | // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it. |
| 204 | return NULL; |
| 205 | } |
| 206 | // Ensure we have permission to access the other api |
| 207 | if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) { |
| 208 | throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName"); |
| 209 | } |
| 210 | if (!isset($fkField['FKApiSpec'])) { |
| 211 | $fkField['FKApiSpec'] = \_civicrm_api_get_fields($fkField['FKApiName']); |
| 212 | } |
| 213 | $fieldInfo = $fkField['FKApiSpec'][$fieldName] ?? NULL; |
| 214 | |
| 215 | $keyColumn = \CRM_Utils_Array::value('FKKeyColumn', $fkField, 'id'); |
| 216 | if (!$fieldInfo || !isset($fkField['FKApiSpec'][$keyColumn])) { |
| 217 | // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it. |
| 218 | return NULL; |
| 219 | } |
| 220 | |
| 221 | // Skip if we don't have permission to access this field |
| 222 | if ($this->checkPermissions && !empty($fieldInfo['permission']) && !\CRM_Core_Permission::check($fieldInfo['permission'])) { |
| 223 | return NULL; |
| 224 | } |
| 225 | |
| 226 | $fkTable = \CRM_Core_DAO_AllCoreTables::getTableForClass($fkField['FKClassName']); |
| 227 | $tableAlias = implode('_to_', $subStack) . "_to_$fkTable"; |
| 228 | |
| 229 | // Add acl condition |
| 230 | $joinCondition = array_merge( |
| 231 | ["$prev.$fk = $tableAlias.$keyColumn"], |
| 232 | $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack) |
| 233 | ); |
| 234 | |
| 235 | if (!empty($fkField['FKCondition'])) { |
| 236 | $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']); |
| 237 | } |
| 238 | |
| 239 | $this->join($side, $fkTable, $tableAlias, $joinCondition); |
| 240 | |
| 241 | if (strpos($fieldName, 'custom_') === 0) { |
| 242 | list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias); |
| 243 | } |
| 244 | |
| 245 | // Get ready to recurse to the next level |
| 246 | $fk = $fieldName; |
| 247 | $fkField = &$fkField['FKApiSpec'][$fieldName]; |
| 248 | $prev = $tableAlias; |
| 249 | } |
| 250 | return [$tableAlias, $fieldName]; |
| 251 | } |
| 252 | |
| 253 | /** |
| 254 | * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group") |
| 255 | * |
| 256 | * @param $fkField |
| 257 | * @param $stack |
| 258 | */ |
| 259 | protected function getJoinInfo(&$fkField, $stack) { |
| 260 | if ($fkField['name'] == 'entity_id') { |
| 261 | $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table'; |
| 262 | $entityTable = $this->where[$entityTableParam] ?? NULL; |
| 263 | if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable)) { |
| 264 | $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable); |
| 265 | $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables::getBriefName($fkField['FKClassName']); |
| 266 | } |
| 267 | } |
| 268 | if (!empty($fkField['pseudoconstant']['optionGroupName'])) { |
| 269 | $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue'; |
| 270 | $fkField['FKApiName'] = 'OptionValue'; |
| 271 | $fkField['FKKeyColumn'] = 'value'; |
| 272 | $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')"; |
| 273 | } |
| 274 | } |
| 275 | |
| 276 | /** |
| 277 | * Joins onto a custom field |
| 278 | * |
| 279 | * Adds a join to the query to make this field available for use in a clause. |
| 280 | * |
| 281 | * @param array $customField |
| 282 | * @param string $side |
| 283 | * @param string $baseTable |
| 284 | * @return array |
| 285 | * Returns the table and field name for adding this field to a SELECT or WHERE clause |
| 286 | */ |
| 287 | protected function addCustomField($customField, $side, $baseTable = self::MAIN_TABLE_ALIAS) { |
| 288 | $tableName = $customField["table_name"]; |
| 289 | $columnName = $customField["column_name"]; |
| 290 | $tableAlias = "{$baseTable}_to_$tableName"; |
| 291 | $this->join($side, $tableName, $tableAlias, ["`$tableAlias`.entity_id = `$baseTable`.id"]); |
| 292 | return [$tableAlias, $columnName]; |
| 293 | } |
| 294 | |
| 295 | /** |
| 296 | * Fetch a field from the getFields list |
| 297 | * |
| 298 | * @param string $fieldName |
| 299 | * @return array|null |
| 300 | */ |
| 301 | abstract protected function getField($fieldName); |
| 302 | |
| 303 | /** |
| 304 | * Perform input validation on params that use the join syntax |
| 305 | * |
| 306 | * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency |
| 307 | * in that api permissions to perform the join are checked first. |
| 308 | * |
| 309 | * @param $fieldName |
| 310 | * @param $value |
| 311 | * @throws \Exception |
| 312 | */ |
| 313 | protected function validateNestedInput($fieldName, &$value) { |
| 314 | $stack = explode('.', $fieldName); |
| 315 | $spec = $this->apiFieldSpec; |
| 316 | $fieldName = array_pop($stack); |
| 317 | foreach ($stack as $depth => $name) { |
| 318 | $entity = $spec[$name]['FKApiName']; |
| 319 | $spec = $spec[$name]['FKApiSpec']; |
| 320 | } |
| 321 | $params = [$fieldName => $value]; |
| 322 | \_civicrm_api3_validate_fields($entity, 'get', $params, $spec); |
| 323 | $value = $params[$fieldName]; |
| 324 | } |
| 325 | |
| 326 | /** |
| 327 | * Check permission to join onto another api entity |
| 328 | * |
| 329 | * @param string $entity |
| 330 | * @param array $fieldStack |
| 331 | * The stack of fields leading up to this join |
| 332 | * @return bool |
| 333 | */ |
| 334 | protected function checkPermissionToJoin($entity, $fieldStack) { |
| 335 | if (!$this->checkPermissions) { |
| 336 | return TRUE; |
| 337 | } |
| 338 | // Build an array of params that relate to the joined entity |
| 339 | $params = [ |
| 340 | 'version' => 3, |
| 341 | 'return' => [], |
| 342 | 'check_permissions' => $this->checkPermissions, |
| 343 | ]; |
| 344 | $prefix = implode('.', $fieldStack) . '.'; |
| 345 | $len = strlen($prefix); |
| 346 | foreach ($this->select as $key => $ret) { |
| 347 | if (strpos($key, $prefix) === 0) { |
| 348 | $params['return'][substr($key, $len)] = $ret; |
| 349 | } |
| 350 | } |
| 351 | foreach ($this->where as $key => $param) { |
| 352 | if (strpos($key, $prefix) === 0) { |
| 353 | $params[substr($key, $len)] = $param; |
| 354 | } |
| 355 | } |
| 356 | |
| 357 | return \Civi::service('civi_api_kernel')->runAuthorize($entity, 'get', $params); |
| 358 | } |
| 359 | |
| 360 | /** |
| 361 | * Get acl clause for an entity |
| 362 | * |
| 363 | * @param string $tableAlias |
| 364 | * @param \CRM_Core_DAO|string $baoName |
| 365 | * @param array $stack |
| 366 | * @return array |
| 367 | */ |
| 368 | public function getAclClause($tableAlias, $baoName, $stack = []) { |
| 369 | if (!$this->checkPermissions) { |
| 370 | return []; |
| 371 | } |
| 372 | // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity. |
| 373 | // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity |
| 374 | // and just doing it for the first join takes care of most acl clause deduping. |
| 375 | if (count($stack) === 1 && in_array($stack[0], $this->aclFields)) { |
| 376 | return []; |
| 377 | } |
| 378 | $clauses = $baoName::getSelectWhereClause($tableAlias); |
| 379 | if (!$stack) { |
| 380 | // Track field clauses added to the main entity |
| 381 | $this->aclFields = array_keys($clauses); |
| 382 | } |
| 383 | return array_filter($clauses); |
| 384 | } |
| 385 | |
| 386 | /** |
| 387 | * Orders the query by one or more fields |
| 388 | * |
| 389 | * @throws \API_Exception |
| 390 | * @throws \Civi\API\Exception\UnauthorizedException |
| 391 | */ |
| 392 | protected function buildOrderBy() { |
| 393 | $sortParams = is_string($this->orderBy) ? explode(',', $this->orderBy) : (array) $this->orderBy; |
| 394 | foreach ($sortParams as $index => $item) { |
| 395 | $item = trim($item); |
| 396 | if ($item == '(1)') { |
| 397 | continue; |
| 398 | } |
| 399 | $words = preg_split("/[\s]+/", $item); |
| 400 | if ($words) { |
| 401 | // Direction defaults to ASC unless DESC is specified |
| 402 | $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : ''; |
| 403 | $field = $this->getField($words[0]); |
| 404 | if ($field) { |
| 405 | $this->query->orderBy(self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction, NULL, $index); |
| 406 | } |
| 407 | elseif (strpos($words[0], '.')) { |
| 408 | $join = $this->addFkField($words[0], 'LEFT'); |
| 409 | if ($join) { |
| 410 | $this->query->orderBy("`{$join[0]}`.`{$join[1]}`$direction", NULL, $index); |
| 411 | } |
| 412 | } |
| 413 | else { |
| 414 | throw new \API_Exception("Unknown field specified for sort. Cannot order by '$item'"); |
| 415 | } |
| 416 | } |
| 417 | } |
| 418 | } |
| 419 | |
| 420 | /** |
| 421 | * @param string $side |
| 422 | * @param string $tableName |
| 423 | * @param string $tableAlias |
| 424 | * @param array $conditions |
| 425 | */ |
| 426 | public function join($side, $tableName, $tableAlias, $conditions) { |
| 427 | // INNER JOINs take precedence over LEFT JOINs |
| 428 | if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) { |
| 429 | $this->joins[$tableAlias] = $side; |
| 430 | $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions)); |
| 431 | } |
| 432 | } |
| 433 | |
| 434 | /** |
| 435 | * Populate where clauses |
| 436 | * |
| 437 | * @throws \Civi\API\Exception\UnauthorizedException |
| 438 | * @throws \Exception |
| 439 | */ |
| 440 | abstract protected function buildWhereClause(); |
| 441 | |
| 442 | /** |
| 443 | * Populate $this->selectFields |
| 444 | * |
| 445 | * @throws \Civi\API\Exception\UnauthorizedException |
| 446 | */ |
| 447 | protected function buildSelectFields() { |
| 448 | $return_all_fields = (empty($this->select) || !is_array($this->select)); |
| 449 | $return = $return_all_fields ? $this->entityFieldNames : $this->select; |
| 450 | if ($return_all_fields || in_array('custom', $this->select)) { |
| 451 | foreach (array_keys($this->apiFieldSpec) as $fieldName) { |
| 452 | if (strpos($fieldName, 'custom_') === 0) { |
| 453 | $return[] = $fieldName; |
| 454 | } |
| 455 | } |
| 456 | } |
| 457 | |
| 458 | // Always select the ID if the table has one. |
| 459 | if (array_key_exists('id', $this->apiFieldSpec)) { |
| 460 | $this->selectFields[self::MAIN_TABLE_ALIAS . ".id"] = "id"; |
| 461 | } |
| 462 | |
| 463 | // core return fields |
| 464 | foreach ($return as $fieldName) { |
| 465 | $field = $this->getField($fieldName); |
| 466 | if ($field && in_array($field['name'], $this->entityFieldNames)) { |
| 467 | $this->selectFields[self::MAIN_TABLE_ALIAS . ".{$field['name']}"] = $field['name']; |
| 468 | } |
| 469 | elseif (strpos($fieldName, '.')) { |
| 470 | $fkField = $this->addFkField($fieldName, 'LEFT'); |
| 471 | if ($fkField) { |
| 472 | $this->selectFields[implode('.', $fkField)] = $fieldName; |
| 473 | } |
| 474 | } |
| 475 | elseif ($field && strpos($fieldName, 'custom_') === 0) { |
| 476 | list($table_name, $column_name) = $this->addCustomField($field, 'LEFT'); |
| 477 | |
| 478 | if ($field['data_type'] != 'ContactReference') { |
| 479 | // 'ordinary' custom field. We will select the value as custom_XX. |
| 480 | $this->selectFields["$table_name.$column_name"] = $fieldName; |
| 481 | } |
| 482 | else { |
| 483 | // contact reference custom field. The ID will be stored in custom_XX_id. |
| 484 | // custom_XX will contain the sort name of the contact. |
| 485 | $this->query->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`"); |
| 486 | $this->selectFields["$table_name.$column_name"] = $fieldName . "_id"; |
| 487 | // We will call the contact table for the join c_XX. |
| 488 | $this->selectFields["c_$fieldName.sort_name"] = $fieldName; |
| 489 | } |
| 490 | } |
| 491 | } |
| 492 | } |
| 493 | |
| 494 | /** |
| 495 | * Load entity fields |
| 496 | * @return array |
| 497 | */ |
| 498 | abstract protected function getFields(); |
| 499 | |
| 500 | } |