SQL megközelítés

A Nette Database két utat kínál: írhat SQL lekérdezéseket saját maga (SQL megközelítés), vagy hagyhatja, hogy automatikusan generálódjanak (lásd Explorer). Az SQL megközelítés teljes ellenőrzést biztosít a lekérdezések felett, miközben garantálja azok biztonságos összeállítását.

Az adatbázis csatlakozásának és konfigurálásának részleteit a Csatlakozás és konfiguráció fejezetben találja.

Alapvető lekérdezés

Az adatbázis lekérdezéséhez a query() metódus szolgál. Ez egy ResultSet objektumot ad vissza, amely a lekérdezés eredményét reprezentálja. Hiba esetén a metódus kivételt dob. A lekérdezés eredményét foreach ciklussal járhatjuk be, vagy használhatunk néhány segédfüggvényt.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

Az értékek biztonságos beillesztéséhez az SQL lekérdezésekbe paraméterezett lekérdezéseket használunk. A Nette Database ezt maximálisan egyszerűvé teszi – elegendő az SQL lekérdezés után egy vesszőt és az értéket hozzáadni:

$database->query('SELECT * FROM users WHERE name = ?', $name);

Több paraméter esetén kétféle írásmód lehetséges. Vagy „átszőheti“ az SQL lekérdezést paraméterekkel:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Vagy először megírhatja a teljes SQL lekérdezést, majd csatolhatja az összes paramétert:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Védelem az SQL injection ellen

Miért fontos paraméterezett lekérdezéseket használni? Mert megvédenek az SQL injection nevű támadástól, amely során a támadó saját SQL parancsokat csempészhetne be, és ezzel adatokat szerezhetne vagy károsíthatna az adatbázisban.

Soha ne illesszen be változókat közvetlenül az SQL lekérdezésbe! Mindig használjon paraméterezett lekérdezéseket, amelyek megvédenek az SQL injection ellen.

// ❌ VESZÉLYES KÓD - sebezhető az SQL injection-nel szemben
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Biztonságos paraméterezett lekérdezés
$database->query('SELECT * FROM users WHERE name = ?', $name);

Ismerkedjen meg a lehetséges biztonsági kockázatokkal.

Lekérdezési technikák

WHERE feltételek

A WHERE feltételeket asszociatív tömbként írhatja le, ahol a kulcsok az oszlopnevek, az értékek pedig az összehasonlítandó adatok. A Nette Database automatikusan kiválasztja a legmegfelelőbb SQL operátort az érték típusa alapján.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

A kulcsban explicit módon is megadhatja az összehasonlítási operátort:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // a > operátort használja
	'name LIKE' => '%John%', // a LIKE operátort használja
	'email NOT LIKE' => '%example.com%', // a NOT LIKE operátort használja
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

A Nette automatikusan kezeli a speciális eseteket, mint a null értékek vagy tömbök.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // az = operátort használja
	'category_id' => [1, 2, 3], // az IN-t használja
	'description' => null,      // az IS NULL-t használja
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Negatív feltételekhez használja a NOT operátort:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // a <> operátort használja
	'category_id NOT' => [1, 2, 3], // a NOT IN-t használja
	'description NOT' => null,      // az IS NOT NULL-t használja
	'id' => [],                     // kihagyja
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

A feltételek összekapcsolásához az AND operátor használatos. Ezt a ?or helyettesítő karakterrel lehet megváltoztatni.

ORDER BY szabályok

Az ORDER BY rendezést tömb segítségével lehet leírni. A kulcsokban az oszlopokat adjuk meg, az érték pedig egy logikai érték lesz, amely meghatározza, hogy növekvő sorrendben kell-e rendezni:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // növekvő
	'name' => false, // csökkenő
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Adatbeszúrás (INSERT)

Rekordok beszúrásához az INSERT SQL parancsot használjuk.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

A getInsertId() metódus visszaadja az utoljára beszúrt sor ID-jét. Néhány adatbázisnál (pl. PostgreSQL) paraméterként meg kell adni annak a szekvenciának a nevét, amelyből az ID-t generálni kell a $database->getInsertId($sequenceId) segítségével.

