Civi/API/SelectQuery.php

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