W tym wpisie chciałbym zająć się ciekawym problemem jakim jest wydobycie kompletnego zapytania SQL z klasy PDO, oraz przy okazji pokazać na przykładzie czym się różni pojęcie rozszerzania funkcjonalności klasy od jej dekorowania (przyznaję, że ten wątek nie był moim pierwotnym zamiarem ale w sumie cieszę się, że tak wyszło 🙂 )
Trzeba przyznać, że klasa PDO dostępna w języku PHP jest niezwykle wygodna w użyciu. Przygotowujemy sobie szablon zapytania, dołączamy dane parametryzujące i wykonujemy. Rezultat naszego zapytania również jest dosyć łatwy do pobrania w wielu formach gotowych do dalszego przetwarzania. Czy tej klasie mogłoby czegoś brakować? W normalnym działaniu zapewne nie. W końcu jeśli coś działa to lepiej temu czemuś nie przeszkadzać aby mogło sobie działać w spokoju dalej 😉
Co jednak w sytuacji kiedy coś nie działa tak jakbyśmy tego oczekiwali? Wtedy wiele osób, w tym i ja, może dostrzec brak jednej funkcjonalności – nie da się nijak pobrać w szybki i wygodny sposób wykonywanego przez nasz kod zapytania SQL. Oczywiście ogólny kształt zapytania jest nam znany – w końcu sami wprowadziliśmy jego szablon. Ten jednak nie zawiera przekazanych później danych parametryzujących. Chyba nie muszę mówić jak wygodne jest posługiwanie się kompletną formą zapytania SQL? Nie ma się co oszukiwać, że najbardziej cieszy w takich sytuacjach możliwości wykonania Ctrl+C oraz Ctrl+V w oknie zapytań SQL 🙂
Jak można sobie z tym poradzić? Jest na to kilka sposobów, które znam i mogę je tutaj przedstawić oraz całe mnóstwo innych, o których nie mam pojęcia lub nigdy ich nie stosowałem więc o nich nie wspomnę 😉 Pierwszym jaki wymienię jest możliwość skorzystania z metody PDOStatement::debugDumpParams, która zwróci nam szablon zapytania wraz z kompletem informacji odnośnie parametrów. Niestety sposób ten nie pozwala na łatwe wykorzystanie zapytania (trzeba je sobie samemu poskładać). Można również sprawdzić czy nasz serwer SQL pozwala na podgląd wykonywanych zapytań. Niestety jeśli nie pozwala (lub nie mamy dostępu do takiej funkcjonalności), lub chcielibyśmy odczytać to zapytanie wprost z poziomu kodu (np. w celu zapisania go w logu dla administratora) wtedy musimy się już trochę wysilić.
Czy można w takim razie jakoś namówić klasę PDO aby zdradziła jeden ze swoich sekretów jakim jest gotowe do pobrania zapytanie? Prosta odpowiedź brzmi tak. Od razu jednak podkreślę, że jest to bardziej przydatny trick niż faktyczne wydobycie zapytania. Nie będziemy się bowiem odwoływać do tego co jest przekazywane do sterownika bazy danych ale do tego co podała do wykonania sama aplikacja.
Zanim zajmiemy się rozwiązywaniem problemu przyjrzyjmy się najpierw jakiemuś prostemu przykładowi wykorzystującemu klasę PDO. Argumenty konstruktora klasy PDO nie mają dla naszych rozważań większego znaczenia więc pominę w przykładach moment ich definiowania. Nasz przykładowy kod niech ma natomiast za zadanie pobranie obiektu użytkownika (umówmy się, że obiekt ten jest klasy \MojaAplikacja\Uzytkownik) o podanym identyfikatorze z tabeli użytkowników.
//Określamy sobie identyfikator interesującego nas użytkownika.
$idUzytkownika = 1;
//Tworzymy obiekt PDO. Dlaczego piszę \PDO zamiast po prostu PDO?
//To moim zdaniem dobry nawyk, szczególnie kiedy zdarza nam się używać w kodzie przestrzeni nazw.
$pdo = new \PDO($dsn, $login, $passwd, $options);
//Przygotowujemy sobie szablon naszego zapytania.
$query = 'select * from uzytkownicy where idUzytkownika = :idUzytkownika';
//Prosimy nasz obiekt PDO o przygotowanie obiektu \PDOStatement, na którym będziemy dalej działać.
$stmt = $pdo->prepare($query);
//Przekazujemy identyfikator użytkwonika do szablonu.
$stmt->bindValue(':idUzytkownika', $idUzytkownika, \PDO::PARAM_INT);
//Wykonujemy nasze zapytanie.
$stmt->execute();
//Pobieramy pierwszy rekord jako obiekt.
$objUzytkownik = $stmt->fetchObject('\MojaAplikacja\Uzytkownik');
//Zamykamy kursor zwalniając zasoby dla ewentualnego ponownego wykonania zapytania.
$stmt->closeCursor();
Chciałbym przy okazji wspomnieć, że jedną z rzeczy jakie wprost uwielbiam w klasie PDO jest właśnie możliwość pobierania wyników zapytania w formie gotowych obiektów 🙂
Oczywiście nasze kod może być przygotowany na wiele innych sposobów. Jedną z dodatkowych możliwości jest m.in. wykorzystanie metody query obiektu klasy PDO. W naszym przypadku mogłoby to wyglądać mniej więcej tak.
$idUzytkownika = 1;
$pdo = new \PDO($dsn, $login, $passwd, $options);
//W tej metodzie należy zwrócić szczególną uwagę na filtrowanie danych wprowadzanych do zapytania.
//Bardzo łatwo bowiem w tej sytuacji umożliwić atak Sql Injection.
$query = "select * from uzytkownicy where idUzytkownika = {$idUzytkownika}";
//Pobieramy obiekt klasy \PDOStatement wprost z wynikiem zapytania
$stmt = $pdo->query($query);
$objUzytkownik = $stmt->fetchObject('\MojaAplikacja\Uzytkownik');
$stmt->closeCursor();
Jak widać w tym przypadku nie parametryzujemy szablonu zapytania tylko wprost przekazujemy gotowe zapytanie.
Trzecim sposobem jaki chciałbym pokazać to patametryzacja zapytania poprzez wykorzystanie metody execute obiektu klasy PDO.
$idUzytkownika = 1;
$pdo = new \PDO($dsn, $login, $passwd, $options);
$query = 'select * from uzytkownicy where idUzytkownika = :idUzytkownika';
$stmt = $pdo->prepare($query);
//Wykonujemy nasze zapytanie przekazując argumenty
$stmt->execute(array(':idUzytkownika' => $idUzytkownika));
$objUzytkownik = $stmt->fetchObject('\MojaAplikacja\Uzytkownik');
$stmt->closeCursor();
Dlaczego pokazałem różne sposoby przygotowania i wykonania zapytania? Ponieważ aby przygotować rozwiązanie pozwalające na określenie wykonywanego zapytania musimy najpierw zdać sobie sprawę z tego, które obiekty posiadają odpowiednią wiedzę o elementach zapytania oraz w jaki sposób wchodzą w jej posiadanie.
Czy powyższe przykłady wyczerpują wszystkie możliwości wykonywania zapytań SQL przy pomocy PDO? Od razu powiem, że nie. Wystarczy zajrzeć do dokumentacji użytych klas aby zobaczyć, że np. samo wykorzystanie metody execute do przekazania parametrów można zrealizować na więcej sposobów niż pokazałem to tutaj. Od razu dodam również, że przedstawione przeze mnie rozwiązanie również nie pokryje wszystkich możliwości. Mam jednak nadzieję, że pokazana przeze mnie droga umożliwi w razie potrzeby odpowiednie rozbudowanie tego rozwiązania 🙂
Po przeanalizowaniu powyższych przykładów możemy zauważyć dwie rzeczy:
- szablon zapytania (lub nawet całe zapytanie) jest przekazywane obiektowi klasy PDO,
- ewentualne przekazanie parametrów (mowa o sytuacji kiedy nie używamy metody query obiektu klasy PDO) odbywa się poprzez metody klasy PDOStatement.
Wnioski jakie możemy z tego wyciągnąć są następujące:
- ewentualne operacje musimy wykonywać na obiektach obu klas,
- wynikowe zapytanie powinniśmy pobierać zawsze z obiektu klasy PDOStatement.
Czy możemy w jakiś sposób dostać się do tych informacji? Częściowo tak – obiekt kalsy PDOStatement pozwala pobrać wartość atrybutu queryString, który zawiera wprowadzony przez naszą aplikaację szablon. Niby to niewiele – w końcu znamy doskonale ten szablon skoro sami go tami ustawiliśmy. Z powyższych rozważań wynika więc, że aby wydobyć kompletne zapytanie będziemy musieli napisać sobie własne odpowiedniki zarówno klasy PDO jak i PDOStatement.
Jeśli ktoś się w tym momencie przeraził to już uspokajam. Nie będziemy przepisywać kodu obu klas tylko dodamy im kilka funkcjonalności, których same nie posiadają. Mówiąc inaczej rozszerzymy po prostu sposób działania tych klas.
Zacznijmy od klasy PDO. Utwórzmy sobie na jej bazie nową klasę i nazwijmy ją sobie np. PDOExtended. W powyższych przykładach przekazywaliśmy szablon zapytania (bądź kompletne zapytanie) za pomocą metod prepare oraz query – dlatego powinniśmy zainteresować się właśnie nimi.
class PDOExtended extends \PDO
{
public function prepare($statement, array $driver_options = [])
{
return parent::prepare($statement, $driver_options);
}
//Metoda query posiada zgodnie z dokumentacją aż cztery warianty (przeciążenia).
//Aby nie mieszać teraz nikomu w głowie problemem przeciążania metod wykorzystamy tylko
//jeden z wariantów - ten w którym przekazuje się wyłącznie zapytanie SQL.
public function query($statement)
{
return parent::query($statement);
}
}
W powyższym kodzie utworzyliśmy nowe wersje dwóch metod i przekazaliśmy wszystkie argumenty do metod oryginalnych. To co powinno się rzucić na pierwszy rzut oka to dostęp do interesującej nas zmiennej $statement. W niej znajduje się przecież nasz szablon zapytania SQL. Nie będziemy jednak z tego dostępu w tym miejscu korzystać. Naszym obiektem zainteresowania będzie bowiem to co obie metody zwracają.
W obu przypadkach po krzekierowaniu argumentów do oryginalnej metody zwracamy od razu jej wynik. Dzięki temu mamy pewność, że nasze nowe metody zwrócą obiekty poprawnej klasy. W naszym rozwiązaniu takie działanie nie ma jednak większego sensu. Dlaczego? Ponieważ mając dostęp do szablonu zapytania, zarówno poprzez odczyt wartości zmiennej $statement jak i wprost ze zwracanego przez oryginalne metody obiektu, nie za bardzo mamy jak ten szablon wykorzystać. Pamiętamy przecież, że parametryzacja zapytania odbywa się dopiero w tym zwracanym obiekcie klasy PDOStatement, czyli zupełnie poza obiektem klasy PDO. Poza tym patrząc na powyższy fragment kodu trudno oprzeć się słusznemu wrażeniu, że te nasze nowe metody nie wprowadzają żadnego nowego zachowania.
Do czego więc możemy a konkretine powinniśmy wykorzystać nowe wersje metod prepare oraz query? Otóż możemy dzięki nim podmienić zwracany przez nie obiekt z oryginalnej metody na taki przygotowany przez nas 🙂
Jeśli ktoś rzucił okiem na dokumentację klasy PDOStatement to na pewno zauważył coś niepokojącego. Po pierwsze – pole $queryString posiada ograniczenie readonly (czyli nie możemy manipulować sobie zawartością tego pola na utworzonym obiekcie). Po drugie – klasa ta nie posiada konstruktora ani żadnej innej metody, którą moglibyśmy wykorzystać do ustawienia naszego szablonu zapytania. Jakie to rodzi konsekwencje? Otóż chcąc przygotować sobie nowy obiekt zgodny z klasą PDOStatement nie będziemy w stanie przekazać do niego naszego szablonu (zapewne możnaby tego dokonać wykorzystując refleksyjność języka PHP ale ten wątek chciałbym sobie zostawić na potrzeby ewentualnego innego wpisu 😉 ). Oznacza to, że będziemy musieli utworzyć obiekt klasy pochodnej od PDOStatement, który będziemy musieli parametryzować (najlepiej konstruktorem) oryginalnym obiektem (również klasy PDOStatement) zwracanym przez obie oryginalne metody klasy PDO z naszego powyższego kodu. Jeśli ktoś uważa, że brzmi to dziwnie (bądź strasznie) to już uspokajam – w świecie programistów takie działanie jest normalne i doczekało się nawet własnej nazwy 🙂 Mowa jest bowiem o implementacji dosyć popularnego wzorca projektowego o nazwie Dekorator należącego do grupy tzw. wzorców strukturalnych.
Jeśli ktoś już zna ten wzorzec bądź przeczytał o nim coś chwilę temu to zapewne chciałby zapytać czy faktycznie będziemy musieli przekierowywać obsługę wszystkich metod oryginalnej klasy? Okazuje się, że o ile byłoby to rozwiązanie bardzo eleganckie i pożądane (do czego zachęcam!) to w naszym przypadku możemy sobie pozwolić na pewne uproszczenia. Tak na prawdę będziemy zapewne chcieli obsłużyć tylko te metody, z których będziemy zamierzali korzystać 🙂
No dobrze, zobaczmy jak by to mogło wyglądać. Nową klasę nazwiemy sobie PDOStatementDecorator. W poniższym kodzie zamieściłem dodatkowe opisy wyjaśniające co się dzieje i dlaczego coś wygląda tak jak wygląda.
Ponieważ chciałbym w tym wpisie skupić się bardziej na pokazaniu możliwości niż przedstawieniu kompletnych do skopiowania skryptów, dlatego w dalszej części rozważań skupię się wyłącznie na przekazywaniu argumentów poprzez metodę bindValue oraz execute. W razie potrzeby nikt nie powinien mieć jednak problemów z dodaniem do rozwiązania obsługi pozostałych metod.
class PDOStatementDecorator extends \PDOStatement
{
//Musimy przygotować sobie pole do przechowywania oryginalnego obiektu klasy PDOStatement.
private $stmt;
//Potrzebujemy również atrybutu przechowującego przetwarzane zapytanie.
private $query;
public function __construct(\PDOStatement $stmt)
{
//Zapamiętujemy sobie oryginalny obiekt klasy PDOStatement.
$this->stmt = $stmt;
//Odczytujemy i zapamiętujemy sobie szablon zapytania.
$this->query = $stmt->queryString;
}
//W naszym przykładzie ograniczymy się do udekorowania metody bindValue
//pomijając inne jak bindParam.
public function bindValue($parameter, $value, $data_type)
{
//W tym miejscu mamy dostęp do wszystkich informacji o dodawanym parametrze.
//Znając bieżącą wartość z szablonu zapytania możemy wykorzystać tę wiedzę
//do odpowiedniego zmodyfikowania samego zapytania.
//Wartość $replaceValue budujemy tutaj w oparciu o typ.
//Niektóre wartości jak INT możemy przekazywać do zapytania wprost
//ale inne trzeba odpowiednio przygotować (np. dodając apostrofy do wartości tekstowych).
//Jeśli tego nie zrobimy to najczęściej przygotowanego zapytania
//nie da się później wprost wkleić i uruchomić w oknie zapytań SQL.
switch ($data_type) {
case \PDO::PARAM_INT:
$replaceValue = $value;
break;
//W tym miejscu pozwolimy sobie na uproszczenie i wszystko inne
//potraktujemy tak samo.
default:
$replaceValue = "'{$value}'";
}
//Zamieniamy odniesienie do parametru przygotowaną wartością.
//Tak naprawdę jest to najbardziej kluczowy fragment naszego kodu
//dzięki któremu przygotowujemy finalną postać zapytania SQL.
$this->query = str_replace($parameter, $replaceValue, $this->query);
//Przekierowujemy oryginalne dane parametru do oryginalnego obiektu.
//W końcu chcemy również aby nasze zapytanie zostało wykonane.
return $this->stmt->bindValue($parameter, $value, $data_type);
}
//Nasza finalna metoda zwracająca gotowe zapytanie SQL.
public function getQuery()
{
return $this->query;
}
//Nie możemy również zapomnieć o metodzie, którą chcielibyśmy wykorzystywać
//do pobierania danych. W razie potrzeby można dopisać przekierowania również
//dla pozostałych metod jak np. fetchAll.
public function fetchObject($class_name = 'stdClass', array $ctor_args = array())
{
return $this->stmt->fetchObject($class_name, $ctor_args);
}
//Dobrze będzie udostępnić jeszcze inne metody, których będziemy używać
//jak np. zamknięcie kursora
public function closeCursor()
{
return $this->stmt->closeCursor();
}
//Na koniec warto pamiętać również o metodzie, dzięki której będziemy wykonywać zapytanie.
//Szczególnie, że ta metoda również może ustawiać wartości parametryzujące szablon zapytania.
//W metodzie tej na potrzeby tego wpisu zastosujemy pewne uproszczenie, do którego wrócę
//w dalszej części artykułu.
public function execute(array $input_parameters = array())
{
//Wartości parametryzujące przekazuje się za pomocą tablicy.
//W zależności od wykorzystanej metody istotna będzie albo nazwa
//klucza albo kolejność podanych wartości. W tym miejscu zajmiemy się tylko
//wartościami przekazanymi w formie kluczy (to m.in. element wspomnianego uproszczenia).
foreach ($input_parameters as $key => $value) {
//Proszę zwrócić uwagę, że tutaj zawsze dodajemy apostrofy.
//Wrócę do tego w dalszej części wpisu.
$replaceValue = "'{$value}'";
$this->query = str_replace($key, $replaceValue, $this->query);
}
//Musimy pamiętać o wykonaniu właściwej metody execute na oryginalnym obiekcie.
return $this->stmt->execute($input_parameters);
}
}
Teraz dodam obiecane słowa wyjaśnienia odnośnie implementacji dekorowanej metody execute. Najpierw zajmijmy się tym, dlaczego w odróżnieniu od metody bindValue nie sprawdzamy tutaj typu i na jego podstawie nie ustawiamy wartość $replaceValue. Jeśli przyjrzymy się tej metodzie jeszcze raz to zauważymy, że nie otrzymujemy tam od aplikacji żadnej deklaracji co do typów pól. Moglibyśmy oczywiście spróbować się tych typów domyślić np. wykorzystując funkcje takie jak is_int, is_string czy innych funkcji tego typu bądź nawet wyrażeń regularnych. Zamiast jednak próbować zgadywać wykorzystałem pewną cechę obecną w znanych mi bazach SQL – zwykle podawanie każdej wartości otoczonej apostrofami jest uznawane za poprawne (nawet dla typu numerycznego). Oczywiście jeśli jest to możliwe to zawsze lepiej jest określać typ przekazywanej wartości (szczególnie w zapytaniach SQL). Uznałem jednak, że skoro ktoś korzysta z przekazywania argumentów metodą execute, która nie pozwala na przekazywanie takoch deklaracji, zamiast np. bindValue, która to metoda taką deklarację od nas przyjmuje, to można uznać, że taka osoba sama już sobie odfiltrowała te dane wcześniej a ich typ w zapytaniu jest jej w mniejszym bądź większym stopniu i tak obojętny 🙂
Drugie uproszczenie w metodzie execute polega na wspomnianym w komentarzach wykorzystaniu przekazywania nazw parametrów przez klucze tablicy. Drugą ze wspomnianych metodą, której w powyższych przykładach nie pokazywałem, jest określanie wartości poprzez kolejność ich przekazywanania w tablicy. W takiej sytuacji w zapytaniu zamiast nazwanych parametrów mamy znaki zapytania (po jednym dla każdego parametru). Gdybyśmy chcieli przedstawić nasze zapytanie w takiej postaci wyglądałoby ono tak jak poniżej.
$query = 'select * from uzytkownicy where idUzytkownika = ?';
Gdybyśmy chcieli obsłużyć taki przypadek w naszej klasie PDOStatementDecorator musielibyśmy zająć się następującymi kwestiami:
- określić, który z wariantów został wykorzystany w szablonie zapytania (przekazanie wartości poprzez nazwane klucze czy poprzez kolejność występowania),
- zamiast wykorzystać po prostu metodę str_replace trzeba odnajdywać każdorazowo położenie pierwszego znaku zapytania i podmieniać go w łańcuchu zapytania na kolejną wartość z tabeli.
Aby niepotrzebnie nie rozbudowywać tego wpisu (wolę skupić się na pokazaniu samej metody a nie w pełni gotowego rozwiązania 😉 ) zdecydowałem się uprościć rozwiązanie tylko do pokazanego wariantu.
Mając już nową wersję klasy dekoratora możemy wrócić do nasej nowej klasy PDOExtended i uzupełnić napisane przez nas wcześniej metody. Zastosowanie dekoratora jest dosyć proste, więc za wiele zmieniać w kodzie na szczęście nie musimy.
class PDOExtended extends \PDO
{
public function prepare($statement, array $driver_options = [])
{
return new PDOStatementDecorator(parent::prepare($statement, $driver_options));
}
public function query($statement)
{
return new PDOStatementDecorator(parent::query($statement));
}
}
Teraz zastępując w naszym kodzie klasę PDO klasą PDOExtended zwrócony przez nią obiekt klasy PDOStatement będzie zawierał metodę getQuery zdolną do zwracania w wygodny sposób kompletnego zapytania SQL 🙂
Poniżej podaję jeszcze finalny kod naszego rozwiązania (oczyszczony z komentarzy) gdyby ktoś chciał się nim „pobawić”.
class PDOStatementDecorator extends \PDOStatement
{
private $stmt;
private $query;
public function __construct(\PDOStatement $stmt)
{
$this->stmt = $stmt;
$this->query = $stmt->queryString;
}
public function bindValue($parameter, $value, $data_type)
{
switch ($data_type) {
case \PDO::PARAM_INT:
$replaceValue = $value;
break;
default:
$replaceValue = "'{$value}'";
}
$this->query = str_replace($parameter, $replaceValue, $this->query);
return $this->stmt->bindValue($parameter, $value, $data_type);
}
public function getQuery()
{
return $this->query;
}
public function fetchObject($class_name = 'stdClass', array $ctor_args = array())
{
return $this->stmt->fetchObject($class_name, $ctor_args);
}
public function closeCursor()
{
return $this->stmt->closeCursor();
}
public function execute(array $input_parameters = array())
{
foreach ($input_parameters as $key => $value) {
$replaceValue = "'{$value}'";
$this->query = str_replace($key, $replaceValue, $this->query);
}
return $this->stmt->execute($input_parameters);
}
}
class PDOExtended extends \PDO
{
public function prepare($statement, array $driver_options = [])
{
return new PDOStatementDecorator(parent::prepare($statement, $driver_options));
}
public function query($statement)
{
return new PDOStatementDecorator(parent::query($statement));
}
}
$idUzytkownika = 1;
$pdo = new \PDOExtended($dsn, $login, $passwd, $options);
$query = 'select * from uzytkownicy where idUzytkownika = :idUzytkownika';
$stmt = $pdo->prepare($query);
$stmt->bindValue(':idUzytkownika', $idUzytkownika, \PDO::PARAM_INT);
$stmt->execute();
$objUzytkownik = $stmt->fetchObject('\MojaAplikacja\Uzytkownik');
$stmt->closeCursor();
$finalQuery = $stmt->getQuery();
echo $finalQuery;
Powyższy kod powinien zwrócić nam następujący wynik, czyli kompletne zapytanie.
select * from uzytkownicy where idUzytkownika = 1
Na koniec warto jeszcze dodać, iż gdybyśmy np. posiadali obiekt zapisujący logi to moglibyśmy wzbogacić obie nasze klasy w funkcjonalność samodzielnego zapisywania każdego wykonywanego zapytanie SQL do logów. Co więcej – moglibyśmy dodatkowo rozszerzyć metodę execute (warto zwrócić uwagę na to, iż w tym przypadku rozszerzamy klasę a nie ją dekorujemy 😉 ) tak aby przechwycić informacje o ewentualnych błędach i je również zapisać do pliku logu 🙂
Jak widać zastosowane rozwiązanie daje nam sporo zupełnie nowych możliwości. Wszystko natomiast zawdzięczamy prostemu tak na prawdę rozszerzeniu funkcjonalności (oraz udekorowaniu) istniejących klas 🙂
