3. diel - MySQL krok za krokom - Vkladanie a mazanie dát v tabuľke

V minulej lekcii, MySQL krok za krokom - Vytvorenie databázy a tabuľky, sme si vytvorili databázu a v nej tabuľku užívateľov.

V tomto MySQL tutoriále si do tabuľky užívateľov vložíme prvé záznamy. Potom si ukážeme, ako vybraný záznam zmazať alebo editovať.

Vloženie záznamu do tabuľky

Vloženie nového užívateľa si ukážeme opäť najskôr cez phpMyAdmin.

Pridanie užívateľa v phpMyAdmin

Rozklikneme si tabuľku users. V hornej lište vyberieme možnosť Insert. V novom okne vyplníme iba pole v stĺpci Value. Pole user_id ponecháme prázdne, vyplní sa samo vďaka vlastnosti AUTO_INCREMENT, ktorú sme mu predtým nastavili. Obsah polí vyplníme, ako potrebujeme a potvrdíme tlačidlom Go:

phpMyAdmin nám potom ukáže ďalší SQL dotaz, tým je INSERT. Vložiť Johna by sme mohli rovnako aj touto otázkou:

INSERT INTO `users` (
    `user_id`,
    `first_name`,
    `last_name`,
    `birthdate`,
    `article_count`
)
VALUES (NULL, 'John', 'Smith', '1984/3/11', 17);

Prvý riadok je jasný, jednoducho hovoríme "Vlož do užívateľov", na ďalších riadkoch uvádzame stĺpce, v ktorých bude mať nová položka nejaké hodnoty. Stĺpec user_id tu uvádzať nemusíme. Nasleduje slovo VALUES a ďalší zoznam prvkov v zátvorkách. Tentokrát ide o hodnoty, ktoré do tabuľky chceme pridať. Tie sú uvedené v rovnakom poradí, ako názvy stĺpcov vyššie. Textové hodnoty sú v úvodzovkách alebo apostrofoch, všetky hodnoty oddeľujeme čiarkami.

