Podstawy SQL, które musisz znać

Wymagania dotyczące znajomości języka zapytań SQL jest są niemalże wszędzie. Niezależnie od tego, czy chcemy pracować jako pentesterzy, osoby ds. automatyzacji czy frontend developerzy, jest to wiedza, która poszerza nasze postrzeganie i znajomość aplikacji internetowych. Mimo że współczesne frameworki nie wykorzystują surowej składni zapytań SQL w kodzie aplikacji, to pod powłoką są one wywoływane.

Nomenklatura, tabele i rekordy

Na początek trochę teorii. Baza danych składa się z tabel, które z kolei przetrzymują rekordy. Załóżmy, że nasza baza danych wygląda w następujący sposób.

+-------------------------------------------------+
| Database                                        |
+-------------------------------+-----------------+
| Users                         | Articles        |
+---------+----------+----------+---------+-------+
| id (PK) | nickname | password | id (PK) | title |
+---------+----------+----------+---------+-------+

Jak widać, w naszej bazie danych są dwie tabele – users oraz articles. Pierwsza przetrzymuje rekordy użytkowników, a w drugiej artykuły.

• Tabela users ma pola:
  • id (PK) – jest to id użytkownika, które jest dodawane automatycznie przy dodawniu nowego rekordu. Jest ono unikalne dla każdego rekordu,
  • nickname – nazwa użytkownika,
  • password – hasło użytkownika.
• Tabela articles posiada pola:
  • id (PK) – to samo co wyżej,
  • title – nazwa artykułu.

Chcielibyśmy teraz wiedzieć, który artykuł napisał jaki użytkownik. Pomóc nam w tym może id z tabeli users. Jak wiemy, jest ono automatycznie tworzone przy dodaniu do tabeli rekordu. W takim wypadku trzeba edytować powyższą tabelę.

+---------------------------------------------------------------+
| Database                                                      |
+-------------------------------+-------------------------------+
| Users                         | Articles                      |
+---------+----------+----------+---------+-------+-------------+
| id (PK) | nickname | password | id (PK) | title | userId (FK) |
+---------+----------+----------+---------+-------+-------------+

Jak widać, dodaliśmy do tabelki articles pole userId. Teraz dodając rekordy do tabelki articles będziemy mogli się odwołać bezpośrednio do użytkowników z tabeli users poprzez ich id. Nasze pole userId jest więc kluczem obcym (FK = Foreign key). Jak mogłyby wyglądać przykładowe rekordy w tabelach?

+-------------------------------+
| Users                         |
+---------+----------+----------+
| id (PK) | nickname | password |
+---------+----------+----------+
| 1       | Joe      | fR4dg$f2 |
| 2       | Johnny   | h%f@d3f  |
| 3       | Mary     | jFo$f434 |
| 4       | Danny    | vX4fG9   |
+---------+----------+----------+

+-------------------------------+
| Articles                      |
+---------+-------+-------------+
| id (PK) | title | userId (FK) |
+---------+-------+-------------+
| 1       | Test1 | 3           |
| 2       | Art1  | 1           |
| 3       | CSRF  | 1           |
+---------+-------+-------------+

Ok, ale co się w nich dzieje? Tabelka users nie powinna być dla nas problematyczna w odczycie. Z kolei tabela articles przechowuje rekordy, w których odwołuje się do tabelki users dzięki id użytkowników. W taki sposób wiemy, że artykuł o nazwie „Test1” został napisany przez użytkownika, którego id to 3, czyli Mary. Z kolei autorem artykułów „Art1” i „CSRF” jest Joe.

Typy danych

Każda z rodzajów baz danych posiada typy rekordów, które są przechowywane. W zależności od wykorzystanej bazy danych, nazwy typów mogą się różnić. W wyżej stworzonej tabelce articles, nasze pole title byłoby np. typu CHAR. Z kolei userId miałoby typ INT.

Najbardziej znane typy rekordów znajdziecie w poniższej tabelce.

