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

V predchádzajúcej 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 ikonu Insert a new record in the current table. Do tabuľky sa vložil nový záznam, ktorého údaje obsahujú hodnoty NULL, okrem user_id, ktorý sa vyplní vďaka AUTOINCREMENT samo. Po kliknutí na ľubovoľnú bunku v stĺpci môžeme jej hodnotu zmeniť:

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 dopyt na 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, teraz 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.

Ak vkladáme do SQL dopytu 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ť dopytu. Ešte sa k tomu vrátime.

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

Vymazanie záznamu

Skúsme si niekoho vymazať. Asi ste prišli na to, že sa to robí tlačidlom Delete the current record. Skúste si to. Ak 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 sa s ňou aj v ďalších dopytoch. 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.

Nikdy na klauzulu WHERE nezabudnite! Ak napíšete len DELETE FROM "users";, budú vymazaní všetci používatelia v tabuľke!

TRUNCATE TABLE

V niektorých databázach existuje príkaz TRUNCATE TABLE, ktorý veľmi rýchlo zmaže všetky záznamy v tabuľke a zároveň resetuje počítadlo pre primárny kľúč. V SQLite však tento príkaz nenájdeme.

Ak chceme tabuľku úplne vyprázdniť, stačí nám napísať:

DELETE FROM `users`;

Keď tento príkaz použijeme bez podmienky a tabuľka nemá žiadne triggery (udalosti, ktoré sa spúšťajú pri mazaní), SQLite si to vo vnútri optimalizuje tak, že dáta zmaže "naraz". Je to teda podobne rýchle, ako keby sme mali TRUNCATE.

Ak však tabuľka triggery má, riadky sa budú mazať postupne a triggery sa spustia.

Je dobré vedieť, že po tomto mazaní sa v SQLite automaticky neobnoví počítadlo pri stĺpci INTEGER PRIMARY KEY AUTOINCREMENT (pri použití DELETE FROM sa pokračuje ďalšou hodnotou v poradí).

Keď chceme, aby sa po vyprázdnení tabuľky nové záznamy začali číslovať od 1, musíme ešte vymazať záznam v internej tabuľke sqlite_sequence:

DELETE FROM sqlite_sequence WHERE name = 'users';

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. 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 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 dopytu teda vložíme 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

Dopyt sa teda zostaví takto:

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

Dopyt 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ý dopyt 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í), takže vymaže dopyt všetkých používateľov v tabuľke. Poslednej úvodzovky sa útočník zbavil komentárom (dve pomlčky), ktorý v dopyte 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. Ak tieto znaky potrebujeme, musíme ich tzv. odescapovať, teda predsadiť spätnou lomkou. 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 dopytu najskôr odescapovať. Určite si to zistite, kým začnete s databázou pracovať. Ak budete používať tunajšie návody, bude to v nich vždy uvedené.

Odescapovaný dopyt by vyzeral takto:

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

Takýto dopyt je neškodný, pretože časť vložená používateľom je považovaná za 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 dopytu vôbec nezadávať. V dopyte 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ávaniu 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 dopyt 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, ale treba ju zvýšiť o 1, ako v ukážke vyššie.

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