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šnej lekcii C# .NET sa naučíme čítať z databázy jednotlivé riadky.

Spracovanie dát

Ak potrebujeme spustiť nad databázou dopyt, ktorý vracia viac záznamov (ten náš posledný vracal len jedno číslo), musíme na spracovanie výsledku dopytu 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. Pri slovíčkach budeme chcieť vypísať stĺpce: Id, Slovak a English.

Riešenie

SqlCommand command = new SqlCommand("SELECT Id, English, Spanish 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["English"],              // column name
        dataReader.GetString(2));           // column index (Spanish) with conversion to the required data type
}

Výsledok:

Konzolová aplikácia
1 computer ordenador
2 ball pelota
3 dog perro
4 I yo
5 like amor
6 ICT ICT

SQL dopyt 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 Spanish word for translation");
    string word = Console.ReadLine();

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

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

SQL dopyt 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 správne:

Konzolová aplikácia
Enter an Spanish word for translation
ordenador
Translation: computer

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 dopytu a spustí sa nad databázou. Útočníkovi tak dávame plnú kontrolu nad 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 dopytu. Ú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 dopytu vkladáme. Nedá sa spoľahnúť na to, že táto premenná asi nebude obsahovať nič od užívateľa.

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 dopytu namiesto parametrov vložia len špeciálne značky. Parametre sa potom odovzdávajú oddelene. Ukážme si, ako do dopytu 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 Spanish word for translation");
    string word = Console.ReadLine();

    SqlCommand command = new SqlCommand("SELECT English FROM Word WHERE Spanish=@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["English"]);
    }

}
Console.ReadKey();

Všimnite si, že v SQL dopyte 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ú pridané neskôr podľa typu značky. Pred zavolaním metódy ExecuteReader() do dopytu 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 mohol niekto nabúrať.

V nasledujú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ť.

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š.