Lekce 3 - PostgreSQL - Vkládání a mazání dat v tabulce

V minulé lekci, PostgreSQL - Vytvoření databáze a tabulky, jsme si vytvořili databázi a v ní tabulku uživatelů.

V dnešním PostgreSQL tutoriálu budeme vkládat a mazat záznamy, tedy uživatele.

Vložení záznamu do tabulky

Vložení nového uživatele si ukážeme opět nejprve přes pgAdmin. Klikneme pravým tlačítkem na naši tabulku uzivatel, zvolíme View/Edit Data a necháme si zobrazit všechny záznamy pomocí All Rows:

V okně Data Output vyplníme všechna pole až na uzivatel_id. To ponecháme prázdné, jelikož se vyplní samo díky vlastnosti identity. Obsah polí vyplníme jak potřebujeme a úpravy potvrdíme tlačítkem Save Data Changes (ikona databáze se zámkem):

pgAdmin nás následně informuje o úspěšnosti úpravy a doplní automaticky ID:

Vkládat záznamy jde samozřejmě i pomocí SQL. Tento postup si ukážeme na vložení několika uživatelů najednou, pokud nemáte fantazii, klidně vložte ty z tabulky na začátku.

Otevřeme si Query Editor (pravým tlačítkem kliknout na databázi nebo tabulku a zvolit Query Tool). Do okna Query Editor napíšeme příkaz pro vložení záznamů:

INSERT INTO uzivatel
    (jmeno, prijmeni, datum_narozeni, pocet_clanku)
VALUES
    ('Tomáš',  'Marný',  '1989-02-03', 6),
    ('Josef',  'Nový',  '1972-12-20', 9),
    ('Michaela',  'Slavíková',  '1990-08-14', 1);

Příkaz se provede tak, že kliknete na tlačítko Execute/Refresh (ikona šipky) v horní liště:

Výše uvedený příkaz vloží hodnoty do sloupců jmeno, prijmeni, datum_narozeni a pocet_clanku. Hodnotu do sloupce uzivatel_id vkládat nemusíme, protože se vloží automaticky (díky vlastnosti identity).

Pokud vkládáme do SQL dotazu text (zde třeba jméno uživatele), nesmí obsahovat uvozovky, apostrofy a pár dalších znaků. Tyto znaky samozřejmě do textu zapsat můžeme, jen se musí ošetřit, aby si databáze nemyslela, že jde o část dotazu. Ještě se k tomu vrátíme.

Nyní zkontrolujeme, že se záznamy skutečně vložily. Necháme si zobrazit všechny záznamy pomocí View/Edit Data:

Jestliže jste záložku s tabulkou měli stále otevřenou a nové záznamy se v ní neobjevily, tak ji stačí přenačíst pomocí tlačítka Execute/Refresh.

Vymazání záznamu

Pokud chceme vymazat záznamy z tabulky pomocí SQL, máme k dispozici příkazy DELETE FROM a TRUNCATE TABLE.

DELETE FROM

V jazyce SQL vypadá odstranění pomocí příkazu DELETE takto:

DELETE FROM uzivatel WHERE uzivatel_id = 2;

Výsledek:

uzivatel_id	jmeno	prijmeni	datum_narozeni	pocet_clanku
1	Jan	Novák	1984-03-11	17
3	Josef	Nový	1972-12-20	9
4	Michaela	Slavíková	1990-08-14	1

Příkaz je jednoduchý, voláme "vymaž z uživatelů", kde se hodnota ve sloupci uzivatel_id rovná 2. Zaměřme se na klauzuli WHERE, která definuje podmínku. Potkáme ji i v dalších dotazech. Jelikož zde mažeme podle primárního klíče, jsme si jisti, že vždy vymažeme právě jednoho uživatele. Podmínku samozřejmě můžeme rozvinout, závorkovat a používat operátory AND (a zároveň) a OR (nebo):

DELETE FROM uzivatel WHERE (jmeno = 'Jan' AND datum_narozeni >= '1980-1-1') OR (pocet_clanku < 3);

Výsledek:

uzivatel_id	jmeno	prijmeni	datum_narozeni	pocet_clanku
3	Josef	Nový	1972-12-20	9

Příkaz výše vymaže všechny Jany, kteří byli narození po roce 1980 nebo všechny uživatele, kteří napsali méně než 3 články.

POZOR!, nikdy na klauzuli WHERE nezapomeňte, pokud napíšete jen:

DELETE FROM uzivatel;

Budou vymazáni všichni uživatelé v tabulce! Pokud ale chceme docílit vymazání všech záznamů z tabulky použijeme příkaz TRUNCATE TABLE.

TRUNCATE TABLE

Příkaz TRUNCATE TABLE vymaže všechny záznamy. V SQL se celý příkaz zapíše takto:

TRUNCATE TABLE uzivatel;

Proč si tedy pamatovat příkaz TRUNCATE TABLE, když funguje v podstatě stejně jako DELETE TABLE bez použití podmínky? Příkaz TRUNCATE TABLE oproti DELETE FROM:

• je rychlejší,
• nevyžaduje oprávnění DELETE pro tabulku,
• nespouští Triggery (což se občas může hodit).

SQL injection

SQL injection je termín, označující narušení databázového dotazu škodlivým kódem od uživatele.

Rozhodli jsme se tuto pasáž vložit hned na začátek seriálu. Pokud vás nějak zmate, tak si z toho nic nedělejte. Hlavní je o riziku vědět. Bezpečnou práci s databází si ukážeme vždy u příslušného jazyka.

Co je SQL injection

Představme si, že naše tabulka s uživateli je součástí databáze nějaké aplikace. A také, že umožníme uživateli (naší aplikace) mazat uživatele podle příjmení. Do dotazu vložíme tedy nějakou proměnnou, která pochází od uživatele:

DELETE FROM uzivatel WHERE prijmeni = '$prijmeni';

$prijmeni je proměnná, obsahující třeba tento text:

Novák

Dotaz se tedy sestaví takto:

DELETE FROM uzivatel WHERE prijmeni = 'Novák';

Dotaz se provede a vymaže všechny Nováky. To zní jako to, co jsme chtěli. Teď si ale představme, co se stane, když někdo do proměnné zadá toto:

' OR true --

Výsledný dotaz bude vypadat takto:

DELETE FROM uzivatel WHERE prijmeni = '' OR true --';

Protože true je pravda a v podmínce je, že buď musí mít uživatel prázdné příjmení nebo musí platit pravda (což platí), vymaže dotaz všechny uživatele v tabulce. Poslední uvozovky se útočník zbavil komentářem (dvě pomlčky), který v dotazu zruší vše do konce řádku.

Šikovnější útočníci dokáží udělat injekci v kterémkoli SQL příkazu, nejen v DELETE.

Řešení

Nebojte, řešení je velmi jednoduché. Problém dělá několik speciálních znaků v proměnné, jako jsou uvozovky a několik dalších. Pokud tyto znaky potřebujeme, musíme je tzv. odescapovat, tedy předsadit zpětným lomítkem. V aplikaci to za nás nějakým způsobem řeší ovladač databáze, buď to dělá úplně sám nebo data musíme pomocí něj před vložením do dotazu nejprve odescapovat. Určitě si to zjistěte, než začnete s databází pracovat. Pokud budete používat zdejší návody, bude to v nich vždy uvedeno.

Odescapovaný dotaz by vypadal takto (jednoduchá uvozovka se escapuje zdvojením):

DELETE FROM uzivatel WHERE prijmeni = ''' OR 1 --';

Takový dotaz je neškodný, protože část vložená uživatelem je považována jako text. V textu se nevyhodnotí uvozovka a tím pádem ani komentář. Další variantou, jak aplikaci zabezpečit proti injekci, je obsah proměnné do dotazu vůbec nezadávat. V dotazu jsou poté uvedeny pouze zástupné znaky (otazníky):

DELETE FROM uzivatel WHERE prijmeni = ?;

A proměnné se pošlou databázi potom zvlášť a najednou. Ona si je tam sama navkládá tak, aby nevzniklo žádné nebezpečí. To je však teorie okolo konkrétního ovladače databáze a jak bylo řečeno, nalezneme ji u jazyka, ze kterého budeme s databází komunikovat (např. v sekci PHP či Javě).

Editace záznamů

Databáze umožňuje 4 základní operace, které jsou často označovány zkratkou CRUD (Create, Read, Update, Delete). Jsou to tedy vytvoření záznamu, načtení (vyhledání), úprava (editace) a vymazání záznamu. Vytvoření a vymazání již umíme. Chybí nám tedy ještě editace a vyhledávání. Vyhledávání věnujeme celý příští článek, editaci si vysvětlíme ještě dnes.

K úpravě slouží SQL dotaz UPDATE, úprava nějakého uživatele by vypadala asi takto:

UPDATE uzivatel SET
    prijmeni = 'Dolejší',
    pocet_clanku = pocet_clanku + 1
WHERE uzivatel_id = 1;

Za klíčovým slovem UPDATE následuje název tabulky, poté slovo SET a vždy název sloupce = hodnota. Můžeme měnit hodnoty více sloupců, pouze je oddělíme čárkou. Můžeme dokonce použít předchozí hodnotu z databáze a třeba ji zvýšit o 1, jako v ukázce výše.

Upravovat záznamy je ale možné i v okně View/Edit Data, kde stačí pouze změnit nějakou hodnotu v tabulce a potvrdit tlačítkem Save Data Changes:

V další lekci, PostgreSQL - Export, si ukážeme různé typy exportů databáze.