diff options
| author | Charles <sircharlesaze@gmail.com> | 2020-01-09 10:55:03 +0100 |
|---|---|---|
| committer | Charles <sircharlesaze@gmail.com> | 2020-01-09 13:09:38 +0100 |
| commit | 04d6d5ca99ebfd1cebb8ce06618fb3811fc1a8aa (patch) | |
| tree | 5c691241355c943a3c68ddb06b8cf8c60aa11319 /srcs/phpmyadmin/libraries/classes/Database | |
| parent | 7e0d85db834d6351ed85d01e5126ac31dc510b86 (diff) | |
| download | ft_server-04d6d5ca99ebfd1cebb8ce06618fb3811fc1a8aa.tar.gz ft_server-04d6d5ca99ebfd1cebb8ce06618fb3811fc1a8aa.tar.bz2 ft_server-04d6d5ca99ebfd1cebb8ce06618fb3811fc1a8aa.zip | |
phpmyadmin working
Diffstat (limited to 'srcs/phpmyadmin/libraries/classes/Database')
7 files changed, 3855 insertions, 0 deletions
diff --git a/srcs/phpmyadmin/libraries/classes/Database/DatabaseList.php b/srcs/phpmyadmin/libraries/classes/Database/DatabaseList.php new file mode 100644 index 0000000..a9d3889 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/DatabaseList.php @@ -0,0 +1,60 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * holds the PhpMyAdmin\Database\DatabaseList class + * + * @package PhpMyAdmin + * + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database; + +use PhpMyAdmin\ListDatabase; + +/** + * holds the DatabaseList class + * + * @package PhpMyAdmin + */ +class DatabaseList +{ + /** + * Holds database list + * + * @var ListDatabase + */ + protected $databases = null; + + /** + * magic access to protected/inaccessible members/properties + * + * @param string $param parameter name + * + * @return mixed + * @see https://www.php.net/language.oop5.overloading + */ + public function __get($param) + { + switch ($param) { + case 'databases': + return $this->getDatabaseList(); + } + + return null; + } + + /** + * Accessor to PMA::$databases + * + * @return ListDatabase + */ + public function getDatabaseList() + { + if (null === $this->databases) { + $this->databases = new ListDatabase(); + } + + return $this->databases; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/Designer.php b/srcs/phpmyadmin/libraries/classes/Database/Designer.php new file mode 100644 index 0000000..f9d6775 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/Designer.php @@ -0,0 +1,407 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Database\Designer class + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Message; +use PhpMyAdmin\Plugins; +use PhpMyAdmin\Plugins\SchemaPlugin; +use PhpMyAdmin\Relation; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; +use PhpMyAdmin\Database\Designer\DesignerTable; +use stdClass; + +/** + * Set of functions related to database designer + * + * @package PhpMyAdmin + */ +class Designer +{ + /** + * @var DatabaseInterface + */ + private $dbi; + + /** + * @var Relation + */ + private $relation; + + /** + * @var Template + */ + public $template; + + /** + * Designer constructor. + * + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Relation $relation Relation instance + * @param Template $template Template instance + */ + public function __construct(DatabaseInterface $dbi, Relation $relation, Template $template) + { + $this->dbi = $dbi; + $this->relation = $relation; + $this->template = $template; + } + + /** + * Function to get html for displaying the page edit/delete form + * + * @param string $db database name + * @param string $operation 'edit' or 'delete' depending on the operation + * + * @return string html content + */ + public function getHtmlForEditOrDeletePages($db, $operation) + { + $cfgRelation = $this->relation->getRelationsParam(); + return $this->template->render('database/designer/edit_delete_pages', [ + 'db' => $db, + 'operation' => $operation, + 'pdfwork' => $cfgRelation['pdfwork'], + 'pages' => $this->getPageIdsAndNames($db), + ]); + } + + /** + * Function to get html for displaying the page save as form + * + * @param string $db database name + * + * @return string html content + */ + public function getHtmlForPageSaveAs($db) + { + $cfgRelation = $this->relation->getRelationsParam(); + return $this->template->render('database/designer/page_save_as', [ + 'db' => $db, + 'pdfwork' => $cfgRelation['pdfwork'], + 'pages' => $this->getPageIdsAndNames($db), + ]); + } + + /** + * Retrieve IDs and names of schema pages + * + * @param string $db database name + * + * @return array array of schema page id and names + */ + private function getPageIdsAndNames($db) + { + $result = []; + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return $result; + } + + $page_query = "SELECT `page_nr`, `page_descr` FROM " + . Util::backquote($cfgRelation['db']) . "." + . Util::backquote($cfgRelation['pdf_pages']) + . " WHERE db_name = '" . $this->dbi->escapeString($db) . "'" + . " ORDER BY `page_descr`"; + $page_rs = $this->relation->queryAsControlUser( + $page_query, + false, + DatabaseInterface::QUERY_STORE + ); + + while ($curr_page = $this->dbi->fetchAssoc($page_rs)) { + $result[intval($curr_page['page_nr'])] = $curr_page['page_descr']; + } + return $result; + } + + /** + * Function to get html for displaying the schema export + * + * @param string $db database name + * @param int $page the page to be exported + * + * @return string + */ + public function getHtmlForSchemaExport($db, $page) + { + /* Scan for schema plugins */ + /** @var SchemaPlugin[] $export_list */ + $export_list = Plugins::getPlugins( + "schema", + 'libraries/classes/Plugins/Schema/', + null + ); + + /* Fail if we didn't find any schema plugin */ + if (empty($export_list)) { + return Message::error( + __('Could not load schema plugins, please check your installation!') + )->getDisplay(); + } + + return $this->template->render('database/designer/schema_export', [ + 'db' => $db, + 'page' => $page, + 'export_list' => $export_list, + ]); + } + + /** + * Returns array of stored values of Designer Settings + * + * @return array stored values + */ + private function getSideMenuParamsArray() + { + $params = []; + + $cfgRelation = $this->relation->getRelationsParam(); + + if ($cfgRelation['designersettingswork']) { + $query = 'SELECT `settings_data` FROM ' + . Util::backquote($cfgRelation['db']) . '.' + . Util::backquote($cfgRelation['designer_settings']) + . ' WHERE ' . Util::backquote('username') . ' = "' + . $GLOBALS['dbi']->escapeString($GLOBALS['cfg']['Server']['user']) + . '";'; + + $result = $this->dbi->fetchSingleRow($query); + + $params = json_decode((string) $result['settings_data'], true); + } + + return $params; + } + + /** + * Returns class names for various buttons on Designer Side Menu + * + * @return array class names of various buttons + */ + public function returnClassNamesFromMenuButtons() + { + $classes_array = []; + $params_array = $this->getSideMenuParamsArray(); + + if (isset($params_array['angular_direct']) + && $params_array['angular_direct'] == 'angular' + ) { + $classes_array['angular_direct'] = 'M_butt_Selected_down'; + } else { + $classes_array['angular_direct'] = 'M_butt'; + } + + if (isset($params_array['snap_to_grid']) + && $params_array['snap_to_grid'] == 'on' + ) { + $classes_array['snap_to_grid'] = 'M_butt_Selected_down'; + } else { + $classes_array['snap_to_grid'] = 'M_butt'; + } + + if (isset($params_array['pin_text']) + && $params_array['pin_text'] == 'true' + ) { + $classes_array['pin_text'] = 'M_butt_Selected_down'; + } else { + $classes_array['pin_text'] = 'M_butt'; + } + + if (isset($params_array['relation_lines']) + && $params_array['relation_lines'] == 'false' + ) { + $classes_array['relation_lines'] = 'M_butt_Selected_down'; + } else { + $classes_array['relation_lines'] = 'M_butt'; + } + + if (isset($params_array['small_big_all']) + && $params_array['small_big_all'] == 'v' + ) { + $classes_array['small_big_all'] = 'M_butt_Selected_down'; + } else { + $classes_array['small_big_all'] = 'M_butt'; + } + + if (isset($params_array['side_menu']) + && $params_array['side_menu'] == 'true' + ) { + $classes_array['side_menu'] = 'M_butt_Selected_down'; + } else { + $classes_array['side_menu'] = 'M_butt'; + } + + return $classes_array; + } + + /** + * Get HTML to display tables on designer page + * + * @param string $db The database name from the request + * @param DesignerTable[] $designerTables The designer tables + * @param array $tab_pos tables positions + * @param int $display_page page number of the selected page + * @param array $tab_column table column info + * @param array $tables_all_keys all indices + * @param array $tables_pk_or_unique_keys unique or primary indices + * + * @return string html + */ + public function getDatabaseTables( + string $db, + array $designerTables, + array $tab_pos, + $display_page, + array $tab_column, + array $tables_all_keys, + array $tables_pk_or_unique_keys + ) { + $columns_type = []; + foreach ($designerTables as $designerTable) { + $table_name = $designerTable->getDbTableString(); + $limit = count($tab_column[$table_name]['COLUMN_ID']); + for ($j = 0; $j < $limit; $j++) { + $table_column_name = $table_name . '.' . $tab_column[$table_name]['COLUMN_NAME'][$j]; + if (isset($tables_pk_or_unique_keys[$table_column_name])) { + $columns_type[$table_column_name] = 'designer/FieldKey_small'; + } else { + $columns_type[$table_column_name] = 'designer/Field_small'; + if (false !== strpos($tab_column[$table_name]['TYPE'][$j], 'char') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'text')) { + $columns_type[$table_column_name] .= '_char'; + } elseif (false !== strpos($tab_column[$table_name]['TYPE'][$j], 'int') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'float') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'double') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'decimal')) { + $columns_type[$table_column_name] .= '_int'; + } elseif (false !== strpos($tab_column[$table_name]['TYPE'][$j], 'date') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'time') + || false !== strpos($tab_column[$table_name]['TYPE'][$j], 'year')) { + $columns_type[$table_column_name] .= '_date'; + } + } + } + } + return $this->template->render('database/designer/database_tables', [ + 'db' => $GLOBALS['db'], + 'get_db' => $db, + 'has_query' => isset($_REQUEST['query']), + 'tab_pos' => $tab_pos, + 'display_page' => $display_page, + 'tab_column' => $tab_column, + 'tables_all_keys' => $tables_all_keys, + 'tables_pk_or_unique_keys' => $tables_pk_or_unique_keys, + 'tables' => $designerTables, + 'columns_type' => $columns_type, + 'theme' => $GLOBALS['PMA_Theme'], + ]); + } + + + /** + * Returns HTML for Designer page + * + * @param string $db database in use + * @param string $getDb database in url + * @param DesignerTable[] $designerTables The designer tables + * @param array $scriptTables array on foreign key support for each table + * @param array $scriptContr initialization data array + * @param DesignerTable[] $scriptDisplayField displayed tables in designer with their display fields + * @param int $displayPage page number of the selected page + * @param boolean $hasQuery whether this is visual query builder + * @param string $selectedPage name of the selected page + * @param array $paramsArray array with class name for various buttons on side menu + * @param array|null $tabPos table positions + * @param array $tabColumn table column info + * @param array $tablesAllKeys all indices + * @param array $tablesPkOrUniqueKeys unique or primary indices + * + * @return string html + */ + public function getHtmlForMain( + string $db, + string $getDb, + array $designerTables, + array $scriptTables, + array $scriptContr, + array $scriptDisplayField, + $displayPage, + $hasQuery, + $selectedPage, + array $paramsArray, + ?array $tabPos, + array $tabColumn, + array $tablesAllKeys, + array $tablesPkOrUniqueKeys + ): string { + $cfgRelation = $this->relation->getRelationsParam(); + $columnsType = []; + foreach ($designerTables as $designerTable) { + $tableName = $designerTable->getDbTableString(); + $limit = count($tabColumn[$tableName]['COLUMN_ID']); + for ($j = 0; $j < $limit; $j++) { + $tableColumnName = $tableName . '.' . $tabColumn[$tableName]['COLUMN_NAME'][$j]; + if (isset($tablesPkOrUniqueKeys[$tableColumnName])) { + $columnsType[$tableColumnName] = 'designer/FieldKey_small'; + } else { + $columnsType[$tableColumnName] = 'designer/Field_small'; + if (false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'char') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'text')) { + $columnsType[$tableColumnName] .= '_char'; + } elseif (false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'int') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'float') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'double') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'decimal')) { + $columnsType[$tableColumnName] .= '_int'; + } elseif (false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'date') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'time') + || false !== strpos($tabColumn[$tableName]['TYPE'][$j], 'year')) { + $columnsType[$tableColumnName] .= '_date'; + } + } + } + } + + $displayedFields = []; + foreach ($scriptDisplayField as $designerTable) { + if ($designerTable->getDisplayField() !== null) { + $displayedFields[$designerTable->getTableName()] = $designerTable->getDisplayField(); + } + } + + $designerConfig = new stdClass(); + $designerConfig->db = $db; + $designerConfig->scriptTables = $scriptTables; + $designerConfig->scriptContr = $scriptContr; + $designerConfig->server = $GLOBALS['server']; + $designerConfig->scriptDisplayField = $displayedFields; + $designerConfig->displayPage = (int) $displayPage; + $designerConfig->tablesEnabled = $cfgRelation['pdfwork']; + + return $this->template->render('database/designer/main', [ + 'db' => $db, + 'get_db' => $getDb, + 'designer_config' => json_encode($designerConfig), + 'display_page' => (int) $displayPage, + 'has_query' => $hasQuery, + 'selected_page' => $selectedPage, + 'params_array' => $paramsArray, + 'theme' => $GLOBALS['PMA_Theme'], + 'tab_pos' => $tabPos, + 'tab_column' => $tabColumn, + 'tables_all_keys' => $tablesAllKeys, + 'tables_pk_or_unique_keys' => $tablesPkOrUniqueKeys, + 'designerTables' => $designerTables, + 'columns_type' => $columnsType, + ]); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/Designer/Common.php b/srcs/phpmyadmin/libraries/classes/Database/Designer/Common.php new file mode 100644 index 0000000..b80f323 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/Designer/Common.php @@ -0,0 +1,830 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Database\Designer\Common class + * + * @package PhpMyAdmin-Designer + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database\Designer; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Index; +use PhpMyAdmin\Relation; +use PhpMyAdmin\Table; +use PhpMyAdmin\Util; +use function rawurlencode; +use PhpMyAdmin\Database\Designer\DesignerTable; + +/** + * Common functions for Designer + * + * @package PhpMyAdmin-Designer + */ +class Common +{ + /** + * @var Relation + */ + private $relation; + + /** + * @var DatabaseInterface + */ + private $dbi; + + /** + * Common constructor. + * + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Relation $relation Relation instance + */ + public function __construct(DatabaseInterface $dbi, Relation $relation) + { + $this->dbi = $dbi; + $this->relation = $relation; + } + + /** + * Retrieves table info and returns it + * + * @param string $db (optional) Filter only a DB ($table is required if you use $db) + * @param string $table (optional) Filter only a table ($db is now required) + * @return DesignerTable[] with table info + */ + public function getTablesInfo(string $db = null, string $table = null): array + { + $designerTables = []; + $db = ($db === null) ? $GLOBALS['db'] : $db; + // seems to be needed later + $this->dbi->selectDb($db); + if ($db === null && $table === null) { + $tables = $this->dbi->getTablesFull($db); + } else { + $tables = $this->dbi->getTablesFull($db, $table); + } + + foreach ($tables as $one_table) { + $DF = $this->relation->getDisplayField($db, $one_table['TABLE_NAME']); + $DF = is_string($DF) ? $DF : ''; + $DF = ($DF !== '') ? $DF : null; + $designerTables[] = new DesignerTable( + $db, + $one_table['TABLE_NAME'], + is_string($one_table['ENGINE']) ? $one_table['ENGINE'] : '', + $DF + ); + } + + return $designerTables; + } + + /** + * Retrieves table column info + * + * @param DesignerTable[] $designerTables The designer tables + * @return array table column nfo + */ + public function getColumnsInfo(array $designerTables): array + { + //$this->dbi->selectDb($GLOBALS['db']); + $tabColumn = []; + + foreach ($designerTables as $designerTable) { + $fieldsRs = $this->dbi->query( + $this->dbi->getColumnsSql( + $designerTable->getDatabaseName(), + $designerTable->getTableName(), + null, + true + ), + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $j = 0; + while ($row = $this->dbi->fetchAssoc($fieldsRs)) { + if (! isset($tabColumn[$designerTable->getDbTableString()])) { + $tabColumn[$designerTable->getDbTableString()] = []; + } + $tabColumn[$designerTable->getDbTableString()]['COLUMN_ID'][$j] = $j; + $tabColumn[$designerTable->getDbTableString()]['COLUMN_NAME'][$j] = $row['Field']; + $tabColumn[$designerTable->getDbTableString()]['TYPE'][$j] = $row['Type']; + $tabColumn[$designerTable->getDbTableString()]['NULLABLE'][$j] = $row['Null']; + $j++; + } + } + + return $tabColumn; + } + + /** + * Returns JavaScript code for initializing vars + * + * @param DesignerTable[] $designerTables The designer tables + * @return array JavaScript code + */ + public function getScriptContr(array $designerTables): array + { + $this->dbi->selectDb($GLOBALS['db']); + $con = []; + $con["C_NAME"] = []; + $i = 0; + $alltab_rs = $this->dbi->query( + 'SHOW TABLES FROM ' . Util::backquote($GLOBALS['db']), + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + while ($val = @$this->dbi->fetchRow($alltab_rs)) { + $row = $this->relation->getForeigners($GLOBALS['db'], $val[0], '', 'internal'); + + if ($row !== false) { + foreach ($row as $field => $value) { + $con['C_NAME'][$i] = ''; + $con['DTN'][$i] = rawurlencode($GLOBALS['db'] . "." . $val[0]); + $con['DCN'][$i] = rawurlencode($field); + $con['STN'][$i] = rawurlencode( + $value['foreign_db'] . "." . $value['foreign_table'] + ); + $con['SCN'][$i] = rawurlencode($value['foreign_field']); + $i++; + } + } + $row = $this->relation->getForeigners($GLOBALS['db'], $val[0], '', 'foreign'); + + // We do not have access to the foreign keys if he user has partial access to the columns + if ($row !== false && isset($row['foreign_keys_data'])) { + foreach ($row['foreign_keys_data'] as $one_key) { + foreach ($one_key['index_list'] as $index => $one_field) { + $con['C_NAME'][$i] = rawurlencode($one_key['constraint']); + $con['DTN'][$i] = rawurlencode($GLOBALS['db'] . "." . $val[0]); + $con['DCN'][$i] = rawurlencode($one_field); + $con['STN'][$i] = rawurlencode( + (isset($one_key['ref_db_name']) ? + $one_key['ref_db_name'] : $GLOBALS['db']) + . "." . $one_key['ref_table_name'] + ); + $con['SCN'][$i] = rawurlencode($one_key['ref_index_list'][$index]); + $i++; + } + } + } + } + + $tableDbNames = []; + foreach ($designerTables as $designerTable) { + $tableDbNames[] = $designerTable->getDbTableString(); + } + + $ti = 0; + $retval = []; + for ($i = 0, $cnt = count($con["C_NAME"]); $i < $cnt; $i++) { + $c_name_i = $con['C_NAME'][$i]; + $dtn_i = $con['DTN'][$i]; + $retval[$ti] = []; + $retval[$ti][$c_name_i] = []; + if (in_array($dtn_i, $tableDbNames) && in_array($con['STN'][$i], $tableDbNames)) { + $retval[$ti][$c_name_i][$dtn_i] = []; + $retval[$ti][$c_name_i][$dtn_i][$con['DCN'][$i]] = [ + 0 => $con['STN'][$i], + 1 => $con['SCN'][$i], + ]; + } + $ti++; + } + return $retval; + } + + /** + * Returns UNIQUE and PRIMARY indices + * + * @param DesignerTable[] $designerTables The designer tables + * @return array unique or primary indices + */ + public function getPkOrUniqueKeys(array $designerTables): array + { + return $this->getAllKeys($designerTables, true); + } + + /** + * Returns all indices + * + * @param DesignerTable[] $designerTables The designer tables + * @param bool $unique_only whether to include only unique ones + * + * @return array indices + */ + public function getAllKeys(array $designerTables, bool $unique_only = false): array + { + $keys = []; + + foreach ($designerTables as $designerTable) { + $schema = $designerTable->getDatabaseName(); + // for now, take into account only the first index segment + foreach (Index::getFromTable($designerTable->getTableName(), $schema) as $index) { + if ($unique_only && ! $index->isUnique()) { + continue; + } + $columns = $index->getColumns(); + foreach ($columns as $column_name => $dummy) { + $keys[$schema . '.' . $designerTable->getTableName() . '.' . $column_name] = 1; + } + } + } + return $keys; + } + + /** + * Return j_tab and h_tab arrays + * + * @param DesignerTable[] $designerTables The designer tables + * @return array + */ + public function getScriptTabs(array $designerTables): array + { + $retval = [ + 'j_tabs' => [], + 'h_tabs' => [], + ]; + + foreach ($designerTables as $designerTable) { + $key = rawurlencode($designerTable->getDbTableString()); + $retval['j_tabs'][$key] = $designerTable->supportsForeignkeys() ? 1 : 0; + $retval['h_tabs'][$key] = 1; + } + + return $retval; + } + + /** + * Returns table positions of a given pdf page + * + * @param int $pg pdf page id + * + * @return array|null of table positions + */ + public function getTablePositions($pg): ?array + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return []; + } + + $query = " + SELECT CONCAT_WS('.', `db_name`, `table_name`) AS `name`, + `db_name` as `dbName`, `table_name` as `tableName`, + `x` AS `X`, + `y` AS `Y`, + 1 AS `V`, + 1 AS `H` + FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['table_coords']) . " + WHERE pdf_page_number = " . intval($pg); + + return $this->dbi->fetchResult( + $query, + 'name', + null, + DatabaseInterface::CONNECT_CONTROL, + DatabaseInterface::QUERY_STORE + ); + } + + /** + * Returns page name of a given pdf page + * + * @param int $pg pdf page id + * + * @return string|null table name + */ + public function getPageName($pg) + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return null; + } + + $query = "SELECT `page_descr`" + . " FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['pdf_pages']) + . " WHERE " . Util::backquote('page_nr') . " = " . intval($pg); + $page_name = $this->dbi->fetchResult( + $query, + null, + null, + DatabaseInterface::CONNECT_CONTROL, + DatabaseInterface::QUERY_STORE + ); + return ( is_array($page_name) && isset($page_name[0]) ) ? $page_name[0] : null; + } + + /** + * Deletes a given pdf page and its corresponding coordinates + * + * @param int $pg page id + * + * @return boolean success/failure + */ + public function deletePage($pg) + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return false; + } + + $query = "DELETE FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['table_coords']) + . " WHERE " . Util::backquote('pdf_page_number') . " = " . intval($pg); + $success = $this->relation->queryAsControlUser( + $query, + true, + DatabaseInterface::QUERY_STORE + ); + + if ($success) { + $query = "DELETE FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['pdf_pages']) + . " WHERE " . Util::backquote('page_nr') . " = " . intval($pg); + $success = $this->relation->queryAsControlUser( + $query, + true, + DatabaseInterface::QUERY_STORE + ); + } + + return (bool) $success; + } + + /** + * Returns the id of the default pdf page of the database. + * Default page is the one which has the same name as the database. + * + * @param string $db database + * + * @return int|null id of the default pdf page for the database + */ + public function getDefaultPage($db): ?int + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return -1; + } + + $query = "SELECT `page_nr`" + . " FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['pdf_pages']) + . " WHERE `db_name` = '" . $this->dbi->escapeString($db) . "'" + . " AND `page_descr` = '" . $this->dbi->escapeString($db) . "'"; + + $default_page_no = $this->dbi->fetchResult( + $query, + null, + null, + DatabaseInterface::CONNECT_CONTROL, + DatabaseInterface::QUERY_STORE + ); + + if (is_array($default_page_no) && isset($default_page_no[0])) { + return intval($default_page_no[0]); + } + return -1; + } + + /** + * Get the id of the page to load. If a default page exists it will be returned. + * If no such exists, returns the id of the first page of the database. + * + * @param string $db database + * + * @return int id of the page to load + */ + public function getLoadingPage($db) + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return -1; + } + + $page_no = -1; + + $default_page_no = $this->getDefaultPage($db); + if ($default_page_no != -1) { + $page_no = $default_page_no; + } else { + $query = "SELECT MIN(`page_nr`)" + . " FROM " . Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['pdf_pages']) + . " WHERE `db_name` = '" . $this->dbi->escapeString($db) . "'"; + + $min_page_no = $this->dbi->fetchResult( + $query, + null, + null, + DatabaseInterface::CONNECT_CONTROL, + DatabaseInterface::QUERY_STORE + ); + if (is_array($min_page_no) && isset($min_page_no[0])) { + $page_no = $min_page_no[0]; + } + } + return intval($page_no); + } + + /** + * Creates a new page and returns its auto-incrementing id + * + * @param string $pageName name of the page + * @param string $db name of the database + * + * @return int|null + */ + public function createNewPage($pageName, $db) + { + $cfgRelation = $this->relation->getRelationsParam(); + if ($cfgRelation['pdfwork']) { + return $this->relation->createPage( + $pageName, + $cfgRelation, + $db + ); + } + return null; + } + + /** + * Saves positions of table(s) of a given pdf page + * + * @param int $pg pdf page id + * + * @return boolean success/failure + */ + public function saveTablePositions($pg) + { + $pageId = $this->dbi->escapeString($pg); + + $db = $this->dbi->escapeString($_POST['db']); + + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['pdfwork']) { + return false; + } + + $query = "DELETE FROM " + . Util::backquote($cfgRelation['db']) + . "." . Util::backquote( + $cfgRelation['table_coords'] + ) + . " WHERE `pdf_page_number` = '" . $pageId . "'"; + + $res = $this->relation->queryAsControlUser( + $query, + true, + DatabaseInterface::QUERY_STORE + ); + + if (! $res) { + return (bool) $res; + } + + foreach ($_POST['t_h'] as $key => $value) { + $DB = $_POST['t_db'][$key]; + $TAB = $_POST['t_tbl'][$key]; + if (! $value) { + continue; + } + + $query = "INSERT INTO " + . Util::backquote($cfgRelation['db']) . "." + . Util::backquote($cfgRelation['table_coords']) + . " (`db_name`, `table_name`, `pdf_page_number`, `x`, `y`)" + . " VALUES (" + . "'" . $this->dbi->escapeString($DB) . "', " + . "'" . $this->dbi->escapeString($TAB) . "', " + . "'" . $pageId . "', " + . "'" . $this->dbi->escapeString($_POST['t_x'][$key]) . "', " + . "'" . $this->dbi->escapeString($_POST['t_y'][$key]) . "')"; + + $res = $this->relation->queryAsControlUser( + $query, + true, + DatabaseInterface::QUERY_STORE + ); + } + + return (bool) $res; + } + + /** + * Saves the display field for a table. + * + * @param string $db database name + * @param string $table table name + * @param string $field display field name + * + * @return array<bool,string> + */ + public function saveDisplayField($db, $table, $field) + { + $cfgRelation = $this->relation->getRelationsParam(); + if (! $cfgRelation['displaywork']) { + return [ + false, + _pgettext( + 'phpMyAdmin configuration storage is not configured for "Display Features" on designer when user tries to set a display field.', + 'phpMyAdmin configuration storage is not configured for "Display Features".' + ), + ]; + } + + $upd_query = new Table($table, $db, $this->dbi); + $upd_query->updateDisplayField($field, $cfgRelation); + + return [ + true, + null, + ]; + } + + /** + * Adds a new foreign relation + * + * @param string $db database name + * @param string $T1 foreign table + * @param string $F1 foreign field + * @param string $T2 master table + * @param string $F2 master field + * @param string $on_delete on delete action + * @param string $on_update on update action + * @param string $DB1 database + * @param string $DB2 database + * + * @return array array of success/failure and message + */ + public function addNewRelation($db, $T1, $F1, $T2, $F2, $on_delete, $on_update, $DB1, $DB2) + { + $tables = $this->dbi->getTablesFull($DB1, $T1); + $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']); + $tables = $this->dbi->getTablesFull($DB2, $T2); + $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']); + + // native foreign key + if (Util::isForeignKeySupported($type_T1) + && Util::isForeignKeySupported($type_T2) + && $type_T1 == $type_T2 + ) { + // relation exists? + $existrel_foreign = $this->relation->getForeigners($DB2, $T2, '', 'foreign'); + $foreigner = $this->relation->searchColumnInForeigners($existrel_foreign, $F2); + if ($foreigner + && isset($foreigner['constraint']) + ) { + return [ + false, + __('Error: relationship already exists.'), + ]; + } + // note: in InnoDB, the index does not requires to be on a PRIMARY + // or UNIQUE key + // improve: check all other requirements for InnoDB relations + $result = $this->dbi->query( + 'SHOW INDEX FROM ' . Util::backquote($DB1) + . '.' . Util::backquote($T1) . ';' + ); + + // will be use to emphasis prim. keys in the table view + $index_array1 = []; + while ($row = $this->dbi->fetchAssoc($result)) { + $index_array1[$row['Column_name']] = 1; + } + $this->dbi->freeResult($result); + + $result = $this->dbi->query( + 'SHOW INDEX FROM ' . Util::backquote($DB2) + . '.' . Util::backquote($T2) . ';' + ); + // will be used to emphasis prim. keys in the table view + $index_array2 = []; + while ($row = $this->dbi->fetchAssoc($result)) { + $index_array2[$row['Column_name']] = 1; + } + $this->dbi->freeResult($result); + + if (! empty($index_array1[$F1]) && ! empty($index_array2[$F2])) { + $upd_query = 'ALTER TABLE ' . Util::backquote($DB2) + . '.' . Util::backquote($T2) + . ' ADD FOREIGN KEY (' + . Util::backquote($F2) . ')' + . ' REFERENCES ' + . Util::backquote($DB1) . '.' + . Util::backquote($T1) . '(' + . Util::backquote($F1) . ')'; + + if ($on_delete != 'nix') { + $upd_query .= ' ON DELETE ' . $on_delete; + } + if ($on_update != 'nix') { + $upd_query .= ' ON UPDATE ' . $on_update; + } + $upd_query .= ';'; + if ($this->dbi->tryQuery($upd_query)) { + return [ + true, + __('FOREIGN KEY relationship has been added.'), + ]; + } + + $error = $this->dbi->getError(); + return [ + false, + __('Error: FOREIGN KEY relationship could not be added!') + . "<br>" . $error, + ]; + } + + return [ + false, + __('Error: Missing index on column(s).'), + ]; + } + + // internal (pmadb) relation + if ($GLOBALS['cfgRelation']['relwork'] == false) { + return [ + false, + __('Error: Relational features are disabled!'), + ]; + } + + // no need to recheck if the keys are primary or unique at this point, + // this was checked on the interface part + + $q = "INSERT INTO " + . Util::backquote($GLOBALS['cfgRelation']['db']) + . "." + . Util::backquote($GLOBALS['cfgRelation']['relation']) + . "(master_db, master_table, master_field, " + . "foreign_db, foreign_table, foreign_field)" + . " values(" + . "'" . $this->dbi->escapeString($DB2) . "', " + . "'" . $this->dbi->escapeString($T2) . "', " + . "'" . $this->dbi->escapeString($F2) . "', " + . "'" . $this->dbi->escapeString($DB1) . "', " + . "'" . $this->dbi->escapeString($T1) . "', " + . "'" . $this->dbi->escapeString($F1) . "')"; + + if ($this->relation->queryAsControlUser($q, false, DatabaseInterface::QUERY_STORE) + ) { + return [ + true, + __('Internal relationship has been added.'), + ]; + } + + $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL); + return [ + false, + __('Error: Internal relationship could not be added!') + . "<br>" . $error, + ]; + } + + /** + * Removes a foreign relation + * + * @param string $T1 foreign db.table + * @param string $F1 foreign field + * @param string $T2 master db.table + * @param string $F2 master field + * + * @return array array of success/failure and message + */ + public function removeRelation($T1, $F1, $T2, $F2) + { + list($DB1, $T1) = explode(".", $T1); + list($DB2, $T2) = explode(".", $T2); + + $tables = $this->dbi->getTablesFull($DB1, $T1); + $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']); + $tables = $this->dbi->getTablesFull($DB2, $T2); + $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']); + + if (Util::isForeignKeySupported($type_T1) + && Util::isForeignKeySupported($type_T2) + && $type_T1 == $type_T2 + ) { + // InnoDB + $existrel_foreign = $this->relation->getForeigners($DB2, $T2, '', 'foreign'); + $foreigner = $this->relation->searchColumnInForeigners($existrel_foreign, $F2); + + if (isset($foreigner['constraint'])) { + $upd_query = 'ALTER TABLE ' . Util::backquote($DB2) + . '.' . Util::backquote($T2) . ' DROP FOREIGN KEY ' + . Util::backquote($foreigner['constraint']) . ';'; + if ($this->dbi->query($upd_query)) { + return [ + true, + __('FOREIGN KEY relationship has been removed.'), + ]; + } + + $error = $this->dbi->getError(); + return [ + false, + __('Error: FOREIGN KEY relationship could not be removed!') + . "<br>" . $error, + ]; + } + } + + // internal relations + $delete_query = "DELETE FROM " + . Util::backquote($GLOBALS['cfgRelation']['db']) . "." + . $GLOBALS['cfgRelation']['relation'] . " WHERE " + . "master_db = '" . $this->dbi->escapeString($DB2) . "'" + . " AND master_table = '" . $this->dbi->escapeString($T2) . "'" + . " AND master_field = '" . $this->dbi->escapeString($F2) . "'" + . " AND foreign_db = '" . $this->dbi->escapeString($DB1) . "'" + . " AND foreign_table = '" . $this->dbi->escapeString($T1) . "'" + . " AND foreign_field = '" . $this->dbi->escapeString($F1) . "'"; + + $result = $this->relation->queryAsControlUser( + $delete_query, + false, + DatabaseInterface::QUERY_STORE + ); + + if (! $result) { + $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL); + return [ + false, + __('Error: Internal relationship could not be removed!') . "<br>" . $error, + ]; + } + + return [ + true, + __('Internal relationship has been removed.'), + ]; + } + + /** + * Save value for a designer setting + * + * @param string $index setting + * @param string $value value + * + * @return bool whether the operation succeeded + */ + public function saveSetting($index, $value) + { + $cfgRelation = $this->relation->getRelationsParam(); + $success = true; + if ($cfgRelation['designersettingswork']) { + $cfgDesigner = [ + 'user' => $GLOBALS['cfg']['Server']['user'], + 'db' => $cfgRelation['db'], + 'table' => $cfgRelation['designer_settings'], + ]; + + $orig_data_query = "SELECT settings_data" + . " FROM " . Util::backquote($cfgDesigner['db']) + . "." . Util::backquote($cfgDesigner['table']) + . " WHERE username = '" + . $this->dbi->escapeString($cfgDesigner['user']) . "';"; + + $orig_data = $this->dbi->fetchSingleRow( + $orig_data_query, + 'ASSOC', + DatabaseInterface::CONNECT_CONTROL + ); + + if (! empty($orig_data)) { + $orig_data = json_decode($orig_data['settings_data'], true); + $orig_data[$index] = $value; + $orig_data = json_encode($orig_data); + + $save_query = "UPDATE " + . Util::backquote($cfgDesigner['db']) + . "." . Util::backquote($cfgDesigner['table']) + . " SET settings_data = '" . $orig_data . "'" + . " WHERE username = '" + . $this->dbi->escapeString($cfgDesigner['user']) . "';"; + + $success = $this->relation->queryAsControlUser($save_query); + } else { + $save_data = [$index => $value]; + + $query = "INSERT INTO " + . Util::backquote($cfgDesigner['db']) + . "." . Util::backquote($cfgDesigner['table']) + . " (username, settings_data)" + . " VALUES('" . $this->dbi->escapeString($cfgDesigner['user']) + . "', '" . json_encode($save_data) . "');"; + + $success = $this->relation->queryAsControlUser($query); + } + } + + return (bool) $success; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/Designer/DesignerTable.php b/srcs/phpmyadmin/libraries/classes/Database/Designer/DesignerTable.php new file mode 100644 index 0000000..a4c1c6f --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/Designer/DesignerTable.php @@ -0,0 +1,103 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Holds the PhpMyAdmin\Database\Designer\DesignerTable class + * + * @package PhpMyAdmin-Designer + */ +namespace PhpMyAdmin\Database\Designer; + +use PhpMyAdmin\Util; + +/** + * Common functions for Designer + * + * @package PhpMyAdmin-Designer + */ +class DesignerTable +{ + private $tableName; + private $databaseName; + private $tableEngine; + private $displayField; + + /** + * Create a new DesignerTable + * + * @param string $databaseName The database name + * @param string $tableName The table name + * @param string $tableEngine The table engine + * @param string|null $displayField The display field if available + */ + public function __construct( + string $databaseName, + string $tableName, + string $tableEngine, + ?string $displayField + ) { + $this->databaseName = $databaseName; + $this->tableName = $tableName; + $this->tableEngine = $tableEngine; + $this->displayField = $displayField; + } + + /** + * The table engine supports or not foreign keys + * + * @return bool + */ + public function supportsForeignkeys(): bool + { + return Util::isForeignKeySupported($this->tableEngine); + } + + /** + * Get the database name + * + * @return string + */ + public function getDatabaseName(): string + { + return $this->databaseName; + } + + /** + * Get the table name + * + * @return string + */ + public function getTableName(): string + { + return $this->tableName; + } + + /** + * Get the table engine + * + * @return string + */ + public function getTableEngine(): string + { + return $this->tableEngine; + } + + /** + * Get the displayed field + * + * @return string + */ + public function getDisplayField() + { + return $this->displayField; + } + + /** + * Get the db and table separated with a dot + * + * @return string + */ + public function getDbTableString(): string + { + return $this->databaseName . '.' . $this->tableName; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/MultiTableQuery.php b/srcs/phpmyadmin/libraries/classes/Database/MultiTableQuery.php new file mode 100644 index 0000000..b9fa888 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/MultiTableQuery.php @@ -0,0 +1,145 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Handles DB Multi-table query + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\ParseAnalyze; +use PhpMyAdmin\Sql; +use PhpMyAdmin\Template; + +/** + * Class to handle database Multi-table querying + * + * @package PhpMyAdmin + */ +class MultiTableQuery +{ + /** + * DatabaseInterface instance + * + * @access private + * @var DatabaseInterface + */ + private $dbi; + + /** + * Database name + * + * @access private + * @var string + */ + private $db; + + /** + * Default number of columns + * + * @access private + * @var integer + */ + private $defaultNoOfColumns; + + /** + * Table names + * + * @access private + * @var array + */ + private $tables; + + /** + * @var Template + */ + public $template; + + /** + * Constructor + * + * @param DatabaseInterface $dbi DatabaseInterface instance + * @param Template $template Template instance + * @param string $dbName Database name + * @param integer $defaultNoOfColumns Default number of columns + */ + public function __construct( + DatabaseInterface $dbi, + Template $template, + $dbName, + $defaultNoOfColumns = 3 + ) { + $this->dbi = $dbi; + $this->db = $dbName; + $this->defaultNoOfColumns = $defaultNoOfColumns; + + $this->template = $template; + + $this->tables = $this->dbi->getTables($this->db); + } + + /** + * Get Multi-Table query page HTML + * + * @return string Multi-Table query page HTML + */ + public function getFormHtml() + { + $tables = []; + foreach ($this->tables as $table) { + $tables[$table]['hash'] = md5($table); + $tables[$table]['columns'] = array_keys( + $this->dbi->getColumns($this->db, $table) + ); + } + return $this->template->render('database/multi_table_query/form', [ + 'db' => $this->db, + 'tables' => $tables, + 'default_no_of_columns' => $this->defaultNoOfColumns, + ]); + } + + /** + * Displays multi-table query results + * + * @param string $sqlQuery The query to parse + * @param string $db The current database + * @param string $pmaThemeImage Uri of the PMA theme image + * + * @return void + */ + public static function displayResults($sqlQuery, $db, $pmaThemeImage) + { + list( + $analyzedSqlResults, + $db, + ) = ParseAnalyze::sqlQuery($sqlQuery, $db); + + extract($analyzedSqlResults); + $goto = 'db_multi_table_query.php'; + $sql = new Sql(); + $sql->executeQueryAndSendQueryResponse( + null, // analyzed_sql_results + false, // is_gotofile + $db, // db + null, // table + null, // find_real_end + null, // sql_query_for_bookmark - see below + null, // extra_data + null, // message_to_show + null, // message + null, // sql_data + $goto, // goto + $pmaThemeImage, // pmaThemeImage + null, // disp_query + null, // disp_message + null, // query_type + $sqlQuery, // sql_query + null, // selectedTables + null // complete_query + ); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/Qbe.php b/srcs/phpmyadmin/libraries/classes/Database/Qbe.php new file mode 100644 index 0000000..27116d0 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/Qbe.php @@ -0,0 +1,1963 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Handles DB QBE search + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database; + +use PhpMyAdmin\Core; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Message; +use PhpMyAdmin\Relation; +use PhpMyAdmin\SavedSearches; +use PhpMyAdmin\Table; +use PhpMyAdmin\Template; +use PhpMyAdmin\Url; +use PhpMyAdmin\Util; + +/** + * Class to handle database QBE search + * + * @package PhpMyAdmin + */ +class Qbe +{ + /** + * Database name + * + * @access private + * @var string + */ + private $_db; + /** + * Table Names (selected/non-selected) + * + * @access private + * @var array + */ + private $_criteriaTables; + /** + * Column Names + * + * @access private + * @var array + */ + private $_columnNames; + /** + * Number of columns + * + * @access private + * @var integer + */ + private $_criteria_column_count; + /** + * Number of Rows + * + * @access private + * @var integer + */ + private $_criteria_row_count; + /** + * Whether to insert a new column + * + * @access private + * @var array + */ + private $_criteriaColumnInsert; + /** + * Whether to delete a column + * + * @access private + * @var array + */ + private $_criteriaColumnDelete; + /** + * Whether to insert a new row + * + * @access private + * @var array + */ + private $_criteriaRowInsert; + /** + * Whether to delete a row + * + * @access private + * @var array + */ + private $_criteriaRowDelete; + /** + * Already set criteria values + * + * @access private + * @var array + */ + private $_criteria; + /** + * Previously set criteria values + * + * @access private + * @var array + */ + private $_prev_criteria; + /** + * AND/OR relation b/w criteria columns + * + * @access private + * @var array + */ + private $_criteriaAndOrColumn; + /** + * AND/OR relation b/w criteria rows + * + * @access private + * @var array + */ + private $_criteriaAndOrRow; + /** + * Large width of a column + * + * @access private + * @var string + */ + private $_realwidth; + /** + * Minimum width of a column + * + * @access private + * @var int + */ + private $_form_column_width; + /** + * Selected columns in the form + * + * @access private + * @var array + */ + private $_formColumns; + /** + * Entered aliases in the form + * + * @access private + * @var array + */ + private $_formAliases; + /** + * Chosen sort options in the form + * + * @access private + * @var array + */ + private $_formSorts; + /** + * Chosen sort orders in the form + * + * @access private + * @var array + */ + private $_formSortOrders; + /** + * Show checkboxes in the form + * + * @access private + * @var array + */ + private $_formShows; + /** + * Entered criteria values in the form + * + * @access private + * @var array + */ + private $_formCriterions; + /** + * AND/OR column radio buttons in the form + * + * @access private + * @var array + */ + private $_formAndOrCols; + /** + * AND/OR row radio buttons in the form + * + * @access private + * @var array + */ + private $_formAndOrRows; + /** + * New column count in case of add/delete + * + * @access private + * @var integer + */ + private $_new_column_count; + /** + * New row count in case of add/delete + * + * @access private + * @var integer + */ + private $_new_row_count; + /** + * List of saved searches + * + * @access private + * @var array + */ + private $_savedSearchList = null; + /** + * Current search + * + * @access private + * @var SavedSearches + */ + private $_currentSearch = null; + + /** + * @var Relation + */ + private $relation; + + /** + * @var DatabaseInterface + */ + public $dbi; + + /** + * @var Template + */ + public $template; + + /** + * Public Constructor + * + * @param Relation $relation Relation object + * @param Template $template Template object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param string $dbname Database name + * @param array $savedSearchList List of saved searches + * @param SavedSearches $currentSearch Current search id + */ + public function __construct( + Relation $relation, + Template $template, + $dbi, + $dbname, + array $savedSearchList = [], + $currentSearch = null + ) { + $this->_db = $dbname; + $this->_savedSearchList = $savedSearchList; + $this->_currentSearch = $currentSearch; + $this->dbi = $dbi; + $this->relation = $relation; + $this->template = $template; + + $this->_loadCriterias(); + // Sets criteria parameters + $this->_setSearchParams(); + $this->_setCriteriaTablesAndColumns(); + } + + /** + * Initialize criterias + * + * @return static + */ + private function _loadCriterias() + { + if (null === $this->_currentSearch + || null === $this->_currentSearch->getCriterias() + ) { + return $this; + } + + $criterias = $this->_currentSearch->getCriterias(); + $_POST = $criterias + $_POST; + + return $this; + } + + /** + * Getter for current search + * + * @return SavedSearches + */ + private function _getCurrentSearch() + { + return $this->_currentSearch; + } + + /** + * Sets search parameters + * + * @return void + */ + private function _setSearchParams() + { + $criteriaColumnCount = $this->_initializeCriteriasCount(); + + $this->_criteriaColumnInsert = Core::ifSetOr( + $_POST['criteriaColumnInsert'], + null, + 'array' + ); + $this->_criteriaColumnDelete = Core::ifSetOr( + $_POST['criteriaColumnDelete'], + null, + 'array' + ); + + $this->_prev_criteria = isset($_POST['prev_criteria']) + ? $_POST['prev_criteria'] + : []; + $this->_criteria = isset($_POST['criteria']) + ? $_POST['criteria'] + : array_fill(0, $criteriaColumnCount, ''); + + $this->_criteriaRowInsert = isset($_POST['criteriaRowInsert']) + ? $_POST['criteriaRowInsert'] + : array_fill(0, $criteriaColumnCount, ''); + $this->_criteriaRowDelete = isset($_POST['criteriaRowDelete']) + ? $_POST['criteriaRowDelete'] + : array_fill(0, $criteriaColumnCount, ''); + $this->_criteriaAndOrRow = isset($_POST['criteriaAndOrRow']) + ? $_POST['criteriaAndOrRow'] + : array_fill(0, $criteriaColumnCount, ''); + $this->_criteriaAndOrColumn = isset($_POST['criteriaAndOrColumn']) + ? $_POST['criteriaAndOrColumn'] + : array_fill(0, $criteriaColumnCount, ''); + // sets minimum width + $this->_form_column_width = 12; + $this->_formColumns = []; + $this->_formSorts = []; + $this->_formShows = []; + $this->_formCriterions = []; + $this->_formAndOrRows = []; + $this->_formAndOrCols = []; + } + + /** + * Sets criteria tables and columns + * + * @return void + */ + private function _setCriteriaTablesAndColumns() + { + // The tables list sent by a previously submitted form + if (Core::isValid($_POST['TableList'], 'array')) { + foreach ($_POST['TableList'] as $each_table) { + $this->_criteriaTables[$each_table] = ' selected="selected"'; + } + } // end if + $all_tables = $this->dbi->query( + 'SHOW TABLES FROM ' . Util::backquote($this->_db) . ';', + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $all_tables_count = $this->dbi->numRows($all_tables); + if (0 == $all_tables_count) { + Message::error(__('No tables found in database.'))->display(); + exit; + } + // The tables list gets from MySQL + while (list($table) = $this->dbi->fetchRow($all_tables)) { + $columns = $this->dbi->getColumns($this->_db, $table); + + if (empty($this->_criteriaTables[$table]) + && ! empty($_POST['TableList']) + ) { + $this->_criteriaTables[$table] = ''; + } else { + $this->_criteriaTables[$table] = ' selected="selected"'; + } // end if + + // The fields list per selected tables + if ($this->_criteriaTables[$table] == ' selected="selected"') { + $each_table = Util::backquote($table); + $this->_columnNames[] = $each_table . '.*'; + foreach ($columns as $each_column) { + $each_column = $each_table . '.' + . Util::backquote($each_column['Field']); + $this->_columnNames[] = $each_column; + // increase the width if necessary + $this->_form_column_width = max( + mb_strlen($each_column), + $this->_form_column_width + ); + } // end foreach + } // end if + } // end while + $this->dbi->freeResult($all_tables); + + // sets the largest width found + $this->_realwidth = $this->_form_column_width . 'ex'; + } + /** + * Provides select options list containing column names + * + * @param integer $column_number Column Number (0,1,2) or more + * @param string $selected Selected criteria column name + * + * @return string HTML for select options + */ + private function _showColumnSelectCell($column_number, $selected = '') + { + return $this->template->render('database/qbe/column_select_cell', [ + 'column_number' => $column_number, + 'column_names' => $this->_columnNames, + 'selected' => $selected, + ]); + } + + /** + * Provides select options list containing sort options (ASC/DESC) + * + * @param integer $columnNumber Column Number (0,1,2) or more + * @param string $selected Selected criteria 'ASC' or 'DESC' + * + * @return string HTML for select options + */ + private function _getSortSelectCell( + $columnNumber, + $selected = '' + ) { + return $this->template->render('database/qbe/sort_select_cell', [ + 'real_width' => $this->_realwidth, + 'column_number' => $columnNumber, + 'selected' => $selected, + ]); + } + + /** + * Provides select options list containing sort order + * + * @param integer $columnNumber Column Number (0,1,2) or more + * @param integer $sortOrder Sort order + * + * @return string HTML for select options + */ + private function _getSortOrderSelectCell($columnNumber, $sortOrder) + { + $totalColumnCount = $this->_getNewColumnCount(); + return $this->template->render('database/qbe/sort_order_select_cell', [ + 'total_column_count' => $totalColumnCount, + 'column_number' => $columnNumber, + 'sort_order' => $sortOrder, + ]); + } + + /** + * Returns the new column count after adding and removing columns as instructed + * + * @return int new column count + */ + private function _getNewColumnCount() + { + $totalColumnCount = $this->_criteria_column_count; + if (! empty($this->_criteriaColumnInsert)) { + $totalColumnCount += count($this->_criteriaColumnInsert); + } + if (! empty($this->_criteriaColumnDelete)) { + $totalColumnCount -= count($this->_criteriaColumnDelete); + } + return $totalColumnCount; + } + + /** + * Provides search form's row containing column select options + * + * @return string HTML for search table's row + */ + private function _getColumnNamesRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Column:') . '</th>'; + $new_column_count = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (isset($this->_criteriaColumnInsert[$column_index]) + && $this->_criteriaColumnInsert[$column_index] == 'on' + ) { + $html_output .= $this->_showColumnSelectCell( + $new_column_count + ); + $new_column_count++; + } + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$column_index]) + && $this->_criteriaColumnDelete[$column_index] == 'on' + ) { + continue; + } + $selected = ''; + if (isset($_POST['criteriaColumn'][$column_index])) { + $selected = $_POST['criteriaColumn'][$column_index]; + $this->_formColumns[$new_column_count] + = $_POST['criteriaColumn'][$column_index]; + } + $html_output .= $this->_showColumnSelectCell( + $new_column_count, + $selected + ); + $new_column_count++; + } // end for + $this->_new_column_count = $new_column_count; + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides search form's row containing column aliases + * + * @return string HTML for search table's row + */ + private function _getColumnAliasRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Alias:') . '</th>'; + $new_column_count = 0; + + for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$colInd]) + && $this->_criteriaColumnInsert[$colInd] == 'on' + ) { + $html_output .= '<td class="center">'; + $html_output .= '<input type="text"' + . ' name="criteriaAlias[' . $new_column_count . ']">'; + $html_output .= '</td>'; + $new_column_count++; + } // end if + + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$colInd]) + && $this->_criteriaColumnDelete[$colInd] == 'on' + ) { + continue; + } + + $tmp_alias = ''; + if (! empty($_POST['criteriaAlias'][$colInd])) { + $tmp_alias + = $this->_formAliases[$new_column_count] + = $_POST['criteriaAlias'][$colInd]; + }// end if + + $html_output .= '<td class="center">'; + $html_output .= '<input type="text"' + . ' name="criteriaAlias[' . $new_column_count . ']"' + . ' value="' . htmlspecialchars($tmp_alias) . '">'; + $html_output .= '</td>'; + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides search form's row containing sort(ASC/DESC) select options + * + * @return string HTML for search table's row + */ + private function _getSortRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Sort:') . '</th>'; + $new_column_count = 0; + + for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$colInd]) + && $this->_criteriaColumnInsert[$colInd] == 'on' + ) { + $html_output .= $this->_getSortSelectCell($new_column_count); + $new_column_count++; + } // end if + + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$colInd]) + && $this->_criteriaColumnDelete[$colInd] == 'on' + ) { + continue; + } + // If they have chosen all fields using the * selector, + // then sorting is not available, Fix for Bug #570698 + if (isset($_POST['criteriaSort'][$colInd]) + && isset($_POST['criteriaColumn'][$colInd]) + && mb_substr($_POST['criteriaColumn'][$colInd], -2) == '.*' + ) { + $_POST['criteriaSort'][$colInd] = ''; + } //end if + + $selected = ''; + if (isset($_POST['criteriaSort'][$colInd])) { + $this->_formSorts[$new_column_count] + = $_POST['criteriaSort'][$colInd]; + + if ($_POST['criteriaSort'][$colInd] == 'ASC') { + $selected = 'ASC'; + } elseif ($_POST['criteriaSort'][$colInd] == 'DESC') { + $selected = 'DESC'; + } + } else { + $this->_formSorts[$new_column_count] = ''; + } + + $html_output .= $this->_getSortSelectCell( + $new_column_count, + $selected + ); + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides search form's row containing sort order + * + * @return string HTML for search table's row + */ + private function _getSortOrder() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Sort order:') . '</th>'; + $new_column_count = 0; + + for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$colInd]) + && $this->_criteriaColumnInsert[$colInd] == 'on' + ) { + $html_output .= $this->_getSortOrderSelectCell( + $new_column_count, + null + ); + $new_column_count++; + } // end if + + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$colInd]) + && $this->_criteriaColumnDelete[$colInd] == 'on' + ) { + continue; + } + + $sortOrder = null; + if (! empty($_POST['criteriaSortOrder'][$colInd])) { + $sortOrder + = $this->_formSortOrders[$new_column_count] + = $_POST['criteriaSortOrder'][$colInd]; + } + + $html_output .= $this->_getSortOrderSelectCell( + $new_column_count, + $sortOrder + ); + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides search form's row containing SHOW checkboxes + * + * @return string HTML for search table's row + */ + private function _getShowRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Show:') . '</th>'; + $new_column_count = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$column_index]) + && $this->_criteriaColumnInsert[$column_index] == 'on' + ) { + $html_output .= '<td class="center">'; + $html_output .= '<input type="checkbox"' + . ' name="criteriaShow[' . $new_column_count . ']">'; + $html_output .= '</td>'; + $new_column_count++; + } // end if + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$column_index]) + && $this->_criteriaColumnDelete[$column_index] == 'on' + ) { + continue; + } + if (isset($_POST['criteriaShow'][$column_index])) { + $checked_options = ' checked="checked"'; + $this->_formShows[$new_column_count] + = $_POST['criteriaShow'][$column_index]; + } else { + $checked_options = ''; + } + $html_output .= '<td class="center">'; + $html_output .= '<input type="checkbox"' + . ' name="criteriaShow[' . $new_column_count . ']"' + . $checked_options . '>'; + $html_output .= '</td>'; + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides search form's row containing criteria Inputboxes + * + * @return string HTML for search table's row + */ + private function _getCriteriaInputboxRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Criteria:') . '</th>'; + $new_column_count = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$column_index]) + && $this->_criteriaColumnInsert[$column_index] == 'on' + ) { + $html_output .= '<td class="center">'; + $html_output .= '<input type="text"' + . ' name="criteria[' . $new_column_count . ']"' + . ' class="textfield"' + . ' style="width: ' . $this->_realwidth . '"' + . ' size="20">'; + $html_output .= '</td>'; + $new_column_count++; + } // end if + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$column_index]) + && $this->_criteriaColumnDelete[$column_index] == 'on' + ) { + continue; + } + $tmp_criteria = ''; + if (isset($this->_criteria[$column_index])) { + $tmp_criteria = $this->_criteria[$column_index]; + } + if ((empty($this->_prev_criteria) + || ! isset($this->_prev_criteria[$column_index])) + || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria) + ) { + $this->_formCriterions[$new_column_count] = $tmp_criteria; + } else { + $this->_formCriterions[$new_column_count] + = $this->_prev_criteria[$column_index]; + } + $html_output .= '<td class="center">'; + $html_output .= '<input type="hidden"' + . ' name="prev_criteria[' . $new_column_count . ']"' + . ' value="' + . htmlspecialchars($this->_formCriterions[$new_column_count]) + . '">'; + $html_output .= '<input type="text"' + . ' name="criteria[' . $new_column_count . ']"' + . ' value="' . htmlspecialchars($tmp_criteria) . '"' + . ' class="textfield"' + . ' style="width: ' . $this->_realwidth . '"' + . ' size="20">'; + $html_output .= '</td>'; + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides footer options for adding/deleting row/columns + * + * @param string $type Whether row or column + * + * @return string HTML for footer options + */ + private function _getFootersOptions($type) + { + return $this->template->render('database/qbe/footer_options', [ + 'type' => $type, + ]); + } + + /** + * Provides search form table's footer options + * + * @return string HTML for table footer + */ + private function _getTableFooters() + { + $html_output = '<fieldset class="tblFooters">'; + $html_output .= $this->_getFootersOptions("row"); + $html_output .= $this->_getFootersOptions("column"); + $html_output .= '<div class="floatleft">'; + $html_output .= '<input class="btn btn-secondary" type="submit" name="modify"' + . ' value="' . __('Update Query') . '">'; + $html_output .= '</div>'; + $html_output .= '</fieldset>'; + return $html_output; + } + + /** + * Provides a select list of database tables + * + * @return string HTML for table select list + */ + private function _getTablesList() + { + $html_output = '<div class="floatleft width100">'; + $html_output .= '<fieldset>'; + $html_output .= '<legend>' . __('Use Tables') . '</legend>'; + // Build the options list for each table name + $options = ''; + $numTableListOptions = 0; + foreach ($this->_criteriaTables as $key => $val) { + $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>' + . str_replace(' ', ' ', htmlspecialchars($key)) . '</option>'; + $numTableListOptions++; + } + $html_output .= '<select name="TableList[]"' + . ' multiple="multiple" id="listTable"' + . ' size="' . ($numTableListOptions > 30 ? '15' : '7') . '">'; + $html_output .= $options; + $html_output .= '</select>'; + $html_output .= '</fieldset>'; + $html_output .= '<fieldset class="tblFooters">'; + $html_output .= '<input class="btn btn-secondary" type="submit" name="modify" value="' + . __('Update Query') . '">'; + $html_output .= '</fieldset>'; + $html_output .= '</div>'; + return $html_output; + } + + /** + * Provides And/Or modification cell along with Insert/Delete options + * (For modifying search form's table columns) + * + * @param integer $column_number Column Number (0,1,2) or more + * @param array|null $selected Selected criteria column name + * @param bool $last_column Whether this is the last column + * + * @return string HTML for modification cell + */ + private function _getAndOrColCell( + $column_number, + $selected = null, + $last_column = false + ) { + $html_output = '<td class="center">'; + if (! $last_column) { + $html_output .= '<strong>' . __('Or:') . '</strong>'; + $html_output .= '<input type="radio"' + . ' name="criteriaAndOrColumn[' . $column_number . ']"' + . ' value="or"' . ($selected['or'] ?? '') . '>'; + $html_output .= ' <strong>' . __('And:') . '</strong>'; + $html_output .= '<input type="radio"' + . ' name="criteriaAndOrColumn[' . $column_number . ']"' + . ' value="and"' . ($selected['and'] ?? '') . '>'; + } + $html_output .= '<br>' . __('Ins'); + $html_output .= '<input type="checkbox"' + . ' name="criteriaColumnInsert[' . $column_number . ']">'; + $html_output .= ' ' . __('Del'); + $html_output .= '<input type="checkbox"' + . ' name="criteriaColumnDelete[' . $column_number . ']">'; + $html_output .= '</td>'; + return $html_output; + } + + /** + * Provides search form's row containing column modifications options + * (For modifying search form's table columns) + * + * @return string HTML for search table's row + */ + private function _getModifyColumnsRow() + { + $html_output = '<tr class="noclick">'; + $html_output .= '<th>' . __('Modify:') . '</th>'; + $new_column_count = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$column_index]) + && $this->_criteriaColumnInsert[$column_index] == 'on' + ) { + $html_output .= $this->_getAndOrColCell($new_column_count); + $new_column_count++; + } // end if + + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$column_index]) + && $this->_criteriaColumnDelete[$column_index] == 'on' + ) { + continue; + } + + if (isset($this->_criteriaAndOrColumn[$column_index])) { + $this->_formAndOrCols[$new_column_count] + = $this->_criteriaAndOrColumn[$column_index]; + } + $checked_options = []; + if (isset($this->_criteriaAndOrColumn[$column_index]) + && $this->_criteriaAndOrColumn[$column_index] == 'or' + ) { + $checked_options['or'] = ' checked="checked"'; + $checked_options['and'] = ''; + } else { + $checked_options['and'] = ' checked="checked"'; + $checked_options['or'] = ''; + } + $html_output .= $this->_getAndOrColCell( + $new_column_count, + $checked_options, + $column_index + 1 == $this->_criteria_column_count + ); + $new_column_count++; + } // end for + $html_output .= '</tr>'; + return $html_output; + } + + /** + * Provides Insert/Delete options for criteria inputbox + * with AND/OR relationship modification options + * + * @param integer $row_index Number of criteria row + * @param array $checked_options If checked + * + * @return string HTML + */ + private function _getInsDelAndOrCell($row_index, array $checked_options) + { + $html_output = '<td class="value nowrap">'; + $html_output .= '<!-- Row controls -->'; + $html_output .= '<table class="nospacing nopadding">'; + $html_output .= '<tr>'; + $html_output .= '<td class="value nowrap">'; + $html_output .= '<small>' . __('Ins:') . '</small>'; + $html_output .= '<input type="checkbox"' + . ' name="criteriaRowInsert[' . $row_index . ']">'; + $html_output .= '</td>'; + $html_output .= '<td class="value">'; + $html_output .= '<strong>' . __('And:') . '</strong>'; + $html_output .= '</td>'; + $html_output .= '<td>'; + $html_output .= '<input type="radio"' + . ' name="criteriaAndOrRow[' . $row_index . ']" value="and"' + . $checked_options['and'] . '>'; + $html_output .= '</td>'; + $html_output .= '</tr>'; + $html_output .= '<tr>'; + $html_output .= '<td class="value nowrap">'; + $html_output .= '<small>' . __('Del:') . '</small>'; + $html_output .= '<input type="checkbox"' + . ' name="criteriaRowDelete[' . $row_index . ']">'; + $html_output .= '</td>'; + $html_output .= '<td class="value">'; + $html_output .= '<strong>' . __('Or:') . '</strong>'; + $html_output .= '</td>'; + $html_output .= '<td>'; + $html_output .= '<input type="radio"' + . ' name="criteriaAndOrRow[' . $row_index . ']"' + . ' value="or"' . $checked_options['or'] . '>'; + $html_output .= '</td>'; + $html_output .= '</tr>'; + $html_output .= '</table>'; + $html_output .= '</td>'; + return $html_output; + } + + /** + * Provides rows for criteria inputbox Insert/Delete options + * with AND/OR relationship modification options + * + * @param integer $new_row_index New row index if rows are added/deleted + * + * @return string HTML table rows + */ + private function _getInputboxRow($new_row_index) + { + $html_output = ''; + $new_column_count = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_criteriaColumnInsert) + && isset($this->_criteriaColumnInsert[$column_index]) + && $this->_criteriaColumnInsert[$column_index] == 'on' + ) { + $orFieldName = 'Or' . $new_row_index . '[' . $new_column_count . ']'; + $html_output .= '<td class="center">'; + $html_output .= '<input type="text"' + . ' name="Or' . $orFieldName . '" class="textfield"' + . ' style="width: ' . $this->_realwidth . '" size="20">'; + $html_output .= '</td>'; + $new_column_count++; + } // end if + if (! empty($this->_criteriaColumnDelete) + && isset($this->_criteriaColumnDelete[$column_index]) + && $this->_criteriaColumnDelete[$column_index] == 'on' + ) { + continue; + } + $or = 'Or' . $new_row_index; + if (! empty($_POST[$or]) && isset($_POST[$or][$column_index])) { + $tmp_or = $_POST[$or][$column_index]; + } else { + $tmp_or = ''; + } + $html_output .= '<td class="center">'; + $html_output .= '<input type="text"' + . ' name="Or' . $new_row_index . '[' . $new_column_count . ']"' + . ' value="' . htmlspecialchars($tmp_or) . '" class="textfield"' + . ' style="width: ' . $this->_realwidth . '" size="20">'; + $html_output .= '</td>'; + if (! empty(${$or}) && isset(${$or}[$column_index])) { + $GLOBALS[${'cur' . $or}][$new_column_count] + = ${$or}[$column_index]; + } + $new_column_count++; + } // end for + return $html_output; + } + + /** + * Provides rows for criteria inputbox Insert/Delete options + * with AND/OR relationship modification options + * + * @return string HTML table rows + */ + private function _getInsDelAndOrCriteriaRows() + { + $html_output = ''; + $new_row_count = 0; + $checked_options = []; + for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { + if (isset($this->_criteriaRowInsert[$row_index]) + && $this->_criteriaRowInsert[$row_index] == 'on' + ) { + $checked_options['or'] = ' checked="checked"'; + $checked_options['and'] = ''; + $html_output .= '<tr class="noclick">'; + $html_output .= $this->_getInsDelAndOrCell( + $new_row_count, + $checked_options + ); + $html_output .= $this->_getInputboxRow( + $new_row_count + ); + $new_row_count++; + $html_output .= '</tr>'; + } // end if + if (isset($this->_criteriaRowDelete[$row_index]) + && $this->_criteriaRowDelete[$row_index] == 'on' + ) { + continue; + } + if (isset($this->_criteriaAndOrRow[$row_index])) { + $this->_formAndOrRows[$new_row_count] + = $this->_criteriaAndOrRow[$row_index]; + } + if (isset($this->_criteriaAndOrRow[$row_index]) + && $this->_criteriaAndOrRow[$row_index] == 'and' + ) { + $checked_options['and'] = ' checked="checked"'; + $checked_options['or'] = ''; + } else { + $checked_options['or'] = ' checked="checked"'; + $checked_options['and'] = ''; + } + $html_output .= '<tr class="noclick">'; + $html_output .= $this->_getInsDelAndOrCell( + $new_row_count, + $checked_options + ); + $html_output .= $this->_getInputboxRow( + $new_row_count + ); + $new_row_count++; + $html_output .= '</tr>'; + } // end for + $this->_new_row_count = $new_row_count; + return $html_output; + } + + /** + * Provides SELECT clause for building SQL query + * + * @return string Select clause + */ + private function _getSelectClause() + { + $select_clause = ''; + $select_clauses = []; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_formColumns[$column_index]) + && isset($this->_formShows[$column_index]) + && $this->_formShows[$column_index] == 'on' + ) { + $select = $this->_formColumns[$column_index]; + if (! empty($this->_formAliases[$column_index])) { + $select .= " AS " + . Util::backquote($this->_formAliases[$column_index]); + } + $select_clauses[] = $select; + } + } // end for + if (! empty($select_clauses)) { + $select_clause = 'SELECT ' + . htmlspecialchars(implode(", ", $select_clauses)) . "\n"; + } + return $select_clause; + } + + /** + * Provides WHERE clause for building SQL query + * + * @return string Where clause + */ + private function _getWhereClause() + { + $where_clause = ''; + $criteria_cnt = 0; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_formColumns[$column_index]) + && ! empty($this->_formCriterions[$column_index]) + && $column_index + && isset($last_where) + && isset($this->_formAndOrCols) + ) { + $where_clause .= ' ' + . mb_strtoupper($this->_formAndOrCols[$last_where]) + . ' '; + } + if (! empty($this->_formColumns[$column_index]) + && ! empty($this->_formCriterions[$column_index]) + ) { + $where_clause .= '(' . $this->_formColumns[$column_index] . ' ' + . $this->_formCriterions[$column_index] . ')'; + $last_where = $column_index; + $criteria_cnt++; + } + } // end for + if ($criteria_cnt > 1) { + $where_clause = '(' . $where_clause . ')'; + } + // OR rows ${'cur' . $or}[$column_index] + if (! isset($this->_formAndOrRows)) { + $this->_formAndOrRows = []; + } + for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { + $criteria_cnt = 0; + $qry_orwhere = ''; + $last_orwhere = ''; + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + if (! empty($this->_formColumns[$column_index]) + && ! empty($_POST['Or' . $row_index][$column_index]) + && $column_index + ) { + $qry_orwhere .= ' ' + . mb_strtoupper( + $this->_formAndOrCols[$last_orwhere] + ) + . ' '; + } + if (! empty($this->_formColumns[$column_index]) + && ! empty($_POST['Or' . $row_index][$column_index]) + ) { + $qry_orwhere .= '(' . $this->_formColumns[$column_index] + . ' ' + . $_POST['Or' . $row_index][$column_index] + . ')'; + $last_orwhere = $column_index; + $criteria_cnt++; + } + } // end for + if ($criteria_cnt > 1) { + $qry_orwhere = '(' . $qry_orwhere . ')'; + } + if (! empty($qry_orwhere)) { + $where_clause .= "\n" + . mb_strtoupper( + isset($this->_formAndOrRows[$row_index]) + ? $this->_formAndOrRows[$row_index] . ' ' + : '' + ) + . $qry_orwhere; + } // end if + } // end for + + if (! empty($where_clause) && $where_clause != '()') { + $where_clause = 'WHERE ' . htmlspecialchars($where_clause) . "\n"; + } // end if + return $where_clause; + } + + /** + * Provides ORDER BY clause for building SQL query + * + * @return string Order By clause + */ + private function _getOrderByClause() + { + $orderby_clause = ''; + $orderby_clauses = []; + + // Create copy of instance variables + $columns = $this->_formColumns; + $sort = $this->_formSorts; + $sortOrder = $this->_formSortOrders; + if (! empty($sortOrder) + && count($sortOrder) == count($sort) + && count($sortOrder) == count($columns) + ) { + // Sort all three arrays based on sort order + array_multisort($sortOrder, $sort, $columns); + } + + for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { + // if all columns are chosen with * selector, + // then sorting isn't available + // Fix for Bug #570698 + if (empty($columns[$column_index]) + && empty($sort[$column_index]) + ) { + continue; + } + + if (mb_substr($columns[$column_index], -2) == '.*') { + continue; + } + + if (! empty($sort[$column_index])) { + $orderby_clauses[] = $columns[$column_index] . ' ' + . $sort[$column_index]; + } + } // end for + if (! empty($orderby_clauses)) { + $orderby_clause = 'ORDER BY ' + . htmlspecialchars(implode(", ", $orderby_clauses)) . "\n"; + } + return $orderby_clause; + } + + /** + * Provides UNIQUE columns and INDEX columns present in criteria tables + * + * @param array $search_tables Tables involved in the search + * @param array $search_columns Columns involved in the search + * @param array $where_clause_columns Columns having criteria where clause + * + * @return array having UNIQUE and INDEX columns + */ + private function _getIndexes( + array $search_tables, + array $search_columns, + array $where_clause_columns + ) { + $unique_columns = []; + $index_columns = []; + + foreach ($search_tables as $table) { + $indexes = $this->dbi->getTableIndexes($this->_db, $table); + foreach ($indexes as $index) { + $column = $table . '.' . $index['Column_name']; + if (isset($search_columns[$column])) { + if ($index['Non_unique'] == 0) { + if (isset($where_clause_columns[$column])) { + $unique_columns[$column] = 'Y'; + } else { + $unique_columns[$column] = 'N'; + } + } else { + if (isset($where_clause_columns[$column])) { + $index_columns[$column] = 'Y'; + } else { + $index_columns[$column] = 'N'; + } + } + } + } // end while (each index of a table) + } // end while (each table) + + return [ + 'unique' => $unique_columns, + 'index' => $index_columns, + ]; + } + + /** + * Provides UNIQUE columns and INDEX columns present in criteria tables + * + * @param array $search_tables Tables involved in the search + * @param array $search_columns Columns involved in the search + * @param array $where_clause_columns Columns having criteria where clause + * + * @return array having UNIQUE and INDEX columns + */ + private function _getLeftJoinColumnCandidates( + array $search_tables, + array $search_columns, + array $where_clause_columns + ) { + $this->dbi->selectDb($this->_db); + + // Get unique columns and index columns + $indexes = $this->_getIndexes( + $search_tables, + $search_columns, + $where_clause_columns + ); + $unique_columns = $indexes['unique']; + $index_columns = $indexes['index']; + + list($candidate_columns, $needsort) + = $this->_getLeftJoinColumnCandidatesBest( + $search_tables, + $where_clause_columns, + $unique_columns, + $index_columns + ); + + // If we came up with $unique_columns (very good) or $index_columns (still + // good) as $candidate_columns we want to check if we have any 'Y' there + // (that would mean that they were also found in the whereclauses + // which would be great). if yes, we take only those + if ($needsort != 1) { + return $candidate_columns; + } + + $very_good = []; + $still_good = []; + foreach ($candidate_columns as $column => $is_where) { + $table = explode('.', $column); + $table = $table[0]; + if ($is_where == 'Y') { + $very_good[$column] = $table; + } else { + $still_good[$column] = $table; + } + } + if (count($very_good) > 0) { + $candidate_columns = $very_good; + // Candidates restricted in index+where + } else { + $candidate_columns = $still_good; + // None of the candidates where in a where-clause + } + + return $candidate_columns; + } + + /** + * Provides the main table to form the LEFT JOIN clause + * + * @param array $search_tables Tables involved in the search + * @param array $search_columns Columns involved in the search + * @param array $where_clause_columns Columns having criteria where clause + * @param array $where_clause_tables Tables having criteria where clause + * + * @return string table name + */ + private function _getMasterTable( + array $search_tables, + array $search_columns, + array $where_clause_columns, + array $where_clause_tables + ) { + if (count($where_clause_tables) === 1) { + // If there is exactly one column that has a decent where-clause + // we will just use this + return key($where_clause_tables); + } + + // Now let's find out which of the tables has an index + // (When the control user is the same as the normal user + // because he is using one of his databases as pmadb, + // the last db selected is not always the one where we need to work) + $candidate_columns = $this->_getLeftJoinColumnCandidates( + $search_tables, + $search_columns, + $where_clause_columns + ); + + // Generally, we need to display all the rows of foreign (referenced) + // table, whether they have any matching row in child table or not. + // So we select candidate tables which are foreign tables. + $foreign_tables = []; + foreach ($candidate_columns as $one_table) { + $foreigners = $this->relation->getForeigners($this->_db, $one_table); + foreach ($foreigners as $key => $foreigner) { + if ($key != 'foreign_keys_data') { + if (in_array($foreigner['foreign_table'], $candidate_columns)) { + $foreign_tables[$foreigner['foreign_table']] + = $foreigner['foreign_table']; + } + continue; + } + foreach ($foreigner as $one_key) { + if (in_array($one_key['ref_table_name'], $candidate_columns)) { + $foreign_tables[$one_key['ref_table_name']] + = $one_key['ref_table_name']; + } + } + } + } + if (count($foreign_tables)) { + $candidate_columns = $foreign_tables; + } + + // If our array of candidates has more than one member we'll just + // find the smallest table. + // Of course the actual query would be faster if we check for + // the Criteria which gives the smallest result set in its table, + // but it would take too much time to check this + if (! (count($candidate_columns) > 1)) { + // Only one single candidate + return reset($candidate_columns); + } + + // Of course we only want to check each table once + $checked_tables = $candidate_columns; + $tsize = []; + $maxsize = -1; + $result = ''; + foreach ($candidate_columns as $table) { + if ($checked_tables[$table] != 1) { + $_table = new Table($table, $this->_db); + $tsize[$table] = $_table->countRecords(); + $checked_tables[$table] = 1; + } + if ($tsize[$table] > $maxsize) { + $maxsize = $tsize[$table]; + $result = $table; + } + } + // Return largest table + return $result; + } + + /** + * Provides columns and tables that have valid where clause criteria + * + * @return array + */ + private function _getWhereClauseTablesAndColumns() + { + $where_clause_columns = []; + $where_clause_tables = []; + + // Now we need all tables that we have in the where clause + for ($column_index = 0, $nb = count($this->_criteria); $column_index < $nb; $column_index++) { + $current_table = explode('.', $_POST['criteriaColumn'][$column_index]); + if (empty($current_table[0]) || empty($current_table[1])) { + continue; + } // end if + $table = str_replace('`', '', $current_table[0]); + $column = str_replace('`', '', $current_table[1]); + $column = $table . '.' . $column; + // Now we know that our array has the same numbers as $criteria + // we can check which of our columns has a where clause + if (! empty($this->_criteria[$column_index])) { + if (mb_substr($this->_criteria[$column_index], 0, 1) == '=' + || false !== stripos($this->_criteria[$column_index], 'is') + ) { + $where_clause_columns[$column] = $column; + $where_clause_tables[$table] = $table; + } + } // end if + } // end for + return [ + 'where_clause_tables' => $where_clause_tables, + 'where_clause_columns' => $where_clause_columns, + ]; + } + + /** + * Provides FROM clause for building SQL query + * + * @param array $formColumns List of selected columns in the form + * + * @return string FROM clause + */ + private function _getFromClause(array $formColumns) + { + $from_clause = ''; + if (empty($formColumns)) { + return $from_clause; + } + + // Initialize some variables + $search_tables = $search_columns = []; + + // We only start this if we have fields, otherwise it would be dumb + foreach ($formColumns as $value) { + $parts = explode('.', $value); + if (! empty($parts[0]) && ! empty($parts[1])) { + $table = str_replace('`', '', $parts[0]); + $search_tables[$table] = $table; + $search_columns[] = $table . '.' . str_replace( + '`', + '', + $parts[1] + ); + } + } // end while + + // Create LEFT JOINS out of Relations + $from_clause = $this->_getJoinForFromClause( + $search_tables, + $search_columns + ); + + // In case relations are not defined, just generate the FROM clause + // from the list of tables, however we don't generate any JOIN + if (empty($from_clause)) { + // Create cartesian product + $from_clause = implode( + ', ', + array_map([Util::class, 'backquote'], $search_tables) + ); + } + + return $from_clause; + } + + /** + * Formulates the WHERE clause by JOINing tables + * + * @param array $searchTables Tables involved in the search + * @param array $searchColumns Columns involved in the search + * + * @return string table name + */ + private function _getJoinForFromClause(array $searchTables, array $searchColumns) + { + // $relations[master_table][foreign_table] => clause + $relations = []; + + // Fill $relations with inter table relationship data + foreach ($searchTables as $oneTable) { + $this->_loadRelationsForTable($relations, $oneTable); + } + + // Get tables and columns with valid where clauses + $validWhereClauses = $this->_getWhereClauseTablesAndColumns(); + $whereClauseTables = $validWhereClauses['where_clause_tables']; + $whereClauseColumns = $validWhereClauses['where_clause_columns']; + + // Get master table + $master = $this->_getMasterTable( + $searchTables, + $searchColumns, + $whereClauseColumns, + $whereClauseTables + ); + + // Will include master tables and all tables that can be combined into + // a cluster by their relation + $finalized = []; + if (strlen($master) > 0) { + // Add master tables + $finalized[$master] = ''; + } + // Fill the $finalized array with JOIN clauses for each table + $this->_fillJoinClauses($finalized, $relations, $searchTables); + + // JOIN clause + $join = ''; + + // Tables that can not be combined with the table cluster + // which includes master table + $unfinalized = array_diff($searchTables, array_keys($finalized)); + if (count($unfinalized) > 0) { + // We need to look for intermediary tables to JOIN unfinalized tables + // Heuristic to chose intermediary tables is to look for tables + // having relationships with unfinalized tables + foreach ($unfinalized as $oneTable) { + $references = $this->relation->getChildReferences($this->_db, $oneTable); + foreach ($references as $column => $columnReferences) { + foreach ($columnReferences as $reference) { + // Only from this schema + if ($reference['table_schema'] != $this->_db) { + continue; + } + + $table = $reference['table_name']; + + $this->_loadRelationsForTable($relations, $table); + + // Make copies + $tempFinalized = $finalized; + $tempSearchTables = $searchTables; + $tempSearchTables[] = $table; + + // Try joining with the added table + $this->_fillJoinClauses( + $tempFinalized, + $relations, + $tempSearchTables + ); + + $tempUnfinalized = array_diff( + $tempSearchTables, + array_keys($tempFinalized) + ); + // Take greedy approach. + // If the unfinalized count drops we keep the new table + // and switch temporary varibles with the original ones + if (count($tempUnfinalized) < count($unfinalized)) { + $finalized = $tempFinalized; + $searchTables = $tempSearchTables; + } + + // We are done if no unfinalized tables anymore + if (count($tempUnfinalized) === 0) { + break 3; + } + } + } + } + + $unfinalized = array_diff($searchTables, array_keys($finalized)); + // If there are still unfinalized tables + if (count($unfinalized) > 0) { + // Add these tables as cartesian product before joined tables + $join .= implode( + ', ', + array_map([Util::class, 'backquote'], $unfinalized) + ); + } + } + + $first = true; + // Add joined tables + foreach ($finalized as $table => $clause) { + if ($first) { + if (! empty($join)) { + $join .= ", "; + } + $join .= Util::backquote($table); + $first = false; + } else { + $join .= "\n LEFT JOIN " . Util::backquote( + $table + ) . " ON " . $clause; + } + } + + return $join; + } + + /** + * Loads relations for a given table into the $relations array + * + * @param array $relations array of relations + * @param string $oneTable the table + * + * @return void + */ + private function _loadRelationsForTable(array &$relations, $oneTable) + { + $relations[$oneTable] = []; + + $foreigners = $this->relation->getForeigners($GLOBALS['db'], $oneTable); + foreach ($foreigners as $field => $foreigner) { + // Foreign keys data + if ($field == 'foreign_keys_data') { + foreach ($foreigner as $oneKey) { + $clauses = []; + // There may be multiple column relations + foreach ($oneKey['index_list'] as $index => $oneField) { + $clauses[] + = Util::backquote($oneTable) . "." + . Util::backquote($oneField) . " = " + . Util::backquote($oneKey['ref_table_name']) . "." + . Util::backquote($oneKey['ref_index_list'][$index]); + } + // Combine multiple column relations with AND + $relations[$oneTable][$oneKey['ref_table_name']] + = implode(" AND ", $clauses); + } + } else { // Internal relations + $relations[$oneTable][$foreigner['foreign_table']] + = Util::backquote($oneTable) . "." + . Util::backquote($field) . " = " + . Util::backquote($foreigner['foreign_table']) . "." + . Util::backquote($foreigner['foreign_field']); + } + } + } + + /** + * Fills the $finalized arrays with JOIN clauses for each of the tables + * + * @param array $finalized JOIN clauses for each table + * @param array $relations Relations among tables + * @param array $searchTables Tables involved in the search + * + * @return void + */ + private function _fillJoinClauses(array &$finalized, array $relations, array $searchTables) + { + while (true) { + $added = false; + foreach ($searchTables as $masterTable) { + $foreignData = $relations[$masterTable]; + foreach ($foreignData as $foreignTable => $clause) { + if (! isset($finalized[$masterTable]) + && isset($finalized[$foreignTable]) + ) { + $finalized[$masterTable] = $clause; + $added = true; + } elseif (! isset($finalized[$foreignTable]) + && isset($finalized[$masterTable]) + && in_array($foreignTable, $searchTables) + ) { + $finalized[$foreignTable] = $clause; + $added = true; + } + if ($added) { + // We are done if all tables are in $finalized + if (count($finalized) == count($searchTables)) { + return; + } + } + } + } + // If no new tables were added during this iteration, break; + if (! $added) { + return; + } + } + } + + /** + * Provides the generated SQL query + * + * @param array $formColumns List of selected columns in the form + * + * @return string SQL query + */ + private function _getSQLQuery(array $formColumns) + { + $sql_query = ''; + // get SELECT clause + $sql_query .= $this->_getSelectClause(); + // get FROM clause + $from_clause = $this->_getFromClause($formColumns); + if (! empty($from_clause)) { + $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n"; + } + // get WHERE clause + $sql_query .= $this->_getWhereClause(); + // get ORDER BY clause + $sql_query .= $this->_getOrderByClause(); + return $sql_query; + } + + /** + * Provides the generated QBE form + * + * @return string QBE form + */ + public function getSelectionForm() + { + $html_output = '<form action="db_qbe.php" method="post" id="formQBE" ' + . 'class="lock-page">'; + $html_output .= '<div class="width100">'; + $html_output .= '<fieldset>'; + + if ($GLOBALS['cfgRelation']['savedsearcheswork']) { + $html_output .= $this->_getSavedSearchesField(); + } + + $html_output .= '<div class="responsivetable jsresponsive">'; + $html_output .= '<table class="data" style="width: 100%;">'; + // Get table's <tr> elements + $html_output .= $this->_getColumnNamesRow(); + $html_output .= $this->_getColumnAliasRow(); + $html_output .= $this->_getShowRow(); + $html_output .= $this->_getSortRow(); + $html_output .= $this->_getSortOrder(); + $html_output .= $this->_getCriteriaInputboxRow(); + $html_output .= $this->_getInsDelAndOrCriteriaRows(); + $html_output .= $this->_getModifyColumnsRow(); + $html_output .= '</table>'; + $this->_new_row_count--; + $url_params = []; + $url_params['db'] = $this->_db; + $url_params['criteriaColumnCount'] = $this->_new_column_count; + $url_params['rows'] = $this->_new_row_count; + $html_output .= Url::getHiddenInputs($url_params); + $html_output .= '</div>'; + $html_output .= '</fieldset>'; + $html_output .= '</div>'; + // get footers + $html_output .= $this->_getTableFooters(); + // get tables select list + $html_output .= $this->_getTablesList(); + $html_output .= '</form>'; + $html_output .= '<form action="db_qbe.php" method="post" class="lock-page">'; + $html_output .= Url::getHiddenInputs(['db' => $this->_db]); + // get SQL query + $html_output .= '<div class="floatleft desktop50">'; + $html_output .= '<fieldset id="tblQbe">'; + $html_output .= '<legend>' + . sprintf( + __('SQL query on database <b>%s</b>:'), + Util::getDbLink($this->_db) + ); + $html_output .= '</legend>'; + $text_dir = 'ltr'; + $html_output .= '<textarea cols="80" name="sql_query" id="textSqlquery"' + . ' rows="' . (count($this->_criteriaTables) > 30 ? '15' : '7') . '"' + . ' dir="' . $text_dir . '">'; + + if (empty($this->_formColumns)) { + $this->_formColumns = []; + } + $html_output .= $this->_getSQLQuery($this->_formColumns); + + $html_output .= '</textarea>'; + $html_output .= '</fieldset>'; + // displays form's footers + $html_output .= '<fieldset class="tblFooters" id="tblQbeFooters">'; + $html_output .= '<input type="hidden" name="submit_sql" value="1">'; + $html_output .= '<input class="btn btn-primary" type="submit" value="' . __('Submit Query') . '">'; + $html_output .= '</fieldset>'; + $html_output .= '</div>'; + $html_output .= '</form>'; + return $html_output; + } + + /** + * Get fields to display + * + * @return string + */ + private function _getSavedSearchesField() + { + $html_output = __('Saved bookmarked search:'); + $html_output .= ' <select name="searchId" id="searchId">'; + $html_output .= '<option value="">' . __('New bookmark') . '</option>'; + + $currentSearch = $this->_getCurrentSearch(); + $currentSearchId = null; + $currentSearchName = null; + if (null != $currentSearch) { + $currentSearchId = $currentSearch->getId(); + $currentSearchName = $currentSearch->getSearchName(); + } + + foreach ($this->_savedSearchList as $id => $name) { + $html_output .= '<option value="' . htmlspecialchars($id) + . '" ' . ( + $id == $currentSearchId + ? 'selected="selected" ' + : '' + ) + . '>' + . htmlspecialchars($name) + . '</option>'; + } + $html_output .= '</select>'; + $html_output .= '<input type="text" name="searchName" id="searchName" ' + . 'value="' . htmlspecialchars((string) $currentSearchName) . '">'; + $html_output .= '<input type="hidden" name="action" id="action" value="">'; + $html_output .= '<input class="btn btn-secondary" type="submit" name="saveSearch" id="saveSearch" ' + . 'value="' . __('Create bookmark') . '">'; + if (null !== $currentSearchId) { + $html_output .= '<input class="btn btn-secondary" type="submit" name="updateSearch" ' + . 'id="updateSearch" value="' . __('Update bookmark') . '">'; + $html_output .= '<input class="btn btn-secondary" type="submit" name="deleteSearch" ' + . 'id="deleteSearch" value="' . __('Delete bookmark') . '">'; + } + + return $html_output; + } + + /** + * Initialize _criteria_column_count + * + * @return int Previous number of columns + */ + private function _initializeCriteriasCount(): int + { + // sets column count + $criteriaColumnCount = Core::ifSetOr( + $_POST['criteriaColumnCount'], + 3, + 'numeric' + ); + $criteriaColumnAdd = Core::ifSetOr( + $_POST['criteriaColumnAdd'], + 0, + 'numeric' + ); + $this->_criteria_column_count = max( + $criteriaColumnCount + $criteriaColumnAdd, + 0 + ); + + // sets row count + $rows = Core::ifSetOr($_POST['rows'], 0, 'numeric'); + $criteriaRowAdd = Core::ifSetOr($_POST['criteriaRowAdd'], 0, 'numeric'); + $this->_criteria_row_count = min( + 100, + max($rows + $criteriaRowAdd, 0) + ); + + return (int) $criteriaColumnCount; + } + + /** + * Get best + * + * @param array $search_tables Tables involved in the search + * @param array|null $where_clause_columns Columns with where clause + * @param array|null $unique_columns Unique columns + * @param array|null $index_columns Indexed columns + * + * @return array + */ + private function _getLeftJoinColumnCandidatesBest( + array $search_tables, + ?array $where_clause_columns, + ?array $unique_columns, + ?array $index_columns + ) { + // now we want to find the best. + if (isset($unique_columns) && count($unique_columns) > 0) { + $candidate_columns = $unique_columns; + $needsort = 1; + return [ + $candidate_columns, + $needsort, + ]; + } elseif (isset($index_columns) && count($index_columns) > 0) { + $candidate_columns = $index_columns; + $needsort = 1; + return [ + $candidate_columns, + $needsort, + ]; + } elseif (isset($where_clause_columns) && count($where_clause_columns) > 0) { + $candidate_columns = $where_clause_columns; + $needsort = 0; + return [ + $candidate_columns, + $needsort, + ]; + } + + $candidate_columns = $search_tables; + $needsort = 0; + return [ + $candidate_columns, + $needsort, + ]; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Database/Search.php b/srcs/phpmyadmin/libraries/classes/Database/Search.php new file mode 100644 index 0000000..3f1b250 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Database/Search.php @@ -0,0 +1,347 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Handles Database Search + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Database; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Template; +use PhpMyAdmin\Util; + +/** + * Class to handle database search + * + * @package PhpMyAdmin + */ +class Search +{ + /** + * Database name + * + * @access private + * @var string + */ + private $db; + + /** + * Table Names + * + * @access private + * @var array + */ + private $tablesNamesOnly; + + /** + * Type of search + * + * @access private + * @var array + */ + private $searchTypes; + + /** + * Already set search type + * + * @access private + * @var integer + */ + private $criteriaSearchType; + + /** + * Already set search type's description + * + * @access private + * @var string + */ + private $searchTypeDescription; + + /** + * Search string/regexp + * + * @access private + * @var string + */ + private $criteriaSearchString; + + /** + * Criteria Tables to search in + * + * @access private + * @var array + */ + private $criteriaTables; + + /** + * Restrict the search to this column + * + * @access private + * @var string + */ + private $criteriaColumnName; + + /** + * @var DatabaseInterface + */ + private $dbi; + + /** + * @var Template + */ + public $template; + + /** + * Public Constructor + * + * @param DatabaseInterface $dbi DatabaseInterface object + * @param string $db Database name + * @param Template $template Template object + */ + public function __construct(DatabaseInterface $dbi, $db, Template $template) + { + $this->db = $db; + $this->dbi = $dbi; + $this->searchTypes = [ + '1' => __('at least one of the words'), + '2' => __('all of the words'), + '3' => __('the exact phrase as substring'), + '4' => __('the exact phrase as whole field'), + '5' => __('as regular expression'), + ]; + $this->template = $template; + // Sets criteria parameters + $this->setSearchParams(); + } + + /** + * Sets search parameters + * + * @return void + */ + private function setSearchParams() + { + $this->tablesNamesOnly = $this->dbi->getTables($this->db); + + if (empty($_POST['criteriaSearchType']) + || ! is_string($_POST['criteriaSearchType']) + || ! array_key_exists( + $_POST['criteriaSearchType'], + $this->searchTypes + ) + ) { + $this->criteriaSearchType = 1; + unset($_POST['submit_search']); + } else { + $this->criteriaSearchType = (int) $_POST['criteriaSearchType']; + $this->searchTypeDescription + = $this->searchTypes[$_POST['criteriaSearchType']]; + } + + if (empty($_POST['criteriaSearchString']) + || ! is_string($_POST['criteriaSearchString']) + ) { + $this->criteriaSearchString = ''; + unset($_POST['submit_search']); + } else { + $this->criteriaSearchString = $_POST['criteriaSearchString']; + } + + $this->criteriaTables = []; + if (empty($_POST['criteriaTables']) + || ! is_array($_POST['criteriaTables']) + ) { + unset($_POST['submit_search']); + } else { + $this->criteriaTables = array_intersect( + $_POST['criteriaTables'], + $this->tablesNamesOnly + ); + } + + if (empty($_POST['criteriaColumnName']) + || ! is_string($_POST['criteriaColumnName']) + ) { + unset($this->criteriaColumnName); + } else { + $this->criteriaColumnName = $this->dbi->escapeString( + $_POST['criteriaColumnName'] + ); + } + } + + /** + * Builds the SQL search query + * + * @param string $table The table name + * + * @return array 3 SQL queries (for count, display and delete results) + * + * @todo can we make use of fulltextsearch IN BOOLEAN MODE for this? + * PMA_backquote + * DatabaseInterface::freeResult + * DatabaseInterface::fetchAssoc + * $GLOBALS['db'] + * explode + * count + * strlen + */ + private function getSearchSqls($table) + { + // Statement types + $sqlstr_select = 'SELECT'; + $sqlstr_delete = 'DELETE'; + // Table to use + $sqlstr_from = ' FROM ' + . Util::backquote($GLOBALS['db']) . '.' + . Util::backquote($table); + // Gets where clause for the query + $where_clause = $this->getWhereClause($table); + // Builds complete queries + $sql = []; + $sql['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from + . $where_clause; + // here, I think we need to still use the COUNT clause, even for + // VIEWs, anyway we have a WHERE clause that should limit results + $sql['select_count'] = $sqlstr_select . ' COUNT(*) AS `count`' + . $sqlstr_from . $where_clause; + $sql['delete'] = $sqlstr_delete . $sqlstr_from . $where_clause; + + return $sql; + } + + /** + * Provides where clause for building SQL query + * + * @param string $table The table name + * + * @return string The generated where clause + */ + private function getWhereClause($table) + { + // Columns to select + $allColumns = $this->dbi->getColumns($GLOBALS['db'], $table); + $likeClauses = []; + // Based on search type, decide like/regex & '%'/'' + $like_or_regex = (($this->criteriaSearchType == 5) ? 'REGEXP' : 'LIKE'); + $automatic_wildcard = (($this->criteriaSearchType < 4) ? '%' : ''); + // For "as regular expression" (search option 5), LIKE won't be used + // Usage example: If user is searching for a literal $ in a regexp search, + // he should enter \$ as the value. + $criteriaSearchStringEscaped = $this->dbi->escapeString( + $this->criteriaSearchString + ); + // Extract search words or pattern + $search_words = (($this->criteriaSearchType > 2) + ? [$criteriaSearchStringEscaped] + : explode(' ', $criteriaSearchStringEscaped)); + + foreach ($search_words as $search_word) { + // Eliminates empty values + if (strlen($search_word) === 0) { + continue; + } + $likeClausesPerColumn = []; + // for each column in the table + foreach ($allColumns as $column) { + if (! isset($this->criteriaColumnName) + || strlen($this->criteriaColumnName) === 0 + || $column['Field'] == $this->criteriaColumnName + ) { + $column = 'CONVERT(' . Util::backquote($column['Field']) + . ' USING utf8)'; + $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' ' + . "'" + . $automatic_wildcard . $search_word . $automatic_wildcard + . "'"; + } + } // end for + if (count($likeClausesPerColumn) > 0) { + $likeClauses[] = implode(' OR ', $likeClausesPerColumn); + } + } // end for + // Use 'OR' if 'at least one word' is to be searched, else use 'AND' + $implode_str = ($this->criteriaSearchType == 1 ? ' OR ' : ' AND '); + if (empty($likeClauses)) { + // this could happen when the "inside column" does not exist + // in any selected tables + $where_clause = ' WHERE FALSE'; + } else { + $where_clause = ' WHERE (' + . implode(') ' . $implode_str . ' (', $likeClauses) + . ')'; + } + return $where_clause; + } + + /** + * Displays database search results + * + * @return string HTML for search results + */ + public function getSearchResults() + { + $resultTotal = 0; + $rows = []; + // For each table selected as search criteria + foreach ($this->criteriaTables as $eachTable) { + // Gets the SQL statements + $newSearchSqls = $this->getSearchSqls($eachTable); + // Executes the "COUNT" statement + $resultCount = intval($this->dbi->fetchValue( + $newSearchSqls['select_count'] + )); + $resultTotal += $resultCount; + // Gets the result row's HTML for a table + $rows[] = [ + 'table' => htmlspecialchars($eachTable), + 'new_search_sqls' => $newSearchSqls, + 'result_count' => $resultCount, + ]; + } + + return $this->template->render('database/search/results', [ + 'db' => $this->db, + 'rows' => $rows, + 'result_total' => $resultTotal, + 'criteria_tables' => $this->criteriaTables, + 'criteria_search_string' => htmlspecialchars($this->criteriaSearchString), + 'search_type_description' => $this->searchTypeDescription, + ]); + } + + /** + * Provides the main search form's html + * + * @return string HTML for selection form + */ + public function getMainHtml() + { + $choices = [ + '1' => $this->searchTypes[1] . ' ' + . Util::showHint( + __('Words are separated by a space character (" ").') + ), + '2' => $this->searchTypes[2] . ' ' + . Util::showHint( + __('Words are separated by a space character (" ").') + ), + '3' => $this->searchTypes[3], + '4' => $this->searchTypes[4], + '5' => $this->searchTypes[5] . ' ' . Util::showMySQLDocu('Regexp'), + ]; + return $this->template->render('database/search/main', [ + 'db' => $this->db, + 'choices' => $choices, + 'criteria_search_string' => $this->criteriaSearchString, + 'criteria_search_type' => $this->criteriaSearchType, + 'criteria_tables' => $this->criteriaTables, + 'tables_names_only' => $this->tablesNamesOnly, + 'criteria_column_name' => isset($this->criteriaColumnName) + ? $this->criteriaColumnName : null, + ]); + } +} |