{

private const FUNC_OPTIONAL_PARAM = ['NOW', 'RAND', 'UNIX_TIMESTAMP'];

private const FUNC_NO_PARAM = [

'CONNECTION_ID',

'CURRENT_USER',

'CURDATE',

'CURTIME',

'CURRENT_DATE',

'CURRENT_TIME',

'DATABASE',

'LAST_INSERT_ID',

'NOW',

'PI',

'RAND',

'SYSDATE',

'UNIX_TIMESTAMP',

'USER',

'UTC_DATE',

'UTC_TIME',

'UTC_TIMESTAMP',

'UUID',

'UUID_SHORT',

'VERSION',

];

private int $rowOffset = 0;

private int $fieldIndex = 0;

/** @var string[] */

public static array $pluginScripts = [];

public function __construct(

private readonly DatabaseInterface $dbi,

private readonly Relation $relation,

private readonly Transformations $transformations,

private readonly FileListing $fileListing,

private readonly Template $template,

private readonly Config $config,

private readonly ResponseRenderer $responseRenderer,

) {

}

/**

public function getFormParametersForInsertForm(

string $db,

string $table,

array $whereClauseArray,

string $errorUrl,

): array {

$formParams = [

'db' => $db,

'table' => $table,

'goto' => UrlParams::$goto,

'err_url' => $errorUrl,

'sql_query' => $_POST['sql_query'] ?? '',

];

if ($formParams['sql_query'] === '' && isset($_GET['sql_query'], $_GET['sql_signature'])) {

if (Core::checkSqlQuerySignature($_GET['sql_query'], $_GET['sql_signature'])) {

$formParams['sql_query'] = $_GET['sql_query'];

}

}

foreach ($whereClauseArray as $keyId => $whereClause) {

$formParams['where_clause[' . $keyId . ']'] = trim($whereClause);

}

if (isset($_POST['clause_is_unique'])) {

$formParams['clause_is_unique'] = $_POST['clause_is_unique'];

} elseif (isset($_GET['clause_is_unique'])) {

$formParams['clause_is_unique'] = $_GET['clause_is_unique'];

}

return $formParams;

}

/**

private function analyzeWhereClauses(

array $whereClauseArray,

string $table,

string $db,

): array {

$rows = [];

$result = [];

$foundUniqueKey = false;

foreach ($whereClauseArray as $keyId => $whereClause) {

$localQuery = 'SELECT * FROM '

. Util::backquote($db) . '.'

. Util::backquote($table)

. ' WHERE ' . $whereClause . ';';

$result[$keyId] = $this->dbi->query($localQuery);

$rows[$keyId] = $result[$keyId]->fetchAssoc();

if ($rows[$keyId] === []) {

$this->responseRenderer->addHTML(

Generator::getMessage(

__('MySQL returned an empty result set (i.e. zero rows).'),

$localQuery,

),

);

/**

continue;

}

if (! $this->hasUniqueCondition($rows[$keyId], $result[$keyId])) {

continue;

}

$foundUniqueKey = true;

}

return [$result, $rows, $foundUniqueKey];

}

/** @param array<string|null> $row */

private function hasUniqueCondition(array $row, ResultInterface $result): bool

{

$meta = $this->dbi->getFieldsMeta($result);

return (bool) (new UniqueCondition($meta, $row, true))->getWhereClause();

}

/**

private function loadFirstRow(string $table, string $db): ResultInterface

{

return $this->dbi->query(

'SELECT * FROM ' . Util::backquote($db)

. '.' . Util::backquote($table) . ' LIMIT 1;',

);

}

/** @return never[][] */

private function getInsertRows(): array

{

return array_fill(0, $this->config->config->InsertRows, []);

}

/**

public function showTypeOrFunction(string $which, array $urlParams, bool $isShow): string

{

$params = [];

switch ($which) {

case 'function':

$params['ShowFunctionFields'] = $isShow ? 0 : 1;

$params['ShowFieldTypesInDataEditView'] = $this->config->config->ShowFieldTypesInDataEditView;

break;

case 'type':

$params['ShowFieldTypesInDataEditView'] = $isShow ? 0 : 1;

$params['ShowFunctionFields'] = $this->config->config->ShowFunctionFields;

break;

}

$params['goto'] = Url::getFromRoute('/sql');

$thisUrlParams = array_merge($urlParams, $params);

if (! $isShow) {

return ' : <a href="' . Url::getFromRoute('/table/change') . '" data-post="'

. Url::getCommon($thisUrlParams, '', false) . '">'

. $this->showTypeOrFunctionLabel($which)

. '</a>';

}

return '<th><a href="' . Url::getFromRoute('/table/change') . '" data-post="'

. Url::getCommon($thisUrlParams, '', false)

. '" title="' . __('Hide') . '">'

. $this->showTypeOrFunctionLabel($which)

. '</a></th>';

}

/**

private function showTypeOrFunctionLabel(string $which): string

{

return match ($which) {

'function' => __('Function'),

'type' => __('Type'),

default => '',

};

}

/**

private function getColumnTitle(string $fieldName, array $commentsMap): string

{

if (isset($commentsMap[$fieldName])) {

return '<span style="border-bottom: 1px dashed black;" title="'

. htmlspecialchars($commentsMap[$fieldName]) . '">'

. htmlspecialchars($fieldName) . '</span>';

}

return htmlspecialchars($fieldName);

}

/**

public function isColumn(string $columnType, array $types): bool

{

foreach ($types as $oneType) {

if (mb_stripos($columnType, $oneType) === 0) {

return true;

}

}

return false;

}

/**

private function getNullifyCodeForNullColumn(

InsertEditColumn $column,

Foreigners $foreigners,

bool $foreignLink,

): string {

if ($column->trueType === 'enum') {

return mb_strlen($column->type) > 20 ? '1' : '2';

}

if ($column->trueType === 'set') {

return '3';

}

if ($this->relation->searchColumnInForeigners($foreigners, $column->field) !== false) {

return $foreignLink ? '6' : '4';

}

return '5';

}

/**

private function getTextarea(

InsertEditColumn $column,

string $backupField,

string $columnNameAppendix,

string $onChangeClause,

string $formattedDefaultValue,

TypeClass $dataType,

): string {

$theClass = '';

$textAreaRows = $this->config->config->TextareaRows;

$textareaCols = $this->config->config->TextareaCols;

if ($column->isChar) {

/**

$theClass = 'charField';

$textAreaRows = $this->config->config->CharTextareaRows;

$textareaCols = $this->config->config->CharTextareaCols;

$extractedColumnspec = Util::extractColumnSpec($column->type);

$maxlength = $extractedColumnspec['spec_in_brackets'];

} elseif ($this->config->config->LongtextDoubleTextarea && $column->trueType === 'longtext') {

$textAreaRows = $this->config->config->TextareaRows * 2;

$textareaCols = $this->config->config->TextareaCols * 2;

}

//We need to duplicate the first \n or otherwise we will lose

//the first newline entered in a VARCHAR or TEXT column

$specialCharsEncoded = Util::duplicateFirstNewline($formattedDefaultValue);

return $backupField . "\n"

. '<textarea name="fields' . $columnNameAppendix . '"'

. ' class="' . $theClass . '"'

. (isset($maxlength) ? ' data-maxlength="' . $maxlength . '"' : '')

. ' rows="' . $textAreaRows . '"'

. ' cols="' . $textareaCols . '"'

. ' dir="' . LanguageManager::$textDirection->value . '"'

. ' id="field_' . $this->fieldIndex . '_3"'

. ($onChangeClause !== '' ? ' onchange="' . htmlspecialchars($onChangeClause, ENT_COMPAT) . '"' : '')

. ' tabindex="' . $this->fieldIndex . '"'

. ' data-type="' . $dataType->value . '">'

. htmlspecialchars($specialCharsEncoded, ENT_COMPAT)

. '</textarea>';

}

/** @return object{isInteger: bool, minValue: string, maxValue: string} */

private function getIntegerRange(InsertEditColumn $column): object

{

$minValue = '';

$maxValue = '';

$isInteger = in_array($column->trueType, $this->dbi->types->getIntegerTypes(), true);

if ($isInteger) {

$extractedColumnSpec = Util::extractColumnSpec($column->type);

$isUnsigned = $extractedColumnSpec['unsigned'];

$minMaxValues = $this->dbi->types->getIntegerRange($column->trueType, ! $isUnsigned);

$minValue = $minMaxValues[0];

$maxValue = $minMaxValues[1];

}

return new class ($isInteger, $minValue, $maxValue) {

public function __construct(

public readonly bool $isInteger,

public readonly string $minValue,

public readonly string $maxValue,

) {

}

};

}

/**

private function getSelectOptionForUpload(string $vkey, string $fieldHashMd5): string

{

$files = $this->fileListing->getFileSelectOptions(

Util::userDir($this->config->selectedServer['user'], $this->config->config->UploadDir),

);

if ($files === false) {

return '<span style="color:red">' . __('Error') . '</span><br>' . "\n"

. __('The directory you set for upload work cannot be reached.') . "\n";

}

if ($files === '') {

return '';

}

return "<br>\n"

. '<i>' . __('Or') . '</i> '

. __('web server upload directory:') . '<br>' . "\n"

. '<select size="1" name="fields_uploadlocal'

. $vkey . '[' . $fieldHashMd5 . ']">' . "\n"

. '<option value="" selected></option>' . "\n"

. $files

. '</select>' . "\n";

}

/**

private function getMaxUploadSize(string $type): string

{

// find maximum upload size, based on field type

/**

$maxFieldSize = match ($type) {

'tinyblob' => 256,

'blob' => 65536,

'mediumblob' => 16777216,

'longblob' => 4294967296,// yeah, really

};

$thisFieldMaxSize = Util::getUploadSizeInBytes();

return Util::getFormattedMaximumUploadSize(min($thisFieldMaxSize, $maxFieldSize)) . "\n";

}

/**

private function getValueColumnForOtherDatatypes(

InsertEditColumn $column,

string $defaultCharEditing,

string $backupField,

string $columnNameAppendix,

string $onChangeClause,

string $formattedDefaultValue,

string $data,

string $specInBrackets,

): string {

// HTML5 data-* attribute data-type

$dataType = $this->dbi->types->getTypeClass($column->trueType);

$fieldsize = $this->getColumnSize($column, $specInBrackets);

$input = [];

$textareaHtml = '';

$isTextareaRequired = $column->isChar

&& ($this->config->config->CharEditing === 'textarea' || str_contains($data, "\n"));

if ($isTextareaRequired) {

$this->config->set('CharEditing', $defaultCharEditing);

$textareaHtml = $this->getTextarea(

$column,

$backupField,

$columnNameAppendix,

$onChangeClause,

$formattedDefaultValue,

$dataType,

);

} else {

$integerRange = $this->getIntegerRange($column);

$input = [

'value' => $formattedDefaultValue,

'size' => $fieldsize,

'is_char' => $column->isChar,

'is_integer' => $integerRange->isInteger,

'min' => $integerRange->minValue,

'max' => $integerRange->maxValue,

'data_type' => $dataType->value,

'on_change_clause' => $onChangeClause,

'field_index' => $this->fieldIndex,

];

}

return $this->template->render('table/insert/value_column_for_other_datatype', [

'input' => $input,

'textarea_html' => $textareaHtml,

'backup_field' => $backupField,

'is_textarea' => $isTextareaRequired,

'column_name_appendix' => $columnNameAppendix,

'extra' => $column->extra,

'true_type' => $column->trueType,

]);

}

/**

private function getColumnSize(InsertEditColumn $column, string $specInBrackets): int

{

if ($column->isChar) {

$fieldsize = (int) $specInBrackets;

if ($fieldsize > $this->config->config->MaxSizeForInputField) {

/**

$this->config->set('CharEditing', 'textarea');

}

} else {

/**

$fieldsize = $column->length;

}

return min(

max($fieldsize, $this->config->config->MinSizeForInputField),

$this->config->config->MaxSizeForInputField,

);

}

/**

public function getContinueInsertionForm(

string $table,

string $db,

array $whereClauseArray,

string $errorUrl,

): string {

return $this->template->render('table/insert/continue_insertion_form', [

'db' => $db,

'table' => $table,

'where_clause_array' => $whereClauseArray,

'err_url' => $errorUrl,

'goto' => UrlParams::$goto,

'sql_query' => $_POST['sql_query'] ?? null,

'has_where_clause' => isset($_POST['where_clause']),

'insert_rows_default' => $this->config->config->InsertRows,

]);

}

/**

public static function isWhereClauseNumeric(array|string|null $whereClause): bool

{

if ($whereClause === null) {

return false;

}

if (! is_array($whereClause)) {

$whereClause = [$whereClause];

}

// If we have just numeric primary key, we can also edit next

// we are looking for `table_name`.`field_name` = numeric_value

foreach ($whereClause as $clause) {

// preg_match() returns 1 if there is a match

$isNumeric = preg_match('@^[\s]*`[^`]*`[\.]`[^`]*` = [0-9]+@', $clause) === 1;

if ($isNumeric) {

return true;

}

}

return false;

}

/**

private function getHeadAndFootOfInsertRowTable(array $urlParams): string

{

$type = '';

$function = '';

if ($this->config->config->ShowFieldTypesInDataEditView) {

$type = $this->showTypeOrFunction('type', $urlParams, true);

}

if ($this->config->config->ShowFunctionFields) {

$function = $this->showTypeOrFunction('function', $urlParams, true);

}

$template = new Template($this->config);

return $template->render('table/insert/get_head_and_foot_of_insert_row_table', [

'type' => $type,

'function' => $function,

]);

}

/**

private function getDefaultValueAndBackupFieldForExistingRow(

array $currentRow,

InsertEditColumn $column,

string $specInBrackets,

string $columnNameAppendix,

bool $asIs,

): array {

$data = '';

$realNullValue = false;

$currentValue = $currentRow[$column->field] ?? null;

// (we are editing)

if ($currentValue === null) {

$realNullValue = true;

$currentValue = '';

$formattedDefaultValue = '';

$data = '';

} elseif ($column->trueType === 'bit') {

$formattedDefaultValue = $asIs

? $currentValue

: Util::printableBitValue((int) $currentValue, (int) $specInBrackets);

} elseif (

in_array($column->trueType, ['timestamp', 'datetime', 'time'], true)

&& str_contains($currentValue, '.')

) {

$currentValue = $asIs

? $currentValue

: Util::addMicroseconds($currentValue);

$formattedDefaultValue = $currentValue;

} elseif (in_array($column->trueType, Gis::getDataTypes(), true)) {

// Convert gis data to Well Know Text format

$currentValue = $asIs

? $currentValue

: Gis::convertToWellKnownText($currentValue, true);

$formattedDefaultValue = $currentValue;

} else {

// special binary "characters"

if ($column->isBinary || ($column->isBlob && $this->config->config->ProtectBinary !== 'all')) {

$currentValue = $asIs

? $currentValue

: bin2hex($currentValue);

}

$formattedDefaultValue = $currentValue;

$data = $currentValue;

}

/** @var string $defaultAction */

$defaultAction = $_POST['default_action'] ?? $_GET['default_action'] ?? '';

if (

$defaultAction === 'insert'

&& $column->key === 'PRI'

&& str_contains($column->extra, 'auto_increment')

) {

// When copying row, it is useful to empty auto-increment column to prevent duplicate key error.

$data = $formattedDefaultValue = '';

}

// If a timestamp field value is not included in an update

// statement MySQL auto-update it to the current timestamp;

// however, things have changed since MySQL 4.1, so

// it's better to set a fields_prev in this situation

$backupField = '<input type="hidden" name="fields_prev'

. $columnNameAppendix . '" value="'

. htmlspecialchars($currentValue, ENT_COMPAT) . '">';

return [$realNullValue, $formattedDefaultValue, $data, $backupField];

}

