ADO.NET zapytania parametryzowane – C#

W poprzednim wpisie pokazałem, jak napisać prostą klasę w C#, dzięki której można pobierać wyniki prostych zapytań typu select. W tym wpisie pokażę, jak parametryzować zapytania i jak tego nie robić. Zakładam, że czytając ten post, masz drogi czytelniku jakąś klasę na wzór z poprzedniego posta.

W poprzednim poście aplikacja potrafiła wykonać zapytanie ‘select * from klienci’, a gdybyśmy chcieli pokazać konkretnego klienta? Normalnie w SSMS wystarczy napisać zapytanie

select * from klienci where klient_id = 1

Jeśli powyższe zapytanie jest niejasne, zapraszam do lektury o filtrowaniu danych (kurs sql). Oczywiście można w zapytaniu podstawić jakąkolwiek inną tabelę i kolumnę, przykłady opieram na poprzednich postach (zestawy szkoleniowe).

Powyższe zapytanie zwróci nam jeden wiersz.


Zanim przejdziemy do tak zwanego “kodzenia”, jedna uwaga, zmieniłem komputer, na którym prezentuję kod, więc nazwy aplikacji i niektórych kontrolek i obiektów mogły się zmienić.

Gotową klasę można pobrać tutaj
 

By wykonać przykład, który chcę zaprezentować, musimy mieć projekt, posiadający jedną formę, na której znajduje się guzik oraz datagridview.

Gdy nasze zapytanie wkleimy do kodu, gdzie wykonuje się kod po kliknięciu guzika
private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");
dataGridView1.DataSource = db.pobierz_dane("select * from klienci where klient_id=1");
}

Po skompilowaniu, uruchomieniu i kliknięciu guzika pojawi się jeden wiersz w DataGridView:

Oczywiście bez sensu jest zmieniać kod, by zobaczyć inny rekord. Dołóżmy na formę textboxa.

Teraz załóżmy, że użytkownik aplikacji zna numery identyfikacyjne klientów i chce oglądać pojedynczych klientów. Do tego celu będzie w textboxa (pole tekstowe) wpisywał numer klienta.

Najprostszą drogą do osiągnięcia tego celu jest konkatenacja w zapytaniu:
if (textBox1.Text.Length > 0)
{

dataGridView1.DataSource = db.pobierz_dane(“select * from klienci where klient_id=” + textBox1.Text);
}

Powyższy oczywiście zadziała, jeśli użytkownik wpisze liczbę do pola tekstowego. Więc, generalnie nie jest to najlepsze rozwiązanie. Dodatkowo, przy takim rozwiązaniu narażamy się na SQL Injection, użytkownik może dopisać część zapytania, która w skrajnych przypadkach może doprowadzić do katastrofalnych zmian w bazie danych.

Przykład na to, że działa:

Przykład na to, że można popsuć coś w ten sposób:

W między czasie, treść zapytania przeniosłem do zmiennej q, tak by móc zdebuggować program i zobaczyć, jak wygląda zapytanie dostarczane do funkcji. Od razu widać, że taki sposób parametryzacji jest bardzo niebezpieczny.

Aby zrobić to poprowanie, powinniśmy skorzystać z obiektu SqlParameter. Do tego celu najlepiej zmodyfikować naszą klasę do obsługi bazy danych.

Skopiujmy naszą funkcję służącą do pobierania danych i dodajmy argumenty wejściowe:

public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
..

}

Teraz musimy dodać Parametr do obiektu SqlCommand, tak aby nasza funkcja wyglądała, np. tak:
public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Dodaliśmy te dwie linie:
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

Musimy je dodać w miejscu, gdzie już mamy utworzoną instancję obiektu SqlCommand. Kolejność dodawania nie ma znaczenia. Parametry należy dodać do obiektu przed wywołaniem zapytania (metoda ExecuteReader).

Gdy już mamy gotową metodę, możemy zmienić nieco kod w naszej metodzie wywoływanej po kliknięciu guzika:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,”@id”,textBox1.Text);
}
}

Jak widać, w zapytaniu pojawił się @id. Jest to wskaźnik parametru i informacja o jego nazwie, w przypadku SQLServer parametry powinny się zaczynać się od @. Teraz pod @id, przy wykonywaniu zapytania zostanie podstawiona wartość z pola tekstowego, jednak środowisko .NET zadba za nas o niezbędne walidacje tak, by wszystko zadziałało jak należy:

Teraz próba sql injection skończy się wyjątkiem:

Nasz kod nie posiada bloków try i catch, więc aplikacja najzwyczajniej w świecie się wywali. Kwestii Obsługi błędów poświecę osobny wpis.

Oczywiście może zdarzyć się sytuacja, że będziemy potrzebowali większej ilości parametrów, do tego celu możemy skorzystać ze słowników zaimplementowanych w języku C#. Potrzebujemy znowu przeciążyć metodę od pobierania danych.

Słownik w C# jest listą par obiektów, Para jest kluczem i wartością (tak samo jak SQLParameter). Oczywiście możemy przekazać od razu listę SQLParameter do naszej funkcji lub gotowy obiekt SQLCommand. Ale pokazana ścieżka wymusza użycie obiektów Sql* w jednym miejscu. Tak więc nasza funkcja może wyglądać tak:

public DataTable pobierz_dane(string q, Dictionary < string,object > lista_parametrow )
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par; // deklaracja zmiennej
foreach (KeyValuePair<string, object> parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Jak widać w pierwszej linii:
public DataTable pobierz_dane(string q, Dictionary< string,object > lista_parametrow )
mamy deklarację zmiennej typu słownikowego: Dictionary< string,object > lista_parametrow

Później dodaliśmy pętlę foreach (co oznacza dla każdego obiektu w liście/słowniku/tablicy etc. wykonaj…)

foreach (KeyValuePair< string, object > parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

Dla każdej pary słownika tworzymy obiekt KeyValuePair, jest to obiekt, potrafiący przechowywać dwie wartości jednocześnie. Jest to po prostu struktura/obiekt, posiadający dwie właściwości (key, value). W pętli powstają nowe instancje obiektu i dodawane są do listy parametrów. Mimo, iż obiekt par za każdym razem – mogłoby się wydawać – będzie nadpisany, o tyle referencje do pamięci będą poprawne i lista parametrów w obiekcie SQLCommand tak naprawdę będzie zawierać pełną listę dostarczonych parametrów. Przeróbmy nasz przykład, by działał ze słownikiem (na razie dodamy tylko jeden parametr).

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}

Tworzymy obiekt (instancję) słownika i dodajemy do niej jedną parę: klucz i wartość (key,value).
Value jest typem object, co oznacza, że możemy podstawiać tam dowolny typ obiektu, nieważne, czy to będzie data, napis, czy liczba, ten kod zadziała i dane zostaną odpowiednio zwalidowane.

Żeby sprawdzić, czy zadziała to dla większej ilości parametrów, dodajmy jeszcze jedno pole tekstowe. Wyobraźmy sobie, że będziemy porównywać dwóch klientów:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb(“localhost\\sqlexpress”, “zajecia”);

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
lista_par.Add(“@id2”, textBox2.Text);
string q = “select * from klienci where klient_id=@id or klient_id=@id2”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}
}

Dodajemy jeszcze jeden parametr do zapytania, oraz dodajemy go do słownika, efekt: