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.