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

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

V minulém dílu seriálu tutoriálů o PostgreSQL databázi jsme si vytvořili databázi a v ní tabulku uživatelů. Dnes budeme vkládat a mazat záznamy, tedy uživatele.

Vložení záznamu do tabulky

Vložení záznamu provedeme pomocí SQL. Otevřete si okno Query (přihlásit se do pgAdmin, vybrat databázi moje_databáze, kliknout na tlačítko SQL v horní liště pgAdmin). Do horního levého okna vložte příkaz pro vložení záznamu.

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

Příkaz se provede tak, že kliknete na ikonu zelené šipky (Execute query) v horní liště.

Spuštění dotazu INSERT v pgAdmin

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

POZOR! 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áznam skutečně vložil. Nejdříve si aktualizujte tabulku (Tables -> pravé tlačítko myši -> Refresh).

Aktualizace PostgreSQL tabulky v pgAdmin

Poté klikněte na tabulku a zobrazte si všechny záznamy (pravé tlačítko myši -> View Data -> View All Rows). Stejného výsledku dosáhnete označením tabulky a použitím klávesové zkratky Ctrl+D.

Zobrazení všech záznamů v tabulce v pgAdmin

Vložte si pomocí SQL dotazu několik uživatelů, pokud nemáte fantazii, klidně vložte ty z tabulky na začátku. PostgreSQL umožňuje vkládání i více záznamů najednou.

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);
Vkládání uživatelů do databáze

Vymazání záznamu

Vymazání se v jazyce SQL dělá pomocí příkazu DELETE. Zároveň je potřeba specifikovat id záznamu, který chceme vymazat.

DELETE FROM uzivatel WHERE uzivatel_id = 2;
Odstranění záznamu v pgAdmin

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);
Příkaz se složitější podmínkou

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!

Prázdná databáze

SQL injection

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

Rozhodl jsem 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, stejně si bezpečnou práci s databází ukážeme vždy u příslušného jazyka.

Co je SQL injecton

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ředstavte, 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, naleznete ji u jazyka, ze kterého budete 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í), update (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;
Záznam před editací
Záznam po editaci

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 se oddělí čá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.

Příště půjdeme na slíbené vyhledávání.


 

  Aktivity (1)

Článek pro vás napsal vita
Avatar
vita

Jak se ti líbí článek?
Celkem (4 hlasů) :
3.753.753.753.75 3.75


 



 

 

Komentáře

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zatím nikdo nevložil komentář - buď první!