Ochrona przed SQL-injection i innymi atakami: PREPARE, EXECUTE, parameterized queries

No dobra, najpierw dowiedzmy się, co właściwie chronimy, a dokładniej — przed kim. Na początku tego poziomu już wspominaliśmy o SQL-injection, czyli jednym z najpopularniejszych i najbardziej destrukcyjnych ataków na bazy danych. Atak polega na tym, że ktoś wysyła złośliwy kod SQL w Twoje zapytanie, żeby je "oszukać" i dostać się do danych, do których nie powinien mieć dostępu. Teraz rozkminimy to dokładniej.

Przykład SQL-injection

Załóżmy, że masz webapkę z prostym polem na login i hasło. Backend wykonuje zapytanie SQL, żeby sprawdzić, czy taki użytkownik istnieje w bazie:

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

To zapytanie wygląda spoko... dopóki użytkownik wpisuje poprawne dane. Ale co się stanie, jeśli ktoś wpisze:

• Nazwa użytkownika: admin' --
• Hasło: (zostawiamy puste)

W efekcie zapytanie zamieni się w coś takiego:

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

Zwróć uwagę na --, które w SQL oznacza początek komentarza. Wszystko po nich jest ignorowane. W efekcie sprawdzanie hasła jest pomijane i atakujący wchodzi jak admin!

Potencjalne skutki SQL-injection

SQL-injection może mieć naprawdę fatalne skutki:

1. Nieautoryzowany dostęp do danych. Na przykład ktoś może podejrzeć poufne dane, jak hasła użytkowników.
2. Usuwanie lub modyfikacja danych. Ktoś może wywalić całą tabelę albo zrobić w niej niezły bałagan.
3. Wykonanie dowolnego kodu SQL. Wyobraź sobie, że ktoś odpala DROP DATABASE... No, koszmar.

Ale spokojnie, nie panikujemy! PostgreSQL daje nam zestaw narzędzi, które pomogą się przed tym obronić.

Jak się bronić? Metody zapobiegania SQL-injection

1. Używanie przygotowanych zapytań (PREPARE i EXECUTE)

Przygotowane zapytania (prepared statements) to taki sprawdzony przepis na Twój kod SQL. Działa to tak: "przygotowujesz" zapytanie SQL raz, a potem osobno przekazujesz do niego dane. Dzięki temu nie da się wstrzyknąć złośliwego kodu.

Oto przykład jak to zrobić dobrze:

Przygotuj zapytanie używając PREPARE.

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

Zapytanie z dwoma parametrami $1 i $2 czeka na prawdziwe wartości, które podasz później.

Użyj EXECUTE, żeby wykonać zapytanie.

EXECUTE user_login('admin', 'password123');

W tym momencie PostgreSQL automatycznie zabezpiecza wszystkie dane od użytkownika, więc nie da się wstrzyknąć złośliwego kodu SQL.

Zalety tego podejścia:

• Nie da się zrobić SQL-injection, bo parametry są traktowane jak zwykłe dane, a nie część kodu SQL.
• Zapytania są bezpieczniejsze i szybciej się wykonują dzięki cache'owaniu planu wykonania.

2. Zapytania z parametrami (Parameterized Queries)

Ta metoda jest szczególnie popularna w aplikacjach pisanych np. w Pythonie czy Javie. Zamiast ręcznie bawić się w PREPARE i EXECUTE, możesz użyć bibliotek albo ORM, które same ogarniają parametry.

Przykład w Pythonie z biblioteką psycopg2:

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# Użycie zapytania z parametrami
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# Twoje dane są całkowicie bezpieczne!
result = cursor.fetchall()
print(result)

Zwróć uwagę na %s w zapytaniu SQL — to miejsce na parametr. Biblioteka psycopg2 sama zadba o bezpieczne przekazanie danych.

3. Walidacja danych wejściowych

Jeśli przekazujesz dane od użytkownika, upewnij się, że są takie, jakich oczekujesz. Na przykład:

• Dla tekstu użyj wyrażeń regularnych, żeby sprawdzić, czy nie ma tam niedozwolonych znaków.
• Dla liczb sprawdź, czy to naprawdę liczby.

Przykład w Pythonie:

import re

username = input("Podaj nazwę użytkownika: ")

# Dozwolone tylko litery, cyfry i podkreślenia
if re.match(r"^\w+$", username):
    print("Nazwa użytkownika poprawna")
else:
    print("Niebezpieczna nazwa użytkownika!")

4. Używanie minimalnych uprawnień

Zadbaj, żeby role używane do wykonywania zapytań miały tylko niezbędne uprawnienia. Na przykład nie dawaj roli dostępu do DROP TABLE czy ALTER TABLE, jeśli nie jest to konieczne.

5. Logowanie podejrzanych działań

Możesz użyć parametrów PostgreSQL do śledzenia aktywności użytkowników:

• log_statement = 'all' — logowanie wszystkich zapytań.
• log_connections = on — logowanie wszystkich połączeń z bazą.

Te ustawienia pomagają wykryć potencjalnie niebezpieczne akcje.

Przykłady implementacji

Przykład 1: Użycie przygotowanego zapytania w SQL

-- Tworzymy przygotowane zapytanie
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- Wykonujemy zapytanie z bezpiecznymi parametrami
EXECUTE check_credentials('admin', 'password123');

Przykład 2: Zapytanie z parametrem w Pythonie

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

Zalecenia dotyczące bezpieczeństwa

Zawsze sprawdzaj dane wejściowe. Nigdy nie ufaj danym od użytkownika.

Używaj tylko przygotowanych zapytań lub zapytań z parametrami. To Twój główny mur przed SQL-injection.

Przydzielaj role z minimalnymi uprawnieniami. To ogranicza szkody, jeśli ktoś się włamie.

Włącz logowanie i regularnie sprawdzaj logi. Bądź na bieżąco z tym, co się dzieje w Twojej bazie.

SQL-injection to naprawdę groźna sprawa, ale jeśli używasz przygotowanych zapytań, zapytań z parametrami i trzymasz się dobrych praktyk, Twoja baza będzie bezpieczna i możesz spać spokojnie, nie bojąc się, że ktoś "przypadkiem" wywali Ci wszystkie tabele. PostgreSQL daje Ci do tego wszystkie narzędzia, więc do dzieła!