Lekce 3 - MS-SQL krok za krokem: Vkládání a mazání dat v tabulce
V minulé lekci, MS-SQL krok za krokem: Vytvoření databáze a tabulky, jsme si vytvořili databázi a v ní tabulku uživatelů.
Dnes budeme v MS-SQL tutoriálu 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 Visual Studio.
Přejděme do okna SQL Server Object Explorer a najděme si naši
databázi s tabulkou Uzivatele. Necháme si zobrazit data této
tabulky. Pravým tlačítkem klikneme na tabulku Uzivatele a
vybereme View Data:
Do prázdného řádku s hodnotami NULL doplníme hodnoty
nového uživatele. Díky Identity nemusíme hodnotu sloupce
Id nastavovat, nastaví se sama:
Datum můžeme zadat v různých formátech. V případě námi použitého
datového typu date je však doporučené používat formát
YYYY-MM-DD, kde jednotlivým složkám odpovídají:
YYYY– rok,MM– měsíc,DD– den.
U datového typu datetime2 (nebo datetime), do
kterého lze uložit s datem i čas, se pak používá formát
YYYY-MM-DD hh:mm:ss (případně
YYYY-MM-DDThh:mm:ss):
hh– hodiny,mm– minuty,ss– sekundy.
Vykřičníky nám říkají, že hodnota byla změněna, ale změny nebyly do databáze odeslány. Přidání řádku potvrdíme klávesou Enter nebo kliknutím na další řádek, data se do databáze odešlou a tudíž vykřičníky zmizí.
SQL dotaz pro vložení záznamu
Na pozadí všeho je opět SQL dotaz. Vložit Jana Nováka bychom mohli stejně i tímto dotazem:
INSERT INTO [Uzivatele] ( [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku] ) VALUES ('Jan', 'Novák', '1984-03-11', 17);
První řádek je celkem jasný, jednoduše říkáme "Vlož do uživatelů".
Dále do závorek uvádíme sloupce, ve kterých bude mít nová položka
nějaké hodnoty. Sloupec s Id zde neuvádíme, protože
spoléháme na databázi, že jej za nás vyplní. Následuje klíčové slovo
VALUES a další výčet prvků v závorkách, tentokrát hodnot
sloupců nového záznamu. Ty jdou v tom pořadí, jaké jsme uvedli u názvů
sloupců. Textové hodnoty a datumy jsou v apostrofech (jednoduchým
uvozovkách) ', všechny hodnoty oddělujeme čárkami.
Pokud vkládáme do SQL dotazu text (zde třeba jméno
uživatele), nesmí obsahovat 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žení více záznamů najednou
Vložme si pomocí SQL dotazu několik uživatelů, pokud nemáte fantazii, klidně vložte ty z následující tabulky:
| Jméno | Příjmení | Datum narození | Počet článků |
|---|---|---|---|
| Jan | Novák | 11.3.1984 | 17 |
| Tomáš | Marný | 1.2.1989 | 6 |
| Josef | Nový | 20.12.1972 | 9 |
| Michaela | Slavíková | 14.8.1990 | 1 |
Připomeňme si, že soubor, do kterého můžeme psát naše SQL dotazy a následně je spouštět na databázi, otevřeme přes okno SQL Server Object Explorer. Stačí zde kliknout na naši databázi pravým tlačítkem a vybrat New Query...:
Můžeme využít možnosti vložit více záznamů v rámci jednoho dotazu. Hodnoty jednotlivých záznamů uvedených v závorkách oddělíme čárkami:
INSERT INTO [Uzivatele] ( [Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku] ) VALUES ('Tomáš', 'Marný', '1989-02-01', 6), ('Josef', 'Nový', '1972-12-20', 9), ('Michaela', 'Slavíková', '1990-08-14', 1);
Ve Visual Studiu bude naplněná tabulka vypadat následovně:
Všimněme si, že v liště nad tabulkou můžeme nastavit
hodnotu Max Rows, tedy maximální počet zobrazených záznamů. Lze
zadat libovolné přirozené číslo nebo speciální hodnotu All,
díky které se zobrazí úplně všechny záznamy.
Formátování SQL dotazů
Všimněme si, že jsme dotaz výše rozepsali na více řádků. Ve skutečnosti to není vůbec nutné. Databáze by si s dotazem poradila, i kdyby byl celý na jednom řádku:
INSERT INTO [Uzivatele] ([Jmeno], [Prijmeni], [DatumNarozeni], [PocetClanku]) VALUES ('Tomáš', 'Marný', '1989-02-01', 6), ('Josef', 'Nový', '1972-12-20', 9), ('Michaela', 'Slavíková', '1990-08-14', 1);
Určitě ale vidíme, že takto zapsaný dotaz je přece jen méně přehledný a to je navíc ještě vcelku krátký. Proto dotazy často píšeme na více řádků. Bílých znaků (mezery, tabulátory, nové řádky) můžeme uvést mezi jednotlivými slovy dotazu, kolik chceme.
Vymazání záznamu
Zkusme si někoho vymazat. Asi byste přišli na to, že se to dělá
klávesou Delete po označení celého řádku kliknutím na šedý
sloupec vlevo. Zkuste si to. Pokud chceme vymazat záznamy z tabulky pomocí
SQL, máme k dispozici příkazy DELETE a
TRUNCATE TABLE.
Příkaz DELETE
V jazyce SQL vypadá odstranění pomocí příkazu DELETE
takto:
DELETE FROM [Uzivatele] WHERE [Id] = 2;
Příkaz je jednoduchý, voláme "vymaž z uživatelů, kde se hodnota ve
sloupci Id rovná 2". Zkusme si jej spustit. Uživatel
s Id 2 se opravdu smaže:
Klauzule WHERE
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.
Jednoduché podmínky zadáváme ve formátu
sloupec operátor hodnota. Mezi základní operátory patří:
=– je rovno,>– je větší,<– je menší,>=– je větší nebo rovno,<=– je menší nebo rovno,!=– není rovno.
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 [DatumNarozeni] > '1980-1-1') OR ([PocetClanku] < 3);
Příkaz výše vymaže všechny Jany, kteří byli narození od roku 1980, nebo všechny uživatele, kteří napsali méně než 3 články.
Ke klauzuli WHERE a podmínkám se ještě
vrátíme v lekci MS-SQL krok za krokem:
Výběr dat (vyhledávání).
Bez klauzule WHERE
U příkazu DELETE však nikdy na klauzuli WHERE
nesmíme zapomenout. Pokud napíšeme jen:
DELETE FROM [Uzivatele];
Budou vymazáni všichni uživatelé v tabulce!
Příkaz TRUNCATE TABLE
Pokud chceme docílit vymazání všech záznamů z tabulky použijeme
příkaz TRUNCATE TABLE. Příkaz TRUNCATE TABLE
vymaže všechny záznamy. V SQL se celý příkaz zapíše
takto:
TRUNCATE TABLE [Uzivatele];
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í
DELETEpro tabulku, - nespouští tzv. triggery, což se občas může hodit (na triggery se zaměříme v budoucích lekcích),
- vyresetuje číslování nových záznamů pomocí Identity zpět
na počáteční hodnotu (při použití
DELETE FROMse 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.
Rozhodli jsme se tuto pasáž vložit hned na začátek kurzu. Pokud vás nějak zmate, tak si z toho nic nedělejte, hlavní je o riziku vědět. Stejně se o bezpečné práci s databází dozvíte, až s ní budete pracovat v konkrétním programovacím jazyce.
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 textu 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 text
Novák. Výsledný SQL dotaz tedy bude vypadat 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é
prijmeni zadá následující kus SQL kódu:
' OR 1 = 1 --
Výsledný SQL dotaz pak bude vypadat takto:
DELETE FROM [Uzivatele] WHERE [Prijmeni] = '' OR 1 = 1 --';
Protože 1 = 1 je z logického hlediska vždy pravda a v
podmínce je, že uživatel musí mít prázdné příjmení nebo musí
platit pravda (což platí), vymaže dotaz
všechny uživatele v tabulce. Posledního apostrofu se
útočník zbavil komentářem (dvě pomlčky --), který v dotazu
zruší vše do konce řádku. Komentáře jsou části "kódu", které slouží
pro zápis různých poznámek programátora a při vykonávání se
ignorují.
Š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 apostrofy a několik dalších. Pokud tyto znaky potřebujeme, musíme je tzv. odescapovat, přesněji místo jednoho apostrofu napíšeme dva za sebou. V aplikaci to za nás nějakým způsobem řeší ovladač databáze, buď to dělá úplně sám, anebo data musíme pomocí něj před vložením do dotazu nejprve odescapovat.
Odescapovaný dotaz by vypadal takto:
DELETE FROM [Uzivatele] WHERE [Prijmeni] = ''' OR 1 = 1 --';
Apostrof od uživatele je zdvojený. Takový dotaz je neškodný, protože část vložená uživatelem je považována jako text. V textu se nevyhodnotí apostrof, který útočník na začátek příjmení zapsal. 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 (nejčastěji zavináč a název proměnné):
DELETE FROM [Uzivatele] WHERE [Prijmeni] = @prijmeni;
A proměnné se pošlou databázi zvlášť a najednou. Ona si je tam sama navkládá tak, aby nevzniklo žádné nebezpečí. Jakým způsobem to zajistit, opět záleží na konkrétním jazyce.
Jak na to například v jazyce C# .NET si ukazujeme v sekcích Databáze v C# - ADO.NET nebo Entity Framework Core v C# .NET.
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í (vyhledání),
- Update – editace,
- Delete – 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 následující lekce, editaci si vysvětlíme ještě dnes.
Na editaci ve Visual Studiu byste jistě přišli, stačí přepsat data v
tabulce a potvrdit klávesou Enter. K úpravě slouží SQL příkaz
UPDATE, úprava nějakého uživatele by vypadala takto:
UPDATE [Uzivatele] SET [Prijmeni] = 'Dolejší', [PocetClanku] = [PocetClanku] + 1 WHERE [Id] = 1;
Za klíčovým slovem UPDATE následuje název tabulky, poté
klíčové slovo SET a měněné hodnoty sloupců ve tvaru
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 u sloupce
PocetClanku.
Stejně jako u DELETE platí, že nesmíme
zapomenou na klauzuli WHERE, jinak dojde ke změně všech
záznamů v databázi!
V další lekci, MS-SQL krok za krokem: Export, si ukážeme různé typy exportů databáze.
