PowerShell i wykonywanie poleceń SQL Server

Wywoływanie w wersji uproszczonej

W jednym z artykułów na temat PowerShell opisywałem już sposób łączenia się z SQL Server. Wpis można zobaczyć pod tym linkiem: Wykonywanie zapytań w SQL Server z poziomu PowerShell. W tamtym przypadku połączenie wykonywane było przy pomocy dostarczanego wraz z SQL Server modułu SQLPS. Moduł daje nam dostęp do wielu oszarów SQL Server, których sterowanie z poiomu PowerShell wydaje się bardzo kuszące. Wykonywanie zapytań też wydaje się na tyle łatwe, że pisanie kolejnego artykułu na ten temat mija się z celem. W tamtym artykule pokazałem proste instrukcje SELECT. Istrukcje były stałym ciągiem znaków, nie przyjmowały żadnych parametrów. Nie pokazywałem też w jaki sposób można wywoływać instrukcje INSERT oraz UPDATE. Nie dlatego, że nie da się ich wykonać. Powód jest nieco inny.

Niebezpieczeństwo SQL Injection

Sposób przekazywania parametrów do metody Invoke-SqlCmd nie jest do końca przemyślany. Może i łatwo jest to wszystko zintegrować z resztą infrastruktury PowerShell, ale kosztem bezpieczeństwa. A bezpieczeństwo powinno w takich przypadkach, mimo wszystko, stać na pierwszym miejscu.