Typ rekordu	Opis
INT(SIZE)	Średniej wielkości liczba całkowita. Zakres dopuszczalnych wartości jest od -2147483648 do 2147483647. W przypadku typu INT z atrybutem unsigned, jego zakres wynosi od 0 do 4294967295.
DOUBLE(SIZE, D)	Liczba zmiennoprzecinkowa, której całkowita ilość cyfr jest określona jako SIZE, a liczba cyfr po przecinku jako parametr D.
VARCHAR(SIZE)	Łańczuch znaków o zmiennej długości (litery, cyfry i znaki specjalne). Jego maksymalną długość określa parametr SIZE, który może przyjmować wartości od 0 do 255. Domyślnie parametr ten jest ustawiony na 1.
TEXT	Przechowuje ciąg znaków o maksymalnej długości 65 535 bajtów
DATETIME(FSP)	Data i czas w formacie RRRR-MM-DD hh:mm:ss
TIMESTAMP	Znacznik czasu w formacie RRRR-MM-DD hh:mm:ss. Ten typ, w porównaniu do DATETIME używany jest najczęściej do śledzenia zmian w rekordach i są często aktualizowane za każdym razem, gdy rekord jest zmieniany.
ENUM(VAL1, VAL2, VAL3…)	Typ, który przyjmuje jedną z zdefiniowanych wartości z listy. Jeśli zostanie wprowadzona wartość spoza niej, to wartość pola ENUM zostanie ustawione jako puste.
BOOLEAN	Wartość 0 uznawane jest za fałsz, a wszystkie wartości niezerowe traktowane są jako true.
JSON	Przechowuje dane w formacie JSON.

Składnia SQL i podstawowe operacje na bazach danych

Zaczniemy od stworzenia bazy danych z rekordami, aby móc na niej operować. Najpierw wchodzimy do powłoki mySQL poprzez polecenie:

mysql -u root -p

Teraz możemy używać już standardowej składni SQL. Stwórzmy zatem bazę danych o nazwie my_test_database.

MariaDB [(none)]> CREATE DATABASE my_test_database;
Query OK, 1 row affected (0.001 sec)

Teraz możemy podglądnąć, czy nasza baza danych została utworzona.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_test_database   |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

Teraz chcielibyśmy utworzyć kilka tabel w naszej bazie danych. Aby przejść do jej edycji, użyjemy polecenia USE.

MariaDB [(none)]> USE my_test_database
Database changed

Teraz możemy operować na nowo utworzonej bazie danych. Spróbujmy zatem stworzyć tabelę users.

CREATE TABLE users
(
  id              INT unsigned NOT NULL AUTO_INCREMENT,
  firstName       VARCHAR(150) NOT NULL,
  surname         VARCHAR(150) NOT NULL,
  birth           DATE NOT NULL,
  PRIMARY KEY     (id)
);

Na początku inicjujemy utworzenie tabeli users, w której są następujące pola:

• id – jest to pole typu INT z atrybutem unsigned (dzięki któremu jego zakres wynosi od 0 do 4294967295). Nie może być ono puste i jest uzupełniane automatycznie przy dodawaniu rekordu,
• firstName – jest to pole typu VARCHAR, którego makymalna długość wynosi 150 znaków. Nie może być ono puste,
• surname – jest to pole typu VARCHAR, którego makymalna długość wynosi 150 znaków. Nie może być ono puste,
• birth – jest to pole typu DATE, które nie może być puste.

Na końcu wskazujemy że „id” jest polem unikalnym. Teraz sprawdzamy nasze tabele w obecnej bazie danych.

MariaDB [my_test_database]> SHOW TABLES;
+----------------------------+
| Tables_in_my_test_database |
+----------------------------+
| users                      |
+----------------------------+
1 row in set (0.000 sec)

Teraz będziemy chcieli sprawdzić szczegółowy opis tabeli.

MariaDB [my_test_database]> DESCRIBE users;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(150)     | NO   |     | NULL    |                |
| surname   | varchar(150)     | NO   |     | NULL    |                |
| birth     | date             | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

Spróbujmy wypełnić naszą tabelę rekordami.

INSERT INTO users ( firstName, surname, birth) VALUES
  ( 'Alice', 'Clavo', '2011-04-27' ),
  ( 'Jacob', 'Covik', '2008-12-09' ),
  ( 'Johnny', 'Servus', '2012-02-01' );

