SQL přístup
Nette Database nabízí dvě cesty: můžete psát SQL dotazy sami (SQL přístup), nebo je nechat generovat automaticky (viz Explorer). SQL přístup vám dává plnou kontrolu nad dotazy a přitom zajišťuje jejich bezpečné sestavení.
Detaily k připojení a konfiguraci databáze najdete v kapitole Připojení a konfigurace.
Základní dotazování
Pro dotazování do databáze slouží metoda query()
. Ta vrací objekt ResultSet, který reprezentuje výsledek dotazu.
V případě selhání metoda vyhodí výjimku. Výsledek dotazu můžeme procházet pomocí cyklu
foreach
, nebo použít některou z pomocných funkcí.
Pro bezpečné vkládání hodnot do SQL dotazů používáme parametrizované dotazy. Nette Database je dělá maximálně jednoduché – stačí za SQL dotaz přidat čárku a hodnotu:
Při více parametrech máte dvě možnosti zápisu. Buď můžete SQL dotaz „prokládat“ parametry:
Nebo napsat nejdříve celý SQL dotaz a pak připojit všechny parametry:
Ochrana před SQL injection
Proč je důležité používat parametrizované dotazy? Protože vás chrání před útokem zvaným SQL injection, při kterém by útočník mohl podstrčit vlastní SQL příkazy a tím získat nebo poškodit data v databázi.
Nikdy nevkládejte proměnné přímo do SQL dotazu! Vždy používejte parametrizované dotazy, které vás ochrání před SQL injection.
Seznamte se s možnými bezpečnostními riziky.
Techniky dotazování
Podmínky WHERE
Podmínky WHERE můžete zapsat jako asociativní pole, kde klíče jsou názvy sloupců a hodnoty jsou data pro porovnání. Nette Database automaticky vybere nejvhodnější SQL operátor podle typu hodnoty.
V klíči můžete také explicitně specifikovat operátor pro porovnání:
Nette automaticky ošetřuje speciální případy jako null
hodnoty nebo pole.
Pro negativní podmínky použijte operátor NOT
:
Pro spojování podmínek se používá operátor AND
. To lze změnit pomocí zástupného symbolu ?or.
Pravidla ORDER BY
Řazení ORDER BY
se dá zapsat pomocí pole. V klíčích uvedeme sloupce a hodnotou bude boolean určující,
zda řadit vzestupně:
Vkládání dat (INSERT)
Pro vkládání záznamů se používá SQL příkaz INSERT
.
Metoda getInsertId()
vrátí ID naposledy vloženého řádku. U některých databází (např. PostgreSQL) je
nutné jako parametr specifikovat název sekvence, ze které se má ID generovat pomocí
$database->getInsertId($sequenceId)
.
Jako parametry můžeme předávat i speciální hodnoty jako soubory, objekty DateTime nebo výčtové typy.
Vložení více záznamů najednou:
Vícenásobný INSERT je mnohem rychlejší, protože se provede jediný databázový dotaz, namísto mnoha jednotlivých.
Bezpečnostní upozornění: Nikdy nepoužívejte jako $values
nevalidovaná data. Seznamte se s možnými riziky.
Aktualizace dat (UPDATE)
Pro aktualizacizáznamů se používá SQL příkaz UPDATE
.
Počet ovlivněných řádků vrátí $result->getRowCount()
.
Pro UPDATE můžeme využít operátorů +=
a -=
:
Příklad vložení, nebo úpravy záznamu, pokud již existuje. Použijeme techniku ON DUPLICATE KEY UPDATE
:
Všimněte si, že Nette Database pozná, v jakém kontextu SQL příkazu parametr s polem vkládáme a podle toho z něj
sestaví SQL kód. Takže z prvního pole sestavil (id, name, year) VALUES (123, 'Jim', 1978)
, zatímco druhé
převedl do podoby name = 'Jim', year = 1978
. Podroběji se tomu věnujeme v části Hinty pro sestavování SQL.
Mazání dat (DELETE)
Pro mazání záznamů se používá SQL příkaz DELETE
. Příklad se získáním počtu
smazaných řádků:
Hinty pro sestavování SQL
Hint je speciální zástupný symbol v SQL dotazu, který říká, jak se má hodnota parametru přepsat do SQL výrazu:
Hint | Popis | Automaticky se použije |
---|---|---|
?name |
použije pro vložení názvu tabulky nebo sloupce | – |
?values |
vygeneruje (key, ...) VALUES (value, ...) |
INSERT ... ? , REPLACE ... ? |
?set |
vygeneruje přiřazení key = value, ... |
SET ? , KEY UPDATE ? |
?and |
spojí podmínky v poli operátorem AND |
WHERE ? , HAVING ? |
?or |
spojí podmínky v poli operátorem OR |
– |
?order |
vygeneruje klauzuli ORDER BY |
ORDER BY ? , GROUP BY ? |
Pro dynamické vkládání názvů tabulek a sloupců do dotazu slouží zástupný symbol ?name
. Nette Database
se postará o správné ošetření identifikátorů podle konvencí dané databáze (např. uzavření do zpětných uvozovek
v MySQL).
Upozornění: symbol ?name
používejte pouze pro názvy tabulek a sloupců z validovaných vstupů, jinak
se vystavujete bezpečnostnímu riziku.
Ostatní hinty obvykle není potřeba uvádět, neboť Nette používá při skládání SQL dotazu chytrou autodetekci (viz
třetí sloupec tabulky). Ale můžete jej využít například v situaci, kdy chcete spojit podmínky pomocí OR
namísto AND
:
Speciální hodnoty
Kromě běžných skalárních typů (string, int, bool) můžete jako parametry předávat i speciální hodnoty:
- soubory:
fopen('image.gif', 'r')
vloží binární obsah souboru - datum a čas: objekty
DateTime
se převedou na databázový formát - výčtové typy: instance
enum
se převedou na jejich hodnotu - SQL literály: vytvořené pomocí
Connection::literal('NOW()')
se vloží přímo do dotazu
U databází, které nemají nativní podporu pro datový typ datetime
(jako SQLite a Oracle), se
DateTime
převádí na hodnotu určenou v konfiguraci databáze položkou
formatDateTime
(výchozí hodnota je U
– unix timestamp).
SQL literály
V některých případech potřebujete jako hodnotu uvést přímo SQL kód, který se ale nemá chápat jako řetězec a
escapovat. K tomuto slouží objekty třídy Nette\Database\SqlLiteral
. Vytváří je metoda
Connection::literal()
.
Nebo alternativě:
SQL literály mohou obsahovat parametry:
Díky čemuž můžeme vytvářet zajímavé kombinace:
Získání dat
Zkratky pro SELECT dotazy
Pro zjednodušení načítání dat nabízí Connection
několik zkratek, které kombinují volání
query()
s následujícím fetch*()
. Tyto metody přijímají stejné parametry jako
query()
, tedy SQL dotaz a volitelné parametry. Plnohodnotný popis metod fetch*()
najdete níže.
fetch($sql, ...$params): ?Row |
Provede dotaz a vrátí první řádek jako objekt Row |
fetchAll($sql, ...$params): array |
Provede dotaz a vrátí všechny řádky jako pole objektů Row |
fetchPairs($sql, ...$params): array |
Provede dotaz a vrátí asocitivní pole, kde první sloupec představuje klíč a druhý hodnotu |
fetchField($sql, ...$params): mixed |
Provede dotaz a vrátí hodnotu prvního políčka z prvního řádku |
fetchList($sql, ...$params): ?array |
Provede dotaz a vrací první řádek jako indexované pole |
Příklad:
foreach
– iterace přes řádky
Po vykonání dotazu se vrací objekt ResultSet,
který umožňuje procházet výsledky několika způsoby. Nejsnazší způsob, jak vykonat dotaz a získat řádky, je
iterováním v cyklu foreach
. Tento způsob je paměťově nejúspornější, neboť vrací data postupně a
neukládá si je do paměti najednou.
ResultSet
lze iterovat pouze jednou. Pokud potřebujete iterovat opakovaně, musíte nejprve načíst
data do pole, například pomocí metody fetchAll()
.
fetch(): ?Row
Vrací řádek jako objekt Row
. Pokud už neexistují další řádky, vrací null
. Posune interní
ukazatel na další řádek.
fetchAll(): array
Vrací všechny zbývající řádky z ResultSetu
jako pole objektů Row
.
fetchPairs(string|int|null $key = null, string|int|null $value = null): array
Vrátí výsledky jako asociativní pole. První argument určuje název sloupce, který se použije jako klíč v poli, druhý argument určuje název sloupce, který se použije jako hodnota:
Pokud uvedeme pouze první parametr, bude hodnotou celý řádek, tedy objekt Row
:
V případě duplicitních klíčů se použije hodnota z posledního řádku. Při použití null
jako klíče
bude pole indexováno numericky od nuly (pak ke kolizím nedochází):
fetchPairs(Closure $callback): array
Alternativně můžete jako parametr uvést callback, který bude pro každý řádek vracet buď samotnou hodnotu, nebo dvojici klíč-hodnota.
fetchField(): mixed
Vrací hodnotu prvního políčka z aktuálního řádku. Pokud už neexistují další řádky, vrací null
.
Posune interní ukazatel na další řádek.
fetchList(): ?array
Vrací řádek jako indexované pole. Pokud už neexistují další řádky, vrací null
. Posune interní ukazatel
na další řádek.
getRowCount(): ?int
Vrací počet ovlivněných řádků posledním dotazem UPDATE
nebo DELETE
. Pro SELECT
je to počet vrácených řádků, ale ten nemusí být znám – v takovém případě metoda vrátí null
.
getColumnCount(): ?int
Vrací počet sloupců v ResultSetu
.
Informace o dotazech
Pro ladicí účely můžeme získat informace o posledním provedeném dotazu:
Pro zobrazení výsledku jako HTML tabulky lze použít:
ResultSet nabízí informace o typech sloupců:
Logování dotazů
Můžeme implementovat vlastní logování dotazů. Událost onQuery
je pole callbacků, které se zavolají po
každém provedeném dotazu: