[PHP][PDO] Podstawy cz.3 - bindowanie zmiennych i zapytania sparametryzowane

Parametryzowanie zapytań to niezwykle ciekawe rozwiązanie. Jest to o wiele lepsze i bezpieczniejsze rozwiązanie, niż wysyłanie zapytań do bazy wraz z zagnieżdżonymi w nim danymi pobranymi ze zmiennych. W artykule tym zobaczymy ile korzyści może płynąć z takiego właśnie sposobu. Parametryzowanie zapytań przede wszystkim pozwala na oddzielenie wstrzykiwanych w zapytania danych od treści samego zapytania i wprowadza kilka aspektów bezpieczenstwa, które postaram się w skrócie omówić. Opisywany sposób generowania zapytań radzę sobie dokładnie przetestować i przyzwyczaić się do niego, gdyż wszystkie kolejne rzeczy, które będą tutaj opisywane opierać się będą właśnie na takim podejściu.

1. Jak to działa?

Przyjrzyjmy się zapytaniu:
[code] $stmt = $dbh->query('SELECT login, password FROM customers WHERE customer_id =' . $id);[/code]
W zapytaniu tym próbujemy pobrać login i hasło klienta o numerze id pobieranym ze zmiennej $id.
Co jest złego w tym zapytaniu? Generalnie nic. Ale popatrzmy teraz na poniższe:
[code] $stmt = $dbh->prepare('SELECT login, password FROM customers WHERE customer_id = :id');[/code]
W powyższym zapytaniu zniknęła nam zmienna $id, a zamiast niej pojawił się zapis :id.
Dodatkowo nie korzystamy już z query(), a z prepare().

Przeanalizujmy teraz poniższy kod:
[code] $stmt = $dbh->prepare('SELECT login, password FROM customers WHERE customer_id = :id');  //1
$stmt->bindValue(':id', $id, PDO::PARAM_INT);  //2
$result = $stmt->execute();  //3
$stmt->closeCursor();  //4[/code]

2. Co się tutaj wyrabia? - bindValue(), prepare() i execute()

1) Za pomocą metody prepare() przygotowujemy jedynie SZKIELET zapytania, zamiast całego zapytania uzupełnionego od razu wartością pobraną ze zmiennej $id. Użyliśmy tutaj zapisu :id, który jest placeholderem dla zmiennej. Samą zmienną prześlemy do bazy dopiero w kolejnym kroku. Nazwy placeholderów mogą być dowolne (nie muszą być takie same jak nazwy zmiennej), byleby zostały pokryte w metodach bindValue().

2) Metodą:
[code]  $dbh->bindValue(':id', $id, PDO::PARAM_INT )[/code]
przypisujemy (bindujemy) wartość zmiennej $id do placeholdera :id. Zauważmy, że w trzecim parametrze podajemy typ naszej zmiennej, w tym przypadku jest to INTEGER informując tym samym bazę jakiego typu wartości się spodziewać. Podanie tutaj do zmiennej $id wartości typu STRING spowoduje NIEWYKONANIE zapytania, gdyż wymuszony jest typ INTEGER.

3) Dopiero w tym kroku wysyłamy całość do bazy metodą execute(). Jednocześnie do zmiennej $result zwracamy jest wynik wykonania zapytania (true|false). Oczywiście pobrane rekordy wylistować możemy tak jak poprzednio za pomocą $stmt->fetch(). Poza sposobem wykonania zapytania nic się tutaj innego w tym momencie nie zmieniło.

4) Zwalniamy kursor.

Lista możliwych parametrów podawanych w trzecim argumencie bindValue() znajduje się poniżej:

• PDO::PARAM_BOOL – boolean
• PDO::PARAM_NULL – null
• PDO::PARAM_INT – integer
• PDO::PARAM_STR – char, varchar, string
• PDO::PARAM_LOB – SQL large object datatype

Co ciekawe - brakuje tutaj typów zmiennoprzecinkowych.
Zmienne np. typu FLOAT dodajemy poprzez PDO::PARAM_STR i rzutowanie, np.:
[code]bindValue(':cena', (float) $cena, PDO::PARAM_STR);[/code]

3. Zwracanie liczby wierszy jakich dotyczyło zapytanie - rowCount()

