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

V minulej lekcii, SQLite - Vytvorenie databázy a tabuľky , sme si vytvorili databázu av nej tabuľku užívateľov.

Dnes budeme vkladať a mazať záznamy, teda užívateľov.

Vloženie záznamu do tabuľky

Vloženie nového užívateľa si ukážeme opäť najskôr cez DB Browser for SQLite. Najprv si otvoríme súbor s databázou. Potom prejdeme na panel Browse Data, uistíme sa, že nižšie máme vybranú správnu tabuľku používateľa a klikneme na New Record. Do tabuľky sa vložil nový záznam, ktorého údaje sú zatiaľ, okrem uzivateľa_id, ktorý sa vyplní vďaka AUTOINCREMENT samo, NULL. Po kliknutí na údaj ho môžeme zmeniť podľa ľubovôle.

Znovu pripomínam, že ak chceme naozaj zapísať zmeny do databázy, musíme kliknúť na Write Changes, čo môžeme preventívne urobiť práve teraz.

Ekvivalentný SQL dotaz pre pridanie Jana Nováka by vyzeral takto:

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

Prvý riadok je opäť jasný, jednoducho hovoríme "Vlož do užívateľov", ďalšie riadky sú stĺpce, v ktorých bude mať nová položka nejaké hodnoty. Stĺpec s id tu neuvádzame, ten sa vypĺňa sám. Nasleduje slovo values a ďalší výpočet prvkov v zátvorkách, tentoraz hodnôt. Tie idú v tom poradí, aké sme uviedli pri názvoch stĺpcov. Textové hodnoty sú v úvodzovkách alebo apostrofoch, všetky hodnoty oddeľujeme čiarkami.

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

Vložte si pomocou SQL dotazu pomocou panela Execute SQL niekoľko užívateľov, pokiaľ nemáte fantáziu, pokojne vložte tie z tabuľky na začiatku:

Vymazanie záznamu

Skúsme si niekoho vymazať. Asi by ste prišli na to, že sa to robí tým tlačidlom Delete Record. Skúste si to. Pokiaľ chceme vymazať záznamy z tabuľky pomocou SQL, máme k dispozícii príkazy DELETE FROM a TRUNCATE TABLE.

DELETE FROM

V jazyku SQL vyzerá odstránenie pomocou príkazu DELETE takto:

DELETE FROM `uzivatele` WHERE `uzivatele_id` = 2;

Príkaz je jednoduchý, voláme "vymaž z užívateľov", kde sa hodnota v stĺpci uzivatele_id rovná 2. Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme ju aj v ďalších otázkach. Keďže tu mažeme 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átvorkovať a používať operátory AND (a zároveň) a OR (alebo):

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

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

POZOR!, nikdy na klauzule WHERE nezabudnite, pokiaľ napíšete len:

DELETE FROM "uzivatele";

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

TRUNCATE TABLE

Príkaz TRUNCATE TABLE vymaže všetky záznamy. V SQLite, na rozdiel od iných variantov SQL, tento príkaz síce neexistuje, ale je veľmi ľahké ho nahradiť:

DELETE FROM `uzivatele`;
DELETE FROM SQLITE_SEQUENCE WHERE name = "uzivatele";

Pokiaľ použijeme DELETE FROM bez podmienky, SQLite automaticky použije TRUNCATE optimizer, teda TRUNCATE optimalizátor. Urobí teda príkaz TRUNCATE TABLE automaticky. Druhým riadkom vyresetujeme hodnotu primárneho kľúča, čo sa v iných variantoch SQL, kde príkaz TRUNCATE TABLE existuje, deje automaticky.

Prečo si teda pamätať príkaz TRUNCATE TABLE (pre iné varianty SQL), keď funguje v podstate rovnako ako DELETE FROM bez použitia podmienky? Príkaz TRUNCATE TABLE oproti DELETE FROM:

• je rýchlejší,
• nevyžaduje oprávnenie DELETE pre tabuľku,
• nespúšťa Triggery (čo sa občas môže hodiť),
• vyresetuje AUTO INCREMENT hodnotu späť na počiatočnú hodnotu (pri použití DELETE FROM sa pokračuje ďalšou hodnotou v poradí).

SQL injection

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

Rozhodol som sa túto pasáž vložiť hneď na začiatok seriálu. Pokiaľ 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 u príslušného jazyka.

Čo je SQL injecton

Predstavme si, že naša tabuľka s použí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ľov podľa priezviska. Do dotazu vložíme teda nejakú premennú, ktorá pochádza od užívateľa:

DELETE FROM "uzivatele" WHERE "prijmeni" = '$prijmeni';

$priezvisko je premenná, obsahujúca napríklad tento text:

Novák

Otázka sa teda zostaví takto:

DELETE FROM "uzivatele" WHERE "prijmeni" = 'Novák';

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

' OR 1 --

Výsledná otázka bude vyzerať takto:

DELETE FROM "uzivatele" WHERE "prijmeni" = '' OR 1 --';

Pretože 1 je z logického hľadiska vždy pravda av 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 otázke zruší všetko do konca riadku. Šikovnejší útočníci dokážu urobiť injekciu v ktoromkoľvek SQL príkaze, 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. Pokiaľ tieto znaky potrebujeme, musíme ich tzv. odescapovať, 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 najskôr odcapiť. Určite si to zistite, kým začnete s databázou pracovať. Pokiaľ budete používať tunajšie návody, bude to v nich vždy uvedené.

Odescapovaný dotaz by vyzeral takto:

DELETE FROM "uzivatele" WHERE "prijmeni" = '\' OR 1 --';

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

DELETE FROM "uzivatele" WHERE "prijmeni" = ?;

A premenné sa pošlú databáze potom zvlášť a naraz. Ona si ich tam sama navkladá 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 pri jazyku, z ktorého budete s databázou komunikovať (napr. v sekcii PHP, Python).

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ácia a vyhľadávanie. Vyhľadávanie venujeme celý budúci článok, editáciu si vysvetlíme ešte dnes.

Na editáciu v DB Browseri už sme vlastne narazili, stačí kliknúť na nejakú hodnotu a prepísať ju. Na úpravu slúži SQL dotaz UPDATE, úprava nejakého užívateľa by vyzerala asi takto:

UPDATE "uzivatele" SET "prijmeni" = 'Dolejší', "pocet_clanku" = "pocet_clanku" + 1 WHERE "uzivatele_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 viacerých stĺpcov, iba sa oddelia čiarkou. Môžeme dokonca použiť predchádzajúcu hodnotu z databázy a treba ju zvýšiť o 1, ako v ukážke vyššie.

V ďalšej lekcii, SQLite - Výber dát (vyhľadávanie) , si ukážeme sľúbené vyhľadávanie.