Ćwiczenia SQL i PHP: Bezpieczeństwo i Transakcje (XAMPP)

Setup · DDL · relacje

SStruktura bazy danych szkola

Załóż istnienie bazy szkola oraz tabel klasa i uczniowie. Kod wykonaj w phpMyAdmin → SQL.

SQL · DDL

-- Używamy bazy danych 'szkola' (założonej wcześniej)
USE szkola;

-- (opcjonalnie) tworzenie bazy:
-- CREATE DATABASE IF NOT EXISTS szkola
--   CHARACTER SET utf8mb4
--   COLLATE utf8mb4_polish_ci;

-- Tabela klasa
CREATE TABLE IF NOT EXISTS klasa (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  nazwa VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

-- Tabela uczniowie
CREATE TABLE IF NOT EXISTS uczniowie (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  imie      VARCHAR(50) NOT NULL,
  nazwisko  VARCHAR(50) NOT NULL,
  wiek      INT,
  id_klasa  INT,
  FOREIGN KEY (id_klasa) REFERENCES klasa(id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

-- Dane przykładowe
INSERT INTO klasa (nazwa) VALUES
  ('3A - IT'),
  ('3B - Ekono'),
  ('4C - MatFiz'),
  ('1A - Podst');

INSERT INTO uczniowie (imie, nazwisko, wiek, id_klasa) VALUES 
  ('Jan',       'Kowalski',  19, 1), 
  ('Anna',      'Nowak',     17, 1), 
  ('Piotr',     'Zielinski', 19, 2),
  ('Magda',     'Wojcik',    18, 2),
  ('Krzysztof', 'Duda',      17, 3);

Kontekst

…Zadania 1–20

Poprzednie części kursu (1–20) realizowały podstawy SELECT, INSERT, UPDATE, relacji i prostych raportów dla bazy szkola.

PHP · Prepared Statements

21Bezpieczne dodawanie ucznia (Prepared Statement)

Użyj Prepared Statements w PHP, aby bezpiecznie dodać nowego ucznia (ochrona przed SQL Injection).

PHP · mysqli

<?php
// Zakładamy, że $conn to poprawny obiekt mysqli (np. z dbconnect.php)

$imie_input     = 'Beata'; 
$nazwisko_input = 'Kowalska';
$id_klasy_input = 2; // np. '3B - Ekono'

// 1. Przygotowanie zapytania z symbolami zastępczymi (?)
$stmt = mysqli_prepare(
  $conn,
  "INSERT INTO uczniowie (imie, nazwisko, id_klasa) VALUES (?, ?, ?)"
);

// 2. Powiązanie parametrów (s - string, s - string, i - integer)
mysqli_stmt_bind_param($stmt, "ssi",
  $imie_input,
  $nazwisko_input,
  $id_klasy_input
);

// 3. Wykonanie zapytania
mysqli_stmt_execute($stmt);

// 4. Sprawdzenie powodzenia
if (mysqli_stmt_affected_rows($stmt) > 0) {
  echo "Bezpiecznie dodano ucznia.";
} else {
  echo "Błąd dodawania: " . mysqli_error($conn);
}

// 5. Zamknięcie instrukcji
mysqli_stmt_close($stmt);
?>

Prepared Statement kompiluje zapytanie raz, a dane przekazywane są jako parametry – dzięki temu silnik SQL nie interpretuje ich jako kod (ochrona przed SQL Injection).

Transakcje · COMMIT / ROLLBACK

22Transakcja z wycofaniem zmian (ROLLBACK)

Przenieś ucznia o ID=1 do klasy o ID=3, a równolegle wykonaj drugą operację z błędem. Jeśli coś się nie powiedzie – wycofaj całą transakcję.

PHP · TRANSACTION

<?php
// $conn – obiekt mysqli, tabele InnoDB

// 1. Wyłączenie auto-commit i start transakcji
mysqli_autocommit($conn, false);
mysqli_begin_transaction($conn);

$success = true;

// 2. Zmiana klasy ucznia ID=1
$q1 = "UPDATE uczniowie SET id_klasa = 3 WHERE id = 1";
if (!mysqli_query($conn, $q1)) {
  $success = false;
}

// 3. Celowy błąd (np. nieistniejąca kolumna)
$q2 = "UPDATE uczniowie SET nieistniejaca_kolumna = 1 WHERE id = 1";
if (!mysqli_query($conn, $q2)) {
  $success = false;
}

// 4. Decyzja COMMIT / ROLLBACK
if ($success) {
  mysqli_commit($conn);
  echo "Transakcja zakończona pomyślnie (COMMIT).";
} else {
  mysqli_rollback($conn);
  echo "Transakcja nieudana (ROLLBACK). Zmiany wycofane.";
}

// 5. Przywrócenie auto-commit
mysqli_autocommit($conn, true);
?>

Transakcja traktuje kilka operacji jak jedną całość – albo wszystkie się udają (COMMIT), albo w razie błędu przywracany jest stan sprzed transakcji (ROLLBACK).

GROUP BY + HAVING

23Klasy z liczbą uczniów > 2

Wyświetl nazwy klas, które mają więcej niż 2 uczniów. Użyj GROUP BY i HAVING w zapytaniu łączącym tabele.

PHP · SQL

<?php
$q = "SELECT k.nazwa,
           COUNT(u.id) AS liczba_uczniow
      FROM klasa k
      JOIN uczniowie u ON k.id = u.id_klasa
      GROUP BY k.id, k.nazwa
      HAVING COUNT(u.id) > 2";

$result = mysqli_query($conn, $q);
?>

GROUP BY grupuje dane po klasach, COUNT(u.id) liczy uczniów w każdej z nich, a HAVING filtruje tylko te grupy, które spełniają warunek (liczba uczniów > 2).

LEFT JOIN · klasy bez uczniów

24Lista klas z liczbą uczniów (także 0)

Wyświetl wszystkie klasy, także bez uczniów. Obok nazwy pokaż liczbę uczniów (0, jeśli brak). Użyj LEFT JOIN.

PHP · SQL

<?php
$q = "SELECT k.nazwa,
           COUNT(u.id) AS liczba_uczniow
      FROM klasa k
      LEFT JOIN uczniowie u ON k.id = u.id_klasa
      GROUP BY k.id, k.nazwa
      ORDER BY k.nazwa";

$result = mysqli_query($conn, $q);
?>

LEFT JOIN zwraca wszystkie rekordy z klasa, a COUNT(u.id) policzy tylko istniejących uczniów – klasy bez uczniów dostaną licznik 0.

CONCAT · aliasy

25Pełne imię i nazwisko ucznia

Wyświetl pełne imię i nazwisko uczniów w jednej kolumnie PelneImie oraz ich wiek, ale tylko dla osób młodszych niż 19 lat.

PHP · SQL

<?php
$q = "SELECT
           CONCAT(imie, ' ', nazwisko) AS PelneImie,
           wiek
      FROM uczniowie
      WHERE wiek < 19
      ORDER BY wiek DESC";

$result = mysqli_query($conn, $q);
?>

CONCAT łączy imię i nazwisko w jeden ciąg, a alias AS PelneImie nadaje wynikowej kolumnie czytelną nazwę dla raportów lub tabel HTML.