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 používateľov.

V tomto MySQL tutoriále si do tabuľky použí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 používateľa si ukážeme opäť najskôr cez phpMyAdmin.

Pridanie použí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 Smitha by sme tiež mohli aj týmto dotazom:

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 použí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.

Ak 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 používateľa SQL dotazom

Poďme si pridať do našej tabuľky ďalších používateľov. Tentoraz si vyskúšame napísať vlastný SQL dotaz. Do tabuľky ním vložíme ďalších troch použí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 použí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 použí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 používateľov", kde sa hodnota user_id rovná 1.

Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme sa s ňou 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 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` = 'Mary' AND `birthdate` >= '1980-1-1') OR (`article_count` < 3);

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

Pri príkaze DELETE nikdy nesmieme klauzulu WHERE zabudnúť uviesť! Ak 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 použí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 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';

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šetkých Brownov. To znie ako niečo, č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 používateľov v našej tabuľke. Poslednej ú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. Ak 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 odescapovať.

Spätná lomka

Spätnú lomku môžeme na slovenskej klávesnici 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 dotaz je neškodný, pretože apostrof vložený použí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 – čítanie alebo vyhľadanie záznamu,
• Update – editácia záznamu,
• Delete – vymazanie záznamu.

Vytvorenie a vymazanie záznamu už ovládame. Chýba nám teda ešte editácia a vyhľadávanie. Vyhľadávaniu 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 phpMyAdmine nie je nijak 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 trebárs 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.