GateArch

Overview

GateArch is a full-stack web application providing a student information portal backed by a PHP 8 and MySQL server. Students log in with hashed credentials, view their enrolled courses, check grades, and access course details from a personalised dashboard. An admin panel provides CRUD operations for managing students, courses, and enrollments. The application is structured around a PHP routing layer served through Apache, with PDO prepared statements used for all database interactions to prevent SQL injection. Sessions handle authentication state, and role-based access control at the PHP level restricts admin routes to users with the admin role. The frontend is a responsive HTML/CSS/JavaScript interface that uses form-based interactions with server-side processing, no single-page framework, demonstrating a solid grasp of traditional multi-page application architecture.

Architecture

Tech Stack

• PHP 8, Server-side routing, session management, role-based access control, form processing
• MySQL, Relational database with four tables: users, courses, enrollments, grades; foreign key constraints enforce referential integrity
• PDO (PHP Data Objects), All database queries use prepared statements with bound parameters, protecting all bound values from SQL injection (dynamic identifiers still need allow-listing)
• Apache, HTTP server; .htaccess used to restrict direct access to PHP include files
• PHP Sessions, session_start() on every protected page; session data stores user ID and role; session is destroyed on logout
• HTML5 / CSS3 / JavaScript, Responsive frontend; CSS Grid/Flexbox for dashboard layout; JavaScript for client-side form validation before POST submission

Build Process

CREATE TABLE users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(80)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('student','admin') NOT NULL DEFAULT 'student'
);

CREATE TABLE courses (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  credits     TINYINT NOT NULL,
  description TEXT
);

CREATE TABLE enrollments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  UNIQUE KEY uq_enrollment (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES users(id)   ON DELETE CASCADE,
  FOREIGN KEY (course_id)  REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE grades (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  enrollment_id INT NOT NULL,
  grade         DECIMAL(5,2),
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE
);

// auth.php - login processing
session_start();
$stmt = $pdo->prepare('SELECT id, password_hash, role FROM users WHERE username = ?');
$stmt->execute([$_POST['username']]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user && password_verify($_POST['password'], $user['password_hash'])) {
    $_SESSION['user_id'] = $user['id'];
    $_SESSION['role']    = $user['role'];
    header('Location: ' . ($user['role'] === 'admin' ? 'admin.php' : 'dashboard.php'));
    exit;
}
// fallthrough: show login error

// dashboard.php - fetch enrolled courses with grades
$stmt = $pdo->prepare('
    SELECT c.name, c.credits, g.grade
    FROM enrollments e
    JOIN courses c ON c.id = e.course_id
    LEFT JOIN grades g ON g.enrollment_id = e.id
    WHERE e.student_id = ?
    ORDER BY c.name
');
$stmt->execute([$_SESSION['user_id']]);
$courses = $stmt->fetchAll(PDO::FETCH_ASSOC);

// admin.php - role guard (top of every admin file)
session_start();
if (!isset($_SESSION['role']) || $_SESSION['role'] !== 'admin') {
    header('Location: login.php');
    exit;
}

// CSRF token generation and embedding
$_SESSION['csrf'] = bin2hex(random_bytes(32));
// In form HTML:
// <input type="hidden" name="csrf" value="<?= $_SESSION['csrf'] ?>">

// CSRF verification on POST
if (!hash_equals($_SESSION['csrf'], $_POST['csrf'] ?? '')) {
    http_response_code(403); exit('CSRF validation failed');
}

Authentication & Request Flow

Challenges & Solutions

Role-Based Routing Without a Framework. Without a routing framework like Laravel, every protected PHP file needed its own role check at the top. The pattern was standardised into a single require 'includes/auth-guard.php' include that accepts a required role parameter. This reduced the guard to a single line at the top of each protected file, making it easy to audit which files are protected and ensuring consistent behaviour.

SQL Injection Prevention. The most critical security concern in any PHP/MySQL application is SQL injection. Every query in the application uses PDO prepared statements with bound parameters, no string concatenation of user input into SQL anywhere in the codebase. This was enforced as a code review rule: any query using string interpolation was rejected and rewritten. PDO also enables proper error handling via exceptions rather than relying on the return value of mysql_query.

What I Learned

• The full PHP session lifecycle: session_start(), writing to $_SESSION, reading it on subsequent requests, and destroying it on logout
• password_hash() with PASSWORD_DEFAULT uses the current default algorithm (bcrypt, though PHP reserves the right to change it) with an automatically generated salt. The hash includes the algorithm identifier and salt, so password_verify() needs only the input and the stored hash
• PDO prepared statements are the correct, non-negotiable approach to database queries with user input; string concatenation into SQL is never acceptable
• CSRF protection requires generating a random token per session, embedding it in every state-changing form, and verifying it on POST before processing the request
• The Post/Redirect/Get (PRG) pattern prevents duplicate form submissions on page refresh by redirecting after every successful POST operation
• Relational schema design with foreign keys and ON DELETE CASCADE maintains referential integrity at the database level, independent of application logic

How I Built It

Phase 1: Relational schema design. I started by mapping the domain to four tables (users, courses, enrollments, grades) with foreign keys and ON DELETE CASCADE, so referential integrity is enforced by the database itself rather than relying on application code to clean up orphaned rows.

Phase 2: Authentication and session management. Next came the login flow: registration hashes passwords with password_hash($password, PASSWORD_DEFAULT) (bcrypt under the current default), login verifies with password_verify(), and the user ID plus role are written into the PHP session. A shared auth-guard.php include puts the role check at the top of every protected file.

Phase 3: Student dashboard. I built the dashboard query as a JOIN across enrollments, courses, and grades, scoped by the session user ID through a PDO prepared statement, so a student can only ever see their own records.

Phase 4: Admin CRUD with the PRG pattern. The admin panel reuses the same PDO patterns. Every successful POST ends in a redirect (Post/Redirect/Get), so refreshing the page never re-submits a form.

Phase 5: Hardening and frontend polish. Finally I added CSRF tokens (random_bytes(32) stored in the session, verified on POST), htmlspecialchars() on all rendered user data, .htaccess rules blocking direct access to include files, a responsive CSS Grid dashboard, and JavaScript client-side validation layered over authoritative server-side validation.

What You Need

Challenges and Lessons Learned

Role-based routing without a framework. Hand-rolling the role guard meant every protected file needed its own check. Standardising it into a single include with a required-role parameter made protection auditable at a glance. The lesson: when you skip a framework, you own the discipline the framework would have given you, and small conventions matter a lot.

Treating SQL injection as a hard rule. The rule that no user input is ever concatenated into SQL forced a careful pass over every query. It also taught me that PDO error mode should be set to exceptions, so database failures surface loudly instead of silently returning false.

PHP session lifecycle edge cases. Sessions must be destroyed (not just unset) on logout, and session fixation is a real risk on shared hosting. This build taught me the full lifecycle: start, write, read, regenerate after privilege change, and destroy.

PRG everywhere. Any form that ends with a rendered page is a duplicate-submission bug waiting for a refresh. Redirecting after every successful POST became a reflex I now apply to every server-rendered project.

What Is Next

• Pagination, sorting, and search on the admin tables so they stay usable with thousands of rows
• A self-service password reset flow using expiring one-time tokens sent by email
• An audit log table recording admin actions (who changed what, when) for accountability
• Email notifications for grade postings and enrollment changes
• Server-side unit tests for the authentication and calculation paths
• A longer-term migration path to a framework (e.g. Laravel) once the hand-rolled architecture starts showing its limits