Teraz sprawdźmy rekordy w naszej tabeli users.

MariaDB [my_test_database]> SELECT * FROM users;
+----+-----------+---------+------------+
| id | firstName | surname | birth      |
+----+-----------+---------+------------+
|  1 | Alice     | Clavo   | 2011-04-27 |
|  2 | Jacob     | Covik   | 2008-12-09 |
|  3 | Johnny    | Servus  | 2012-02-01 |
+----+-----------+---------+------------+
3 rows in set (0.000 sec)

Powiedzmy, że chcielibyśmy dostać nazwisko osoby z tabeli users, która nazywa się 'Alice’. Jak możemy to zrobić?

MariaDB [my_test_database]> SELECT surname FROM users WHERE firstName = 'Alice'; 
+---------+
| surname |
+---------+
| Clavo   |
+---------+
1 row in set (0.000 sec)

Teraz dajmy na to chcemy usunąć osobę o nazwisku 'Covik’.

MariaDB [my_test_database]> DELETE FROM users WHERE surname = 'Covik';
Query OK, 1 row affected (0.001 sec)

MariaDB [my_test_database]> SELECT * FROM users;
+----+-----------+---------+------------+
| id | firstName | surname | birth      |
+----+-----------+---------+------------+
|  1 | Alice     | Clavo   | 2011-04-27 |
|  3 | Johnny    | Servus  | 2012-02-01 |
+----+-----------+---------+------------+
2 rows in set (0.000 sec)

Jak widać, rekord został usunięty. Zdarzają się sytuację, w których potrzebujemy dodać do naszej tabeli dodatkową kolumnę.

MariaDB [my_test_database]> ALTER TABLE users ADD gender CHAR(1) AFTER surname;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

Sprawdźmy rezultat komendy.

MariaDB [my_test_database]> DESCRIBE users;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(150)     | NO   |     | NULL    |                |
| surname   | varchar(150)     | NO   |     | NULL    |                |
| gender    | char(1)          | YES  |     | NULL    |                |
| birth     | date             | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

Tak jak wskazaliśmy w poleceniu, do tabeli została dodana kolumna gender o rodzaju CHAR po kolumnie surname. Domyślnie ustawione jest, że może ona przyjmować wartość null. Analogicznie sytuacja wygląda z usuwaniem kolumn.

MariaDB [my_test_database]> ALTER TABLE users DROP gender;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [my_test_database]> DESCRIBE users;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(150)     | NO   |     | NULL    |                |
| surname   | varchar(150)     | NO   |     | NULL    |                |
| birth     | date             | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

W taki sposób prezentują się podstawowe operacje na bazach danych. Przejdźmy do czegoś konkretniejszego.

JOIN w zapytaniach SQL

Iloczyn kartezjański

Aby zaprezentować iloczyn kartezjański, dodajmy do naszej istniejącej bazy danych tabelę profiles wraz z przykładowymi rekordami.

CREATE TABLE articles
(
  id              INT unsigned NOT NULL AUTO_INCREMENT,
  title           VARCHAR(200) NOT NULL,
  description     TEXT,
  PRIMARY KEY     (id)
);

INSERT INTO articles ( title, description) VALUES
  ( 'My first title', 'Lorem ipsum dolor sit' ),
  ( 'My second title', 'Mit amet' ),
  ( 'My third title', 'Ipsum mit dolor sit' );

Iloczynem kartezjańskim jest zbiór, w którm dla każdego wiersza ze wskazanej tabeli będzie dopasowany wiersz z drugiej tabeli. Ponieważ w pierwszej tabeli są dwa rekordy a w drugiej trzy, łącznie będzie ich sześć.

