Squiz Matrix  4.12.2
 All Data Structures Namespaces Functions Variables Pages
trigger_action_execute_database_query.inc
1 <?php
17 require_once SQ_INCLUDE_PATH.'/general_occasional.inc';
18 require_once SQ_CORE_PACKAGE_PATH.'/system/triggers/trigger_action/trigger_action.inc';
19 require_once SQ_SYSTEM_ROOT.'/core/attributes/email_format/email_format.inc';
20 require_once SQ_SYSTEM_ROOT.'/core/attributes/parameter_map/parameter_map.inc';
21 
33 class Trigger_Action_Execute_Database_Query extends Trigger_Action
34 {
35 
36 
46  public static function execute($settings, &$state)
47  {
48  $options = Array(
49  'details' => translate('database_details'),
50  'dsn' => translate('dsn'),
51  'connector' => translate('db_connector'),
52  );
53  $method = array_get_index($settings, 'method', '');
54  $type = array_get_index($settings, 'type', 'pgsql');
55  $database = array_get_index($settings, 'database', '');
56  $host = array_get_index($settings, 'host', '');
57  $dsn = array_get_index($settings, 'dsn', '');
58  $connector = array_get_index($settings, 'connector', '0');
59  $username = array_get_index($settings, 'username', '');
60  $password = array_get_index($settings, 'password', '');
61  $sql = array_get_index($settings, 'sql', '');
62  $prefix = array_get_index($settings, 'prefix', date('dmY'));
63  if (!array_key_exists($method, $options)) {
64  $method = '';
65  }//end if
66 
67  // Load the current asset
68  if (empty($state['asset'])) {
69  // grab the asset if assetid is given, but not the asset.
70  if (empty($state['assetid'])) {
71  $state['asset'] = NULL;
72  } else {
73  $state['asset'] = $GLOBALS['SQ_SYSTEM']->am->getAsset($state['assetid']);
74  }//end if
75  }//end if
76 
77  // Find a DSN
78  $actual_dsn = '';
79 
80  // Try the DB Connector
81  if ($method == 'connector' && !empty($connector)) {
82  $connector = $GLOBALS['SQ_SYSTEM']->am->getAsset($connector);
83  if (!empty($connector)) {
84  $actual_dsn = $connector->getDSN();
85  }//end if
86  }//end if
87 
88  // Try the DSN string
89  if ($method == 'dsn' && !empty($dsn)) {
90  $actual_dsn = $dsn;
91  }//end if
92 
93  // Try database details
94  if ($method == 'details' && !empty($type) && !empty($host) && !empty($database)) {
95  switch ($type) {
96  case 'pgsql':
97  $actual_dsn = 'pgsql:';
98  break;
99  case 'mysql':
100  $actual_dsn = 'mysql:';
101  break;
102  }
103  $actual_dsn .= "dbname=$database;host=$host";
104  }//end if
105 
106  // Nothing worked, don't execute
107  if (empty($actual_dsn)) return FALSE;
108 
109  if ($method == 'connector') {
110  // DSN is already properly formed
111  $connect_dsn = $actual_dsn;
112  } else {
113  $connect_dsn = Array('DSN' => $actual_dsn, 'user' => $username, 'password' => $password, 'type' => $type);
114  }//end if
115 
116  // Try to connect
117  try {
118  $connection = MatrixDAL::dbConnect($connect_dsn, 'trigger_action_db');
119  } catch (Exception $e) {
120  return FALSE;
121  }
122 
123  // Try to execute the SQL
124  if ($connection === FALSE) return FALSE;
125  $sql = trim($sql, ';');
126 
127  // Replace keywords
128  $matches = Array();
129  $keyword_wrapper = '%';
130  $keyword_pattern = '('.$keyword_wrapper.'([a-zA-Z_\-0-9\.]+)'.$keyword_wrapper.')';
131  preg_match_all ('/'.$keyword_pattern.'/', $sql, $matches, PREG_PATTERN_ORDER);
132 
133  if (!isset($matches[2])) $matches[2] = Array();
134 
135  $keywords =& $matches[2];
136 
137  $patterns = Array();
138  $replacements = Array();
139  $bind_vars = Array();
140  foreach ($keywords as $id => $keyword) {
141  $value = '';
142  if (!is_null($state['asset'])) {
143  $value = $state['asset']->getKeywordReplacement($keyword);
144  }//end if
145  if ($value != $keyword_wrapper.$keyword.$keyword_wrapper) {
146  // Keyword cannot be found, don't bother doing anything else
147  $value = str_replace('$', '\$', $value);
148  $replacements[] = ':'.$keyword.$id;
149  $patterns[] = '/('.$keyword_wrapper.$keyword.$keyword_wrapper.')/';
150  $bind_vars[$keyword.$id] = $value;
151  }//end if
152  }//end foreach
153 
154  // Treat the global keywords separately
155  $includes_globals = FALSE;
156  foreach(array_unique($keywords) as $keyword) {
157  if (0 === strpos($keyword, 'globals_')) {
158  $whole_keyword = $keyword_wrapper.$keyword.$keyword_wrapper;
159  $sql = str_replace($whole_keyword, MatrixDAL::quote($whole_keyword), $sql);
160  $includes_globals = TRUE;
161  }//end if
162  }//end foreach
163 
164  $sql = preg_replace($patterns, $replacements, $sql, '1');
165  if($includes_globals) $sql = replace_global_keywords($sql);
166 
167  // Check for wildcards on the bind vars
168  foreach ($bind_vars as $bind_var => $bind_value) {
169  // Wild card at the start
170  if (preg_match('/%:'.$bind_var.'/i', $sql) != FALSE) {
171  $sql = str_replace('%:'.$bind_var, ':'.$bind_var, $sql);
172  $bind_vars[$bind_var] = '%'.$bind_vars[$bind_var];
173  }//end if
174 
175  // Wild card at the end
176  if (preg_match('/'.$bind_var.'%/i', $sql) != FALSE) {
177  $sql = str_replace(':'.$bind_var.'%', ':'.$bind_var, $sql);
178  $bind_vars[$bind_var] = $bind_vars[$bind_var].'%';
179  }//end if
180  }//end foreach
181 
182  try {
183  if (!isset($GLOBALS['SQ_SYSTEM']->tm) || !($GLOBALS['SQ_SYSTEM']->tm instanceof Transaction_Manager)) {
184  require_once SQ_INCLUDE_PATH.'/transaction_manager.inc';
185  $GLOBALS['SQ_SYSTEM']->tm = new Transaction_Manager();
186  }
187  MatrixDAL::changeDb('trigger_action_db');
188  $GLOBALS['SQ_SYSTEM']->tm->begin('trigger_action_db');
189  $query = MatrixDAL::preparePdoQuery($sql);
190  foreach ($bind_vars as $bind_var => $bind_value) {
191  MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value);
192  }
193  MatrixDAL::execPdoQuery($query);
194  } catch (Exception $e) {
195  $GLOBALS['SQ_SYSTEM']->tm->rollback('trigger_action_db');
196  MatrixDAL::restoreDb();
197  trigger_localised_error('DATA0001', E_USER_WARNING, $e->getMessage(), $sql);
198  return FALSE;
199  }
200 
201  // commit the transaction
202  $GLOBALS['SQ_SYSTEM']->tm->commit('trigger_action_db');
203  MatrixDAL::restoreDb();
204 
205  // Return a response
206  return TRUE;
207 
208  }//end execute()
209 
210 
221  public static function getInterface($settings, $prefix, $write_access=FALSE)
222  {
223  $method = array_get_index($settings, 'method', 'details');
224  $type = array_get_index($settings, 'type', 'pgsql');
225  $database = array_get_index($settings, 'database', '');
226  $host = array_get_index($settings, 'host', '');
227  $dsn = array_get_index($settings, 'dsn', '');
228  $connector = array_get_index($settings, 'connector', '0');
229  $username = array_get_index($settings, 'username', '');
230  $password = array_get_index($settings, 'password', '');
231  $sql = array_get_index($settings, 'sql', '');
232  $options = Array(
233  'details' => translate('database_details'),
234  'dsn' => translate('dsn'),
235  'connector' => translate('db_connector'),
236  );
237  if (!array_key_exists($method, $options) || empty($method)) {
238  $method = 'details';
239  }//end if
240  $enable = ' style="display:block;visibility:visible;"';
241  $disable = ' style="display:none;visibility:hidden;"';
242  ob_start();
243  ?>
244  <script type="text/javascript">
245  function toggleDatabaseSetup(connectionType)
246  {
247  // Disbale all by default
248  var username = document.getElementById('trigger_action_user_username');
249  var password = document.getElementById('trigger_action_user_password');
250  var dbdetail = document.getElementById('trigger_action_database_details');
251  var dbcomdsn = document.getElementById('trigger_action_dsn');
252  var dbconnec = document.getElementById('trigger_action_connector');
253  dbdetail.style.display = "none";
254  dbdetail.style.visibility = "hidden";
255  dbcomdsn.style.display = "none";
256  dbcomdsn.style.visibility = "hidden";
257  dbconnec.style.display = "none";
258  dbconnec.style.visibility = "hidden";
259  username.style.display = "block";
260  username.style.visibility = "visible";
261  password.style.display = "block";
262  password.style.visibility = "visible";
263 
264  if (connectionType == "dsn") {
265  dbcomdsn.style.display = "block";
266  dbcomdsn.style.visibility = "visible";
267  } else if (connectionType == "connector") {
268  dbconnec.style.display = "block";
269  dbconnec.style.visibility = "visible";
270  username.style.display = "none";
271  username.style.visibility = "hidden";
272  password.style.display = "none";
273  password.style.visibility = "hidden";
274  } else {
275  dbdetail.style.display = "block";
276  dbdetail.style.visibility = "visible";
277  }
278  }
279  </script>
280  <table class="sq-backend-table" style="width:auto">
281  <tr>
282  <th><?php echo translate('connection_type'); ?></th>
283  <td><?php
284  if ($write_access) {
285  combo_box($prefix.'[method]', $options, FALSE, $method, '1', 'onchange="toggleDatabaseSetup(document.getElementById(\''.$prefix.'[method]\').value)"');
286  } else {
287  echo $options[$method];
288  }//end if
289  ?>
290  </td>
291  </tr>
292  <tr>
293  <th><?php echo translate('connection_setup'); ?></th>
294  <td>
295  <table class="sq-backend-table" style="width:auto">
296  <?php $style = ($method != 'connector') ? $enable : $disable; ?>
297  <tr id="trigger_action_user_username" <?php echo $style; ?>>
298  <th style="width:100px;"><?php echo translate('user_name');?></th>
299  <td><?php
300  if ($write_access) {
301  text_box($prefix.'[username]', $username, 35);
302  } else {
303  echo $username;
304  }//end if
305  ?>
306  </td>
307  </tr>
308  <tr id="trigger_action_user_password" <?php echo $style; ?>>
309  <th style="width:100px;"><?php echo translate('password');?></th>
310  <td><?php
311  if ($write_access) {
312  password_box($prefix.'[password]', $password, 35);
313  } else {
314  echo translate('data_password_not_displayed');
315  }//end if
316  ?>
317  </td>
318  </tr>
319  <?php $style = ($method == 'details') ? $enable : $disable; ?>
320  <tr id="trigger_action_database_details" <?php echo $style; ?>>
321  <th style="width:100px;"><?php echo translate('database_details'); ?></th>
322  <td>
323  <table class="sq-backend-table" style="width:auto">
324  <tr>
325  <th><?php echo translate('database_type');?></th>
326  <td>
327  <?php
328  $db_options = Array('pgsql' => 'PostgreSQL', 'oci' => 'Oracle', 'mysql' => 'MySQL');
329  if (!array_key_exists($type, $db_options) || empty($type)) {
330  $type = 'pgsql';
331  }//end if
332  if ($write_access) {
333  combo_box($prefix.'[type]', $db_options, FALSE, $type);
334  } else {
335  echo $db_options[$type];
336  }//end if
337  ?>
338  </td>
339  </tr>
340  <tr>
341  <th><?php echo translate('database_name');?></th>
342  <td><?php
343  if ($write_access) {
344  text_box($prefix.'[database]', $database, 35);
345  } else {
346  echo $database;
347  }//end if
348  ?>
349  </td>
350  </tr>
351  <tr>
352  <th><?php echo translate('host_name'); ?></th>
353  <td><?php
354  if ($write_access) {
355  text_box($prefix.'[host]', $host, 35);
356  } else {
357  echo $host;
358  }//end if
359  ?>
360  </td>
361  </tr>
362  </table>
363  </td>
364  </tr>
365  <?php $style = ($method == 'dsn') ? $enable : $disable; ?>
366  <tr id="trigger_action_dsn" <?php echo $style; ?>>
367  <th style="width:100px;"><?php echo translate('dsn'); ?></th>
368  <td><?php
369  if ($write_access) {
370  text_box($prefix.'[dsn]', $dsn, 70);
371  } else {
372  echo $dsn;
373  }//end if
374  ?>
375  </td>
376  </tr>
377  <?php $style = ($method == 'connector') ? $enable : $disable; ?>
378  <tr id="trigger_action_connector" <?php echo $style; ?>>
379  <th style="width:100px;"><?php echo translate('db_connector'); ?></th>
380  <td><?php
381  if ($write_access) {
382  asset_finder($prefix.'[connector]', $connector, Array('db_connector'=>'D'));
383  } else {
384  if (!empty($connector)) {
385  echo get_asset_tag_line($connector);
386  } else {
387  echo translate('no_asset_selected');
388  }//end if
389  }//end if
390  ?>
391  </td>
392  </tr>
393  </table>
394  </td>
395  </tr>
396  <tr>
397  <th><?php echo translate('sql_query'); ?></th>
398  <td><?php
399  if ($write_access) {
400  text_area($prefix.'[sql]', $sql, 100, 40, 0, 'style="width: 80%; height: 30em; font-family: \'Courier New\', Courier, monospace; display: block"');
401  } else {
402  echo $sql;
403  echo "<br />\n";
404  }//end if
405  echo translate('trigger_execute_database_query_note');
406  ?>
407  </td>
408  </tr>
409  </table>
410 
411  <?php
412  $interface = ob_get_contents();
413  ob_end_clean();
414 
415  return $interface;
416 
417  }//end getInterface()
418 
419 
431  public static function processInterface(&$settings, $request_data)
432  {
433 
434  // Save the request data to the settings array
435  $settings['method'] = array_get_index($request_data, 'method', 'details');
436  $settings['type'] = array_get_index($request_data, 'type', 'pgsql');
437  $settings['database'] = array_get_index($request_data, 'database', '');
438  $settings['host'] = array_get_index($request_data, 'host', '');
439  $settings['dsn'] = array_get_index($request_data, 'dsn', '');
440  $settings['connector'] = array_get_index((array_get_index($request_data, 'connector', Array())), 'assetid', '0');
441  $settings['username'] = array_get_index($request_data, 'username', '');
442  $settings['password'] = array_get_index($request_data, 'password', '');
443  $settings['sql'] = array_get_index($request_data, 'sql', '');
444 
445  return FALSE;
446 
447  }//end processInterface()
448 
449 
450 }//end class
451 
452 ?>