Lekce 3 - Oracle krok za krokem: Vkládání a mazání dat v tabulce

V minulé lekci, Oracle krok za krokem: Vytvoření schématu a tabulky, jsme si ukázali, jak vytvořit schéma a tabulku.

Dnes budeme vkládat a mazat záznamy, tedy uživatele.

Před započetím samotné lekce bych vás chtěl informovat, že zde budeme, co se týče datumu atp., pracovat s českou lokalizací pro SQL Developer. Pokud je např. váš operační systém v angličtině, pro tento kurz vám doporučuji si v SQL Developeru změnit preference na české. Je to možné skrze menu Tools -> Preferences -> Database -> NLS. Ideálně byste měli mít vše stejně, jako na obrázku níže:

Pokud máme hotovo, přejdeme nyní k obsahu samotné lekce:

Vložení záznamu do tabulky

Vložení nového uživatele si ukážeme opět nejprve přes SQL Worksheet. Rozklikneme si tabulku UZIVATELE. Zobrazí se nám přehled naší tabulky. Z horní lišty přehledu tabulky poté vybereme položku Data a klikneme na tlačítko Insert Row (třetí zleva) nebo využijeme klávesovou zkratku CTRL+I. Do tabulky se nám nyní vložil prázdný řádek (záznam). Následně vyplníme data záznamu, přičemž musíme myslet na to, o jaký datový typ se jedná:

Pozn.: Vkládání ID manuálně není příliš praktické, ale pro tuto lekci si s tím vystačíme. Později v kurzu si ukážeme, jak nastavit, aby se nám ID záznamů vkládalo a navyšovalo automaticky.

Nyní už pouze stačí kliknout na tlačítko Commit Changes (páté zleva), nebo stisknout klávesu F11, čímž potvrdíme vložení záznamu do tabulky. Pokud jsme všechny hodnoty zadali správně, záznam se přidá do tabulky, a SQL Developer nám tuto skutečnost oznámí v logu:

Rovnou si můžeme všimnout, že jsme využili další SQL dotaz - INSERT. Vložit Pavla Novotného bychom mohli stejně i tímto dotazem:

INSERT INTO UZIVATELE (
    UZIVATELE_ID,
    JMENO,
    PRIJMENI,
    DATUM_NAROZENI,
    POCET_CLANKU
)
VALUES (
    1, 'Pavel',  'Novotný',  '1.1.1999', 0
);

První řádek je opět jasný, prostě říkáme "Vlož do uživatelů", další řádky jsou sloupce, ve kterých bude mít nová položka nějaké hodnoty. Následuje klauzule VALUES a další výčet prvků v závorkách, tentokrát hodnot. Ty jdou v tom pořadí, jaké jsme uvedli u názvů sloupců. Textové hodnoty jsou v uvozovkách nebo apostrofech, všechny hodnoty oddělujeme čárkami.

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í si pomocí SQL dotazu vložíme několik uživatelů najednou. Využijeme k tomu INSERT ALL. SQL dotaz bude vypadat třeba takto:

INSERT ALL
INTO UZIVATELE (
    UZIVATELE_ID, JMENO, PRIJMENI, DATUM_NAROZENI, POCET_CLANKU
)
VALUES (
    2, 'Karel',  'Novák',  '25.6.1979', 10
)
INTO UZIVATELE (
    UZIVATELE_ID, JMENO, PRIJMENI, DATUM_NAROZENI, POCET_CLANKU
)
VALUES (
    3, 'Adam',  'Mladý',  '12.8.2001', 2
)
INTO UZIVATELE (
    UZIVATELE_ID, JMENO, PRIJMENI, DATUM_NAROZENI, POCET_CLANKU
)
VALUES (
    4, 'Petr',  'Starý',  '13.4.1992', 18
)
SELECT 1 FROM DUAL;

DUAL je integrovaný vztah v Oracle, který slouží jako fiktivní relace pro vložení do klauzule FROM, když nic jiného není vhodné, nyní toto více rozebírat nebudeme, ať se zbytečně nepleteme Řekneme si pouze, že dotaz INSERT ALL vyžaduje poddotaz SELECT, který mu právě tímto řádkem poskytujeme.

Záznamy o uživatelích v tabulce UZIVATELE budou tedy vypadat takto:

Vymazání záznamu

Zkusme si někoho vymazat. V přehledu tabulky zvolíme záznam, který chceme odstranit a na liště klikneme na červený křížek. Poté změnu v tabulce opět musíme potvrdit kliknutím na tlačítko Commit Changes. 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 UZIVATELE WHERE UZIVATELE_ID = 4;

Příkaz je jednoduchý, voláme "vymaž z uživatelů", kde se hodnota ve sloupci UZIVATELE_ID rovná 4. 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 UZIVATELE WHERE (JMENO = 'Pavel' AND DATUM_NAROZENI >= '1.1.1980') OR (POCET_CLANKU > 5);

Příkaz výše vymaže všechny Pavly, kteří byli narození po roce 1980 nebo všechny uživatele, kteří napsali více než 5 článků.

POZOR!, nikdy na klauzuli WHERE nezapomeňte. Pokud napíšete jen DELETE FROM UZIVATELE; 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 UZIVATELE;

Narozdíl od jiných SŘBD se nás Oracle nebude ptát a příkaz TRUNCATE TABLE rovnou provede. Dávejte si tedy pozor, kdy ho použijete, neboť jeho použití je nevratné

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ší,
• narozdíl od DELETE musí mít uživatel, který ho chce provést oprávnění DROP ANY TABLE,
• nespouští Triggery (což se občas může hodit),
• vyresetuje AUTO INCREMENT hodnotu zpět na počáteční hodnotu (při použití DELETE FROM se pokračuje další hodnotou v pořadí).

SQL injection

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

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 UZIVATELE WHERE PRIJMENI = $prijmeni;

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

Novák

Dotaz se tedy sestaví takto:

DELETE FROM UZIVATELE 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 1 --

Výsledný dotaz bude vypadat takto:

DELETE FROM UZIVATELE WHERE PRIJMENI = '' OR 1 --';

Protože 1 je z logického hlediska vždy 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:

DELETE FROM UZIVATELE 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 UZIVATELE 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).

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.

Editace záznamu skrze SQL Worksheet je velice jednoduchá, jednoduše dvakrát klikneme na údaj, který chceme upravit a napíšeme novou hodnotu. Změnu poté opět nesmíme zapomenout potvrdit tlačítkem Commit Changes.

UPDATE UZIVATELE SET PRIJMENI = 'Dolejší', POCET_CLANKU  = POCET_CLANKU + 1 WHERE UZIVATELE_ID = 3;

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.

V příští lekci, Oracle krok za krokem: Export, si ukážeme různé typy exportů databáze.