GateArch

Designed the relational schema to support the core features: user authentication and roles, course catalog, student-course enrollment, and grade recording. Foreign keys with ON DELETE CASCADE on the enrollments and grades tables ensure that deleting a student or course cleans up related records automatically.

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 REFERENCES users(id)    ON DELETE CASCADE,
  course_id  INT NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
  UNIQUE KEY uq_enrollment (student_id, course_id)
);

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

User passwords are never stored in plaintext. Registration uses password_hash($password, PASSWORD_DEFAULT) to store a bcrypt hash. Login uses password_verify($input, $stored_hash) to check the submission against the stored hash without ever decoding it. On successful verification, the user's ID and role are written to the session.

// 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

The student dashboard queries enrolled courses and grades using a JOIN across three tables. All values are bound via PDO to prevent injection, including the session user ID that scopes the query to the authenticated student.

// 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);

Every admin-facing PHP file begins with a session role check. If the check fails, the user is redirected to the login page — there is no route that an unauthenticated or non-admin user can reach admin functionality through.

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

Admin CRUD operations use the same PDO prepared statement pattern. Form submissions are processed on POST, and the page redirects after successful operations (Post/Redirect/Get pattern) to prevent duplicate submissions on refresh.

The dashboard uses a CSS Grid layout with a sidebar for navigation and a main content area for course/grade tables. JavaScript validates form fields (non-empty, numeric grades in range 0–100) before submission, providing immediate feedback without a round-trip to the server. Server-side validation also runs on every POST as the authoritative check.

Beyond PDO prepared statements, additional security measures were applied: CSRF tokens on all state-changing forms (generated with bin2hex(random_bytes(32)), stored in session, verified on POST), htmlspecialchars() wrapping all user-supplied data rendered into HTML output to prevent XSS, and .htaccess rules to deny direct HTTP access to PHP include and config files.

// 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');
}