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. Pravým tlačítkem klikněte na tabulku Uzivatele a vyberte Show Table Data.

Do prázdného řádku s hodnotami NULL doplňte hodnoty jak potřebujete. Díky Identity nemusíme hodnotu sloupce Id nastavovat, nastaví se sama.

Vykřičníky nám říkají, že hodnota byla změněna, ale změny nebyly do databáze odeslány. Potvrďte řádek klávesou Enter nebo klikněte na další řádek, data se do databáze odešlou a tudíž vykřičníky zmizí.

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-11-03', 17
);

První řádek je opět jasný, jednoduše ří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 klíčové 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.

Vložte si pomocí SQL dotazu několik uživatelů, pokud nemáte fantazii, klidně vložte ty z tabulky na začátku:

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 FROM a TRUNCATE TABLE.

DELETE FROM

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

DELETE FROM [Uzivatele] WHERE [Id] = 2;

Zkuste si to, Visual Studio 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 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 [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.

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 Visual Studio 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 kurzu. 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 až budeme s databází pracovat z C# .NET.

Co je SQL injecton

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 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é prijmeni zadá toto:

' OR 1 = 1 --

Výsledný dotaz 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 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, přesněji místo jedné uvozovky napíšeme 2 za sebou. 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.

Odescapovaný dotaz by vypadal takto:

DELETE FROM [Uzivatele] WHERE [Prijmeni] = ''' OR 1 = 1 --';

Uvozovka 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í uvozovka, kterou ú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 potom zvlášť a najednou. Ona si je tam sama navkládá tak, aby nevzniklo žádné nebezpečí. Toto si budeme ukazovat v dalších sekcích o databázích v C# .NET.

Editace záznamů

Databáze umožňuje 4 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 celou příští lekci, 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. K úpravě slouží SQL dotaz UPDATE, úprava nějakého uživatele by vypadala asi takto:

UPDATE [Uzivatele] SET [Prijmeni] = 'Dolejší', [PocetClanku] = [PocetClanku] + 1 WHERE [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. Jako u DELETE platí, že nesmíte 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.