Polecenie Invoke-SqlCmd nie pozwala wręcz przekazać parametrów w sposób bezpieczny (przykład wstrzyknięcia SQL można znaleźć między innymi tutaj: Invoke-SqlCmd considered harmful. Odradzałbym stosowanie tego polecenia dla zapytań mających jakiekolwiek parametry, niekoniecznie tylko tych tekstowych. To dlatego pokusiłem się o przygotowanie oddzielnej funkcji do wywoływani poleceń SQL.

Stare dobre ADO.NET

PowerShell jest doskonale zintegrowany z całym środowiskim .NET. W zasadzie wszystko to, co może być wykonane w programie .NET, może być z powodzeniem wykanane w PowerShellu. Pozostaje oczywiście kwestia czytelności kodu, jego jakości i wydajności, ale zrobić można wszystko. Aby zasygnalizować technikę działania tym, którzy nigdy w czystym ADO.NET nie pisali i przypomnieć tym, którzy kiedyś pisali (tak, tak, przez te wszystkie narzędzia typu NHibernate, LINQ to SQL, Entity Framework już mało kto to pamięta), przedstawię ogólny schemat działania:

• Stwórz obiekt połączenia SqlConnection,
• Stwórz obiekt polecenia SQL w ramach tego połączenia, SqlCommand
• Otwórz połączenie, wykonaj polecenie i pobierz reprezentujący operację odczytu obiekt SqlDataReader,
• Wykonaj iterację po kolejnych rekordach zwróconych przez zapyatanie

Wiemy, że możemy wywoływać dowolne zapytania SQL. Nie wiemy ile kolumn mają zwracane rekordy i jakie są nazwy tych kolumn. Teoretycznie moglibyśmy zwrócić typ object, ale nie jest to dobre z wielu względów. ADO.NET rozwiązuje to w prosty sposób: zwraca wszystkie dane potrzebne do rozpoznania wyniku w obiekcie SqlDataReader. Mamy właściwość FieldCount, która zwraca liczbę kolumn, a także motodę GetName, która pobiera nazwę kolumny pod wskazanym indeksem. To wszystko pozwala nam przetworzyć pobierane z bazy rekordy na ich wygodniejsze w obsłudze odpowiedniki w PowerShell. Popatrzmy na poniższy listing:

function Invoke-Sql(
    $ConnectionString,
    $Query
) {
    $conn = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
    $cmd = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $Query,$conn
    $conn.Open()
    $reader = $cmd.ExecuteReader()
    if ($reader.Read()) {
      [string[]]$columns = 0..($reader.FieldCount-1) |
          % { if ($reader.GetName($_)) { $reader.GetName($_) } else { "(no name $_)" } }
      do {
        $obj = @{}
        0..($reader.FieldCount-1) | % { $obj.Add($columns[$_], $reader[$_]) }
        [PSCustomObject]$obj
      } while ($reader.Read())
    }
    $reader.Dispose()
    $cmd.Dispose()
    $conn.Dispose()
}

Warto wiedzieć, że nie wszystkie kolumny zwracane z SQL Server muszą mieć nazwę. To dlatego potrzebne jest odpowiednie przetworzenie niektórych kolumn i przezwanie ich. Ja postanowiłem nadać im nazwę (no name N), gdzie N jest numerem kolumny, która nie ma nazwy. Raz przygotowana tablica kolumn wykorzystywana jest w pętli w celu utworzenia kolejnych rekordów. Tak przygotowaną funkcję można wykorzystać w następujący sposób:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" "SELECT name FROM sys.tables"

W wyniku otrzymamy listę tabel w bazie Test w postaci pełnoprawnych obiektów PowerShell.

Własna metoda a Invoke-SqlCmd

Wydaje sie, że w przypadku zwykłego zapytania SELECT nie ma potrzeby wymyślania własnych rozwiązań. Zgodzę się z tym w pełni. Równie dobrze zadziała tutaj wywołanie Invoke-SqlCmd. Co jednak wtedy, gdy musimy pobrać nazwy wszystkich tabel zaczynających się od konkretnej litery? Co gdybyśmy zechcieli pobrać dane użytkownika o konkretnym ID, zmodyfikować jakiś rekord lub wstawić dane do tabeli? W przypadku Invoke-SqlCmd musimy sklejać zapytania. W przypadku naszej metody też. Różnica jest taka, że funkcja Invoke-SqlCmd jest już skończona. Funckja Invoke-Sql jest nasza i możemy ją rozbudować.

Przekazywanie parametrów

Wykorzystaliśmy stare dobre ADO.NET do wykonania zapytania, wykorzystamy ten sam mechanizm do przekazania parametrów. Uznałem, że najlepszym sposobem przekazania wartości będzie słownik, tablica z funkcją skrótu (tablica z haszowaniem). Postanowiłem również, że wiązanie danych będzie się odbywało na podstawie nazw, to jest nazwy właściwości będą musiały się pokrywać z nazwanymi parametrami zapytania. Przedstawione założenia doprowadziły do powstania następującej metody:

function Invoke-Sql(
    $ConnectionString,
    $Query,
    $Parameters
) {
    $conn = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
    $cmd = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $Query,$conn
    $conn.Open()
    if ($Parameters) {
      foreach ($arg in $Parameters.GetEnumerator()){
        $cmd.Parameters.AddWithValue($arg.Key, $arg.Value) | Out-Null;
      }
    }
    $reader = $cmd.ExecuteReader()
    if ($reader.Read()) {
      [string[]]$columns = 0..($reader.FieldCount-1) |
          % { if ($reader.GetName($_)) { $reader.GetName($_) } else { "(no name $_)" } }
      do {
        $obj = @{}
        0..($reader.FieldCount-1) | % { $obj[$columns[$_]] = $reader[$_] }
        [PSCustomObject]$obj
      } while ($reader.Read())
    }
    $reader.Dispose()
    $cmd.Dispose()
    $conn.Dispose()
}

Dla każdej pary klucz-wartość ze słownika wywoływana jest metoda AddWithValue właściwości Parameters. Metoda ta zwraca obiekt, dlatego konieczne jest przekierowanie go do polecenia Out-Null. W przecwnym razie wartości te nakładałyby się na zwracane przez naszą funkcję obiekty reprezentujące wynik zapytania. Po takiej modyfikacji możliwe staje się przekazywanie parametrów:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" "SELECT @name Name, @value Column1" @{name="Tekst";value=5}

Wynikiem działania takiego zapytania będzie:

Name  Column1
----  -------
Tekst       5

Przykłady zastosowania

Metoda była pisana głównie do wykonywania zapytań pobierających dane. Nic nie stoi jednak na przeszkodzie, aby użyć ich w zupełnie innych celach. Popatrzmy na kolejne przykłady:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" -Query "CREATE TABLE TestTable(ID int, Name varchar(10))"

Powyższe zapytanie nie zwróci żadnych wyników, ale z powodzeniem utworzy tabelę z dwoma kolumnami. Popatrzmy teraz na operację INSERT:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" -Query "INSERT TestTable(ID, Name) VALUES (1, 'Name1')"

Gdybyśmy zechceili użyć parametrów, moglibyśmy to zrobić na przykłąd tak:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" `
    -Query "INSERT TestTable(ID, Name) VALUES (@id, @name)" `
    -Parameters @{id=2; name='name2'}

Próba pobrania rekordów nie zaskoczy nas:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" `
    -Query "SELECT * FROM TestTable"

Nie zaskoczy nas również wynik:

ID Name 
-- ---- 
 1 Name1
 2 name2

Podobnie można wykonać instrukcję UPDATE:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" `
    -Query "UPDATE TestTable SET ID=ID+1"

Pokazana instrukcja nie zwróci żadnych wyników. Wykona się po cichu. Do tego stopnia, że możemy mieć wątpliwości czy instrukcja rzeczywiście się wykonała. W takich przypadkach zalecam posłużenie się rozszerzoną instrukcją, symulującą w pewnym stopniu wywołanie ExecuteNonQuery zalecaną w ADO.NET dla instrukcji INSERT, UPDATE i DELETE. Popatrzmy na przykład takiego rozszerzonego wsadu:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" `
    -Query "UPDATE TestTable SET ID=ID+1; SELECT @@ROWCOUNT"

Po wykonaniu się tej instrukcji otrzymamy liczbę zmodyfikowanych rekordów:

(no name 0)
-----------
          2

Osobiście wolę nieco inną wersję, kładącą nacisk na zmienione rekordy. To rekordy są podmiotem, nie ich ilość. Kluczem jest, chyba rzadko używana, sekcja OUTPUT zapytania:

Invoke-Sql "Server=.;Database=Test;Integrated Security=SSPI" `
    -Query "UPDATE TestTable SET ID=ID+1 OUTPUT inserted.*"

W wyniku dostaniemy listę zmodyfikowanych rekordów. Jest to stan tuż po wykonaniu instrukcji:

ID Name 
-- ---- 
 2 Name1
 3 name2

Podsumowanie

Jak widać prosta funkcja, wykorzystująca ogólnie znane mechanizmy .NET, może zbudować most pomiędzy światem skryptowym PowerShell a bazą danych SQL Server. Wiązanie po nazwie pozwala przekazywać wartości z potoku bezpośrednio do zapytania. I Odwrotnie - pełnoprawne obiekty zwracane z SQL Server mogą stać się wartościami przekazywanymi do potoku, a poprzez ten potok do kolejnych poleceń PowerShella. Co niezwykle ważne, poprzez wykorzystanie parametrów zabezpieczamy się przed niezwykle groźnym, wciąż obecnym wrogiem - wstrzykiwaniem SQL, znamym poza granicami naszego kraju pod nazwą SQL Injection. Tworzenie tabeli, INSERT, DELETE, przebudowa indeksu, kopia zapasowa - wszytko to, co da się wyrazić przy pomocy SQL, da się przepchnąć przez ADO.NET obudowane zgrabną funkcją PowerShell.

Kategoria:SQL ServerPowerShell