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ů.
Dnes budeme vkládat a mazat záznamy, tedy uživatele.
Vložení záznamu do tabulky
Vložení nového uživatele si ukážeme opět nejprve přes phpMyAdmin.
Rozklikněte si tabulku uzivatele. V horní liště vybereme možnost
Vložit. Vyplníme pouze ta pole v sloupci Hodnota a pole
uzivatele_id ponecháme prázdné. To se vyplní samo díky vlastnosti
AUTO_INCREMENT
. Obsah polí vyplníme jak potřebujeme a potvrdíme
tlačítkem Proveď:

Nyní si tabulku rozklikneme a vidíme, že Jan Novák je opravdu uložen v databázi.
phpMyAdmin nám ukázal 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 opět jasný, prostě říkáme "Vlož do uživatelů", další řádky jsou sloupce, ve kterých bude mít nová položka nějaké hodnoty. Sloupec s id zde neuvádíme. Následuje slovo VALUES a další výčet prvků v závorkách, tentokrát hodnot. Ty jdou v tom pořadí, jaké jsme uvedli u názvů sloupců. Textové hodnoty jsou v uvozovkách nebo apostrofech, všechny hodnoty oddělujeme čárkami.
POZOR! Pokud vkládáme do SQL dotazu text (zde třeba jméno uživatele), nesmí obsahovat uvozovky, apostrofy a pár dalších znaků. Tyto znaky samozřejmě do textu zapsat můžeme, jen se musí ošetřit, aby si databáze nemyslela, že jde o část dotazu. Ještě se k tomu vrátíme.
Nyní si pomocí SQL dotazu vložíme několik uživatelů. Pokud nemáte fantazii, klidně vložte ty první tři z tabulky, kterou jsme měli v první lekci:

SQL příkaz bude vypadat třeba takto:
INSERT INTO `uzivatele` ( `jmeno`, `prijmeni`, `datum_narozeni`, `pocet_clanku` ) VALUES ('Jan', 'Novák', '1984-11-03', 17), ('Tomáš', 'Marný', '1989-02-01', 6), ('Josef', 'Nový', '1972-12-20', 9);
Uživatelé v tabulce v phpMyAdmin bude vypadat takto:

Vymazání záznamů
Zkusme si někoho vymazat. Asi byste přišli na to, že se to dělá tím
červeným tlačítkem odstranit. Zkuste si to. Pokud chceme vymazat záznamy z
tabulky pomocí SQL, máme k dispozici příkazy DELETE FROM
a
TRUNCATE TABLE
.
DELETE FROM
V jazyce SQL vypadá odstranění pomocí příkazu DELETE
takto:
DELETE FROM `uzivatele` WHERE `uzivatele_id` = 2;
Zkuste si to, phpMyAdmin je na DELETE
opatrný a bude se vás
ptát.
Příkaz je jednoduchý, voláme "vymaž z uživatelů", kde se hodnota ve
sloupci 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);
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ž 3 články.
POZOR!, nikdy na klauzuli WHERE nezapomeňte, pokud napíšete jen:
DELETE FROM `uzivatele`;
Budou vymazáni všichni uživatelé v tabulce! Pokud ale
chceme docílit vymazání všech záznamů z tabulky použijeme příkaz
TRUNCATE TABLE
.
TRUNCATE TABLE
Příkaz TRUNCATE TABLE
vymaže všechny
záznamy. V SQL se celý příkaz 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 TABLE
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
hodnotu zpět na počáteční hodnotu (při použitíDELETE FROM
se pokračuje další hodnotou v pořadí)
SQL injection
SQL injection je termín, označující narušení databázového dotazu škodlivým kódem od uživatele.
Rozhodl jsem se tuto pasáž vložit hned na začátek seriálu. Pokud vás nějak zmate, tak si z toho nic nedělejte, hlavní je o riziku vědět, stejně si bezpečnou práci s databází ukážeme vždy u příslušného jazyka.
Co je SQL injection
Představme si, že naše tabulka s uživateli je součástí databáze nějaké aplikace. A také, že 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';
$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ředstavte, 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 --';
Protože 1 je z logického hlediska vždy pravda a v podmínce je, že buď musí mít uživatel prázdné příjmení nebo musí platit pravda (což platí), vymaže dotaz všechny uživatele v tabulce. Poslední uvozovky se útočník zbavil komentářem (dvě pomlčky), který v dotazu zruší vše do konce řádku.
Šikovnější útočníci dokáží udělat injekci v kterémkoli SQL příkazu, nejen v DELETE.
Řešení
Nebojte, řešení je velmi jednoduché. Problém dělá několik speciálních znaků v proměnné, jako jsou uvozovky a několik dalších. 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 pomocí něj před vložením do dotazu nejprve odescapovat. Určitě si to zjistěte, než začnete s databází pracovat. Pokud budete používat zdejší návody, bude to v nich vždy uvedeno.
Odescapovaný dotaz by vypadal takto:
DELETE FROM `uzivatele` WHERE `prijmeni` = '\' OR 1 --';
Takový dotaz je neškodný, protože část vložená uživatelem je považována jako text. V textu se nevyhodnotí uvozovka a tím pádem ani 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` = ?;
A proměnné se pošlou databázi potom zvlášť a najednou. Ona si je tam 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 (např. v sekci PHP).
Editace záznamů
Databáze umožňuje 4 základní operace, které jsou často označovány zkratkou CRUD (Create, Read, Update, Delete). Jsou to tedy vytvoření záznamu, načtení (vyhledání), update (editace) a vymazání záznamu. Vytvoření a vymazání již umíme. Chybí nám tedy ještě editace a vyhledávání. Vyhledávání věnujeme celý příští článek, editaci si vysvětlíme ještě dnes.
Na editaci v phpMyAdminu byste jistě přišli, stačí rozkliknout tabulku a
u daného záznamu kliknout na Upravit. K úpravě slouží SQL dotaz
UPDATE
, úprava nějakého uživatele by vypadala asi takto:
UPDATE `uzivatele` SET `prijmeni` = 'Dolejší', `pocet_clanku` = `pocet_clanku` + 1 WHERE `uzivatele_id` = 1;
Za klíčovým slovem UPDATE
následuje název tabulky, poté
slovo SET
a vždy název sloupce = hodnota. 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.