Pokiaľ vkladáme do SQL dotazu text obsahujúci úvodzovky alebo apostrofy (napríklad O'Connor) a pár ďalších znakov, musíme ho ošetriť, aby databáza vedela, že ide o časť textu. V opačnom prípade by takýto znak považovala za ukončenie reťazca a dotaz by vyhodnotila ako chybný. Ešte sa k tomu vrátime.

Teraz si tabuľku znova rozklikneme a vidíme, že John Smith je skutočne uložený v databáze:

Pridanie užívateľa SQL dotazom

Poďme si pridať do našej tabuľky ďalších užívateľov. Tentoraz si vyskúšame napísať vlastnú SQL otázku. Do tabuľky ním vložíme ďalších troch užívateľov:

First name	Last name	Date of birth	Number of articles
John	Smith	1984/3/11	17
Thomas	Brown	1989/2/1	6
Jack	Newman	1972/12/20	9
Mary	Emmerson	1990/8/14	1

V dotaze môžeme uviesť hodnoty pre všetkých troch užívateľov. Výsledný SQL dotaz bude vyzerať takto:

INSERT INTO `users` (
    `user_id`,
    `first_name`,
    `last_name`,
    `birthdate`,
    `article_count`
)
VALUES (NULL, 'Thomas', 'Brown', '1989/2/1', 6),
       (NULL, 'Jack', 'Newman', '1972/12/20', 9),
       (NULL, 'Mary', 'Emmerson', '1990/8/14', 1).

Tabuľka s novo pridanými užívateľmi bude v phpMyAdmin vyzerať takto:

Vymazanie záznamov

Skúsme si aj niekoho vymazať. V phpMyAdmin jednoducho klikneme na červené tlačidlo Delete. Otvorí sa nám dialógové okno, v ktorom máme potvrdiť zmazanie záznamu. V ňom je tiež uvedený zodpovedajúci SQL dotaz:

Príkaz DELETE FROM

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

DELETE FROM `users` WHERE `user_id` = 1;

Skúsme si ešte zmazať Mary Emmerson, phpMyAdmin je na mazanie opatrný, vykonanie príkazu budeme musieť v ďalšom okne znova potvrdiť.

Príkaz je jednoduchý, voláme "vymaž z užívateľov", kde sa hodnota user_id rovná 1.

Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme ju aj v ďalších príkazoch. Keďže tu mažeme podľa primárneho kľúča, sme si istí, že vždy vymažeme práve jedného uží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` = 'Mary' AND `birthdate` >= '1980-1-1') OR (`article_count` < 3);

Taký príkaz vyššie vymaže všetky Mary, ktoré boli narodené po roku 1980 alebo všetkých užívateľov, ktorí napísali menej ako tri člankov.

Pri príkaze DELETE nikdy nesmieme klauzulu WHERE zabudnúť uviesť! Pokiaľ napíšeme len DELETE FROM 'users';, budú vymazaní všetci používatelia v tabuľke.

Príkaz TRUNCATE TABLE

Niekedy však môžeme zámerne chcieť vymazať všetky údaje našej tabuľky. V takom prípade je však lepšie použiť príkaz TRUNCATE TABLE, ktorý tiež vymaže všetky záznamy. V SQL sa zapíše takto:

TRUNCATE TABLE `users`;

Rovnako ako pri príkaze DELETE, aj príkaz TRUNCATE si phpMyAdmin stráži a pre istotu sa nás pred vykonaním spýta.

Prečo si teda pamätať príkaz TRUNCATE TABLE, 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 späť na počiatočnú hodnotu.

Čo sú to trigerry sa dozvieme neskôr v lekcii MySQL krok za krokom - Triggery.

SQL injection

SQL injection je termín, označujúci narušenie databázového dotazu škodlivým kódom od užívateľa. Spomíname ju hneď na začiatok seriálu, aby sme o možnom riziku vedeli. Ako bezpečne pracovať s databázou si ukážeme neskôr vždy pri príslušnom jazyku.

Príklad na SQL injection

Predstavme si, že naša tabuľka s používateľmi je súčasťou databázy nejakej aplikácie. V nej 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 `users` WHERE `last_name` = '$last_name';

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

Brown

Dotaz sa teda zostaví takto:

DELETE FROM `users` WHERE `last_name` = 'Brown';

Dotaz sa vykoná a vymaže všetky Browny. To znie ako to, čo sme chceli. Teraz si ale predstavme, č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 --';

V podmienke teraz je, že buď musí mať používateľ prázdne priezvisko alebo musí platiť, že 1 je pravda (čo platí). Tento dotaz teda vymaže všetkých užívateľov v našej tabuľke. Posledné úvodzovky sa útočník zbavil komentárom. Ten v SQL uvádzajú dve pomlčky a zrušia tak všetko do konca riadku.

Šikovnejší útočníci dokážu urobiť injekciu v ktoromkoľvek SQL príkaze, nielen v DELETE.

Ochrana proti SQL injection

Riešenie problému je veľmi jednoduché. Už vieme, že ho spôsobuje niekoľko špeciálnych znakov v premennej, ako sú úvodzovky a podobne. 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 pred vložením do dotazu sami najprv odcapovať.

Spätné lomeno

Spätné lomeno na slovenskej klávesnici môžeme napísať pomocou pravého Alt a písmena Q:

Odescapovaný dotaz by vyzeral takto:

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

Takáto otázka je neškodná, pretože apostrof vložený užívateľom je považovaný za text. Nevyhodnotia sa teda ako ukončenie reťazca a tým pádom ani dve pomlčky nebudú považované za 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` = ?;

Premenné sa potom pošlú databáze osobitne a naraz. Ona si ich do otázok 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ť.

Editácia záznamov

Databáza umožňuje štyri základné operácie, ktoré sú často označované skratkou CRUD:

• Create – vytvorenie záznamu,
• Read – načítanie alebo vyhľadanie záznamu,
• Update – editácia záznamu,
• Delete – vymazanie záznamu.

Vytvorenie a vymazanie záznamu už vieme. Chýba nám teda ešte editácia a vyhľadávanie. Vyhľadávanie venujeme samostatnú lekciu MySQL krok za krokom - Výber dát (vyhľadávanie), editáciu si vysvetlíme ešte dnes.

Editácia v phpMyAdminu nie je opäť nijako zložitá. Stačí rozkliknúť tabuľku a pri danom zázname kliknúť na možnosť Edit. V SQL slúži na úpravu dát dotaz UPDATE. Pre úpravu dát nejakého používateľa by sme ho zapísali napríklad takto:

UPDATE `users` SET `last_name` = 'Brown', `article_count` = `article_count` + 1 WHERE `user_id` = 3;

Za kľúčovým slovom UPDATE nasleduje názov tabuľky, potom slovo SET. Za ním uvádzame vždy názov stĺpca a po = hodnotu, ktorú mu chceme nastaviť. 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, MySQL krok za krokom - Export, si ukážeme rôzne typy exportov databázy.