';
$retval .= '';
return $retval;
}
/**
* Set the found errors and build the params
*
* @param string[] $itemParamName The parameter names
* @param string[] $itemParamDir The direction parameter (see $this->directions)
* @param array $itemParamType The parameter type
* @param array $itemParamLength A length or not for the parameter
* @param array $itemParamOpsText An optional charset for the parameter
* @param array $itemParamOpsNum An optional parameter for a $itemParamType NUMBER
* @param string $itemType The item type (PROCEDURE/FUNCTION)
* @param bool $warnedAboutLength A boolean that will be switched if a the length warning is given
*/
private function processParamsAndBuild(
array $itemParamName,
array $itemParamDir,
array $itemParamType,
array $itemParamLength,
array $itemParamOpsText,
array $itemParamOpsNum,
string $itemType,
bool &$warnedAboutLength
): string {
global $errors, $dbi;
$params = '';
$warnedAboutDir = false;
for ($i = 0, $nb = count($itemParamName); $i < $nb; $i++) {
if (empty($itemParamName[$i])
|| empty($itemParamType[$i])
) {
$errors[] = __(
'You must provide a name and a type for each routine parameter.'
);
break;
}
if ($itemType === 'PROCEDURE'
&& ! empty($itemParamDir[$i])
&& in_array($itemParamDir[$i], $this->directions)
) {
$params .= $itemParamDir[$i] . ' '
. Util::backquote($itemParamName[$i])
. ' ' . $itemParamType[$i];
} elseif ($itemType === 'FUNCTION') {
$params .= Util::backquote($itemParamName[$i])
. ' ' . $itemParamType[$i];
} elseif (! $warnedAboutDir) {
$warnedAboutDir = true;
$errors[] = sprintf(
__('Invalid direction "%s" given for parameter.'),
htmlspecialchars($itemParamDir[$i])
);
}
if ($itemParamLength[$i] != ''
&& ! preg_match(
'@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
. 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|'
. 'SERIAL|BOOLEAN)$@i',
$itemParamType[$i]
)
) {
$params .= '(' . $itemParamLength[$i] . ')';
} elseif ($itemParamLength[$i] == ''
&& preg_match(
'@^(ENUM|SET|VARCHAR|VARBINARY)$@i',
$itemParamType[$i]
)
) {
if (! $warnedAboutLength) {
$warnedAboutLength = true;
$errors[] = __(
'You must provide length/values for routine parameters'
. ' of type ENUM, SET, VARCHAR and VARBINARY.'
);
}
}
if (! empty($itemParamOpsText[$i])) {
if ($dbi->types->getTypeClass($itemParamType[$i]) === 'CHAR') {
if (! in_array($itemParamType[$i], ['VARBINARY', 'BINARY'])) {
$params .= ' CHARSET '
. mb_strtolower(
$itemParamOpsText[$i]
);
}
}
}
if (! empty($itemParamOpsNum[$i])) {
if ($dbi->types->getTypeClass($itemParamType[$i]) === 'NUMBER') {
$params .= ' '
. mb_strtoupper(
$itemParamOpsNum[$i]
);
}
}
if ($i == count($itemParamName) - 1) {
continue;
}
$params .= ', ';
}
return $params;
}
/**
* Set the found errors and build the query
*
* @param string $query The existing query
* @param bool $warnedAboutLength If the length warning was given
*/
private function processFunctionSpecificParameters(
string $query,
bool $warnedAboutLength
): string {
global $errors, $dbi;
$itemReturnType = $_POST['item_returntype'] ?? null;
if (! empty($itemReturnType)
&& in_array(
$itemReturnType,
Util::getSupportedDatatypes()
)
) {
$query .= 'RETURNS ' . $itemReturnType;
} 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',
$itemReturnType
)
) {
$query .= '(' . $_POST['item_returnlength'] . ')';
} elseif (empty($_POST['item_returnlength'])
&& preg_match(
'@^(ENUM|SET|VARCHAR|VARBINARY)$@i',
$itemReturnType
)
) {
if (! $warnedAboutLength) {
$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($itemReturnType) === 'CHAR') {
$query .= ' CHARSET '
. mb_strtolower($_POST['item_returnopts_text']);
}
}
if (! empty($_POST['item_returnopts_num'])) {
if ($dbi->types->getTypeClass($itemReturnType) === 'NUMBER') {
$query .= ' '
. mb_strtoupper($_POST['item_returnopts_num']);
}
}
return $query . ' ';
}
/**
* Composes the query necessary to create a routine from an HTTP request.
*
* @return string The CREATE [ROUTINE | PROCEDURE] query.
*/
public function getQueryFromRequest(): string
{
global $errors;
$itemType = $_POST['item_type'] ?? '';
$itemDefiner = $_POST['item_definer'] ?? '';
$itemName = $_POST['item_name'] ?? '';
$query = 'CREATE ';
if (! empty($itemDefiner)) {
if (mb_strpos($itemDefiner, '@') !== false) {
$arr = explode('@', $itemDefiner);
$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 ($itemType === 'FUNCTION'
|| $itemType === 'PROCEDURE'
) {
$query .= $itemType . ' ';
} else {
$errors[] = sprintf(
__('Invalid routine type: "%s"'),
htmlspecialchars($itemType)
);
}
if (! empty($itemName)) {
$query .= Util::backquote($itemName);
} else {
$errors[] = __('You must provide a routine name!');
}
$warnedAboutLength = false;
$itemParamName = $_POST['item_param_name'] ?? '';
$itemParamType = $_POST['item_param_type'] ?? '';
$itemParamLength = $_POST['item_param_length'] ?? '';
$itemParamDir = (array) ($_POST['item_param_dir'] ?? []);
$itemParamOpsText = (array) ($_POST['item_param_opts_text'] ?? []);
$itemParamOpsNum = (array) ($_POST['item_param_opts_num'] ?? []);
$params = '';
if (! empty($itemParamName)
&& ! empty($itemParamType)
&& ! empty($itemParamLength)
&& is_array($itemParamName)
&& is_array($itemParamType)
&& is_array($itemParamLength)
) {
$params = $this->processParamsAndBuild(
$itemParamName,
$itemParamDir,
$itemParamType,
$itemParamLength,
$itemParamOpsText,
$itemParamOpsNum,
$itemType,
$warnedAboutLength// Will possibly be modified by the function
);
}
$query .= '(' . $params . ') ';
if ($itemType === 'FUNCTION') {
$query = $this->processFunctionSpecificParameters($query, $warnedAboutLength);
}
if (! empty($_POST['item_comment'])) {
$query .= "COMMENT '" . $this->dbi->escapeString($_POST['item_comment'])
. "' ";
}
if (isset($_POST['item_isdeterministic'])) {
$query .= 'DETERMINISTIC ';
} else {
$query .= 'NOT DETERMINISTIC ';
}
$itemSqlDataAccess = $_POST['item_sqldataaccess'] ?? '';
if (! empty($itemSqlDataAccess)
&& in_array($itemSqlDataAccess, $this->sqlDataAccess)
) {
$query .= $itemSqlDataAccess . ' ';
}
$itemSecurityType = $_POST['item_securitytype'] ?? '';
if (! empty($itemSecurityType)) {
if ($itemSecurityType === 'DEFINER'
|| $itemSecurityType === 'INVOKER'
) {
$query .= 'SQL SECURITY ' . $itemSecurityType . ' ';
}
}
$itemDefinition = $_POST['item_definition'] ?? '';
if (! empty($itemDefinition)) {
$query .= $itemDefinition;
} else {
$errors[] = __('You must provide a routine definition.');
}
return $query;
}
/**
* @see handleExecuteRoutine
*
* @param array $routine The routine params
*
* @return string[] The SQL queries / SQL query parts
*/
private function getQueriesFromRoutineForm(array $routine): array
{
$queries = [];
$end_query = [];
$args = [];
$all_functions = $this->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 = $this->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') {
continue;
}
if ($routine['item_param_dir'][$i] !== 'OUT'
&& $routine['item_param_dir'][$i] !== 'INOUT'
) {
continue;
}
$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";
}
return $queries;
}
private function handleExecuteRoutine(): void
{
global $db;
// Build the queries
$routine = $this->getDataFromName(
$_POST['item_name'],
$_POST['item_type'],
false
);
if ($routine === null) {
$message = __('Error in processing request:') . ' ';
$message .= sprintf(
__('No routine with name %1$s found in database %2$s.'),
htmlspecialchars(Util::backquote($_POST['item_name'])),
htmlspecialchars(Util::backquote($db))
);
$message = Message::error($message);
if ($this->response->isAjax()) {
$this->response->setRequestStatus(false);
$this->response->addJSON('message', $message);
exit;
}
echo $message->getDisplay();
unset($_POST);
//NOTE: Missing exit ?
}
$queries = is_array($routine) ? $this->getQueriesFromRoutineForm($routine) : [];
// Get all the queries as one SQL statement
$multiple_query = implode('', $queries);
$outcome = true;
$affected = 0;
// Execute query
if (! $this->dbi->tryMultiQuery($multiple_query)) {
$outcome = false;
}
// Generate output
$output = '';
$nbResultsetToDisplay = 0;
if ($outcome) {
// Pass the SQL queries through the "pretty printer"
$output = Generator::formatSql(implode("\n", $queries));
// 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.',
(int) $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: ') . $this->dbi->getError()
);
}
// Print/send output
if ($this->response->isAjax()) {
$this->response->setRequestStatus($message->isSuccess());
$this->response->addJSON('message', $message->getDisplay() . $output);
$this->response->addJSON('dialog', false);
exit;
}
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
}
/**
* Handles requests for executing a routine
*
* @return void
*/
public function handleExecute()
{
global $db;
/**
* Handle all user requests other than the default of listing routines
*/
if (! empty($_POST['execute_routine']) && ! empty($_POST['item_name'])) {
$this->handleExecuteRoutine();
} elseif (! empty($_GET['execute_dialog']) && ! empty($_GET['item_name'])) {
/**
* Display the execute form for a routine.
*/
$routine = $this->getDataFromName(
$_GET['item_name'],
$_GET['item_type'],
true
);
if ($routine !== null) {
$form = $this->getExecuteForm($routine);
if ($this->response->isAjax()) {
$title = __('Execute routine') . ' ' . Util::backquote(
htmlentities($_GET['item_name'], ENT_QUOTES)
);
$this->response->addJSON('message', $form);
$this->response->addJSON('title', $title);
$this->response->addJSON('dialog', true);
} else {
echo "\n\n
" . __('Execute routine') . "
\n\n";
echo $form;
}
exit;
}
if ($this->response->isAjax()) {
$message = __('Error in processing request:') . ' ';
$message .= sprintf(
__('No routine with name %1$s found in database %2$s.'),
htmlspecialchars(Util::backquote($_GET['item_name'])),
htmlspecialchars(Util::backquote($db))
);
$message = Message::error($message);
$this->response->setRequestStatus(false);
$this->response->addJSON('message', $message);
exit;
}
}
}
/**
* Browse row array
*
* @param array $row Columns
*/
private function browseRow(array $row): ?string
{
$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
* getDataFromName()
*
* @return string HTML code for the routine execution dialog.
*/
public function getExecuteForm(array $routine): string
{
global $db, $cfg;
// 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 .= '