3. díl - MS-SQL krok za krokem: Vkládání a mazání dat v tabulce

MS-SQL krok za krokem MS-SQL krok za krokem: Vkládání a mazání dat v tabulce

V minulém dílu seriálu tutoriálů o MS-SQL databázi 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 Visual Studio. Pravým tlačítkem klikněte na tabulku uzivatele a vyberte Show Table Data.

Show Table Data

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

Designer – přidání řádku

Vykřičníky nám říkají, že hodnota byla změněna, ale změny nebyly do databáze odeslány. Potvrďte klávesou Enter, 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 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:

Uživatelé v MS-SQL tabulce

Vymazání záznamu

Zkusme si někoho vymazat. Asi byste přišli na to, že se to dělá klávesou delete. Zkuste si to.

Vymazání se v jazyce SQL dělá pomocí příkazu DELETE:

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í 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!

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 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 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, 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.

Odescapovaný dotaz by vypadal takto:

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

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, 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 ve Visual Studiu byste jistě přišli, stačí přepsat hodnoty 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.

Příště půjdeme na slíbené vyhledávání.


 

  Aktivity (1)

Článek pro vás napsal Michal Žůrek (misaz)
Avatar
Autor se věnuje tvorbě aplikací pro počítače, mobilní telefony, mikroprocesory a tvorbě webových stránek a webových aplikací. Nejraději programuje ve Visual Basicu a TypeScript. Ovládá HTML, CSS, JavaScript, TypeScript, C# a Visual Basic.

Jak se ti líbí článek?
Celkem (12 hlasů) :
4.666674.666674.666674.666674.66667


 



 

 

Komentáře

Avatar
Josef Cabadaj:

Zdravím, u druhého příkladu na mazání záznamu je špatně napsáno, že :"vymaže všechny Jany, kteří byli narození po roce 1980". Jelikož v příkazu je : [DatumNarozeni] >= '1980-1-1' vymaže i všechny Jany, kteří jsou narozeni i roku 1980.

 
Odpovědět 5. srpna 9:41
Avatar
David Čápka
Tým ITnetwork
Avatar
Odpovídá na Josef Cabadaj
David Čápka:

Určitě bych nepoužil pojem "špatně napsáno", je to maximálně "nepřesně". Nemyslím si, že "po roce" znamená explicitně od dalšího roku, běžně se říká, že po roce X to bylo špatné třeba, tak to bylo také myšlené, že ten rok se něco stalo a tím pádem se do toho může nebo nemůže počítat. V tomto případě je to úplně jedno a proto to zatím nebudeme opravovat, až budeme dělat větší revizi, tak si komentáře všimneme a upravíme to.

Odpovědět  +1 5. srpna 10:00
Miluji svou práci a zdejší komunitu, baví mě se rozvíjet, děkuji každému členovi za to, že zde působí.
Avatar
Odpovídá na David Čápka
Josef Cabadaj:

Bral jsem to z opačné strany, Kdy nejdříve bývá textové zadání a pak programátor píše skript. Na zadaní např.: Smaž všechny doklady po roce 1980, bych nikdy nesmazal doklady roku 1980.
Dobře, souhlasím. Jde o interpretaci textu, jak si to kdo vyloží slovní popis toho příkladu.

 
Odpovědět 5. srpna 10:19
Avatar
Odpovídá na David Čápka
Josef Cabadaj:

A ještě bych upozornil, že v dalších kapitolách tentýž význam pro stejný text je jiný.
V 4 kapitole používáte : narozené po roce 1960, zápis [DatumNarozeni] >= '1960-1-1' (tedy včetně roku 1960)
V 5 kapitole používáte : narození po roce 1980 , zápis [DatumNarozeni] > '1980-12-31' (tedy bez roku 1980)
Prostě pro začínající nebo nezkušené to může být matoucí. Já jsem spíše ovlivněn praxí, proto mě to připadlo "špatně". Ale jak jsem psal jde o interpretaci, jak si to kdo vyloží.

 
Odpovědět 5. srpna 10:40
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 4 zpráv z 4.