3. diel - PostgreSQL - Vkladanie a mazanie dát v tabuľke

V minulom dieli seriálu tutoriálov o PostgreSQL databáze sme si vytvorili databázu a v nej tabuľku užívateľov. Dnes budeme vkladať a mazať záznamy, teda používateľa.

Vloženie zápisu do tabuľky

Vloženie záznamu vykonáme pomocou SQL. Otvorte si okno Query (prihlásiť sa do pgAdmin, vybrať databázu moje_databáze, kliknúť na tlačidlo SQL v hornej lište pgAdmin). Do horného ľavého okna vložte príkaz pre vloženie zápisu.

INSERT INTO uzivatel
    (jmeno, prijmeni, datum_narozeni, pocet_clanku)
VALUES
    ('Jan',  'Novák',  '1984-03-11', 17);

Príkaz sa vykoná tak, že kliknete na ikonu zelenej šípky (Execute query) v hornej lište.

Vyššie uvedený príkaz vloží hodnoty do stĺpcov mena, priezviska, datum_narozeni a pocet_clanku. Hodnotu do stĺpca uzivatele_id vkladať nemusíme, pretože sa vloží automaticky (vďaka tomu, že má typ serial).

POZOR! Pokiaľ vkladáme do SQL dotazu text (tu treba meno používateľa), nesmie obsahovať úvodzovky, apostrofy a pár ďalších znakov. Tieto znaky samozrejme do textu zapísať môžeme, len sa musí ošetriť, aby si databázy nemyslela, že ide o časť dotazu. Ešte sa k tomu vrátime.

Teraz skontrolujeme, že sa záznam skutočne vložil. Najskôr si aktualizujte tabuľku (Tables -> pravé tlačidlo myši -> Refresh).

Potom kliknite na tabuľku a prezrite si všetky záznamy (pravé tlačidlo myši -> View Data -> View All Rows). Rovnaký výsledok dosiahnete označením tabuľky a použitím klávesovej skratky Ctrl + D.

Vložte si pomocou SQL dotazu niekoľko užívateľov, ak nemáte fantáziu, pokojne vložte tých z tabuľky na začiatku. PostgreSQL umožňuje vkladanie aj viac záznamov naraz.

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);

Vymazanie záznamu

Vymazanie sa v jazyku SQL robí pomocou príkazu DELETE. Zároveň je potreba špecifikovať id záznamu, ktorý chceme vymazať.

DELETE FROM uzivatel WHERE uzivatel_id = 2;

Príkaz je jednoduchý, voláme "vymaž z užívateľov", kde sa hodnota v stĺpci uzivatel_id rovná 2. Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme ju aj v ďalších otázkach. Keďže tu natierame podľa primárneho kľúča, sme si istí, že vždy vymažeme práve jedného používateľa. Podmienku samozrejme môžeme rozvinúť, závorkovat a používať operátormi AND (a zároveň) a OR (alebo):

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

Príkaz vyššie vymaže všetky Jany, ktorí boli narodení po roku 1980 alebo všetkých užívateľov, ktorí napísali menej ako 3 články.

POZOR!, Nikdy na klauzulu WHERE nezabudnite, ak napíšete len:

DELETE FROM uzivatel;

Budú vymazaní všetci užívatelia v tabuľke!

SQL injection

SQL injection je termín, označujúci narušenie databázového dopytu škodlivým kódom od užívateľa.

Rozhodol som sa túto pasáž vložiť hneď na začiatok seriálu. Ak vás nejako zmätie, tak si z toho nič nerobte, hlavné je o riziku vedieť, rovnako si bezpečnú prácu s databázou ukážeme vždy pri príslušnom jazyka.

Čo je SQL injecton

Predstavme si, že naša tabuľka s užívateľmi je súčasťou databázy nejakej aplikácie. A tiež, že umožníme užívateľovi (našej aplikácie) mazať užívateľa podľa priezviska. Do dotazu vložíme teda nejakú premennú, ktorá pochádza od užívateľa:

DELETE FROM uzivatel WHERE prijmeni = '$prijmeni';

$ Priezvisko je premenná, obsahujúci potrebné tento text:

Novák

Otázka sa teda utvorí takto:

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

Dotaz sa vykoná a vymaže všetky Nováky. To znie ako to, čo sme chceli. Teraz si ale predstavte, čo sa stane, keď niekto do premennej zadá toto:

' OR true --

Výsledný dotaz bude vyzerať takto:

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

Pretože true je pravda a v podmienke je, že buď musí mať užívateľ prázdne priezvisko alebo musí platiť pravda (čo platí), vymaže dotaz všetkých užívateľov v tabuľke. Posledný úvodzovky sa útočník zbavil komentárom (dve pomlčky), ktorý v dotaze zruší všetko do konca riadku. Šikovnejší útočníci dokážu urobiť injekciu v ktoromkoľvek SQL príkazu, nielen v DELETE.

Riešenie

Nebojte, riešenie je veľmi jednoduché. Problém robí niekoľko špeciálnych znakov v premennej, ako sú úvodzovky a niekoľko ďalších. Ak tieto znaky potrebujeme, musíme ich tzv. Odescapovat, teda predsadiť spätným lomítkom. V aplikácii to za nás nejakým spôsobom rieši ovládač databázy, buď to robí úplne sám alebo dáta musíme pomocou neho pred vložením do dotazu najprv odescapovat. Určite si to zistite, ako začnete s databázou pracovať. Ak budete používať tunajšie návody, bude to v nich vždy uvedené.

Odescapovaný dotaz by vyzeral takto (jednoduchá úvodzovky sa escapuje zdvojením):

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

Takýto dotaz je neškodný, pretože časť vložená užívateľom je považovaná ako text. V texte sa nevyhodnotí úvodzovky a tým pádom ani komentár. Ďalším variantom, ako aplikáciu zabezpečiť proti injekciu, je obsah premennej do dotazu vôbec nezadávať. V dotaze sú potom uvedené iba zástupné znaky (otázniky):

DELETE FROM uzivatel WHERE prijmeni = ?;

A premenné sa pošlú databázu potom zvlášť a naraz. Ona si ich tam sama navkládá tak, aby nevzniklo žiadne nebezpečenstvo. To je však teória okolo konkrétneho ovládača databázy a ako bolo povedané, nájdete ju u jazyka, z ktorého budete s databázou komunikovať (napr. V sekcii PHP či Jave).

Editácia záznamov

Databáza umožňuje 4 základné operácie, ktoré sú často označované skratkou CRUD (Create, Read, Update, Delete). Sú to teda vytvorenie záznamu, načítanie (vyhľadanie), update (editácia) a vymazanie záznamu. Vytvorenie a vymazanie už vieme. Chýba nám teda ešte editácie a vyhľadávania. Vyhľadávanie venujeme celý budúci článok, editáciu si vysvetlíme ešte dnes.

K úprave slúži SQL dotaz UPDATE, úprava nejakého užívateľa by vyzerala asi takto:

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

Za kľúčovým slovom UPDATE nasleduje názov tabuľky, potom slovo SET a vždy názov stĺpca = hodnota. Môžeme meniť hodnoty viac stĺpcov, iba sa oddelí čiarkou. Môžeme dokonca použiť predchádzajúca hodnotu z databázy a treba ju zvýšiť o 1, ako v ukážke vyššie.

Nabudúce pôjdeme na sľúbené vyhľadávanie.