Zapytania przygotowane w PHP — ćwiczenia z parametrami SQL

Najprostszy sposób na zbudowanie zapytania SQL z danych użytkownika — sklejenie tekstu — jest też najgroźniejszy: wystarczy, że ktoś wpisze ' OR '1'='1, a zapytanie zwróci wszystkie wiersze (to atak SQL injection). Zapytania przygotowane oddzielają polecenie SQL od danych, więc dane nigdy nie zostaną potraktowane jako kod. Na lekcji uczniowie używają PDO na bazie SQLite w pamięci, a w aplikacji z MySQL zmieni się tylko tekst połączenia.

Scenariusz jest przeznaczony na 45 minut dla klasy znającej podstawy SQL i tablice w PHP — na przykład po lekcji o PDO i pierwszych zapytaniach. Do ćwiczeń nie potrzeba serwera bazy danych: SQLite jest wbudowane w PHP.

Przygotowanie stanowisk

Potrzebny jest PHP 8.x uruchamiany w terminalu (na przykład z pakietu XAMPP; sprawdź polecenie php -v). Rozpakuj pakiet z sekcji materiałów do osobnego folderu i przejdź do niego w terminalu. Skrypty uruchamiasz poleceniem php start.php; serwer WWW nie jest potrzebny, a wynik pojawia się w terminalu. Jeśli pobierasz pojedyncze pliki .php.txt ze strony zamiast ZIP, usuń końcówkę .txt lokalnie.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• przygotować zapytanie (prepare) i wykonać je z parametrami (execute);
• użyć parametrów nazwanych (:imie) i pozycyjnych (?);
• pobrać wyniki (fetchAll, fetchColumn) i sprawdzić liczbę zmienionych wierszy (rowCount);
• wykazać różnicę między sklejaniem tekstu a zapytaniem przygotowanym na przykładzie SQL injection.

Zapytanie przygotowane

Zamiast wstawiać dane do tekstu zapytania, zapisujemy w zapytaniu symbole zastępcze — nazwane (:imie) lub pozycyjne (?) — a wartości przekazujemy osobno: $stmt->execute([":imie" => $imie]). Silnik bazy dostaje polecenie i dane oddzielnie, więc nawet tekst ' OR '1'='1 zostanie potraktowany jako zwykły napis szukanego imienia. Zapytanie przygotowane można wykonać wiele razy z różnymi danymi (jak w pętli wstawiającej cztery wiersze).

PDO ustawiamy tak, aby błędy zgłaszały wyjątki: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION). Wyniki pobieramy metodami fetchAll(PDO::FETCH_ASSOC) (tablica wierszy z nazwami kolumn), fetch (jeden wiersz) lub fetchColumn (pierwsza kolumna pierwszego wiersza); rowCount() podaje liczbę wierszy zmienionych przez UPDATE i DELETE. Parametrem może być tylko wartość — nazw tabel i kolumn tak nie podstawimy; takie fragmenty wybiera się z listy dozwolonych wartości w kodzie.

Zadania

Zadanie 1. Wstawianie. Utwórz bazę w pamięci i tabelę uczniowie (id, imie, klasa, punkty). Zapytaniem przygotowanym z parametrami nazwanymi wstaw w pętli cztery wiersze, w tym uczennicę o imieniu O'Brien. Wypisz liczbę wierszy.

Zadanie 2. Wybieranie. Zapytaniem z parametrami pozycyjnymi wybierz uczniów klasy 2A z co najmniej 36 punktami (od najlepszego), a zapytaniem z parametrem nazwanym — imię ucznia o id 3.

Zadanie 3. SQL injection. Dla wpisu ' OR '1'='1 porównaj liczbę znalezionych wierszy po sklejeniu tekstu i w zapytaniu przygotowanym. Zwiększ o 5 punktów uczniów klasy 2B i wypisz liczbę zmienionych wierszy.

Dla chętnych: wstaw trzy liczby zapytaniem z parametrem pozycyjnym i wypisz je posortowane (FETCH_COLUMN).

Rozwiązanie i wyniki

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE uczniowie (id INTEGER PRIMARY KEY, imie TEXT NOT NULL, klasa TEXT NOT NULL, punkty INTEGER)");

