Show sourcecode
The following files exists in this folder. Click to view.
public_html/smartkortet/database/
createdb.php
353 lines UTF-8 Windows (CRLF)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353
<?php
declare(strict_types=1);
require_once __DIR__ . '/../includes/functions.php';
startAppSession();
require_once __DIR__ . '/../includes/auth.php';
$admin = requireAdminAuth();
$config = require __DIR__ . '/../config/app.php';
$db = $config['db'] ?? [];
$host = (string) ($db['host'] ?? '127.0.0.1');
$port = (int) ($db['port'] ?? 3306);
$name = (string) ($db['name'] ?? 'matkortet');
$user = (string) ($db['user'] ?? 'root');
$pass = (string) ($db['pass'] ?? '');
$charset = (string) ($db['charset'] ?? 'utf8mb4');
$schemaPath = __DIR__ . '/schema.sql';
$schemaExists = is_file($schemaPath);
if (empty($_SESSION['createdb_csrf'])) {
$_SESSION['createdb_csrf'] = bin2hex(random_bytes(24));
}
$messageType = 'info';
$message = 'Välj en åtgärd nedan för att köra schema eller egen SQL.';
$results = [];
try {
$dsn = sprintf('mysql:host=%s;port=%d;charset=%s', $host, $port, $charset);
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
} catch (Throwable $e) {
http_response_code(500);
$messageType = 'error';
$message = 'Kunde inte ansluta till databasen: ' . $e->getMessage();
$pdo = null;
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && $pdo instanceof PDO) {
$postedToken = (string) ($_POST['csrf_token'] ?? '');
if (!hash_equals($_SESSION['createdb_csrf'], $postedToken)) {
http_response_code(419);
$messageType = 'error';
$message = 'Ogiltig CSRF-token. Ladda om sidan och försök igen.';
} else {
$action = (string) ($_POST['action'] ?? '');
if ($action === 'run_schema') {
if (!$schemaExists) {
$messageType = 'error';
$message = 'schema.sql hittades inte.';
} else {
$schemaSql = file_get_contents($schemaPath);
if ($schemaSql === false) {
$messageType = 'error';
$message = 'Kunde inte läsa schema.sql.';
} else {
$results = executeSqlBatch($pdo, $schemaSql);
$messageType = hasErrors($results) ? 'error' : 'success';
$message = hasErrors($results)
? 'schema.sql kördes med fel i en eller flera statements.'
: 'schema.sql kördes klart utan fel.';
}
}
}
if ($action === 'run_custom') {
$customSql = trim((string) ($_POST['custom_sql'] ?? ''));
if ($customSql === '') {
$messageType = 'error';
$message = 'Skriv SQL i konsolen först.';
} else {
$results = executeSqlBatch($pdo, $customSql);
$messageType = hasErrors($results) ? 'error' : 'success';
$message = hasErrors($results)
? 'SQL kördes men minst ett statement gav fel.'
: 'SQL kördes klart.';
}
}
}
}
$customSqlValue = (string) ($_POST['custom_sql'] ?? '');
function executeSqlBatch(PDO $pdo, string $sql): array
{
$statements = splitSqlStatements($sql);
$results = [];
foreach ($statements as $index => $statement) {
$item = [
'index' => $index + 1,
'sql' => $statement,
'status' => 'ok',
'type' => 'exec',
'rows_affected' => 0,
'rows' => [],
'error' => '',
];
try {
$stmt = $pdo->query($statement);
if ($stmt instanceof PDOStatement && $stmt->columnCount() > 0) {
$item['type'] = 'select';
$item['rows'] = $stmt->fetchAll();
$item['rows_affected'] = count($item['rows']);
} else {
$item['rows_affected'] = $stmt instanceof PDOStatement ? $stmt->rowCount() : 0;
}
} catch (Throwable $e) {
$item['status'] = 'error';
$item['error'] = $e->getMessage();
}
$results[] = $item;
}
return $results;
}
function hasErrors(array $results): bool
{
foreach ($results as $result) {
if (($result['status'] ?? '') === 'error') {
return true;
}
}
return false;
}
function splitSqlStatements(string $sql): array
{
$sql = preg_replace('/^\s*--.*$/m', '', $sql);
if ($sql === null) {
return [];
}
$statements = [];
$buffer = '';
$inSingle = false;
$inDouble = false;
$len = strlen($sql);
for ($i = 0; $i < $len; $i++) {
$char = $sql[$i];
$prev = $i > 0 ? $sql[$i - 1] : '';
if ($char === "'" && !$inDouble && $prev !== '\\') {
$inSingle = !$inSingle;
} elseif ($char === '"' && !$inSingle && $prev !== '\\') {
$inDouble = !$inDouble;
}
if ($char === ';' && !$inSingle && !$inDouble) {
$trimmed = trim($buffer);
if ($trimmed !== '') {
$statements[] = $trimmed;
}
$buffer = '';
continue;
}
$buffer .= $char;
}
$trimmed = trim($buffer);
if ($trimmed !== '') {
$statements[] = $trimmed;
}
return $statements;
}
?>
<!doctype html>
<html lang="sv">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Matkortet DB Console</title>
<style>
:root {
--bg: #f1f5f9;
--card: #ffffff;
--text: #0f172a;
--muted: #475569;
--border: #cbd5e1;
--accent: #0f766e;
--danger: #b91c1c;
--ok: #166534;
}
* { box-sizing: border-box; }
body {
margin: 0;
font-family: system-ui, -apple-system, Segoe UI, Roboto, sans-serif;
background: var(--bg);
color: var(--text);
}
.wrapper {
max-width: 1100px;
margin: 0 auto;
padding: 20px;
}
.card {
background: var(--card);
border: 1px solid var(--border);
border-radius: 14px;
padding: 16px;
margin-bottom: 14px;
}
h1 { margin: 0 0 8px 0; }
.sub { color: var(--muted); margin: 0; }
.notice { padding: 10px 12px; border-radius: 10px; margin-top: 12px; font-weight: 600; }
.notice.info { background: #e2e8f0; color: #0f172a; }
.notice.success { background: #dcfce7; color: var(--ok); }
.notice.error { background: #fee2e2; color: var(--danger); }
textarea {
width: 100%;
min-height: 220px;
font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, monospace;
font-size: 14px;
padding: 12px;
border: 1px solid var(--border);
border-radius: 10px;
}
button {
border: none;
background: var(--accent);
color: #fff;
font-weight: 700;
padding: 10px 14px;
border-radius: 10px;
cursor: pointer;
}
button.secondary {
background: #334155;
}
.control-row {
margin-top: 10px;
display: flex;
gap: 10px;
flex-wrap: wrap;
}
.statement {
border: 1px solid var(--border);
border-radius: 10px;
padding: 12px;
margin-bottom: 10px;
}
.statement pre {
margin: 8px 0;
white-space: pre-wrap;
word-break: break-word;
font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, monospace;
font-size: 13px;
}
.small { color: var(--muted); font-size: 13px; }
table {
width: 100%;
border-collapse: collapse;
margin-top: 10px;
}
th, td {
text-align: left;
border-bottom: 1px solid var(--border);
padding: 7px;
font-size: 13px;
}
</style>
</head>
<body>
<div class="wrapper">
<section class="card">
<h1>Matkortet DB Console</h1>
<p class="sub">Ansluten mot <strong><?= htmlspecialchars($host, ENT_QUOTES, 'UTF-8') ?>:<?= $port ?></strong> med databasnamn <strong><?= htmlspecialchars($name, ENT_QUOTES, 'UTF-8') ?></strong>.</p>
<p class="sub">Använd endast lokalt. Kör aldrig denna sida i publik produktion.</p>
<div class="notice <?= htmlspecialchars($messageType, ENT_QUOTES, 'UTF-8') ?>"><?= htmlspecialchars($message, ENT_QUOTES, 'UTF-8') ?></div>
</section>
<section class="card">
<h2>Kör schema.sql</h2>
<p class="small">Fil: <?= htmlspecialchars($schemaPath, ENT_QUOTES, 'UTF-8') ?> (<?= $schemaExists ? 'hittad' : 'saknas' ?>)</p>
<form method="post">
<input type="hidden" name="csrf_token" value="<?= htmlspecialchars($_SESSION['createdb_csrf'], ENT_QUOTES, 'UTF-8') ?>">
<input type="hidden" name="action" value="run_schema">
<button type="submit" <?= !$schemaExists || !($pdo instanceof PDO) ? 'disabled' : '' ?>>Kör schema.sql</button>
</form>
</section>
<section class="card">
<h2>SQL Console</h2>
<form method="post">
<input type="hidden" name="csrf_token" value="<?= htmlspecialchars($_SESSION['createdb_csrf'], ENT_QUOTES, 'UTF-8') ?>">
<input type="hidden" name="action" value="run_custom">
<textarea name="custom_sql" placeholder="Skriv SQL här... (flera statements stödjs)"><?= htmlspecialchars($customSqlValue, ENT_QUOTES, 'UTF-8') ?></textarea>
<div class="control-row">
<button type="submit" <?= !($pdo instanceof PDO) ? 'disabled' : '' ?>>Kör SQL</button>
<button class="secondary" type="button" onclick="this.form.custom_sql.value=''">Rensa</button>
</div>
</form>
</section>
<?php if ($results): ?>
<section class="card">
<h2>Resultat</h2>
<?php foreach ($results as $result): ?>
<div class="statement">
<strong>Statement #<?= (int) $result['index'] ?></strong>
<div class="small">Status: <?= htmlspecialchars((string) $result['status'], ENT_QUOTES, 'UTF-8') ?></div>
<pre><?= htmlspecialchars((string) $result['sql'], ENT_QUOTES, 'UTF-8') ?></pre>
<?php if ($result['status'] === 'error'): ?>
<div class="notice error"><?= htmlspecialchars((string) $result['error'], ENT_QUOTES, 'UTF-8') ?></div>
<?php else: ?>
<div class="small">Påverkade/rader returnerade: <?= (int) $result['rows_affected'] ?></div>
<?php if ($result['type'] === 'select' && !empty($result['rows'])): ?>
<table>
<thead>
<tr>
<?php foreach (array_keys($result['rows'][0]) as $column): ?>
<th><?= htmlspecialchars((string) $column, ENT_QUOTES, 'UTF-8') ?></th>
<?php endforeach; ?>
</tr>
</thead>
<tbody>
<?php foreach ($result['rows'] as $row): ?>
<tr>
<?php foreach ($row as $value): ?>
<td><?= htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8') ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php endif; ?>
<?php endif; ?>
</div>
<?php endforeach; ?>
</section>
<?php endif; ?>
</div>
</body>
</html>