/**

private function getDefaultValue(

string|null $defaultValue,

string $trueType,

): string {

if ($defaultValue === null) {

$defaultValue = '';

}

if ($trueType === 'bit') {

return Util::convertBitDefaultValue($defaultValue);

}

if (in_array($trueType, ['timestamp', 'datetime', 'time'], true)) {

return Util::addMicroseconds($defaultValue);

}

if ($trueType === 'binary' || $trueType === 'varbinary') {

return bin2hex($defaultValue);

}

return $defaultValue;

}

/**

public function setSessionForEditNext(string $oneWhereClause): void

{

$localQuery = 'SELECT * FROM ' . Util::backquote(Current::$database)

. '.' . Util::backquote(Current::$table) . ' WHERE '

. str_replace('` =', '` >', $oneWhereClause) . ' LIMIT 1;';

$res = $this->dbi->query($localQuery);

$row = $res->fetchRow();

$meta = $this->dbi->getFieldsMeta($res);

// must find a unique condition based on unique key,

// not a combination of all fields

$uniqueCondition = (new UniqueCondition($meta, $row, true))->getWhereClause();

if ($uniqueCondition === '') {

return;

}

$_SESSION['edit_next'] = $uniqueCondition;

}

/**

public function getGotoInclude(string|false $gotoInclude): string

{

$validOptions = ['new_insert', 'same_insert', 'edit_next'];

if (isset($_POST['after_insert']) && in_array($_POST['after_insert'], $validOptions, true)) {

return '/table/change';

}

if (UrlParams::$goto !== '') {

if (preg_match('@^[a-z_]+\.php$@', UrlParams::$goto) !== 1) {

// this should NOT happen

//UrlParams::$goto = false;

$gotoInclude = str_contains(UrlParams::$goto, 'index.php?route=/sql') ? '/sql' : false;

} else {

$gotoInclude = UrlParams::$goto;

}

if (UrlParams::$goto === 'index.php?route=/database/sql' && Current::$table !== '') {

Current::$table = '';

}

}

if (! $gotoInclude) {

$gotoInclude = Current::$table === '' ? '/database/sql' : '/table/sql';

}

return $gotoInclude;

}

/**

public function getErrorUrl(array $urlParams): string

{

return $_POST['err_url'] ?? Url::getFromRoute('/table/change', $urlParams);

}

/**

public function executeSqlQuery(array $query): array

{

Current::$sqlQuery = implode('; ', $query) . ';';

// to ensure that the query is displayed in case of

// "insert as new row" and then "insert another new row"

Current::$displayQuery = Current::$sqlQuery;

$totalAffectedRows = 0;

$lastMessages = [];

$warningMessages = [];

$errorMessages = [];

foreach ($query as $singleQuery) {

if (isset($_POST['submit_type']) && $_POST['submit_type'] === 'showinsert') {

$lastMessages[] = Message::notice(__('Showing SQL query'));

continue;

}

if ($this->config->config->IgnoreMultiSubmitErrors) {

$result = $this->dbi->tryQuery($singleQuery);

} else {

$result = $this->dbi->query($singleQuery);

}

if (! $result) {

$errorMessages[] = $this->dbi->getError();

} else {

$totalAffectedRows += (int) $this->dbi->affectedRows();

$insertId = $this->dbi->insertId();

if ($insertId !== 0) {

// insert_id is id of FIRST record inserted in one insert, so if we

// inserted multiple rows, we had to increment this

if ($totalAffectedRows > 0) {

$insertId += $totalAffectedRows - 1;

}

$lastMessage = Message::notice(__('Inserted row id: %1$d'));

$lastMessage->addParam($insertId);

$lastMessages[] = $lastMessage;

}

}

$warningMessages = $this->getWarningMessages();

}

return [$totalAffectedRows, $lastMessages, $warningMessages, $errorMessages];

}

/**

private function getWarningMessages(): array

{

$warningMessages = [];

foreach ($this->dbi->getWarnings() as $warning) {

$warningMessages[] = htmlspecialchars((string) $warning);

}

return $warningMessages;

}

/**

public function getDisplayValueForForeignTableColumn(

string $whereComparison,

Foreigners $foreigners,

string $relationField,

): string {

$foreigner = $this->relation->searchColumnInForeigners($foreigners, $relationField);

if (! is_array($foreigner)) {

return '';

}

$displayField = $this->relation->getDisplayField($foreigner['foreign_db'], $foreigner['foreign_table']);

// Field to display from the foreign table?

if ($displayField !== '') {

$dispsql = 'SELECT ' . Util::backquote($displayField)

. ' FROM ' . Util::backquote($foreigner['foreign_db'])

. '.' . Util::backquote($foreigner['foreign_table'])

. ' WHERE ' . Util::backquote($foreigner['foreign_field'])

. $whereComparison;

$dispresult = $this->dbi->tryQuery($dispsql);

if ($dispresult && $dispresult->numRows() > 0) {

return (string) $dispresult->fetchValue();

}

}

return '';

}

/**

public function getLinkForRelationalDisplayField(

Foreigners $foreigners,

string $relationField,

string $whereComparison,

string $dispval,

string $relationFieldValue,

): string {

$foreigner = $this->relation->searchColumnInForeigners($foreigners, $relationField);

if (! is_array($foreigner)) {

return '';

}

if ($_SESSION['tmpval']['relational_display'] === 'K') {

// user chose "relational key" in the display options, so

// the title contains the display field

$title = $dispval !== ''

? ' title="' . htmlspecialchars($dispval) . '"'

: '';

} else {

$title = ' title="' . htmlspecialchars($relationFieldValue) . '"';

}

$sqlQuery = 'SELECT * FROM '

. Util::backquote($foreigner['foreign_db'])

. '.' . Util::backquote($foreigner['foreign_table'])

. ' WHERE ' . Util::backquote($foreigner['foreign_field'])

. $whereComparison;

$urlParams = [

'db' => $foreigner['foreign_db'],

'table' => $foreigner['foreign_table'],

'pos' => '0',

'sql_signature' => Core::signSqlQuery($sqlQuery),

'sql_query' => $sqlQuery,

];

$output = '<a href="' . Url::getFromRoute('/sql', $urlParams) . '"' . $title . '>';

if ($_SESSION['tmpval']['relational_display'] === 'D') {

// user chose "relational display field" in the

// display options, so show display field in the cell

$output .= htmlspecialchars($dispval);

} else {

// otherwise display data in the cell

$output .= htmlspecialchars($relationFieldValue);

}

$output .= '</a>';

return $output;

}

/**