Edit File: Routines.php
";
$retval .= "";
return $retval;
} // end self::getEditorForm()
/**
* Composes the query necessary to create a routine from an HTTP request.
*
* @return string The CREATE [ROUTINE | PROCEDURE] query.
*/
public static function getQueryFromRequest()
{
global $_REQUEST, $errors, $param_sqldataaccess, $param_directions, $dbi;
$_POST['item_type'] = isset($_POST['item_type'])
? $_POST['item_type'] : '';
$query = 'CREATE ';
if (! empty($_POST['item_definer'])) {
if (mb_strpos($_POST['item_definer'], '@') !== false) {
$arr = explode('@', $_POST['item_definer']);
$do_backquote = true;
if (substr($arr[0], 0, 1) === "`"
&& substr($arr[0], -1) === "`"
) {
$do_backquote = false;
}
$query .= 'DEFINER=' . Util::backquote($arr[0], $do_backquote);
$do_backquote = true;
if (substr($arr[1], 0, 1) === "`"
&& substr($arr[1], -1) === "`"
) {
$do_backquote = false;
}
$query .= '@' . Util::backquote($arr[1], $do_backquote) . ' ';
} else {
$errors[] = __('The definer must be in the "username@hostname" format!');
}
}
if ($_POST['item_type'] == 'FUNCTION'
|| $_POST['item_type'] == 'PROCEDURE'
) {
$query .= $_POST['item_type'] . ' ';
} else {
$errors[] = sprintf(
__('Invalid routine type: "%s"'),
htmlspecialchars($_POST['item_type'])
);
}
if (! empty($_POST['item_name'])) {
$query .= Util::backquote($_POST['item_name']);
} else {
$errors[] = __('You must provide a routine name!');
}
$params = '';
$warned_about_dir = false;
$warned_about_length = false;
if (! empty($_POST['item_param_name'])
&& ! empty($_POST['item_param_type'])
&& ! empty($_POST['item_param_length'])
&& is_array($_POST['item_param_name'])
&& is_array($_POST['item_param_type'])
&& is_array($_POST['item_param_length'])
) {
$item_param_name = $_POST['item_param_name'];
$item_param_type = $_POST['item_param_type'];
$item_param_length = $_POST['item_param_length'];
for ($i=0, $nb = count($item_param_name); $i < $nb; $i++) {
if (! empty($item_param_name[$i])
&& ! empty($item_param_type[$i])
) {
if ($_POST['item_type'] == 'PROCEDURE'
&& ! empty($_POST['item_param_dir'][$i])
&& in_array($_POST['item_param_dir'][$i], $param_directions)
) {
$params .= $_POST['item_param_dir'][$i] . " "
. Util::backquote($item_param_name[$i])
. " " . $item_param_type[$i];
} elseif ($_POST['item_type'] == 'FUNCTION') {
$params .= Util::backquote($item_param_name[$i])
. " " . $item_param_type[$i];
} elseif (! $warned_about_dir) {
$warned_about_dir = true;
$errors[] = sprintf(
__('Invalid direction "%s" given for parameter.'),
htmlspecialchars($_POST['item_param_dir'][$i])
);
}
if ($item_param_length[$i] != ''
&& !preg_match(
'@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
. 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|'
. 'SERIAL|BOOLEAN)$@i',
$item_param_type[$i]
)
) {
$params .= "(" . $item_param_length[$i] . ")";
} elseif ($item_param_length[$i] == ''
&& preg_match(
'@^(ENUM|SET|VARCHAR|VARBINARY)$@i',
$item_param_type[$i]
)
) {
if (! $warned_about_length) {
$warned_about_length = true;
$errors[] = __(
'You must provide length/values for routine parameters'
. ' of type ENUM, SET, VARCHAR and VARBINARY.'
);
}
}
if (! empty($_POST['item_param_opts_text'][$i])) {
if ($dbi->types->getTypeClass($item_param_type[$i]) == 'CHAR') {
if(! in_array($item_param_type[$i], array('VARBINARY', 'BINARY'))) {
$params .= ' CHARSET '
. mb_strtolower(
$_POST['item_param_opts_text'][$i]
);
}
}
}
if (! empty($_POST['item_param_opts_num'][$i])) {
if ($dbi->types->getTypeClass($item_param_type[$i]) == 'NUMBER') {
$params .= ' '
. mb_strtoupper(
$_POST['item_param_opts_num'][$i]
);
}
}
if ($i != (count($item_param_name) - 1)) {
$params .= ", ";
}
} else {
$errors[] = __(
'You must provide a name and a type for each routine parameter.'
);
break;
}
}
}
$query .= "(" . $params . ") ";
if ($_POST['item_type'] == 'FUNCTION') {
$item_returntype = isset($_POST['item_returntype'])
? $_POST['item_returntype']
: null;
if (! empty($item_returntype)
&& in_array(
$item_returntype, Util::getSupportedDatatypes()
)
) {
$query .= "RETURNS " . $item_returntype;
} else {
$errors[] = __('You must provide a valid return type for the routine.');
}
if (! empty($_POST['item_returnlength'])
&& !preg_match(
'@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|'
. 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i',
$item_returntype
)
) {
$query .= "(" . $_POST['item_returnlength'] . ")";
} elseif (empty($_POST['item_returnlength'])
&& preg_match(
'@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $item_returntype
)
) {
if (! $warned_about_length) {
$errors[] = __(
'You must provide length/values for routine parameters'
. ' of type ENUM, SET, VARCHAR and VARBINARY.'
);
}
}
if (! empty($_POST['item_returnopts_text'])) {
if ($dbi->types->getTypeClass($item_returntype) == 'CHAR') {
$query .= ' CHARSET '
. mb_strtolower($_POST['item_returnopts_text']);
}
}
if (! empty($_POST['item_returnopts_num'])) {
if ($dbi->types->getTypeClass($item_returntype) == 'NUMBER') {
$query .= ' '
. mb_strtoupper($_POST['item_returnopts_num']);
}
}
$query .= ' ';
}
if (! empty($_POST['item_comment'])) {
$query .= "COMMENT '" . $GLOBALS['dbi']->escapeString($_POST['item_comment'])
. "' ";
}
if (isset($_POST['item_isdeterministic'])) {
$query .= 'DETERMINISTIC ';
} else {
$query .= 'NOT DETERMINISTIC ';
}
if (! empty($_POST['item_sqldataaccess'])
&& in_array($_POST['item_sqldataaccess'], $param_sqldataaccess)
) {
$query .= $_POST['item_sqldataaccess'] . ' ';
}
if (! empty($_POST['item_securitytype'])) {
if ($_POST['item_securitytype'] == 'DEFINER'
|| $_POST['item_securitytype'] == 'INVOKER'
) {
$query .= 'SQL SECURITY ' . $_POST['item_securitytype'] . ' ';
}
}
if (! empty($_POST['item_definition'])) {
$query .= $_POST['item_definition'];
} else {
$errors[] = __('You must provide a routine definition.');
}
return $query;
} // end self::getQueryFromRequest()
/**
* Handles requests for executing a routine
*
* @return void
*/
public static function handleExecute()
{
global $_GET, $_POST, $_REQUEST, $GLOBALS, $db;
$response = Response::getInstance();
/**
* Handle all user requests other than the default of listing routines
*/
if (! empty($_POST['execute_routine']) && ! empty($_POST['item_name'])) {
// Build the queries
$routine = self::getDataFromName(
$_POST['item_name'], $_POST['item_type'], false
);
if ($routine === false) {
$message = __('Error in processing request:') . ' ';
$message .= sprintf(
Words::get('not_found'),
htmlspecialchars(Util::backquote($_POST['item_name'])),
htmlspecialchars(Util::backquote($db))
);
$message = Message::error($message);
if ($response->isAjax()) {
$response->setRequestStatus(false);
$response->addJSON('message', $message);
exit;
} else {
echo $message->getDisplay();
unset($_POST);
}
}
$queries = array();
$end_query = array();
$args = array();
$all_functions = $GLOBALS['dbi']->types->getAllFunctions();
for ($i = 0; $i < $routine['item_num_params']; $i++) {
if (isset($_POST['params'][$routine['item_param_name'][$i]])) {
$value = $_POST['params'][$routine['item_param_name'][$i]];
if (is_array($value)) { // is SET type
$value = implode(',', $value);
}
$value = $GLOBALS['dbi']->escapeString($value);
if (! empty($_POST['funcs'][$routine['item_param_name'][$i]])
&& in_array(
$_POST['funcs'][$routine['item_param_name'][$i]],
$all_functions
)
) {
$queries[] = "SET @p$i="
. $_POST['funcs'][$routine['item_param_name'][$i]]
. "('$value');\n";
} else {
$queries[] = "SET @p$i='$value';\n";
}
$args[] = "@p$i";
} else {
$args[] = "@p$i";
}
if ($routine['item_type'] == 'PROCEDURE') {
if ($routine['item_param_dir'][$i] == 'OUT'
|| $routine['item_param_dir'][$i] == 'INOUT'
) {
$end_query[] = "@p$i AS "
. Util::backquote($routine['item_param_name'][$i]);
}
}
}
if ($routine['item_type'] == 'PROCEDURE') {
$queries[] = "CALL " . Util::backquote($routine['item_name'])
. "(" . implode(', ', $args) . ");\n";
if (count($end_query)) {
$queries[] = "SELECT " . implode(', ', $end_query) . ";\n";
}
} else {
$queries[] = "SELECT " . Util::backquote($routine['item_name'])
. "(" . implode(', ', $args) . ") "
. "AS " . Util::backquote($routine['item_name'])
. ";\n";
}
// Get all the queries as one SQL statement
$multiple_query = implode("", $queries);
$outcome = true;
$affected = 0;
// Execute query
if (! $GLOBALS['dbi']->tryMultiQuery($multiple_query)) {
$outcome = false;
}
// Generate output
if ($outcome) {
// Pass the SQL queries through the "pretty printer"
$output = Util::formatSql(implode($queries, "\n"));
// Display results
$output .= "
";
$message = __('Your SQL query has been executed successfully.');
if ($routine['item_type'] == 'PROCEDURE') {
$message .= '
';
// TODO : message need to be modified according to the
// output from the routine
$message .= sprintf(
_ngettext(
'%d row affected by the last statement inside the '
. 'procedure.',
'%d rows affected by the last statement inside the '
. 'procedure.',
$affected
),
$affected
);
}
$message = Message::success($message);
if ($nbResultsetToDisplay == 0) {
$notice = __(
'MySQL returned an empty result set (i.e. zero rows).'
);
$output .= Message::notice($notice)->getDisplay();
}
} else {
$output = '';
$message = Message::error(
sprintf(
__('The following query has failed: "%s"'),
htmlspecialchars($multiple_query)
)
. '
'
. __('MySQL said: ') . $GLOBALS['dbi']->getError()
);
}
// Print/send output
if ($response->isAjax()) {
$response->setRequestStatus($message->isSuccess());
$response->addJSON('message', $message->getDisplay() . $output);
$response->addJSON('dialog', false);
exit;
} else {
echo $message->getDisplay() , $output;
if ($message->isError()) {
// At least one query has failed, so shouldn't
// execute any more queries, so we quit.
exit;
}
unset($_POST);
// Now deliberately fall through to displaying the routines list
}
return;
} elseif (! empty($_GET['execute_dialog']) && ! empty($_GET['item_name'])) {
/**
* Display the execute form for a routine.
*/
$routine = self::getDataFromName(
$_GET['item_name'], $_GET['item_type'], true
);
if ($routine !== false) {
$form = self::getExecuteForm($routine);
if ($response->isAjax()) {
$title = __("Execute routine") . " " . Util::backquote(
htmlentities($_GET['item_name'], ENT_QUOTES)
);
$response->addJSON('message', $form);
$response->addJSON('title', $title);
$response->addJSON('dialog', true);
} else {
echo "\n\n
" . __("Execute routine") . "
\n\n";
echo $form;
}
exit;
} elseif (($response->isAjax())) {
$message = __('Error in processing request:') . ' ';
$message .= sprintf(
Words::get('not_found'),
htmlspecialchars(Util::backquote($_GET['item_name'])),
htmlspecialchars(Util::backquote($db))
);
$message = Message::error($message);
$response->setRequestStatus(false);
$response->addJSON('message', $message);
exit;
}
}
}
/**
* Browse row array
*
* @param array $row Columns
*
* @return string
*/
private static function browseRow(array $row)
{
$output = null;
foreach ($row as $value) {
if ($value === null) {
$value = '
NULL';
} else {
$value = htmlspecialchars($value);
}
$output .= "
" . $value . " | ";
}
return $output;
}
/**
* Creates the HTML code that shows the routine execution dialog.
*
* @param array $routine Data for the routine returned by
* self::getDataFromName()
*
* @return string HTML code for the routine execution dialog.
*/
public static function getExecuteForm(array $routine)
{
global $db, $cfg;
$response = Response::getInstance();
// Escape special characters
$routine['item_name'] = htmlentities($routine['item_name'], ENT_QUOTES);
for ($i = 0; $i < $routine['item_num_params']; $i++) {
$routine['item_param_name'][$i] = htmlentities(
$routine['item_param_name'][$i],
ENT_QUOTES
);
}
// Create the output
$retval = "";
$retval .= "\n\n";
$retval .= "