3. diel - MS-SQL krok za krokom: Vkladanie a mazanie dát v tabuľke
V predchádzajúcej lekcii, MS-SQL krok za krokom: Vytvorenie databázy a tabuľky, sme si vytvorili databázu a v nej tabuľku užívateľov.
Dnes budeme v MS-SQL tutoriáli vkladať a mazať záznamy, teda používateľov.
Vloženie záznamu do tabuľky
Vloženie nového používateľa si ukážeme opäť najprv cez Visual
Studio. Prejdime do okna SQL Server Object Explorer a nájdime si našu
databázu s tabuľkou Pouzivatelia. Necháme si zobraziť dáta
tejto tabuľky. Pravým tlačidlom klikneme na tabuľku
Pouzivatelia a vyberieme View Data:
Do prázdneho riadka s hodnotami NULL doplníme hodnoty nového
používateľa. Vďaka Identity nemusíme hodnotu stĺpca
Id nastavovať, nastaví sa sama:
Dátum môžeme zadať v rôznych formátoch. V prípade nami použitého
dátového typu date je však odporúčané používať formát
YYYY-MM-DD, kde jednotlivým zložkám zodpovedajú:
YYYY– rok,MM– mesiac,DD– deň.
Pri dátovom type datetime2 (alebo datetime), do
ktorého možno spolu s dátumom uložiť aj čas, sa potom používa formát
YYYY-MM-DD hh:mm:ss (prípadne
YYYY-MM-DDThh:mm:ss):
hh– hodiny,mm– minúty,ss– sekundy.
Výkričníky nám hovoria, že hodnota bola zmenená, ale zmeny neboli do databázy odoslané. Pridanie riadka potvrdíme klávesom Enter alebo kliknutím na ďalší riadok, dáta sa do databázy odošlú, a teda výkričníky zmiznú.
SQL dotaz na vloženie záznamu
Na pozadí všetkého je opäť SQL dotaz. Vložiť Jána Kováča by sme mohli rovnako aj týmto dotazom:
INSERT INTO [Pouzivatelia] ( [Meno], [Priezvisko], [DatumNarodenia], [PocetClankov] ) VALUES ('Ján', 'Kováč', '1984-03-11', 17);
Prvý riadok je celkom jasný, jednoducho hovoríme „Vlož do
používateľov“. Ďalej do zátvoriek uvádzame stĺpce, v ktorých bude mať
nová položka nejaké hodnoty. Stĺpec s Id tu neuvádzame,
pretože sa spoliehame na databázu, že ho za nás vyplní. Nasleduje
kľúčové slovo VALUES a ďalší výpočet prvkov v
zátvorkách, tentoraz hodnôt stĺpcov nového záznamu. Tie idú v takom
poradí, aké sme uviedli pri názvoch stĺpcov. Textové hodnoty a dátumy sú
v apostrofoch (jednoduchých úvodzovkách) ', všetky hodnoty
oddeľujeme čiarkami.
Ak do SQL dotazu vkladáme text (tu napríklad meno
používateľa), nesmie obsahovať apostrofy ' a pár ďalších
znakov. Tieto znaky samozrejme do textu zapísať môžeme, len sa musia
ošetriť, aby si databáza nemyslela, že ide o časť dotazu. Ešte sa k tomu
vrátime.
Vloženie viacerých záznamov naraz
Vložme si pomocou SQL dotazu niekoľko používateľov, ak nemáte fantáziu, pokojne vložte tých z nasledujúcej tabuľky:
| Meno | Priezvisko | Dátum narodenia | Počet článkov |
|---|---|---|---|
| Ján | Kováč | 11.3.1984 | 17 |
| Tomáš | Horváth | 1.2.1989 | 6 |
| Jozef | Tóth | 20.12.1972 | 9 |
| Michaela | Sláviková | 14.8.1990 | 1 |
Pripomeňme si, že súbor, do ktorého môžeme písať naše SQL dotazy a následne ich spúšťať na databáze, otvoríme cez okno SQL Server Object Explorer. Stačí tu kliknúť na našu databázu pravým tlačidlom a vybrať New Query...:
Môžeme využiť možnosť vložiť viac záznamov v rámci jedného dotazu. Hodnoty jednotlivých záznamov uvedených v zátvorkách oddelíme čiarkami:
INSERT INTO [Pouzivatelia] ( [Meno], [Priezvisko], [DatumNarodenia], [PocetClankov] ) VALUES ('Tomáš', 'Horváth', '1989-02-01', 6), ('Jozef', 'Tóth', '1972-12-20', 9), ('Michaela', 'Sláviková', '1990-08-14', 1);
Teraz v SQL Server Object Exploreri klikneme pravým tlačidlom na tabuľku
databazaPreWeb a zvolíme View Data. Naplnená tabuľka
bude vyzerať nasledovne:
Všimnime si, že v lište nad tabuľkou môžeme nastaviť
hodnotu Max Rows, teda maximálny počet zobrazených záznamov. Možno
zadať ľubovoľné prirodzené číslo alebo špeciálnu hodnotu
All, vďaka ktorej sa zobrazia úplne všetky záznamy.
Formátovanie SQL dotazov
Všimnime si, že sme dotaz vyššie rozpísali na viac riadkov. V skutočnosti to vôbec nie je nutné. Databáza by si s dotazom poradila, aj keby bol celý na jednom riadku:
INSERT INTO [Pouzivatelia] ([Meno], [Priezvisko], [DatumNarodenia], [PocetClankov]) VALUES ('Tomáš', 'Horváth', '1989-02-01', 6), ('Jozef', 'Tóth', '1972-12-20', 9), ('Michaela', 'Sláviková', '1990-08-14', 1);
Určite však vidíme, že takto zapísaný dotaz je predsa len menej prehľadný, a to je navyše ešte celkom krátky. Preto dotazy často píšeme na viac riadkov. Bielych znakov (medzery, tabulátory, nové riadky) môžeme medzi jednotlivými slovami dotazu uviesť, koľko chceme.
Vymazanie záznamu
Skúsme si niekoho vymazať. Asi by ste prišli na to, že sa to robí
klávesom Delete po označení celého riadka kliknutím na sivý
stĺpec vľavo. Skúste si to. Ak chceme vymazať záznamy z tabuľky pomocou
SQL, máme k dispozícii príkazy DELETE a
TRUNCATE TABLE.
Príkaz DELETE
V jazyku SQL vyzerá odstránenie pomocou príkazu DELETE
takto:
DELETE FROM [Pouzivatelia] WHERE [Id] = 2;
Príkaz je jednoduchý, voláme „vymaž z používateľov, kde sa hodnota v
stĺpci Id rovná 2“. Skúsme si ho spustiť.
Používateľ s Id 2 sa naozaj zmaže (nezabudnite obnoviť
zobrazenie dát prvým tlačidlom Refresh):
Klauzula WHERE
Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku.
Stretneme sa s ňou aj v ďalších dotazoch. Keďže tu mažeme podľa
primárneho kľúča, sme si istí, že vždy vymažeme práve jedného
používateľa. Jednoduché podmienky zadávame vo formáte
stĺpec operátor hodnota. Medzi základné operátory patria:
=– rovná sa,>– je väčší,<– je menší,>=– je väčší alebo rovný,<=– je menší alebo rovný,!=– nerovná sa.
Podmienku samozrejme môžeme rozvinúť, zátvorkovať a používať
operátory AND (a zároveň) a OR (alebo):
DELETE FROM [Pouzivatelia] WHERE ([Meno] = 'Ján' AND [DatumNarodenia] > '1980-1-1') OR ([PocetClankov] < 3);
Príkaz vyššie vymaže všetkých Jánov, ktorí sa narodili od roku 1980, alebo všetkých používateľov, ktorí napísali menej ako 3 články.
Ku klauzule WHERE a podmienkam sa ešte vrátime v
lekcii MS-SQL krok za
krokom: Výber dát (vyhľadávanie).
Bez klauzuly WHERE
Pri príkaze DELETE však nikdy nesmieme zabudnúť na klauzulu
WHERE. Ak napíšeme iba:
DELETE FROM [Pouzivatelia];
Budú vymazaní všetci používatelia v tabuľke!
Príkaz TRUNCATE TABLE
Ak chceme dosiahnuť vymazanie všetkých záznamov z tabuľky, použijeme
príkaz TRUNCATE TABLE. Príkaz TRUNCATE TABLE vymaže
všetky záznamy. V SQL sa celý príkaz zapíše takto:
TRUNCATE TABLE [Pouzivatelia];
Prečo si teda pamätať príkaz TRUNCATE TABLE, keď funguje v
podstate rovnako ako DELETE FROM bez použitia podmienky? Príkaz
TRUNCATE TABLE oproti DELETE FROM:
- je rýchlejší,
- nevyžaduje oprávnenie
DELETEpre tabuľku, - nespúšťa tzv. triggery, čo sa občas môže hodiť (na triggery sa zameriame v budúcich lekciách),
- vyresetuje číslovanie nových záznamov pomocou Identity späť
na počiatočnú hodnotu (pri použití
DELETE FROMsa pokračuje ďalšou hodnotou v poradí).
SQL injection
SQL injection je termín označujúci narušenie databázového dotazu škodlivým kódom od používateľa.
Rozhodli sme sa túto pasáž vložiť hneď na začiatok kurzu. Ak vás nejako zmetie, nič si z toho nerobte, hlavné je o riziku vedieť. Rovnako sa o bezpečnej práci s databázou dozviete až vtedy, keď s ňou budete pracovať v konkrétnom programovacom jazyku.
Čo je SQL injection
Predstavme si, že naša tabuľka s používateľmi je súčasťou databázy nejakej aplikácie. A tiež, že používateľovi (našej aplikácie) umožníme mazať používateľov podľa priezviska. Do textu dotazu teda vložíme nejakú premennú, ktorá pochádza od používateľa:
"DELETE FROM [Pouzivatelia] WHERE [Priezvisko] = '" + priezvisko + "'";
priezvisko je premenná obsahujúca napríklad text
Kováč. Výsledný SQL dotaz teda bude vyzerať takto:
DELETE FROM [Pouzivatelia] WHERE [Priezvisko] = 'Kováč';
Dotaz sa vykoná a vymaže všetkých Kováčov. To znie ako to, čo sme
chceli. Teraz si však predstavme, čo sa stane, keď niekto do premennej
priezvisko zadá nasledujúci kus SQL kódu:
' OR 1 = 1 --
Výsledný SQL dotaz potom bude vyzerať takto:
DELETE FROM [Pouzivatelia] WHERE [Priezvisko] = '' OR 1 = 1 --';
Pretože 1 = 1 je z logického hľadiska vždy pravda a v
podmienke je, že používateľ musí mať prázdne priezvisko alebo
musí platiť pravda (čo platí), dotaz vymaže
všetkých používateľov v tabuľke. Posledného apostrofu sa
útočník zbavil komentárom (dve pomlčky --), ktorý v dotaze
zruší všetko do konca riadka. Komentáre sú časti „kódu“, ktoré
slúžia na zápis rôznych poznámok programátora a pri vykonávaní sa
ignorujú.
Šikovnejší útočníci dokážu urobiť injekciu v
ktoromkoľvek SQL príkaze, nielen v DELETE.
Riešenie
Nebojte sa, riešenie je veľmi jednoduché. Problém robí niekoľko špeciálnych znakov v premennej, ako sú apostrofy a niekoľko ďalších. Ak tieto znaky potrebujeme, musíme ich tzv. odescapovať, presnejšie namiesto jedného apostrofu napíšeme dva za sebou. V aplikácii to za nás nejakým spôsobom rieši ovládač databázy, buď to robí úplne sám, alebo dáta musíme pomocou neho pred vložením do dotazu najprv odescapovať.
Odescapovaný dotaz by vyzeral takto:
DELETE FROM [Pouzivatelia] WHERE [Priezvisko] = ''' OR 1 = 1 --';
Apostrof od používateľa je zdvojený. Takýto dotaz je neškodný, pretože časť vložená používateľom je považovaná za text. V texte sa nevyhodnotí apostrof, ktorý útočník na začiatok priezviska zapísal. Ďalšou variantou, ako aplikáciu zabezpečiť proti injekcii, je obsah premennej do dotazu vôbec nezadávať. V dotaze sú potom uvedené iba zástupné znaky (najčastejšie zavináč a názov premennej):
DELETE FROM [Pouzivatelia] WHERE [Priezvisko] = @priezvisko;
A premenné sa pošlú databáze zvlášť a naraz. Ona si ich tam sama povkladá tak, aby nevzniklo žiadne nebezpečenstvo. Akým spôsobom to zabezpečiť, opäť záleží na konkrétnom jazyku.
Ako na to napríklad v jazyku C# .NET si ukazujeme v sekciách Databázy v C# - ADO.NET alebo Entity Framework Core v C# .NET.
Editácia záznamov
Databáza umožňuje štyri základné operácie, ktoré sa často označujú skratkou CRUD:
- Create – vytvorenie záznamu,
- Read – načítanie (vyhľadanie),
- Update – editácia,
- Delete – vymazanie záznamu.
Vytvorenie a vymazanie už vieme. Chýba nám teda ešte editácia a vyhľadávanie. Vyhľadávaniu venujeme nasledujúce lekcie, editáciu si vysvetlíme ešte dnes.
Na editáciu vo Visual Studiu by ste určite prišli, stačí prepísať
dáta v tabuľke a potvrdiť klávesom Enter. Na úpravu slúži SQL
príkaz UPDATE, úprava nejakého používateľa by vyzerala
takto:
UPDATE [Pouzivatelia] SET [Priezvisko] = 'Novák', [PocetClankov] = [PocetClankov] + 1 WHERE [Id] = 1;
Za kľúčovým slovom UPDATE nasleduje názov tabuľky, potom
kľúčové slovo SET a menené hodnoty stĺpcov v tvare
názov stĺpca = hodnota. Môžeme meniť hodnoty viacerých
stĺpcov, iba sa oddelia čiarkou. Môžeme dokonca použiť predchádzajúcu
hodnotu z databázy a napríklad ju zvýšiť o 1, ako v ukážke vyššie pri
stĺpci PocetClankov.
Rovnako ako pri DELETE platí, že nesmieme
zabudnúť na klauzulu WHERE, inak dôjde k zmene všetkých
záznamov v databáze!
V nasledujúcej lekcii, MS-SQL krok za krokom: Export, si ukážeme rôzne typy exportov databázy.
