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 lekcii MS-SQL vkladať a mazať záznamy, teda užívateľov.

Vloženie záznamu do tabuľky

Vloženie nového užívateľa si ukážeme opäť najprv cez Visual Studio. Pravým tlačidlom kliknite na tabuľku Users a vyberte Show Table Data:

Do prázdneho riadku s hodnotami NULL doplňte hodnoty, ktoré potrebujete. Vďaka IDENTITY nemusíme hodnotu stĺpca Id nastavovať, nastaví sa sama:

Výkričníky nám hovoria, že hodnota bola zmenená, ale zmeny neboli do databázy odoslané. Potvrďte riadok klávesom Enter alebo kliknite na ďalší riadok, dáta sa do databázy odošlú a teda výkričníky zmiznú.

Na pozadí všetkého je opäť SQL dopyt. Vložiť rovnaký záznam by sme mohli aj týmto dopytom:

INSERT INTO [Users] (
    [FirstName],
    [LastName],
    [BirthDate],
    [NumberOfArticles])
VALUES (
'John', 'Smith', '1984-11-03', 17
);

Prvý riadok je opäť jasný, jednoducho hovoríme "Vlož do užívateľov", ďalšie riadky sú stĺpce, v ktorých bude mať nová položka nejaké hodnoty. Stĺpec s Id tu neuvádzame. Nasleduje kľúčové slovo VALUES a ďalší výpočet prvkov v zátvorkách, tentoraz hodnôt. Tie idú v tom poradí, aké sme uviedli pri názvoch stĺpcov. Textové hodnoty sú v úvodzovkách alebo apostrofoch, všetky hodnoty oddeľujeme čiarkami.

POZOR! Ak vkladáme do SQL dopytu text (tu treba meno užívateľa), nesmie obsahovať úvodzovky, 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ť dopytu. Ešte sa k tomu vrátime.

Pomocou SQL dopytu si vložte niekoľko užívateľov, a ak nemáte fantáziu, pokojne vložte tých z tabuľky na začiatku:

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 riadku kliknutím na šedý 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 FROM a TRUNCATE TABLE.

DELETE FROM

V jazyku SQL vyzerá odstránenie pomocou príkazu DELETE takto:

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

Skúste si to, Visual Studio je s DELETE opatrné a bude sa vás pýtať.

Príkaz je jednoduchý, voláme "vymaž z užívateľov", kde sa hodnota v stĺpci Id rovná 2. Zamerajme sa na klauzulu WHERE, ktorá definuje podmienku. Stretneme sa s ňou aj v ďalších dopytoch. 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. Podmienku samozrejme môžeme rozvinúť, zátvorkovať a používať operátory AND (a zároveň) a OR (alebo):

DELETE FROM [Users] WHERE ([FirstName] = 'John' AND [BirthDate] > '1980-1-1') OR ([NumberOfArticles] < 3);

Príkaz vyššie vymaže všetkých Johnov, ktorí boli narodení od roku 1980 alebo všetkých užívateľov, ktorí napísali menej ako 3 články.

POZOR!, nikdy na klauzule WHERE nezabudnite, ak napíšete len:

DELETE FROM [Users];

Budú vymazaní všetci používatelia v tabuľke! Ak ale chceme docieliť vymazanie všetkých záznamov z tabuľky, použijeme príkaz TRUNCATE TABLE.

TRUNCATE TABLE

Príkaz TRUNCATE TABLE vymaže všetky záznamy. V SQL sa celý príkaz zapíše takto:

TRUNCATE TABLE [Users];

Rovnako ako pri príkaze DELETE, aj príkaz TRUNCATE si Visual Studio stráži a pre istotu sa nás pred vykonaním spýta.

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 DELETE pre tabuľku,
• nespúšťa Triggery (čo sa občas môže hodiť),
• vyresetuje AUTO INCREMENT hodnotu späť na počiatočnú hodnotu (pri použití DELETE FROM sa pokračuje ďalšou hodnotou v poradí).

SQL injection

SQL injection je termín, označujúci narušenie databázového dopytu škodlivým kódom od užívateľa.