MariaDB [my_test_database]> SELECT * FROM users, articles;
+----+-----------+---------+------------+----+-----------------+-----------------------+
| id | firstName | surname | birth      | id | title           | description           |
+----+-----------+---------+------------+----+-----------------+-----------------------+
|  1 | Alice     | Clavo   | 2011-04-27 |  1 | My first title  | Lorem ipsum dolor sit |
|  3 | Johnny    | Servus  | 2012-02-01 |  1 | My first title  | Lorem ipsum dolor sit |
|  1 | Alice     | Clavo   | 2011-04-27 |  2 | My second title | Mit amet              |
|  3 | Johnny    | Servus  | 2012-02-01 |  2 | My second title | Mit amet              |
|  1 | Alice     | Clavo   | 2011-04-27 |  3 | My third title  | Ipsum mit dolor sit   |
|  3 | Johnny    | Servus  | 2012-02-01 |  3 | My third title  | Ipsum mit dolor sit   |
+----+-----------+---------+------------+----+-----------------+-----------------------+
6 rows in set (0.001 sec)

Ok, ale całość wydaje się na ten moment nieczytelna. Dwa razy powtarza się kolumna id. Spróbujmy zatem zmienić jej wyświetlaną u góry nazwę.

SELECT 
  users.id AS userId, 
  users.firstName, 
  users.surname, 
  users.birth, 
  articles.id as articleId, 
  articles.title, 
  articles.description 
FROM users, articles;

Po wykonaniu polecenia dostajemy:

+--------+-----------+---------+------------+-----------+-----------------+-----------------------+
| userId | firstName | surname | birth      | articleId | title           | description           |
+--------+-----------+---------+------------+-----------+-----------------+-----------------------+
|      1 | Alice     | Clavo   | 2011-04-27 |         1 | My first title  | Lorem ipsum dolor sit |
|      3 | Johnny    | Servus  | 2012-02-01 |         1 | My first title  | Lorem ipsum dolor sit |
|      1 | Alice     | Clavo   | 2011-04-27 |         2 | My second title | Mit amet              |
|      3 | Johnny    | Servus  | 2012-02-01 |         2 | My second title | Mit amet              |
|      1 | Alice     | Clavo   | 2011-04-27 |         3 | My third title  | Ipsum mit dolor sit   |
|      3 | Johnny    | Servus  | 2012-02-01 |         3 | My third title  | Ipsum mit dolor sit   |
+--------+-----------+---------+------------+-----------+-----------------+-----------------------+
6 rows in set (0.000 sec)

INNER JOIN

Stwórzmy tabelę books przetrzymującą klucze obce (Foreign key) do tabeli users.

CREATE TABLE books
(
  id              INT unsigned NOT NULL AUTO_INCREMENT,
  title           VARCHAR(200) NOT NULL,
  userId          INT unsigned,
  PRIMARY KEY     (id),
  FOREIGN KEY (userId)
    REFERENCES users(id)
    ON DELETE CASCADE
);

INSERT INTO books ( title, userId ) VALUES
  ( 'My first book title', 1 ),
  ( 'My second book title', 3 ),
  ( 'My third book title', 1 );

Mając w nowo utworzonej tabelce rekordy powiązane z tabelą users, możemy użyć złączenia INNER JOIN. Polega ono na wybraniu z iloczynu kartezjańskiego wierszy, dla których warunek jest spełniony.

SELECT *
  FROM books INNER JOIN users
    ON books.userId = users.id;

+----+----------------------+--------+----+-----------+---------+------------+
| id | title                | userId | id | firstName | surname | birth      |
+----+----------------------+--------+----+-----------+---------+------------+
|  1 | My first book title  |      1 |  1 | Alice     | Clavo   | 2011-04-27 |
|  2 | My second book title |      3 |  3 | Johnny    | Servus  | 2012-02-01 |
|  3 | My third book title  |      1 |  1 | Alice     | Clavo   | 2011-04-27 |
+----+----------------------+--------+----+-----------+---------+------------+
3 rows in set (0.000 sec)

W taki sposób dostaliśmy wiersze z iloczyna kartezjańskiego, w których id użytkownika jest równe wartości wskazanej w kolumnie userId w tabeli books.

Jak można się domyśleć po wcześniejszych przykładach, możemy w instrukcji SELECT zdefiniować kolumny, które mają zostać wyświetlone.

SELECT users.firstName,
  users.surname,
  books.title as articleTitle
  FROM books INNER JOIN users
  ON books.userId = users.id;

