From bf00d1b6a4760264502229e1c1b6bda31d677629 Mon Sep 17 00:00:00 2001 From: "Donald A. Lobo" Date: Tue, 8 Oct 2013 15:24:29 +0100 Subject: [PATCH] CRM-13554 - validate values of limit and offset ---------------------------------------- * CRM-13554: Improve string validation in the query engine http://issues.civicrm.org/jira/browse/CRM-13554 --- CRM/Contact/BAO/Query.php | 4 +++ .../Form/Search/Custom/ActivitySearch.php | 2 ++ CRM/Contact/Form/Search/Custom/Base.php | 3 +++ .../Search/Custom/ContributionAggregate.php | 3 +++ .../Form/Search/Custom/EventAggregate.php | 2 ++ CRM/Contact/Page/AJAX.php | 6 +++++ CRM/Core/BAO/PrevNextCache.php | 3 +++ CRM/Mailing/BAO/Mailing.php | 6 +++++ CRM/Mailing/BAO/Recipients.php | 3 +++ CRM/Report/Form.php | 3 +++ CRM/Report/Form/Contribute/TopDonor.php | 25 +++++++++++-------- 11 files changed, 49 insertions(+), 11 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index d7d0271e25..381d4d2b8f 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4000,6 +4000,8 @@ civicrm_relationship.start_date > {$today} $sql .= " ORDER BY $sort "; } if ($row_count > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); $sql .= " LIMIT $offset, $row_count "; } @@ -4185,6 +4187,8 @@ civicrm_relationship.start_date > {$today} if ($rowCount > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); $limit = " LIMIT $offset, $rowCount "; // ok here is a first hack at an optimization, lets get all the contact ids diff --git a/CRM/Contact/Form/Search/Custom/ActivitySearch.php b/CRM/Contact/Form/Search/Custom/ActivitySearch.php index 8c9c50b22b..5113f49fa4 100644 --- a/CRM/Contact/Form/Search/Custom/ActivitySearch.php +++ b/CRM/Contact/Form/Search/Custom/ActivitySearch.php @@ -213,6 +213,8 @@ class CRM_Contact_Form_Search_Custom_ActivitySearch implements CRM_Contact_Form_ } if ($rowcount > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); $sql .= " LIMIT $offset, $rowcount "; } return $sql; diff --git a/CRM/Contact/Form/Search/Custom/Base.php b/CRM/Contact/Form/Search/Custom/Base.php index 9e18c666f4..781cfc9071 100644 --- a/CRM/Contact/Form/Search/Custom/Base.php +++ b/CRM/Contact/Form/Search/Custom/Base.php @@ -132,6 +132,9 @@ class CRM_Contact_Form_Search_Custom_Base { } if ($rowcount > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $sql .= " LIMIT $offset, $rowcount "; } } diff --git a/CRM/Contact/Form/Search/Custom/ContributionAggregate.php b/CRM/Contact/Form/Search/Custom/ContributionAggregate.php index b5be40461f..72c6700d8d 100644 --- a/CRM/Contact/Form/Search/Custom/ContributionAggregate.php +++ b/CRM/Contact/Form/Search/Custom/ContributionAggregate.php @@ -134,6 +134,7 @@ $having // Define ORDER BY for query in $sort, with default value if (!empty($sort)) { if (is_string($sort)) { + $sort = CRM_Utils_Type::escape($sort, 'String'); $sql .= " ORDER BY $sort "; } else { @@ -146,6 +147,8 @@ $having } if ($rowcount > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); $sql .= " LIMIT $offset, $rowcount "; } return $sql; diff --git a/CRM/Contact/Form/Search/Custom/EventAggregate.php b/CRM/Contact/Form/Search/Custom/EventAggregate.php index 75a3f69582..d749ccdf43 100644 --- a/CRM/Contact/Form/Search/Custom/EventAggregate.php +++ b/CRM/Contact/Form/Search/Custom/EventAggregate.php @@ -159,6 +159,8 @@ class CRM_Contact_Form_Search_Custom_EventAggregate extends CRM_Contact_Form_Sea } if ($rowcount > 0 && $offset >= 0) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); $sql .= " LIMIT $offset, $rowcount "; } diff --git a/CRM/Contact/Page/AJAX.php b/CRM/Contact/Page/AJAX.php index 0b339c6b4b..103247f861 100644 --- a/CRM/Contact/Page/AJAX.php +++ b/CRM/Contact/Page/AJAX.php @@ -692,6 +692,9 @@ WHERE sort_name LIKE '%$name%'"; $offset = CRM_Utils_Array::value('offset', $_GET, 0); $rowCount = CRM_Utils_Array::value('rowcount', $_GET, 20); + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + // add acl clause here list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { @@ -782,6 +785,9 @@ LIMIT {$offset}, {$rowCount} $offset = CRM_Utils_Array::value('offset', $_GET, 0); $rowCount = CRM_Utils_Array::value('rowcount', $_GET, 20); + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + // add acl clause here list($aclFrom, $aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('cc'); if ($aclWhere) { diff --git a/CRM/Core/BAO/PrevNextCache.php b/CRM/Core/BAO/PrevNextCache.php index e1536ef996..0928b4435a 100644 --- a/CRM/Core/BAO/PrevNextCache.php +++ b/CRM/Core/BAO/PrevNextCache.php @@ -152,6 +152,9 @@ WHERE cacheKey = %1 } if ($rowCount) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $query .= " LIMIT {$offset}, {$rowCount}"; } diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index c1343f1850..2fb1ca7be8 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -467,6 +467,9 @@ AND $mg.mailing_id = {$mailing_id} $aclWhere = $aclWhere ? "WHERE {$aclWhere}" : ''; $limitString = NULL; if ($limit && $offset !== NULL) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $limitString = "LIMIT $offset, $limit"; } @@ -2237,6 +2240,9 @@ LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id } if ($rowCount) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $query .= " LIMIT $offset, $rowCount "; } diff --git a/CRM/Mailing/BAO/Recipients.php b/CRM/Mailing/BAO/Recipients.php index 166d6afae4..36caf69ec3 100644 --- a/CRM/Mailing/BAO/Recipients.php +++ b/CRM/Mailing/BAO/Recipients.php @@ -56,6 +56,9 @@ WHERE mailing_id = %1 ) { $limitString = NULL; if ($limit && $offset !== NULL) { + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $limitString = "LIMIT $offset, $limit"; } diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index 0b4d0dc173..8edffd8de8 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -2487,6 +2487,9 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $this->set(CRM_Utils_Pager::PAGE_ID, $pageId); $offset = ($pageId - 1) * $rowCount; + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $this->_limit = " LIMIT $offset, " . $rowCount; return array($offset, $rowCount); } diff --git a/CRM/Report/Form/Contribute/TopDonor.php b/CRM/Report/Form/Contribute/TopDonor.php index 78dffba2d5..6106542c22 100644 --- a/CRM/Report/Form/Contribute/TopDonor.php +++ b/CRM/Report/Form/Contribute/TopDonor.php @@ -45,7 +45,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { 'barChart' => 'Bar Chart', 'pieChart' => 'Pie Chart', ); - + function __construct() { $this->_columns = array( 'civicrm_contact' => @@ -83,7 +83,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { 'avg' => ts('Average'), ), ), - 'currency' => + 'currency' => array('required' => TRUE, 'no_display' => TRUE, ), @@ -134,7 +134,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { ), 'grouping' => 'email-fields', ), - + 'civicrm_phone' => array( 'dao' => 'CRM_Core_DAO_Phone', @@ -248,14 +248,14 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { function from() { $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} - INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} + INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0 - LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id + LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1 - LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} + LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND - {$this->_aliases['civicrm_phone']}.is_primary = 1 + {$this->_aliases['civicrm_phone']}.is_primary = 1 "; } @@ -337,7 +337,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { $setVariable = " SET @rows:=0, @rank=0 "; CRM_Core_DAO::singleValueQuery($setVariable); - $sql = " {$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} + $sql = " {$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} ORDER BY civicrm_contribution_total_amount_sum DESC ) as abc {$this->_outerCluase} $this->_limit "; @@ -363,8 +363,8 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { function add2group($groupID) { if (is_numeric($groupID)) { - $sql = " -{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} + $sql = " +{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} ORDER BY civicrm_contribution_total_amount_sum DESC ) as abc {$this->_outerCluase}"; $dao = CRM_Core_DAO::executeQuery($sql); @@ -401,6 +401,9 @@ ORDER BY civicrm_contribution_total_amount_sum DESC $this->set(CRM_Utils_Pager::PAGE_ID, $pageId); $offset = ($pageId - 1) * $rowCount; + $offset = CRM_Utils_Type::escape($offset, 'Int'); + $rowCount = CRM_Utils_Type::escape($rowCount, 'Int'); + $this->_limit = " LIMIT $offset, " . $rowCount; } } -- 2.25.1