Paraméterként átadhatunk Speciális értékek is, mint például fájlokat, DateTime objektumokat vagy enum típusokat.

Több rekord beszúrása egyszerre:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

A többszörös INSERT sokkal gyorsabb, mert egyetlen adatbázis-lekérdezés hajtódik végre, sok különálló helyett.

Biztonsági figyelmeztetés: Soha ne használjon validálatlan adatokat $values-ként. Ismerkedjen meg a lehetséges kockázatokkal.

Adatfrissítés (UPDATE)

Rekordok frissítéséhez az UPDATE SQL parancsot használjuk.

// Egy rekord frissítése
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Az érintett sorok számát a $result->getRowCount() adja vissza.

Az UPDATE-hez használhatjuk a += és -= operátorokat:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // a login_count inkrementálása
], 1);

Példa egy rekord beszúrására vagy módosítására, ha már létezik. Az ON DUPLICATE KEY UPDATE technikát használjuk:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Figyelje meg, hogy a Nette Database felismeri, milyen kontextusban illesztjük be a tömböt tartalmazó paramétert az SQL parancsba, és ennek megfelelően állítja össze belőle az SQL kódot. Tehát az első tömbből (id, name, year) VALUES (123, 'Jim', 1978)-t állított össze, míg a másodikat name = 'Jim', year = 1978 formára alakította át. Részletesebben ezzel az SQL összeállítási tippek részben foglalkozunk.

Adattörlés (DELETE)

Rekordok törléséhez a DELETE SQL parancsot használjuk. Példa a törölt sorok számának lekérésével:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

SQL összeállítási tippek

A hint egy speciális helyettesítő karakter az SQL lekérdezésben, amely megmondja, hogyan kell a paraméter értékét SQL kifejezéssé átírni:

Táblák és oszlopok nevének dinamikus beillesztéséhez a lekérdezésbe a ?name helyettesítő karakter szolgál. A Nette Database gondoskodik az azonosítók helyes kezeléséről az adott adatbázis konvenciói szerint (pl. backtickekbe zárás MySQL-ben).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (MySQL-ben)

Figyelmeztetés: a ?name szimbólumot csak validált bemenetekből származó tábla- és oszlopnevekhez használja, különben biztonsági kockázatnak teszi ki magát.

A többi hintet általában nem szükséges megadni, mivel a Nette okos automatikus felismerést használ az SQL lekérdezés összeállításakor (lásd a táblázat harmadik oszlopát). De használhatja például olyan helyzetben, amikor a feltételeket OR helyett AND-del szeretné összekötni:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Speciális értékek

A szokásos skalár típusokon (string, int, bool) kívül speciális értékeket is átadhat paraméterként:

• fájlok: fopen('image.gif', 'r') beilleszti a fájl bináris tartalmát
• dátum és idő: a DateTime objektumok adatbázis formátumra konvertálódnak
• enum típusok: az enum példányok értékükre konvertálódnak
• SQL literálok: a Connection::literal('NOW()') segítségével létrehozottak közvetlenül beillesztődnek a lekérdezésbe

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Azoknál az adatbázisoknál, amelyek nem rendelkeznek natív támogatással a datetime adattípushoz (mint a SQLite és az Oracle), a DateTime az adatbázis konfigurációjában a formatDateTime tétellel meghatározott értékre konvertálódik (az alapértelmezett érték U – unix timestamp).

SQL literálok

Néhány esetben szükség van arra, hogy értékként közvetlenül SQL kódot adjunk meg, amelyet azonban nem szabad stringként értelmezni és escapelni. Erre szolgálnak a Nette\Database\SqlLiteral osztály objektumai. Ezeket a Connection::literal() metódus hozza létre.

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Vagy alternatívaként:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

Az SQL literálok tartalmazhatnak paramétereket:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Ennek köszönhetően érdekes kombinációkat hozhatunk létre:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Adatlekérés

Rövidítések SELECT lekérdezésekhez

Az adatbetöltés egyszerűsítésére a Connection több rövidítést kínál, amelyek kombinálják a query() hívást a következő fetch*() hívásokkal. Ezek a metódusok ugyanazokat a paramétereket fogadják el, mint a query(), azaz az SQL lekérdezést és az opcionális paramétereket. A fetch*() metódusok teljes leírását alább találja.

Példa:

// fetchField() - visszaadja az első cella értékét
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

foreach – iteráció a sorokon

A lekérdezés végrehajtása után egy ResultSet objektumot kapunk vissza, amely lehetővé teszi az eredmények több módon történő bejárását. A legegyszerűbb módja a lekérdezés végrehajtásának és a sorok lekérésének a foreach ciklussal történő iterálás. Ez a módszer a memóriatakarékosabb, mivel az adatokat fokozatosan adja vissza, és nem tárolja őket egyszerre a memóriában.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

A ResultSet-et csak egyszer lehet iterálni. Ha ismételten kell iterálni, először be kell tölteni az adatokat egy tömbbe, például a fetchAll() metódussal.

fetch(): ?Row

Visszaad egy sort Row objektumként. Ha nincs több sor, null-t ad vissza. A belső mutatót a következő sorra mozgatja.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // betölti az első sort
if ($row) {
	echo $row->name;
}

fetchAll(): array

Visszaadja a ResultSet-ből az összes fennmaradó sort Row objektumok tömbjeként.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // betölti az összes sort
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs(string|int|null $key = null, string|int|null $value = null): array

Visszaadja az eredményeket asszociatív tömbként. Az első argumentum határozza meg az oszlop nevét, amely a tömb kulcsaként lesz használva, a második argumentum határozza meg az oszlop nevét, amely értékként lesz használva:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Ha csak az első paramétert adjuk meg, az érték a teljes sor lesz, azaz egy Row objektum:

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

Duplikált kulcsok esetén az utolsó sor értéke lesz használva. Ha null-t használunk kulcsként, a tömb numerikusan lesz indexelve nullától kezdve (ekkor nem történik ütközés):

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs(Closure $callback): array

Alternatívaként megadhat egy callbacket paraméterként, amely minden sorhoz vagy magát az értéket, vagy egy kulcs-érték párt ad vissza.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// A callback visszaadhat egy tömböt is kulcs & érték párral:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Visszaadja az aktuális sor első mezőjének értékét. Ha nincs több sor, null-t ad vissza. A belső mutatót a következő sorra mozgatja.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // betölti a nevet az első sorból

fetchList(): ?array

Visszaad egy sort indexelt tömbként. Ha nincs több sor, null-t ad vissza. A belső mutatót a következő sorra mozgatja.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Visszaadja az utolsó UPDATE vagy DELETE lekérdezés által érintett sorok számát. SELECT esetén ez a visszaadott sorok száma, de ez nem mindig ismert – ebben az esetben a metódus null-t ad vissza.

getColumnCount(): ?int

Visszaadja az oszlopok számát a ResultSet-ben.

Információk a lekérdezésekről

Debuggolási célokra lekérhetjük az utoljára végrehajtott lekérdezés információit:

echo $database->getLastQueryString();   // kiírja az SQL lekérdezést

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // kiírja az SQL lekérdezést
echo $result->getTime();           // kiírja a végrehajtási időt másodpercben

Az eredmény HTML táblázatként való megjelenítéséhez használható:

$result = $database->query('SELECT * FROM articles');
$result->dump();

A ResultSet információkat kínál az oszloptípusokról:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column típusa $type->type"; // pl. 'id típusa int'
}

Lekérdezések naplózása

Implementálhatunk saját lekérdezés-naplózást. Az onQuery esemény egy callback tömb, amely minden végrehajtott lekérdezés után meghívódik:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Lekérdezés: ' . $result->getQueryString());
	$logger->info('Idő: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Nagy eredményhalmaz: ' . $result->getRowCount() . ' sor');
	}
};