5. diel - SqlDataReader a pripojená databáza v C# .NET

V predchádzajúcom kvíze, Kvíz - Databáza, prístupy, pripojená aplikácia v C#- ADO.NET , sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

V dnešnom C# .NET tutoriále sa naučíme čítať z databázy jednotlivé riadky.

Spracovanie dát

Ak potrebujeme spustiť nad databázou dotaz, ktorý vracia viac záznamov (ten náš posledný vracal len jedno číslo), musíme na spracovanie výsledku dotazu využiť triedu *DataReader. Inštanciu tejto triedy ako výsledok vráti metóda ExecuteReader() triedy Command. Trieda *DataReader umožňuje prechádzať výslednú množinu dát po jednotlivých záznamoch. Na to slúži metóda Read(). Prístup k jednotlivým atribútom záznamu môžeme získať pomocou indexu alebo názvu stĺpca uvedeného v hranatých zátvorkách, prípadne pomocou metód Get...(), ktoré nám hodnoty rovno prevedú na požadovaný dátový typ.

Príklad 4

Vypíšte na obrazovku všetky slovíčka. U slovíčok budeme chcieť vypísať stĺpce: Id, Czech a English.

Riešenie

SqlCommand command = new SqlCommand("SELECT Id, Czech, English FROM Word", connection);
connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read()) // until we go through all the records
{
    Console.WriteLine("{0} {1} {2}",
        dataReader[0],                      // column index (Id)
        dataReader["Czech"],                // column name
        dataReader.GetString(2));           // column index (English) with conversion to the required data type
}

Výsledok:

Konzolová aplikácia
1 počítač computer
2 míč ball
3 pes dog
4 já I
5 mít rád like
6 ictdemy ictdemy

SQL dotaz namiesto COUNT teraz obsahuje zoznam stĺpcov, ktoré vyberáme. SqlDataReader číta riadok po riadku čo databáza vrátila a výsledky vypisuje do konzoly.

Odovzdávanie parametrov

Urobme z aplikácie skutočný slovníček a nechajme užívateľa zadať slovíčko, ktoré mu následne preložíme.

SQL injekcia

Najprv si ukážme, ako sa to nemá robiť. Naivne by sme si mohli nechať zadať slovíčko a to potom priamo vložiť ako vyhľadávanú frázu do dotazu. Zdrojový kód aplikácie by vyzeral takto:

// this source code is dangerous
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DictionaryDB;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    Console.WriteLine("Enter an English word for translation");
    string word = Console.ReadLine();

    SqlCommand command = new SqlCommand("SELECT Czech FROM Word WHERE English='" + word + "'", connection);

    SqlDataReader dataReader = prikaz.ExecuteReader();
    while (dataReader.Read()) // until we go through all the records
    {
        Console.WriteLine("Translation: {0}", dataReader["Czech"]);
    }
}
Console.ReadKey();

SQL dotaz je podobný tomu predchádzajúcemu. Nezaujímajú nás však už všetky riadky, ale iba tie, kde má stĺpec English určitú hodnotu. Podmienku v SQL zapíšeme pomocou klauzuly WHERE.

Hoci sa zdá, že aplikácia funguje korektne:

Konzolová aplikácia
Enter an English word for translation
computer
Translation: počítač

Zamyslite sa nad tým, čo sa stane, keď nejaký používateľ zadá na preklad tento reťazec:

'; DROP TABLE Word --

Škodlivý kód sa nám vloží priamo do dotazu a spustí sa nad databázou. Útočníkovi tak dávame plnú kontrolu a našimi dátami, v tomto prípade nám nenávratne vymaže celú tabuľku. To je ešte pomerne nevinný útok, mohol by nám vziať aj heslá používateľov a podobne.

Odovzdávanie parametrov

Bezpečnostný problém spôsobuje samozrejme priame vkladanie hodnôt do textu SQL dotazu. Útok sa preto nazýva SQL injection, ako vloženie cudzieho SQL kódu do nášho.

Platí zásada, že musíme počítať s tým, že škodlivý kód môže byť v každom parametri, ktorý do dotazu vkladáme. Nedá sa spoľahnúť na to, že táto premenná asi nebude nič od užívateľa obsahovať.

Aplikácia sa mení a do premennej by sa mohla časom dostať aj napríklad len časť hodnoty, ktorú zadal používateľ. A verte mi, že používatelia sú vynaliezaví a budú našu aplikáciu skúšať a budú nám tam tieto hodnoty vkladať.

V minulosti sa parametre ošetrovali špeciálnou funkciou, ktorá tzv. zoscapovala škodlivé znaky. Moderné otázky sa píšu pomocou tzv. Prepared Statements. Tie fungujú tak, že sa do dotazu namiesto parametrov vložia len špeciálne značky. Parametre sa potom odovzdávajú oddelene. Ukážme si, ako do dotazu vložiť parametre správne:

string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DictionaryDB;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    Console.WriteLine("Enter an English word for translation");
    string word = Console.ReadLine();

    SqlCommand command = new SqlCommand("SELECT Czech FROM Word WHERE English=@word", connection);
    command.Parameters.AddWithValue("@word", word);

    SqlDataReader dataReader = command.ExecuteReader();
    while (dataReader.Read()) // until we go through all the records
    {
        Console.WriteLine("Translation: {0}", dataReader["Czech"]);
    }

}
Console.ReadKey();

Všimnite si, že v SQL dotaze je len zástupná značka, ktorá sa označuje zavináčom @ a ľubovoľným názvom. Nepíšeme okolo nej ani apostrofy, tie sú dodané neskôr podľa typu značky. Pred zavolaním metódy ExecuteReader() do dotazu pripojíme parametre, v našom prípade parameter @word, ktorého hodnotou bude premenná slovo. Databáza si sama parametre ošetrí a nemusíme sa báť, že by nám aplikáciu niekto mohol nabúrať.

V budúcej lekcii, Databázy v C# .NET - INSERT, UPDATE, DELETE a COUNT, si ukážeme, ako záznamy v databáze pridávať, mazať a editovať. Zdrojové kódy dnešného programu sú ako vždy v prílohe na stiahnutie.

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.