Spis treści:

Kategoria:SQL ServerPowerShell


PowerShell i wykonywanie poleceń SQL Server

Najczęściej używane skrypty PowerShell w pracy programisty - ikona konsoli

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

, 2016-08-16

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.