Show sourcecode
The following files exists in this folder. Click to view.
account.php
create_quiz.js
create_quiz.php
fetch_table.php
frontpage.php
header.php
login.php
quiz.php
quiz_answer_finished.php
quiz_creation_finished.php
signup.php
style.css
fetch_table.php
140 lines UTF-8 Windows (CRLF)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140
<?php
/**
* --------------------------------
* Skript som hämtar all data från en table given av $_GET
* Avsedd endast för admin syfte då den hämtar allt
* --------------------------------
*/
use function PHPSTORM_META\sql_injection_subst;
session_start();
/**
* Fetches all data from $FETCHTABLE table
*/
// Signalera att vi kommer retunera JSON
header('Content-Type: application/json');
include('../../incl/dbconnection.php');
/**
* @var PDO $dbconn
*/
$table = $_GET["table"];
// Kolla om requester är admin
if (!isset($_SESSION["isAdmin"]) || $_SESSION["isAdmin"] == 0) {
http_response_code(403);
echo json_encode(['error' => 'Unauthorized']);
exit;
}
// Validera tabellnamn
if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table)) {
http_response_code(400);
echo json_encode(['error' => 'Ogiltigt tabellnamn']);
exit;
}
$ID_TO_TEXT_MAP = [
'question_id' => 'question_text',
'quiz_id' => 'quiz_name',
'option_id' => 'option_text',
'user_id' => 'username'
];
// print_r($ID_TO_TEXT_MAP);
try {
// Hitta fks
$sqlFk = "SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'min_databas'
AND TABLE_NAME = ?
AND REFERENCED_TABLE_NAME IS NOT NULL";
$sqlSelectPart1 = "SELECT "; // Select all the columns
$sqlSelectPart2 = " FROM $table"; // JOIN ON
// Hämta primärnyckeln för tabellen
$sqlPk = "SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = :table
AND COLUMN_KEY = 'PRI'
LIMIT 1";
$pkStmt = $dbconn->prepare($sqlPk);
$pkStmt->execute([':table' => $table]);
$primaryKey = $pkStmt->fetch(PDO::FETCH_COLUMN);
foreach ($dbconn->query("SELECT * FROM $table LIMIT 1")->fetch(PDO::FETCH_ASSOC) as $col => $val) {
if (array_key_exists($col, $ID_TO_TEXT_MAP) && $col != $primaryKey) {
continue;
};
$sqlSelectPart1 .= $table . "." . "$col, ";
}
$debugSQL = "SELECT
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND COLUMN_NAME = 'quiz_result_id'
AND TABLE_NAME IN ('quiz_answers', 'quiz_quiz_results', 'quiz_options', 'quiz_questions')";
// print_r($dbconn->query($debugSQL)->fetchAll());
$fkStmt = $dbconn->prepare($sqlFk);
$fkStmt->execute([$table]);
foreach ($fkStmt->fetchAll(PDO::FETCH_ASSOC) as $key) {
// Check if we're already selecting key
if (!str_contains($sqlSelectPart1, $key["REFERENCED_COLUMN_NAME"])) {
// if column is mapped, select mapped column, else select base column
if (array_key_exists($key["REFERENCED_COLUMN_NAME"], $ID_TO_TEXT_MAP)) {
$sqlSelectPart1 .= $key["REFERENCED_TABLE_NAME"] . "." . $ID_TO_TEXT_MAP[$key["REFERENCED_COLUMN_NAME"]] . ", ";
} else {
$sqlSelectPart1 .= $key["REFERENCED_TABLE_NAME"] . "." . $key["REFERENCED_COLUMN_NAME"] . ", ";
}
}
$sqlSelectPart2 .= " JOIN " . $key["REFERENCED_TABLE_NAME"]
. " ON " . $key["TABLE_NAME"] . "." . $key["COLUMN_NAME"]
. "=" . $key["REFERENCED_TABLE_NAME"] . "." . $key["REFERENCED_COLUMN_NAME"];
// Specialfall, orkar inte fixa perfekt lösning
if ($key["REFERENCED_COLUMN_NAME"] == 'quiz_result_id' && $table == "quiz_answers") {
$sqlSelectPart1 .= "quiz_users.username as user, quiz_quizzes.quiz_name, ";
// $sqlSelectPart2 .= " JOIN quiz_quiz_results ON quiz_answers.quiz_result_id = quiz_quiz_results.quiz_result_id";
$sqlSelectPart2 .= " JOIN quiz_users ON quiz_quiz_results.user_id=quiz_users.user_id";
$sqlSelectPart2 .= " JOIN quiz_quizzes ON quiz_quiz_results.quiz_id=quiz_quizzes.quiz_id";
}
// echo json_encode($key);
}
$sqlSelectPart1 = rtrim($sqlSelectPart1, ", ");
$sqlSelect = $sqlSelectPart1 . $sqlSelectPart2;
// echo json_encode($sqlSelect);
// Kör SQL kod
$selectStmt = $dbconn->prepare($sqlSelect);
$selectStmt->execute();
// Hämta datan från $table, spara i associative array format
$results = $selectStmt->fetchAll(PDO::FETCH_ASSOC);
// Gör om array till json kod
echo json_encode($results);
http_response_code(200);
} catch (PDOException $e) {
http_response_code(500);
echo json_encode(['error' => 'Database query failed: ' . $e->getMessage()]);
}