phpmyadmin/libraries/rte/rte_triggers.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for trigger management.
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Sets required globals
  14  *
  15  * @return void
  16  */
  17 function PMA_TRI_setGlobals()
  18 {
  19     global $action_timings, $event_manipulations;
  20
  21     // Some definitions for triggers
  22     $action_timings      = array('BEFORE',
  23                                  'AFTER');
  24     $event_manipulations = array('INSERT',
  25                                  'UPDATE',
  26                                  'DELETE');
  27 }
  28
  29 /**
  30  * Main function for the triggers functionality
  31  *
  32  * @return void
  33  */
  34 function PMA_TRI_main()
  35 {
  36     global $db, $table;
  37
  38     PMA_TRI_setGlobals();
  39     /**
  40      * Process all requests
  41      */
  42     PMA_TRI_handleEditor();
  43     PMA_TRI_handleExport();
  44     /**
  45      * Display a list of available triggers
  46      */
  47     $items = PMA_DBI_get_triggers($db, $table);
  48     echo PMA_RTE_getList('trigger', $items);
  49     /**
  50      * Display a link for adding a new trigger,
  51      * if the user has the necessary privileges
  52      */
  53     echo PMA_TRI_getFooterLinks();
  54 } // end PMA_TRI_main()
  55
  56 /**
  57  * Handles editor requests for adding or editing an item
  58  *
  59  * @return void
  60  */
  61 function PMA_TRI_handleEditor()
  62 {
  63     global $_REQUEST, $_POST, $errors, $db, $table;
  64
  65     if (! empty($_REQUEST['editor_process_add'])
  66         || ! empty($_REQUEST['editor_process_edit'])
  67     ) {
  68         $sql_query = '';
  69
  70         $item_query = PMA_TRI_getQueryFromRequest();
  71
  72         if (! count($errors)) { // set by PMA_RTN_getQueryFromRequest()
  73             // Execute the created query
  74             if (! empty($_REQUEST['editor_process_edit'])) {
  75                 // Backup the old trigger, in case something goes wrong
  76                 $trigger = PMA_TRI_getDataFromName($_REQUEST['item_original_name']);
  77                 $create_item = $trigger['create'];
  78                 $drop_item = $trigger['drop'] . ';';
  79                 $result = PMA_DBI_try_query($drop_item);
  80                 if (! $result) {
  81                     $errors[] = sprintf(
  82                         __('The following query has failed: "%s"'),
  83                         htmlspecialchars($drop_item)
  84                     )
  85                     . '<br />'
  86                     . __('MySQL said: ') . PMA_DBI_getError(null);
  87                 } else {
  88                     $result = PMA_DBI_try_query($item_query);
  89                     if (! $result) {
  90                         $errors[] = sprintf(
  91                             __('The following query has failed: "%s"'),
  92                             htmlspecialchars($item_query)
  93                         )
  94                         . '<br />'
  95                         . __('MySQL said: ') . PMA_DBI_getError(null);
  96                         // We dropped the old item, but were unable to create the new one
  97                         // Try to restore the backup query
  98                         $result = PMA_DBI_try_query($create_item);
  99                         if (! $result) {
 100                             // OMG, this is really bad! We dropped the query,
 101                             // failed to create a new one
 102                             // and now even the backup query does not execute!
 103                             // This should not happen, but we better handle
 104                             // this just in case.
 105                             $errors[] = __(
 106                                 'Sorry, we failed to restore the dropped trigger.'
 107                             )
 108                             . '<br />'
 109                             . __('The backed up query was:')
 110                             . "\"" . htmlspecialchars($create_item) . "\""
 111                             . '<br />'
 112                             . __('MySQL said: ') . PMA_DBI_getError(null);
 113                         }
 114                     } else {
 115                         $message = PMA_Message::success(
 116                             __('Trigger %1$s has been modified.')
 117                         );
 118                         $message->addParam(
 119                             PMA_Util::backquote($_REQUEST['item_name'])
 120                         );
 121                         $sql_query = $drop_item . $item_query;
 122                     }
 123                 }
 124             } else {
 125                 // 'Add a new item' mode
 126                 $result = PMA_DBI_try_query($item_query);
 127                 if (! $result) {
 128                     $errors[] = sprintf(
 129                         __('The following query has failed: "%s"'),
 130                         htmlspecialchars($item_query)
 131                     )
 132                     . '<br /><br />'
 133                     . __('MySQL said: ') . PMA_DBI_getError(null);
 134                 } else {
 135                     $message = PMA_Message::success(
 136                         __('Trigger %1$s has been created.')
 137                     );
 138                     $message->addParam(
 139                         PMA_Util::backquote($_REQUEST['item_name'])
 140                     );
 141                     $sql_query = $item_query;
 142                 }
 143             }
 144         }
 145
 146         if (count($errors)) {
 147             $message = PMA_Message::error(__('<b>One or more errors have occured while processing your request:</b>'));
 148             $message->addString('<ul>');
 149             foreach ($errors as $string) {
 150                 $message->addString('<li>' . $string . '</li>');
 151             }
 152             $message->addString('</ul>');
 153         }
 154
 155         $output = PMA_Util::getMessage($message, $sql_query);
 156         if ($GLOBALS['is_ajax_request']) {
 157             $response = PMA_Response::getInstance();
 158             if ($message->isSuccess()) {
 159                 $items = PMA_DBI_get_triggers($db, $table, '');
 160                 $trigger = false;
 161                 foreach ($items as $value) {
 162                     if ($value['name'] == $_REQUEST['item_name']) {
 163                         $trigger = $value;
 164                     }
 165                 }
 166                 $insert = false;
 167                 if (empty($table)
 168                     || ($trigger !== false && $table == $trigger['table'])
 169                 ) {
 170                     $insert = true;
 171                     $response->addJSON('new_row', PMA_TRI_getRowForList($trigger));
 172                     $response->addJSON(
 173                         'name',
 174                         htmlspecialchars(
 175                             strtoupper($_REQUEST['item_name'])
 176                         )
 177                     );
 178                 }
 179                 $response->addJSON('insert', $insert);
 180                 $response->addJSON('message', $output);
 181             } else {
 182                 $response->addJSON('message', $message);
 183                 $response->isSuccess(false);
 184             }
 185             exit;
 186         }
 187     }
 188
 189     /**
 190      * Display a form used to add/edit a trigger, if necessary
 191      */
 192     if (count($errors)
 193         || (empty($_REQUEST['editor_process_add'])
 194         && empty($_REQUEST['editor_process_edit'])
 195         && (! empty($_REQUEST['add_item'])
 196         || ! empty($_REQUEST['edit_item']))) // FIXME: this must be simpler than that
 197     ) {
 198         // Get the data for the form (if any)
 199         if (! empty($_REQUEST['add_item'])) {
 200             $title = PMA_RTE_getWord('add');
 201             $item = PMA_TRI_getDataFromRequest();
 202             $mode = 'add';
 203         } else if (! empty($_REQUEST['edit_item'])) {
 204             $title = __("Edit trigger");
 205             if (! empty($_REQUEST['item_name'])
 206                 && empty($_REQUEST['editor_process_edit'])
 207             ) {
 208                 $item = PMA_TRI_getDataFromName($_REQUEST['item_name']);
 209                 if ($item !== false) {
 210                     $item['item_original_name'] = $item['item_name'];
 211                 }
 212             } else {
 213                 $item = PMA_TRI_getDataFromRequest();
 214             }
 215             $mode = 'edit';
 216         }
 217         if ($item !== false) {
 218             // Show form
 219             $editor = PMA_TRI_getEditorForm($mode, $item);
 220             if ($GLOBALS['is_ajax_request']) {
 221                 $response = PMA_Response::getInstance();
 222                 $response->addJSON('message', $editor);
 223                 $response->addJSON('title', $title);
 224             } else {
 225                 echo "\n\n<h2>$title</h2>\n\n$editor";
 226                 unset($_POST);
 227             }
 228             exit;
 229         } else {
 230             $message  = __('Error in processing request') . ' : ';
 231             $message .= sprintf(
 232                 PMA_RTE_getWord('not_found'),
 233                 htmlspecialchars(PMA_Util::backquote($_REQUEST['item_name'])),
 234                 htmlspecialchars(PMA_Util::backquote($db))
 235             );
 236             $message = PMA_message::error($message);
 237             if ($GLOBALS['is_ajax_request']) {
 238                 $response = PMA_Response::getInstance();
 239                 $response->isSuccess(false);
 240                 $response->addJSON('message', $message);
 241                 exit;
 242             } else {
 243                 $message->display();
 244             }
 245         }
 246     }
 247 } // end PMA_TRI_handleEditor()
 248
 249 /**
 250  * This function will generate the values that are required to for the editor
 251  *
 252  * @return array    Data necessary to create the editor.
 253  */
 254 function PMA_TRI_getDataFromRequest()
 255 {
 256     $retval = array();
 257     $indices = array('item_name',
 258                      'item_table',
 259                      'item_original_name',
 260                      'item_action_timing',
 261                      'item_event_manipulation',
 262                      'item_definition',
 263                      'item_definer');
 264     foreach ($indices as $index) {
 265         $retval[$index] = isset($_REQUEST[$index]) ? $_REQUEST[$index] : '';
 266     }
 267     return $retval;
 268 } // end PMA_TRI_getDataFromRequest()
 269
 270 /**
 271  * This function will generate the values that are required to complete
 272  * the "Edit trigger" form given the name of a trigger.
 273  *
 274  * @param string $name The name of the trigger.
 275  *
 276  * @return array Data necessary to create the editor.
 277  */
 278 function PMA_TRI_getDataFromName($name)
 279 {
 280     global $db, $table, $_REQUEST;
 281
 282     $temp = array();
 283     $items = PMA_DBI_get_triggers($db, $table, '');
 284     foreach ($items as $value) {
 285         if ($value['name'] == $name) {
 286             $temp = $value;
 287         }
 288     }
 289     if (empty($temp)) {
 290         return false;
 291     } else {
 292         $retval = array();
 293         $retval['create']                  = $temp['create'];
 294         $retval['drop']                    = $temp['drop'];
 295         $retval['item_name']               = $temp['name'];
 296         $retval['item_table']              = $temp['table'];
 297         $retval['item_action_timing']      = $temp['action_timing'];
 298         $retval['item_event_manipulation'] = $temp['event_manipulation'];
 299         $retval['item_definition']         = $temp['definition'];
 300         $retval['item_definer']            = $temp['definer'];
 301         return $retval;
 302     }
 303 } // end PMA_TRI_getDataFromName()
 304
 305 /**
 306  * Displays a form used to add/edit a trigger
 307  *
 308  * @param string $mode If the editor will be used edit a trigger
 309  *                     or add a new one: 'edit' or 'add'.
 310  * @param array  $item Data for the trigger returned by PMA_TRI_getDataFromRequest()
 311  *                     or PMA_TRI_getDataFromName()
 312  *
 313  * @return string HTML code for the editor.
 314  */
 315 function PMA_TRI_getEditorForm($mode, $item)
 316 {
 317     global $db, $table, $event_manipulations, $action_timings;
 318
 319     // Escape special characters
 320     $need_escape = array(
 321                        'item_original_name',
 322                        'item_name',
 323                        'item_definition',
 324                        'item_definer'
 325                    );
 326     foreach ($need_escape as $key => $index) {
 327         $item[$index] = htmlentities($item[$index], ENT_QUOTES, 'UTF-8');
 328     }
 329     $original_data = '';
 330     if ($mode == 'edit') {
 331         $original_data = "<input name='item_original_name' "
 332                        . "type='hidden' value='{$item['item_original_name']}'/>\n";
 333     }
 334     $query  = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` ";
 335     $query .= "WHERE `TABLE_SCHEMA`='" . PMA_Util::sqlAddSlashes($db) . "' ";
 336     $query .= "AND `TABLE_TYPE`='BASE TABLE'";
 337     $tables = PMA_DBI_fetch_result($query);
 338
 339     // Create the output
 340     $retval  = "";
 341     $retval .= "<!-- START " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 342     $retval .= "<form class='rte_form' action='db_triggers.php' method='post'>\n";
 343     $retval .= "<input name='{$mode}_item' type='hidden' value='1' />\n";
 344     $retval .= $original_data;
 345     $retval .= PMA_generate_common_hidden_inputs($db, $table) . "\n";
 346     $retval .= "<fieldset>\n";
 347     $retval .= "<legend>" . __('Details') . "</legend>\n";
 348     $retval .= "<table class='rte_table' style='width: 100%'>\n";
 349     $retval .= "<tr>\n";
 350     $retval .= "    <td style='width: 20%;'>" . __('Trigger name') . "</td>\n";
 351     $retval .= "    <td><input type='text' name='item_name' maxlength='64'\n";
 352     $retval .= "               value='{$item['item_name']}' /></td>\n";
 353     $retval .= "</tr>\n";
 354     $retval .= "<tr>\n";
 355     $retval .= "    <td>" . __('Table') . "</td>\n";
 356     $retval .= "    <td>\n";
 357     $retval .= "        <select name='item_table'>\n";
 358     foreach ($tables as $key => $value) {
 359         $selected = "";
 360         if ($mode == 'add' && $value == $table) {
 361             $selected = " selected='selected'";
 362         } else if ($mode == 'edit' && $value == $item['item_table']) {
 363             $selected = " selected='selected'";
 364         }
 365         $retval .= "<option$selected>";
 366         $retval .= htmlspecialchars($value);
 367         $retval .= "</option>\n";
 368     }
 369     $retval .= "        </select>\n";
 370     $retval .= "    </td>\n";
 371     $retval .= "</tr>\n";
 372     $retval .= "<tr>\n";
 373     $retval .= "    <td>" . _pgettext('Trigger action time', 'Time') . "</td>\n";
 374     $retval .= "    <td><select name='item_timing'>\n";
 375     foreach ($action_timings as $key => $value) {
 376         $selected = "";
 377         if (! empty($item['item_action_timing'])
 378             && $item['item_action_timing'] == $value
 379         ) {
 380             $selected = " selected='selected'";
 381         }
 382         $retval .= "<option$selected>$value</option>";
 383     }
 384     $retval .= "    </select></td>\n";
 385     $retval .= "</tr>\n";
 386     $retval .= "<tr>\n";
 387     $retval .= "    <td>" . __('Event') . "</td>\n";
 388     $retval .= "    <td><select name='item_event'>\n";
 389     foreach ($event_manipulations as $key => $value) {
 390         $selected = "";
 391         if (! empty($item['item_event_manipulation'])
 392             && $item['item_event_manipulation'] == $value
 393         ) {
 394             $selected = " selected='selected'";
 395         }
 396         $retval .= "<option$selected>$value</option>";
 397     }
 398     $retval .= "    </select></td>\n";
 399     $retval .= "</tr>\n";
 400     $retval .= "<tr>\n";
 401     $retval .= "    <td>" . __('Definition') . "</td>\n";
 402     $retval .= "    <td><textarea name='item_definition' rows='15' cols='40'>";
 403     $retval .= $item['item_definition'];
 404     $retval .= "</textarea></td>\n";
 405     $retval .= "</tr>\n";
 406     $retval .= "<tr>\n";
 407     $retval .= "    <td>" . __('Definer') . "</td>\n";
 408     $retval .= "    <td><input type='text' name='item_definer'\n";
 409     $retval .= "               value='{$item['item_definer']}' /></td>\n";
 410     $retval .= "</tr>\n";
 411     $retval .= "</table>\n";
 412     $retval .= "</fieldset>\n";
 413     if ($GLOBALS['is_ajax_request']) {
 414         $retval .= "<input type='hidden' name='editor_process_{$mode}'\n";
 415         $retval .= "       value='true' />\n";
 416         $retval .= "<input type='hidden' name='ajax_request' value='true' />\n";
 417     } else {
 418         $retval .= "<fieldset class='tblFooters'>\n";
 419         $retval .= "    <input type='submit' name='editor_process_{$mode}'\n";
 420         $retval .= "           value='" . __('Go') . "' />\n";
 421         $retval .= "</fieldset>\n";
 422     }
 423     $retval .= "</form>\n\n";
 424     $retval .= "<!-- END " . strtoupper($mode) . " TRIGGER FORM -->\n\n";
 425
 426     return $retval;
 427 } // end PMA_TRI_getEditorForm()
 428
 429 /**
 430  * Composes the query necessary to create a trigger from an HTTP request.
 431  *
 432  * @return string  The CREATE TRIGGER query.
 433  */
 434 function PMA_TRI_getQueryFromRequest()
 435 {
 436     global $_REQUEST, $db, $errors, $action_timings, $event_manipulations;
 437
 438     $query = 'CREATE ';
 439     if (! empty($_REQUEST['item_definer'])) {
 440         if (strpos($_REQUEST['item_definer'], '@') !== false) {
 441             $arr = explode('@', $_REQUEST['item_definer']);
 442             $query .= 'DEFINER=' . PMA_Util::backquote($arr[0]);
 443             $query .= '@' . PMA_Util::backquote($arr[1]) . ' ';
 444         } else {
 445             $errors[] = __('The definer must be in the "username@hostname" format');
 446         }
 447     }
 448     $query .= 'TRIGGER ';
 449     if (! empty($_REQUEST['item_name'])) {
 450         $query .= PMA_Util::backquote($_REQUEST['item_name']) . ' ';
 451     } else {
 452         $errors[] = __('You must provide a trigger name');
 453     }
 454     if (! empty($_REQUEST['item_timing'])
 455         && in_array($_REQUEST['item_timing'], $action_timings)
 456     ) {
 457         $query .= $_REQUEST['item_timing'] . ' ';
 458     } else {
 459         $errors[] = __('You must provide a valid timing for the trigger');
 460     }
 461     if (! empty($_REQUEST['item_event'])
 462         && in_array($_REQUEST['item_event'], $event_manipulations)
 463     ) {
 464         $query .= $_REQUEST['item_event'] . ' ';
 465     } else {
 466         $errors[] = __('You must provide a valid event for the trigger');
 467     }
 468     $query .= 'ON ';
 469     if (! empty($_REQUEST['item_table'])
 470         && in_array($_REQUEST['item_table'], PMA_DBI_get_tables($db))
 471     ) {
 472         $query .= PMA_Util::backquote($_REQUEST['item_table']);
 473     } else {
 474         $errors[] = __('You must provide a valid table name');
 475     }
 476     $query .= ' FOR EACH ROW ';
 477     if (! empty($_REQUEST['item_definition'])) {
 478         $query .= $_REQUEST['item_definition'];
 479     } else {
 480         $errors[] = __('You must provide a trigger definition.');
 481     }
 482
 483     return $query;
 484 } // end PMA_TRI_getQueryFromRequest()
 485
 486 ?>