Lekce 3 - SQLite - Vkládání a mazání dat v tabulce
V minulé lekci, SQLite - 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 DB Browser for
SQLite. Nejprve si otevřeme soubor s databází. Poté přejdeme na panel
Browse Data, ujistíme se, že níže máme vybranou správnou tabulku
uzivatele
a klikneme na ikonu Insert a new record in the
current table napravo od ikony tiskárny. Do tabulky se vložil nový
záznam, jehož údaje obsahují hodnoty NULL
kromě
uzivatele_id
, které se vyplnilo díky AUTOINCREMENT
samo. Po kliknutí na libovolnou buňku ve sloupci můžeme její hodnotu
změnit:

Zadáme hodnoty dle obrázku. Znovu připomínám, že pokud chceme doopravdy zapsat změny do databáze, musíme kliknout na Write Changes, což můžeme preventivně udělat právě teď.
Ekvivalentní SQL dotaz pro přidání Jana Nováka vypadá takto:
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, ten se vyplňuje sám. Následuje slovo
VALUES
a další výčet prvků v závorce, tentokrát vkládaných
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.
Pokud vkládáme do SQL dotazu text (zde například 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.
Vložme si pomocí SQL dotazu pomocí panelu Execute SQL několik uživatelů. Pro představu zde na obrázku máme i náš SQL dotaz:

Vymazání záznamu
Zkusme si někoho vymazat. Asi byste přišli na to, že se to dělá tím
tlačítkem Delete the current record. 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;
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.
Nikdy na klauzuli WHERE
nezapomeňte! Pokud
napíšete jen DELETE FROM "uzivatele";
, budou vymazáni
všichni uživatelé v tabulce!
TRUNCATE TABLE
V některých databázích existuje příkaz TRUNCATE TABLE
,
který velmi rychle smaže všechny záznamy v tabulce a zároveň resetuje
počítadlo pro primární klíč. V SQLite však tento příkaz nenajdeme.
Pokud chceme tabulku úplně vyprázdnit, stačí nám napsat:
DELETE FROM `uzivatele`;
Když tento příkaz použijeme bez podmínky a tabulka nemá žádné
triggery (události, které se spouští při mazání), SQLite
si to uvnitř optimalizuje tak, že data smaže "naráz". Je to tedy podobně
rychlé, jako kdybychom měli TRUNCATE
.
Pokud ale tabulka triggery má, budou se řádky mazat postupně a triggery se spustí.
Je dobré vědět, že po tomto mazání se v SQLite automaticky
neobnoví počítadlo u sloupce
INTEGER PRIMARY KEY AUTOINCREMENT
(při použití
DELETE FROM
se pokračuje další hodnotou v pořadí).
Když chceme, aby se po vyprázdnění tabulky nové záznamy začaly
číslovat od 1, musíme ještě vymazat záznam v interní tabulce
sqlite_sequence
:
DELETE FROM sqlite_sequence WHERE name = 'uzivatele';
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ř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 --';
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ážou 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 pár 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 za 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, Python).
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 samostatný článek, editaci si vysvětlíme ještě dnes.
Na editaci v DB Browseru už jsme vlastně narazili, stačí kliknout na
nějakou hodnotu a přepsat ji. K úpravě slouží SQL dotaz
UPDATE
, úprava nějakého uživatele vypadá 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, SQLite - Výběr dat (vyhledávání), si ukážeme slíbené vyhledávání.