// Zadanie 1
$wstaw = $pdo->prepare("INSERT INTO uczniowie (imie, klasa, punkty) VALUES (:imie, :klasa, :punkty)");
foreach ([["Anna", "2A", 42], ["Bartek", "2A", 35], ["O'Brien", "2B", 28], ["Ewa", "2B", 39]] as [$imie, $klasa, $punkty]) {
    $wstaw->execute([":imie" => $imie, ":klasa" => $klasa, ":punkty" => $punkty]);
}
echo $pdo->query("SELECT COUNT(*) FROM uczniowie")->fetchColumn(), " wierszy\n";

// Zadanie 2
$wybierz = $pdo->prepare("SELECT imie, punkty FROM uczniowie WHERE klasa = ? AND punkty >= ? ORDER BY punkty DESC");
$wybierz->execute(["2A", 36]);
foreach ($wybierz->fetchAll(PDO::FETCH_ASSOC) as $wiersz) {
    echo $wiersz["imie"], " ", $wiersz["punkty"], "\n";
}
$jeden = $pdo->prepare("SELECT imie FROM uczniowie WHERE id = :id");
$jeden->execute([":id" => 3]);
echo $jeden->fetchColumn(), "\n";

// Zadanie 3
$wpis = "' OR '1'='1";
$zle = $pdo->query("SELECT COUNT(*) FROM uczniowie WHERE imie = '" . $wpis . "'")->fetchColumn();
$dobrze = $pdo->prepare("SELECT COUNT(*) FROM uczniowie WHERE imie = :imie");
$dobrze->execute([":imie" => $wpis]);
echo "Konkatenacja: ", $zle, " Przygotowane: ", $dobrze->fetchColumn(), "\n";
$zmien = $pdo->prepare("UPDATE uczniowie SET punkty = punkty + :ile WHERE klasa = :klasa");
$zmien->execute([":ile" => 5, ":klasa" => "2B"]);
echo $zmien->rowCount(), " zmienione\n";

Program wypisze:

4 wierszy
Anna 42
O'Brien
Konkatenacja: 4 Przygotowane: 0
2 zmienione

Zapytanie sklejone z wpisem ' OR '1'='1 zwróciło cztery wiersze (wszystkie), a przygotowane — zero. Warto też zauważyć, że apostrof w nazwisku O'Brien nie sprawiał kłopotu w zapytaniu przygotowanym. Zadanie dla chętnych (rozszerzenie.php) wypisuje:

1,2,3

Typowe błędy

Pytania kontrolne i punktacja

1. Czym różni się parametr nazwany od pozycyjnego?
2. Dlaczego zapytanie przygotowane chroni przed SQL injection?
3. Czy można podstawić nazwę tabeli jako parametr?

Odpowiedzi: Nazwany ma etykietę (:imie) i podaje się go w tablicy asocjacyjnej, pozycyjny (?) — kolejność w tablicy indeksowanej. Bo polecenie SQL i dane trafiają do bazy osobno, więc dane nie mogą zmienić struktury zapytania. Nie, parametrem może być tylko wartość.

Proponowana punktacja (razem 10 punktów): zadanie 1 — połączenie i tabela 1 pkt, prepare z parametrami nazwanymi 2 pkt, wynik 1 pkt; zadanie 2 — parametry pozycyjne 2 pkt, parametr nazwany 1 pkt; zadanie 3 — porównanie z SQL injection 2 pkt, UPDATE z rowCount 1 pkt. Zadanie dla chętnych nie jest wymagane do pełnej oceny.

Materiały do pobrania

• Pakiet PHP: materiały do lekcji, rozwiązanie i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Rozpakuj ZIP przed otwarciem plików. Materiały są dostępne bez logowania; publiczne rozwiązanie służy nauce i samodzielnemu sprawdzeniu, a nie poufnemu sprawdzianowi.

Co dalej?

Następny krok to łączenie z bazą MySQL, aplikacja CRUD i ochrona przed XSS przy wypisywaniu. Ten materiał rozwija dział o bazach w PHP w kursie PHP w TablicaPro, gdzie znajdziesz prezentacje, ćwiczenia i testy do prowadzenia lekcji.

Źródła techniczne: PDO w podręczniku PHP oraz SQL injection w OWASP. Zobacz też lekcję o htmlspecialchars.