Túto pasáž som sa rozhodol vložiť hneď na začiatok kurzu. Ak vás nejako zmätie, tak si z toho nič nerobte, hlavné je o riziku vedieť, rovnako si bezpečnú prácu s databázou ukážeme, keď budeme s databázou pracovať z C# .NET.

Čo je SQL injecton

Predstavme si, že naša tabuľka s používateľmi je súčasťou databázy nejakej aplikácie. A tiež, že umožníme užívateľovi (našej aplikácie) mazať užívateľov podľa priezviska. Do textu dopytu vložíme teda nejakú premennú, ktorá pochádza od užívateľa:

"DELETE FROM [Users] WHERE [LastName] = '" + lastName + "'";

lastName je premenná, obsahujúca napríklad tento text:

Smith

Dopyt sa teda zostaví takto:

DELETE FROM [Users] WHERE [LastName] = 'Smith';

Dopyt sa vykoná a vymaže všetky Smithov. To znie ako to, čo sme chceli. Teraz si ale predstavte, čo sa stane, keď niekto do premennej lastName zadá toto:

' OR 1 = 1 --

Výsledný dopyt bude vyzerať takto:

DELETE FROM [Users] WHERE [LastName] = '' OR 1 = 1 --';

Pretože 1 = 1 je z logického hľadiska vždy pravda a v podmienke je, že buď musí mať užívateľ prázdne priezvisko alebo musí platiť pravda (čo platí), vymaže dopyt všetkých užívateľov v tabuľke. Poslednej úvodzovky sa útočník zbavil komentárom (dve pomlčky), ktorý v dopyte zruší všetko do konca riadku. Šikovnejší útočníci dokážu urobiť injekciu v ktoromkoľvek SQL príkaze, nielen v DELETE.

Riešenie

Nebojte, riešenie je veľmi jednoduché. Problém robí niekoľko špeciálnych znakov v premennej, ako sú úvodzovky a niekoľko ďalších. Ak tieto znaky potrebujeme, musíme ich tzv. odescapovať, presnejšie namiesto jednej úvodzovky napíšeme 2 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 dopytu najskôr odescapovať.

Odescapovaný dopyt by vyzeral takto:

DELETE FROM [Users] WHERE [LastName] = ''' OR 1 = 1 --';

Úvodzovka od užívateľa je zdvojená. Takýto dopyt je neškodný, pretože časť vložená užívateľom je považovaná ako text. V texte sa nevyhodnotí úvodzovka, ktorú útočník na začiatok priezviska zapísal. Ďalším variantom, ako aplikáciu zabezpečiť proti injekcii, je obsah premennej do dopytu vôbec nezadávať. V dopyte sú potom uvedené iba zástupné znaky (najčastejšie zavináč a názov premennej):

DELETE FROM [Users] WHERE [LastName] = @lastName;

A premenné sa pošlú databáze potom zvlášť a naraz. Ona si ich tam sama navkladá tak, aby nevzniklo žiadne nebezpečenstvo. Toto si budeme ukazovať v ďalších sekciách o databázach v C# .NET.

Editácia záznamov

Databáza umožňuje 4 základné operácie, ktoré sú často označované skratkou CRUD:

• Create - vytvorenie záznamu
• Read - načítanie (vyhľadanie)
• Update - editácia
• Delete - vymazanie záznamu

Vytvorenie a vymazanie už ovládame. Chýba nám teda ešte editácia a vyhľadávanie. Vyhľadávaniu budeme venovať celú nasledujúcu lekciu, 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ť. Na úpravu slúži SQL dopyt UPDATE, úprava nejakého užívateľa by vyzerala asi takto:

UPDATE [Users] SET [LastName] = 'Williams', [NumberOfArticles] = [NumberOfArticles] + 1 WHERE [Id] = 1;

Za kľúčovým slovom UPDATE nasleduje názov tabuľky, potom slovo SET a vždy 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 treba ju zvýšiť o 1, ako v ukážke vyššie. Ako u DELETE platí, že nesmiete 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.