Czasem konieczne jest pobranie liczby wierszy, których dotyczyło ostatnie zapytanie, np. podczas usuwania rekordów wg zadanego warunku chcemy wiedzieć ile wierszy zostało usuniętych. Metoda execute() zwróci nam jedynie wartość TRUE w przypadku wykonania zapytania, lub FALSE w przypadku niepowodzenia. Aby uzyskać liczbę zaafektowanych wierszy używamy metody $stmt->rowCount(), która pobiera informację o ilości wierszy jakich dotyczyło ostatnie wykonane zapytanie. Metoda ta zwraca poprawną ilość dla zapytań INSERT, UPDATE i DELETE. Z zapytaniem SELECT bywa tutaj różnie. Przykład użycia:

[code] $id = 1;
$stmt = $dbh->prepare('DELETE FROM customers WHERE customer_id > :id');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$result = $stmt->execute();
$count = $stmt->rowCount();
echo 'Usuniętych wierszy: ' . $count;
$stmt->closeCursor();
[/code]

4. Różnica pomiędzy bindValue(), a bindParam()

Do przypisania zmiennej do placeholdera służą dwie bardzo podobne do siebie metody, na pierwszy rzut oka identyczne:
[code]bindValue(placeholder, zmienna, typ)[/code]
i
[code]bindParam(placeholder, zmienna, typ)[/code]
Różnia pomiędzy tymi obiema metodami jednak jest i to dość znaczna.
Popatrzmy na przykłady:

bindValue():
[code]
$id = 2;
$stmt = $dbh->prepare('SELECT * FROM customers WHERE customer_id = :id');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$result = $stmt->execute();
// wykona się zapytanie: 'SELECT * FROM customers WHERE customer_id = 2' [/code]


