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 | |
9cf618c4 | 87 | // Get the names of the fields to be imported. |
4a01628c | 88 | $columnsResult = CRM_Core_DAO::executeQuery( |
9cf618c4 | 89 | 'SHOW FIELDS FROM ' . $tableName); |
4a01628c EM |
90 | |
91 | $columnNames = []; | |
92 | while ($columnsResult->fetch()) { | |
57414073 EM |
93 | if (strpos($columnsResult->Field, ' ') !== FALSE) { |
94 | // Remove spaces as the Database object does this | |
95 | // $keys = str_replace(array(".", " "), "_", array_keys($array)); | |
96 | // https://lab.civicrm.org/dev/core/-/issues/1337 | |
97 | $usableColumnName = str_replace(' ', '_', $columnsResult->Field); | |
98 | CRM_Core_DAO::executeQuery('ALTER TABLE ' . $tableName . ' CHANGE `' . $columnsResult->Field . '` ' . $usableColumnName . ' ' . $columnsResult->Type); | |
99 | $columnNames[] = $usableColumnName; | |
100 | } | |
101 | else { | |
102 | $columnNames[] = $columnsResult->Field; | |
103 | } | |
4a01628c | 104 | } |
8d88fae0 EM |
105 | |
106 | $this->addTrackingFieldsToTable($tableName); | |
4a01628c | 107 | $this->updateUserJobMetadata('DataSource', [ |
3377d521 | 108 | 'table_name' => $tableName, |
4a01628c EM |
109 | 'column_headers' => $columnNames, |
110 | 'number_of_columns' => count($columnNames), | |
111 | ]); | |
6a488035 | 112 | } |
96025800 | 113 | |
6a488035 | 114 | } |