Lekce 3 - MySQL krok za krokem - Vkládání a mazání dat v tabulce

V minulé lekci, MySQL krok za krokem - Vytvoření databáze a tabulky, jsme si vytvořili databázi a v ní tabulku uživatelů.

V tomto MySQL tutoriálu si do tabulky uživatelů vložíme první záznamy. Poté si ukážeme, jak vybraný záznam smazat nebo editovat.

Vložení záznamu do tabulky

Vložení nového uživatele si ukážeme opět nejprve přes phpMyAdmin.

Přidání uživatele v phpMyAdmin

Rozklikneme si tabulku uzivatele. V horní liště vybereme možnost Vložit. V novém okně vyplníme pouze pole v sloupci Hodnota. Pole uzivatele_id ponecháme prázdné, vyplní se samo díky vlastnosti AUTO_INCREMENT, kterou jsme mu dříve nastavili. Obsah polí vyplníme, jak potřebujeme a potvrdíme tlačítkem Proveď:

phpMyAdmin nám poté ukáže další SQL dotaz, tím je INSERT. Vložit Jana Nováka bychom mohli stejně i tímto dotazem:

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

První řádek je jasný, prostě říkáme "Vlož do uživatelů", na další řádcích uvádíme sloupce, ve kterých bude mít nová položka nějaké hodnoty. Sloupec uzivatele_id zde uvádět nemusíme. Následuje slovo VALUES a další výčet prvků v závorkách. Tentokrát jde o hodnoty, které do tabulky chceme přidat. Ty jsou uvedeny ve stejném pořadí, jako názvy sloupců výše. Textové hodnoty jsou v uvozovkách nebo apostrofech, všechny hodnoty oddělujeme čárkami.

Pokud vkládáme do SQL dotazu text obsahující uvozovky nebo apostrofy (například O'Connor) a pár dalších znaků, musíme jej ošetřit, aby databáze věděla, že jde o část textu. V opačném případě by takový znak považovala za ukončení řetězce a dotaz by vyhodnotila jako chybný. Ještě se k tomu vrátíme.

Nyní si tabulku znovu rozklikneme a vidíme, že Jan Novák je opravdu uložen v databázi:

Přidání uživatele SQL dotazem

Pojďme si přidat do naší tabulky další uživatele. Tentokrát si vyzkoušíme napsat vlastní SQL dotaz. Do tabulky jím vložíme další tři uživatele:

V dotazu můžeme uvést hodnoty pro všechny tři uživatele. Výsledný SQL dotaz bude vypadat takto:

INSERT INTO `uzivatele` (
    `jmeno`,
    `prijmeni`,
    `datum_narozeni`,
    `pocet_clanku`
)
VALUES  ('Tomáš', 'Marný', '1989-02-01', 6),
        ('Josef', 'Nový', '1972-12-20', 9),
        ('Michaela', 'Slavíková', '1990-08-14', 1);

Tabulka s nově přidanými uživateli bude v phpMyAdmin vypadat takto:

Vymazání záznamů

Zkusme si také někoho vymazat. V phpMyAdmin jednoduše klikneme na červené tlačítko Odstranit. Otevře se nám dialogové okno, v němž máme potvrdit smazání záznamu. V něm je také uveden odpovídající SQL dotaz:

Příkaz DELETE FROM

V jazyce SQL vypadá odstranění pomocí příkazu DELETE takto:

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

Zkusme si ještě smazat Tomáše Marného, phpMyAdmin je na mazání opatrný, provedení příkazu budeme muset v dalším okně znovu potvrdit.

Příkaz je jednoduchý, voláme "vymaž z uživatelů", kde se hodnota uzivatele_id rovná 2.

Zaměřme se na klauzuli WHERE, která definuje podmínku. Potkáme ji i v dalších dotazech. Jelikož zde mažeme podle primárního klíče, jsme si jisti, že vždy vymažeme právě jednoho uživatele. Podmínku samozřejmě můžeme rozvinout, závorkovat a používat operátory AND (a zároveň) a OR (nebo):

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

Takový příkaz výše vymaže všechny Jany, kteří byli narození po roce 1980 nebo všechny uživatele, kteří napsali méně než tři články.

U příkazu DELETE nikdy nesmíme klauzuli WHERE zapomenout uvést! Pokud napíšeme jen DELETE FROM 'uzivatele';, budou vymazáni všichni uživatelé v tabulce.

Příkaz TRUNCATE TABLE

Někdy ovšem můžeme záměrně chtít vymazat všechna data naší tabulky. V takovém případě je však lepší použít příkaz TRUNCATE TABLE, který také vymaže všechny záznamy. V SQL se zapíše takto:

TRUNCATE TABLE `uzivatele`;

Stejně jako u příkazu DELETE, i příkaz TRUNCATE si phpMyAdmin hlídá a pro jistotu se nás před provedením zeptá.

Proč si tedy pamatovat příkaz TRUNCATE TABLE, když funguje v podstatě stejně jako DELETE FROM bez použití podmínky? Příkaz TRUNCATE TABLE oproti DELETE FROM:

• je rychlejší,
• nevyžaduje oprávnění DELETE pro tabulku,
• nespouští triggery, což se občas může hodit,
• vyresetuje AUTO INCREMENT zpět na počáteční hodnotu.

Co jsou to trigerry se dozvíme později v lekci MySQL krok za krokem - Triggery.

SQL injection

SQL injection je termín, označující narušení databázového dotazu škodlivým kódem od uživatele. Zmiňujeme ji hned na začátek seriálu, abychom o možném riziku věděli. Jak bezpečně pracovat s databází si ukážeme později vždy u příslušného jazyka.

Příklad na SQL injection

Představme si, že naše tabulka s uživateli je součástí databáze nějaké aplikace. V ní umožníme uživateli naší aplikace mazat uživatele podle příjmení. Do dotazu vložíme tedy nějakou proměnnou, která pochází od uživatele:

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

Položka $prijmeni je proměnná, obsahující třeba tento text:

Novák

Dotaz se tedy sestaví takto:

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

Dotaz se provede a vymaže všechny Nováky. To zní jako to, co jsme chtěli. Teď si ale představme, co se stane, když někdo do proměnné zadá toto:

' OR 1 --

Výsledný dotaz bude vypadat takto:

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

V podmínce nyní je, že buď musí mít uživatel prázdné příjmení nebo musí platit, že 1 je pravda (což platí). Tento dotaz tedy vymaže všechny uživatele v naší tabulce. Poslední uvozovky se útočník zbavil komentářem. Ten v SQL uvozují dvě pomlčky a zruší tak vše do konce řádku.

Šikovnější útočníci dokážou udělat injekci v kterémkoli SQL příkazu, nejen v DELETE.

Ochrana proti SQL injection

Řešení problému je velmi jednoduché. Již víme, že ho způsobuje několik speciálních znaků v proměnné, jako jsou uvozovky a podobně. Pokud tyto znaky potřebujeme, musíme je tzv. odescapovat, tedy předsadit zpětným lomítkem. V aplikaci to za nás nějakým způsobem řeší ovladač databáze, buď to dělá úplně sám, nebo data musíme před vložením do dotazu sami nejprve odescapovat.

Zpětné lomeno

Zpětné lomeno na české klávesnici můžeme napsat pomocí pravého Alt a písmene Q:

Odescapovaný dotaz by vypadal takto:

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

Takový dotaz je neškodný, protože apostrof vložený uživatelem je považován za text. Nevyhodnotí se tedy jako ukončení řetězce a tím pádem ani dvě pomlčky nebudou považovány za komentář.

Další variantou, jak aplikaci zabezpečit proti injekci, je obsah proměnné do dotazu vůbec nezadávat. V dotazu jsou poté uvedeny pouze zástupné znaky (otazníky):

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

Proměnné se poté pošlou databázi zvlášť a najednou. Ona si je do dotazů sama navkládá tak, aby nevzniklo žádné nebezpečí. To je však teorie okolo konkrétního ovladače databáze a jak bylo řečeno, naleznete ji u jazyka, ze kterého budete s databází komunikovat.

Editace záznamů

Databáze umožňuje čtyři základní operace, které jsou často označovány zkratkou CRUD:

• Create – vytvoření záznamu,
• Read – načtení neboli vyhledání záznamu,
• Update – editace záznamu,
• Delete – vymazání záznamu.

Vytvoření a vymazání záznamu již umíme. Chybí nám tedy ještě editace a vyhledávání. Vyhledávání věnujeme samostatnou lekci MySQL krok za krokem - Výběr dat (vyhledávání), editaci si vysvětlíme ještě dnes.

Editace v phpMyAdminu není opět nijak složitá. Stačí rozkliknout tabulku a u daného záznamu kliknout na možnost Upravit. V SQL slouží k úpravě dat dotaz UPDATE. Pro úpravu dat nějakého uživatele bychom jej zapsali například takto:

UPDATE `uzivatele` SET `prijmeni` = 'Dolejší', `pocet_clanku` = `pocet_clanku` + 1 WHERE `uzivatele_id` = 4;

Za klíčovým slovem UPDATE následuje název tabulky, poté slovo SET. Za ním uvádíme vždy název sloupce a po = hodnotu, kterou mu chceme nastavit. Můžeme měnit hodnoty více sloupců, pouze se oddělí čárkou. Můžeme dokonce použít předchozí hodnotu z databáze a třeba ji zvýšit o 1, jako v ukázce výše.

V další lekci, MySQL krok za krokem - Export, si ukážeme různé typy exportů databáze.