bindParam():
[code]
$id = 2;
$stmt = $dbh->prepare('SELECT * FROM customers WHERE customer_id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$result = $stmt->execute();
// wykona się zapytanie: 'SELECT * FROM customers WHERE customer_id = 2' [/code]

Jak widać w obu przykładach wykonało się takie samo zapytanie. Gdzie więc jednak tkwi różnica?
Popatrzmy teraz na poniższe dwa przykady, które ową różnicę zobrazują:

bindValue():
[code]
$id = 2;
$stmt = $dbh->prepare('SELECT * FROM customers WHERE customer_id = :id');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$id = 6;
$result = $stmt->execute();
// wykona się zapytanie: 'SELECT * FROM customers WHERE customer_id = 2' [/code]


bindParam():
[code]
$id = 2;
$stmt = $dbh->prepare('SELECT * FROM customers WHERE customer_id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 6;
$result = $stmt->execute();
// wykona się zapytanie: 'SELECT * FROM customers WHERE customer_id = 6' [/code]

W drugim przypadku w zmiennej $id została użyta wartość 6.
Stało się tak dlatego, iż bindParam() pobiera wartość zmiennej przez REFERENCJĘ - warto o tym pamiętać podczas korzystania z bindParam(), gdyż czasem fakt ten prowadzić może do trudnych do wykrycia błędów w logice aplikacji.

5. Szybkie bindowanie - znak zapytania (?)

Istnieje jeszcze jeden sposób przypisania zmiennych do placeholdera, który nie wymaga wywoływania metod bindValue(), ani bindParam(). Polega on na zastąpieniu wszystkich placeholderów znakami zapytania - ?, a następnie na przypisaniu tablicy zmiennych bezpośrednio w metodzie execute(). Zobaczmy na przykładzie:
[code]
$country = 'Polska';
$city = 'Warszawa';
$stmt = $dbh->prepare('SELECT * FROM customers WHERE country=? AND city=?');
$result = $stmt->execute(array($country, $city));
// wykona się zapytanie: 'SELECT * FROM customers WHERE country="Polska" AND city="Warszawa"' [/code]
W przypadku takim jak powyżej wszystkie placeholdery podajemy jako znak zapytania, nastepnie pokrywamy ich wartości w tablicy przekazywanej jako argument do metody execute(). Warto pamiętać, że ważna jest kolejność - zmienne podane w tablicy muszą być podawane w takiej samej kolejności w jakiej występują w zapytaniu. Trzeba też pamiętać, iż zmienne do execute() zawsze podajemy w tablicy, nawet jeśli jest to tylko jedna zmienna.

Przykładowy kod

Wróćmy jednak do metody z użyciem bindValue() i przyjrzyjmy się jak wyglądać może całość naszego kodu. Dla przykładu wyobraźmy sobie, że potrzebujemy pobrać z bazy wszystkich klientów, którzy mieszkają w Polsce i którzy zarejestrowali się w bazie wcześniej niż godzinę temu. Czas rejestracji trzymany jest w bazie w polu created_at i zapisany jako linuxowa liczba sekund, musimy więc od obecnego czasu odjąć ilość sekund jaka upłynęła przez godzinę. i wyświetlić klientów, których czas rejestracji jest mniejszy od tej wartości. Nasz kod wyglądać będzie tak:

[code]<?php

// konfiguracja
$conn_config = array(
  'host' => 'localhost',
  'port' => '3306',
  'user' => 'root',
  'pass' => 'password',
  'db' => 'myDatabase',
  'db_type' => 'mysql',
  'encoding' => 'utf-8'
);

// próba połączenia
try
{
 $dsn = $conn_config['db_type'] .
 ':host=' . $conn_config['host'] .
 ';port=' . $conn_config['port'] .
 ';encoding=' . $conn_config['encoding'] .
 ';dbname=' . $conn_config['db'];


  $dbh = new PDO($dsn, $conn_config['user'],  $conn_config['pass']);

  // ustawienie trybu raportowania błędów
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  define('DB_CONNECTED', true);
  echo '<h1>Connection success!</h1>';


  $now = time();
  $minus_hour = $now - 60*60;
  $country = 'Polska';

  $stmt = $dbh->prepare('SELECT * FROM customers WHERE country = :country AND created_at < :hour');
 // przygotowanie szkieletu zapytania

  $stmt->bindValue(':country', $country, PDO::PARAM_STR); // przypisanie zmiennej $country do :country
  $stmt->bindValue(':hour', $minus_hour, PDO::PARAM_INT); // przypisanie zmiennej $minus_hour do :hour, jak widzimy nazwa placeholdera nie musi się równać nazwie zmiennej
  $result = $stmt->execute(); // wykonanie zapytania

  if($result !== false)
  {
    // wyświetlenie wyniku;
    while($row = $stmt->fetch())
    {
      echo 'id: ' . $row['customer_id']  .
     ', imię:' . $row['first_name']  .
     ', nazwisko:' . $row['last_name'] .
     '<br />';
    }
  }

  // zwalniamy kursor
  $stmt->closeCursor();
  unset($stmt);

  // zamknięcie połączenia z bazą
  $dbh = null;

  // wyłapanie wyjątku w przypadku błędu połączenia z bazą
} catch(PDOException $e)
{
  die('Unable to connect: ' . $e->getMessage());
}
?>
[/code]

6. Jakie z tego płyną korzyści?

1. Szkielet zapytania jest wysyłany jedynie raz, zatem oszczędzamy na ilości zapytań, jeśli np. w tym miejscu mamy zapytanie dodające nowe rekordy – to zyskujemy znacznie na prędkości. Do wysłanego RAZ szkieletu zapytania dosyłane są jedynie wartości zmiennych odpowiadające danym placeholderom.
2. Elastyczność – ten sam szkielet zapytania możemy wykorzystać wielokrotnie, podmieniając jedynie wartości zmiennych.
3. Informujemy jakiego typu zmienną przesyłamy, więc zwalnia nas to jednocześnie z ujmowania odpowiednich wartości w cudzysłowy.
4. Daje nam to z automatu prosty system walidacji, gdyż mechanizm nie przyjmie w parametrze wartości innej, niż określona - nie wyślemy np. stringa do pola zdefiniowanego jako integer.
5. I chyba najważniesze - mechanizm ten całkowicie zabezpiecza przed atakami typu SQL Injection, polegającymi na wstrzykiwaniu do zapytania spreparowanych wartości. Tutaj to nie zadziała, gdyż zmienne nie są wysyłane razem z zapytaniem - są przetwarzane oddzielnie.

Jak widać same korzyści i ani jednego minusa, no moża poza faktem, że kod nam się minimalnie wydłuża. Radzę wyrobić sobie nawyk co do stosowania zapytań sparametryzowanych nawet przy najprostszych zapytaniach. Kod staje się może nieco dłuższy, ale nie powinno to chyba stanowić większego problemu biorąc pod uwagę ogrom korzyści jakie płyną ze stosowania takiego podejścia do odpytywania bazy.

W następnej, ostatniej części zobaczymy jak do bazy dodawać rekordy oraz jak je aktualizować i usuwać. Będzie krótko, gdyż nie różni się to zbytnio od poznanych do tej pory procedur.

Oficjalny, pełny manual biblioteki PDO znajduje się tutaj: http://php.net/manual/en/book.pdo.php

<< Część 2: Pobieranie rekordów

Część 4: Dodawanie, edycja i usuwanie rekordów >>