CRM/Import/DataSource/SQL.php

   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
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Import_DataSource_SQL extends CRM_Import_DataSource {
  18
  19   /**
  20    * Form fields declared for this datasource.
  21    *
  22    * @var string[]
  23    */
  24   protected $submittableFields = ['sqlQuery'];
  25
  26   /**
  27    * Provides information about the data source.
  28    *
  29    * @return array
  30    *   collection of info about this data source
  31    */
  32   public function getInfo(): array {
  33     return [
  34       'title' => ts('SQL Query'),
  35       'permissions' => ['import SQL datasource'],
  36     ];
  37   }
  38
  39   /**
  40    * Set variables up before form is built.
  41    *
  42    * @param CRM_Core_Form $form
  43    */
  44   public function preProcess(&$form) {
  45   }
  46
  47   /**
  48    * This is function is called by the form object to get the DataSource's
  49    * form snippet. It should add all fields necesarry to get the data
  50    * uploaded to the temporary table in the DB.
  51    *
  52    * @param CRM_Core_Form $form
  53    *
  54    * @return void
  55    *   (operates directly on form argument)
  56    */
  57   public function buildQuickForm(&$form) {
  58     $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_SQL');
  59     $form->add('textarea', 'sqlQuery', ts('Specify SQL Query'), ['rows' => 10, 'cols' => 45], TRUE);
  60     $form->addFormRule(['CRM_Import_DataSource_SQL', 'formRule'], $form);
  61   }
  62
  63   /**
  64    * @param $fields
  65    * @param $files
  66    * @param CRM_Core_Form $form
  67    *
  68    * @return array|bool
  69    */
  70   public static function formRule($fields, $files, $form) {
  71     $errors = [];
  72
  73     // Makeshift query validation (case-insensitive regex matching on word boundaries)
  74     $forbidden = ['ALTER', 'CREATE', 'DELETE', 'DESCRIBE', 'DROP', 'SHOW', 'UPDATE', 'REPLACE', 'information_schema'];
  75     foreach ($forbidden as $pattern) {
  76       if (preg_match("/\\b$pattern\\b/i", $fields['sqlQuery'])) {
  77         $errors['sqlQuery'] = ts('The query contains the forbidden %1 command.', [1 => $pattern]);
  78       }
  79     }
  80
  81     return $errors ?: TRUE;
  82   }
  83
  84   /**
  85    * Process the form submission.
  86    *
  87    * @param array $params
  88    * @param string $db
  89    * @param \CRM_Core_Form $form
  90    *
  91    * @throws \API_Exception
  92    * @throws \CRM_Core_Exception
  93    * @throws \Civi\API\Exception\UnauthorizedException
  94    */
  95   public function postProcess(&$params, &$db, &$form) {
  96     $importJob = new CRM_Contact_Import_ImportJob(
  97       CRM_Utils_Array::value('import_table_name', $params),
  98       $params['sqlQuery'], TRUE
  99     );
 100
 101     $form->set('importTableName', $importJob->getTableName());
 102     // Get the names of the fields to be imported. Any fields starting with an
 103     // underscore are considered to be internal to the import process)
 104     $columnsResult = CRM_Core_DAO::executeQuery(
 105       'SHOW FIELDS FROM ' . $importJob->getTableName() . "
 106       WHERE Field NOT LIKE '\_%'");
 107
 108     $columnNames = [];
 109     while ($columnsResult->fetch()) {
 110       $columnNames[] = $columnsResult->Field;
 111     }
 112     $this->updateUserJobMetadata('DataSource', [
 113       'table_name' => $importJob->getTableName(),
 114       'column_headers' => $columnNames,
 115       'number_of_columns' => count($columnNames),
 116     ]);
 117   }
 118
 119 }