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ť rovnaký záznam by sme mohli aj týmto dotazom:
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
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. 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 FROM
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
Dotaz sa teda zostaví takto:
DELETE FROM [Users] WHERE [LastName] = 'Smith';
Dotaz 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ý dotaz 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 dotaz všetkých užívateľov v tabuľke.
Posledné úvodzovky sa útočník zbavil komentárom (dve pomlčky), ktorý v
dotazu 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 odescapovať.
Odescapovaný dotaz by vyzeral takto:
DELETE FROM [Users] WHERE [LastName] = ''' OR 1 = 1 --';
Úvodzovka od užívateľa je zdvojená. Takýto dotaz 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.