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 a v nej tabuľku používateľov.

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

Vloženie záznamu do tabuľky

Vloženie nového použí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 users a klikneme na New Record. Do tabuľky sa vložil nový záznam, ktorého údaje sú zatiaľ, okrem user_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 Johna Smitha by vyzeral takto:

INSERT INTO "users" (
    "first_name",
    "last_name",
    "birth_date",
    "article_count"
)
VALUES (
    'John',  'Smith',  '1984-11-03', 17
);

Prvý riadok je opäť jasný, jednoducho hovoríme "Vlož do použí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 použí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 použí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 `users` WHERE `user_id` = 2;

Príkaz je jednoduchý, voláme "vymaž z používateľov", kde sa hodnota v stĺpci user_id rovná 2. Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme ju aj v ďalších dotazoch. 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 "users" WHERE ("first_name" = 'John' AND "birth_date" >= '1980-1-1') OR ("article_count" < 3);

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

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

DELETE FROM "users";

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 `users`;
DELETE FROM SQLITE_SEQUENCE WHERE name = "users";

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 použí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 používateľovi (našej aplikácie) mazať používateľov podľa priezviska. Do dotazu vložíme teda nejakú premennú, ktorá pochádza od používateľa:

DELETE FROM "users" WHERE "last_name" = '$last_name';

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

Smith

Dotaz sa teda zostaví takto:

DELETE FROM "users" WHERE "last_name" = 'Smith';

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

' OR 1 --

Výsledný dotaz bude vyzerať takto:

DELETE FROM "users" WHERE "last_name" = '' OR 1 --';

Pretože 1 je z logického hľadiska vždy pravda a v podmienke je, že buď musí mať používateľ prázdne priezvisko alebo musí platiť pravda (čo platí), vymaže dotaz všetkých použí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í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 odescapovať. 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 "users" WHERE "last_name" = '\' OR 1 --';

Takýáto dotaz je neškodný, pretože časť vložená použí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 "users" WHERE "last_name" = ?;

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 používateľa by vyzerala asi takto:

UPDATE "users" SET "last_name" = 'Brown', "article_count" = "article_count" + 1 WHERE "user_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.