+-----------+---------+----------------------+
| firstName | surname | articleTitle         |
+-----------+---------+----------------------+
| Alice     | Clavo   | My first book title  |
| Johnny    | Servus  | My second book title |
| Alice     | Clavo   | My third book title  |
+-----------+---------+----------------------+
3 rows in set (0.000 sec)

OUTER JOIN

Istnieją dwa rodzaje złączeń zewnętrznych w mySQL:

• LEFT OUTER JOIN,
• RIGHT OUTER JOIN,

LEFT OUTER JOIN

Najpierw dodajmy nowe rekordy i wywołajmy polecenie z instrukcją LEFT OUTER JOIN.

INSERT INTO books ( title ) VALUES
  ( 'My fourth book title' ),
  ( 'My fifth book title' );

SELECT *
  FROM books LEFT OUTER JOIN users
    ON books.userId = users.id;

+----+----------------------+--------+------+-----------+---------+------------+
| id | title                | userId | id   | firstName | surname | birth      |
+----+----------------------+--------+------+-----------+---------+------------+
|  1 | My first book title  |      1 |    1 | Alice     | Clavo   | 2011-04-27 |
|  2 | My second book title |      3 |    3 | Johnny    | Servus  | 2012-02-01 |
|  3 | My third book title  |      1 |    1 | Alice     | Clavo   | 2011-04-27 |
|  4 | My fourth book title |   NULL | NULL | NULL      | NULL    | NULL       |
|  5 | My fifth book title  |   NULL | NULL | NULL      | NULL    | NULL       |
+----+----------------------+--------+------+-----------+---------+------------+
5 rows in set (0.000 sec)

Wywołanie polecenia zwraca wiersze z lewej tabeli (w poniższym przykładzie jest to books) dla których wskazany warunek jest spełniony oraz te, które nie mają odpowiednika z prawej tabeli.

RIGHT OUTER JOIN

Analogicznie do powyższego, polecenie zwraca wiersze z prawej tabeli, dla których wskazany warunek został spełniony i te, które nie mają odpowiednika z lewej tabeli.

SELECT *
  FROM books RIGHT OUTER JOIN users
    ON books.userId = users.id;

+------+----------------------+--------+----+-----------+---------+------------+
| id   | title                | userId | id | firstName | surname | birth      |
+------+----------------------+--------+----+-----------+---------+------------+
|    1 | My first book title  |      1 |  1 | Alice     | Clavo   | 2011-04-27 |
|    2 | My second book title |      3 |  3 | Johnny    | Servus  | 2012-02-01 |
|    3 | My third book title  |      1 |  1 | Alice     | Clavo   | 2011-04-27 |
+------+----------------------+--------+----+-----------+---------+------------+
3 rows in set (0.001 sec)

FULL OUTER JOIN

Istnieje trzeci sposób złączeń zewnętrznych o którym nie wspomniałem, ponieważ nie jest obsługiwany przez mySQL. Jest to FULL OUTER JOIN i polega on na połączeniu LEFT OUTER JOIN oraz RIGHT OUTER JOIN.

Dodatkowe

W internecie można spotkać się z różnym nazewnictwem. Dla sprostowania:

• CROSS JOIN oznacza iloczyn kartezjański,
• JOIN oznacza INNER JOIN,
• LEFT JOIN oznacza LEFT OUTER JOIN,
• RIGHT JOIN oznacza RIGHT OUTER JOIN,
• FULL JOIN oznacza FULL OUTER JOIN

Podsumowanie

Jak widać, podstawowe operacje na bazach danych są banalnie proste. Sama świadomość tego w jaki sposób działa SQL i operacje na bazach danych pomagają nam w zrozumieniu aplikacji oraz ataków na nie, takich jak SQL injection. O nich samych oraz narzędziach służących do ich wykonywania zajmiemy się w osobnym poście.

Źródła

https://www.w3schools.com/sql/sql_datatypes.asp
https://owasp.org/www-community/attacks/SQL_Injection
https://www.w3schools.com/sql/
https://www.samouczekprogramisty.pl/klauzula-join-w-zapytaniach-sql/