Portions created by the Initial Developer are Copyright (C) 2008-2023 the Initial Developer. All Rights Reserved. Contributor(s): Mark J Crane */ //includes files require_once dirname(__DIR__, 2) . "/resources/require.php"; require_once "resources/check_auth.php"; //check permissions if (permission_exists('sql_query')) { //access granted } else { echo "access denied"; exit; } //show blank iframe initially if (empty($_REQUEST)) { echo ' '; exit; } //add multi-lingual support $language = new text; $text = $language->get(); //pdo database connection if (strlen($_REQUEST['id']) > 0) { require_once "sql_query_pdo.php"; } //validate the token $token = new token; if (!$token->validate('/app/sql_query/sql_query.php')) { message::add($text['message-invalid_token'],'negative'); echo "invalid token\n"; exit; } //get allowed table names switch ($db_type) { case 'sqlite': $sql = "select name from sqlite_master where type='table' order by name;"; break; case 'pgsql': $sql = "select table_name as name from information_schema.tables where table_schema='public' and (table_type='BASE TABLE' or table_type='VIEW') order by table_type, table_name"; break; case 'mysql': $sql = "show tables"; break; } $database = new database; $rows = $database->select($sql, null, 'all'); if (is_array($rows) && @sizeof($rows) != 0) { foreach ($rows as $row) { $tables[] = $row['name']; } } unset($sql, $rows, $row); //show the content if (!empty($_REQUEST)) { //get the sql type, command and name $sql_type = trim($_REQUEST["sql_type"]); $sql_cmd = trim($_REQUEST["command"]); $table_name = trim($_REQUEST["table_name"]); $header = "\n"; $header .= "\n"; $header .= ""; $header .= "\n"; $header .= "\n"; $footer = "\n"; $footer .= "\n"; if (empty($sql_type)) { echo $header; $c = 0; $row_style["0"] = "row_style0"; $row_style["1"] = "row_style1"; //determine queries to run and show if ($sql_cmd != '') { $sql_array = array_filter(explode(";", $sql_cmd)); } if ($table_name != '' && in_array($table_name, $tables)) { $sql_array[] = "select * from ".$table_name; } $show_query = (sizeof($sql_array) > 1) ? true : false; if (is_array($sql_array)) foreach($sql_array as $sql_index => $sql) { $sql = trim($sql); if (sizeof($sql_array) > 1 || $show_query) { if ($sql_index > 0) { echo "

"; } echo "".escape($sql).";
"; } //connect to the database and run the sql query $database = new database; $result = $database->execute($sql, null, 'all'); $message = $database->message; //show the number of records in the result if (!empty($result) && is_array($result)) { echo "".$text['label-records'].": ".count($result).""; echo "

\n"; } else { echo "".$text['label-error'].""; echo "

\n"; echo $message['message']."
\n"; if (!empty($message['error'])) { foreach ($message['error'] as $error) { echo "

".$error."

\n"; } } } //show the results from the SQL query echo "\n"; $x = 0; if (is_array($result[0])) { echo "\n"; echo " \n"; foreach ($result[0] as $key => $value) { echo "\n"; $column_array[$x++] = $key; } echo " \n"; echo "\n"; } $x = 1; if (is_array($result)) { echo "\n"; foreach ($result as &$row) { if ($x++ > 1000) { break; } echo "\n"; if (is_array($column_array)) { foreach ($column_array as $column_index => $column) { echo "\n"; } } echo "\n"; $c = ($c == 0) ? 1 : 0; } echo "\n"; } echo "

".escape($key)."
".escape($row[$column])."

\n"; echo "
\n"; unset($result, $column_array); } echo $footer; } if ($sql_type == "inserts") { echo $header; $sql = trim($sql ?? ''); //get the table data $sql = (strlen($sql_cmd) == 0 && in_array($table_name, $tables)) ? "select * from ".$table_name : $sql_cmd; if (strlen($sql) > 0) { $database = new database; $result = $database->execute($sql); $message = $database->message; if ($message['message'] != 'OK' || $message['code'] != 200) { echo "".$text['label-error'].""; echo "

\n"; echo $message['message'].' ['.$message['code']."]
\n"; if (is_array($message['error']) && @sizeof($message['error']) != 0) { foreach ($message['error'] as $error) { echo "

".$error."

\n"; } } exit; } $x = 0; if (is_array($result[0])) { foreach ($result[0] as $key => $value) { $column_array[$x++] = $key; } } $column_array_count = count($column_array); if (is_array($result)) { foreach ($result as $index => &$row) { echo "

\n";
							echo "insert into ".$table_name." (";
							if (is_array($column_array)) {
								foreach ($column_array as $column) {
									if ($column != "menuid" && $column != "menuparentid") {
										$columns[] = $column;
									}
								}
							}
							if (is_array($columns) && sizeof($columns) > 0) {
								echo implode(', ', $columns);
							}
							echo ") values (";
							if (is_array($column_array)) {
								foreach ($column_array as $column) {
									if ($column != "menuid" && $column != "menuparentid") {
										$values[] = $row[$column] != '' ? "'".escape($row[$column])."'" : 'null';
									}
								}
							}
							if (is_array($values) && sizeof($values) > 0) {
								echo implode(', ', $values);
							}
							echo ");\n";
							echo "

\n"; unset($columns, $values); } } } echo $footer; } if ($sql_type == "csv") { //set the headers header('Content-type: application/octet-binary'); if (strlen($sql_cmd) > 0) { header('Content-Disposition: attachment; filename=data.csv'); } else if (in_array($table_name, $tables)) { header('Content-Disposition: attachment; filename='.$table_name.'.csv'); } //get the table data if (strlen($sql_cmd) > 0) { $sql = $sql_cmd; } else if (in_array($table_name, $tables)) { $sql = "select * from ".$table_name; } if (strlen($sql) > 0) { $database = new database; $result = $database->execute($sql); $message = $database->message; if ($message['message'] != 'OK' || $message['code'] != 200) { echo "".$text['label-error'].""; echo "

\n"; echo $message['message'].' ['.$message['code']."]
\n"; if (is_array($message['error']) && @sizeof($message['error']) != 0) { foreach ($message['error'] as $error) { echo "

".$error."

\n"; } } exit; } //build the column array $x = 0; if (is_array($result[0])) { foreach ($result[0] as $key => $value) { $column_array[$x] = $key; $x++; } } //column names echo '"'.implode('","', $column_array).'"'."\r\n"; //column values if (is_array($result)) { foreach ($result as &$row) { $x = 1; foreach ($column_array as $column) { echo '"'.$row[$column].'"'.($x++ < count($column_array) ? ',' : null); } echo "\n"; } } } } } ?>