Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
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 | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Import_DataSource_SQL extends CRM_Import_DataSource { | |
18 | ||
7b057b66 EM |
19 | /** |
20 | * Form fields declared for this datasource. | |
21 | * | |
22 | * @var string[] | |
23 | */ | |
24 | protected $submittableFields = ['sqlQuery']; | |
25 | ||
e0ef6999 | 26 | /** |
fe482240 | 27 | * Provides information about the data source. |
e0ef6999 | 28 | * |
a6c01b45 CW |
29 | * @return array |
30 | * collection of info about this data source | |
e0ef6999 | 31 | */ |
39dc35d4 | 32 | public function getInfo(): array { |
be2fb01f | 33 | return [ |
11749569 | 34 | 'title' => ts('SQL Query'), |
be2fb01f CW |
35 | 'permissions' => ['import SQL datasource'], |
36 | ]; | |
6a488035 TO |
37 | } |
38 | ||
e0ef6999 EM |
39 | /** |
40 | * This is function is called by the form object to get the DataSource's | |
41 | * form snippet. It should add all fields necesarry to get the data | |
42 | * uploaded to the temporary table in the DB. | |
43 | * | |
c490a46a | 44 | * @param CRM_Core_Form $form |
e0ef6999 | 45 | * |
a6c01b45 CW |
46 | * @return void |
47 | * (operates directly on form argument) | |
e0ef6999 | 48 | */ |
6a488035 TO |
49 | public function buildQuickForm(&$form) { |
50 | $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_SQL'); | |
4367e964 | 51 | $form->add('textarea', 'sqlQuery', ts('Specify SQL Query'), ['rows' => 10, 'cols' => 45], TRUE); |
be2fb01f | 52 | $form->addFormRule(['CRM_Import_DataSource_SQL', 'formRule'], $form); |
6a488035 TO |
53 | } |
54 | ||
e0ef6999 EM |
55 | /** |
56 | * @param $fields | |
57 | * @param $files | |
c490a46a | 58 | * @param CRM_Core_Form $form |
e0ef6999 EM |
59 | * |
60 | * @return array|bool | |
61 | */ | |
00be9182 | 62 | public static function formRule($fields, $files, $form) { |
be2fb01f | 63 | $errors = []; |
6a488035 | 64 | |
e047612e | 65 | // Makeshift query validation (case-insensitive regex matching on word boundaries) |
4a01628c | 66 | $forbidden = ['ALTER', 'CREATE', 'DELETE', 'DESCRIBE', 'DROP', 'SHOW', 'UPDATE', 'REPLACE', 'information_schema']; |
6a488035 TO |
67 | foreach ($forbidden as $pattern) { |
68 | if (preg_match("/\\b$pattern\\b/i", $fields['sqlQuery'])) { | |
be2fb01f | 69 | $errors['sqlQuery'] = ts('The query contains the forbidden %1 command.', [1 => $pattern]); |
6a488035 TO |
70 | } |
71 | } | |
72 | ||
4a01628c | 73 | return $errors ?: TRUE; |
6a488035 TO |
74 | } |
75 | ||
e0ef6999 | 76 | /** |
1a783cdb | 77 | * Initialize the datasource, based on the submitted values stored in the user job. |
0a66a182 | 78 | * |
4a01628c | 79 | * @throws \API_Exception |
0a66a182 | 80 | * @throws \CRM_Core_Exception |
e0ef6999 | 81 | */ |
1a783cdb | 82 | public function initialize(): void { |
3377d521 EM |
83 | $table = CRM_Utils_SQL_TempTable::build()->setDurable(); |
84 | $tableName = $table->getName(); | |
85 | $table->createWithQuery($this->getSubmittedValue('sqlQuery')); | |
6a488035 | 86 | |
4a01628c EM |
87 | // Get the names of the fields to be imported. Any fields starting with an |
88 | // underscore are considered to be internal to the import process) | |
89 | $columnsResult = CRM_Core_DAO::executeQuery( | |
3377d521 | 90 | 'SHOW FIELDS FROM ' . $tableName . " |
4a01628c EM |
91 | WHERE Field NOT LIKE '\_%'"); |
92 | ||
93 | $columnNames = []; | |
94 | while ($columnsResult->fetch()) { | |
95 | $columnNames[] = $columnsResult->Field; | |
96 | } | |
8d88fae0 EM |
97 | |
98 | $this->addTrackingFieldsToTable($tableName); | |
4a01628c | 99 | $this->updateUserJobMetadata('DataSource', [ |
3377d521 | 100 | 'table_name' => $tableName, |
4a01628c EM |
101 | 'column_headers' => $columnNames, |
102 | 'number_of_columns' => count($columnNames), | |
103 | ]); | |
6a488035 | 104 | } |
96025800 | 105 | |
6a488035 | 106 | } |