3. diel - MS-SQL krok za krokom: Vkladanie a mazanie dát v tabuľke

V minulej lekcii, MS-SQL krok za krokom: Vytvorenie databázy a tabuľky, sme si vytvorili databázu av nej tabuľku užívateľov.

Dnes budeme v MS-SQL tutoriále 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 ako 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 dotaz. Vložiť "John Smith" by sme mohli rovnako aj touto otázkou:

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! Pokiaľ vkladáme do SQL dotazu 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ť dotazu. Ešte sa k tomu vrátime.

Vložte si pomocou SQL dotazu niekoľko užívateľov, pokiaľ nemáte fantáziu, pokojne vložte tie 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. Pokiaľ 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 na 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 ju aj v ďalších otázkach. 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, pokiaľ napíšete len:

DELETE FROM [Users];

Budú vymazaní všetci používatelia v tabuľke! Pokiaľ 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 TABLE bez použitia podmienky? Príkaz TRUNCATE TABLE oproti DELETE FROM:

• je rýchlejšia
• 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 dotazu škodlivým kódom od užívateľa.

Rozhodol som sa túto pasáž 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 až 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 dotazu 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

Otázka sa teda zostaví takto:

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

Otázka 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á otázka bude vyzerať takto:

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

Pretože 1 = 1 je z logického hľadiska vždy pravda av podmienke je, že buď musí mať užívateľ prázdne priezvisko alebo musí platiť pravda (čo platí), vymaže dotaz všetkých užívateľov v tabuľke. Posledné úvodzovky sa útočník zbavil komentárom (dve pomlčky), ktorý v otázke 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. Pokiaľ 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 dotazu najskôr odcapiť.

Odescapovaný dotaz by vyzeral takto:

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

Úvodzovka od užívateľa je zdvojená. Takáto otázka 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 dotazu vôbec nezadávať. V dotaze 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ž vieme. Chýba nám teda ešte editácia a vyhľadávanie. Vyhľadávanie venujeme celú budú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 dotaz 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 ďalšej lekcii, MS-SQL krok za krokom: Export, si ukážeme rôzne typy exportov databázy.