SQLアクセス

Nette Databaseは2つの方法を提供します：SQLクエリを自分で記述する（SQLアクセス）、または自動的に生成させる（Explorerを参照）。SQLアクセスはクエリを完全に制御でき、同時に安全な構築を保証します。

データベース接続と設定の詳細については、接続と設定の章を参照してください。

基本的なクエリ

データベースにクエリを実行するには、query()メソッドを使用します。これは、クエリの結果を表すResultSetオブジェクトを返します。失敗した場合、メソッドは例外をスローします。 クエリの結果はforeachループを使用して反復処理するか、ヘルパー関数のいずれかを使用できます。

$result = $database->query('SELECT * FROM users'); foreach ($result as $row) {	echo $row->id;	echo $row->name; } 

SQLクエリに値を安全に挿入するには、パラメータ化されたクエリを使用します。Nette Databaseはこれを最大限に簡単にします – SQLクエリの後にカンマと値を追加するだけです：

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

複数のパラメータがある場合、2つの記述方法があります。SQLクエリにパラメータを「散りばめる」ことができます：

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

または、まず完全なSQLクエリを記述し、次にすべてのパラメータを追加します：

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

SQLインジェクションからの保護

パラメータ化されたクエリを使用することが重要なのはなぜでしょうか？ なぜなら、SQLインジェクションと呼ばれる攻撃から保護してくれるからです。この攻撃では、攻撃者が独自のSQLコマンドを挿入し、それによってデータベース内のデータを取得または破損させる可能性があります。

変数をSQLクエリに直接挿入しないでください！ SQLインジェクションから保護するために、常にパラメータ化されたクエリを使用してください。

// ❌ 危険なコード - SQLインジェクションに対して脆弱 $database->query("SELECT * FROM users WHERE name = '$name'"); // ✅ 安全なパラメータ化されたクエリ $database->query('SELECT * FROM users WHERE name = ?', $name); 

潜在的なセキュリティリスクについて理解してください。

クエリ技術

WHERE条件

WHERE条件は連想配列として記述でき、キーはカラム名、値は比較データです。Nette Databaseは、値の型に基づいて最適なSQL演算子を自動的に選択します。

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

キーで比較演算子を明示的に指定することもできます：

$database->query('SELECT * FROM users WHERE', [	'age >' => 25, // 演算子 > を使用	'name LIKE' => '%John%', // 演算子 LIKE を使用	'email NOT LIKE' => '%example.com%', // 演算子 NOT LIKE を使用 ]); // WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%' 

Netteは、null値や配列などの特殊なケースを自動的に処理します。

$database->query('SELECT * FROM products WHERE', [	'name' => 'Laptop', // 演算子 = を使用	'category_id' => [1, 2, 3], // IN を使用	'description' => null, // IS NULL を使用 ]); // WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL 

否定条件には演算子 NOT を使用します：

$database->query('SELECT * FROM products WHERE', [	'name NOT' => 'Laptop', // 演算子 <> を使用	'category_id NOT' => [1, 2, 3], // NOT IN を使用	'description NOT' => null, // IS NOT NULL を使用	'id' => [], // 省略されます ]); // WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL 

条件を結合するには演算子 AND が使用されます。これはプレースホルダ ?orを使用して変更できます。

ORDER BYルール

ORDER BYソートは配列を使用して記述できます。キーにカラムを指定し、値は昇順でソートするかどうかを示すブール値になります：

$database->query('SELECT id FROM author ORDER BY', [	'id' => true, // 昇順	'name' => false, // 降順 ]); // SELECT id FROM author ORDER BY `id`, `name` DESC 

データの挿入 (INSERT)

レコードを挿入するには、SQLコマンド INSERT を使用します。

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

getInsertId()メソッドは、最後に挿入された行のIDを返します。一部のデータベース（例：PostgreSQL）では、$database->getInsertId($sequenceId)を使用してIDを生成するシーケンス名をパラメータとして指定する必要があります。

パラメータとして、ファイル、DateTimeオブジェクト、または列挙型などの特別な値を渡すこともできます。

複数のレコードを一度に挿入する：

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

複数INSERTは、多くの個別のクエリではなく単一のデータベースクエリが実行されるため、はるかに高速です。

セキュリティ警告: $valuesとして検証されていないデータを使用しないでください。潜在的なリスクについて理解してください。

データの更新 (UPDATE)

レコードを更新するには、SQLコマンド UPDATE を使用します。

// 1つのレコードの更新 $values = [	'name' => 'John Smith', ]; $result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1); 

影響を受けた行数は $result->getRowCount() で返されます。

UPDATEには演算子 += および -= を使用できます：

$database->query('UPDATE users SET ? WHERE id = ?', [	'login_count+=' => 1, // login_count をインクリメント ], 1); 

レコードが存在する場合は挿入、存在しない場合は更新する例。ON DUPLICATE KEY UPDATEテクニックを使用します：

$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 

Nette Databaseが、SQLコマンドのどのコンテキストに配列パラメータを挿入するかを認識し、それに応じてSQLコードを構築することに注意してください。したがって、最初の配列から(id, name, year) VALUES (123, 'Jim', 1978)を構築し、2番目の配列をname = 'Jim', year = 1978の形式に変換しました。これについては、SQL構築のヒントセクションで詳しく説明します。

データの削除 (DELETE)

レコードを削除するには、SQLコマンド DELETE を使用します。削除された行数を取得する例：

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

SQL構築のヒント

ヒントは、SQLクエリ内の特別なプレースホルダーであり、パラメータ値をSQL式にどのように書き換えるかを示します：

テーブル名とカラム名をクエリに動的に挿入するには、プレースホルダ ?name を使用します。Nette Databaseは、特定のデータベースの規則に従って識別子を正しく処理します（たとえば、MySQLではバッククォートで囲む）。

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

警告: シンボル ?name は、検証された入力からのテーブル名とカラム名にのみ使用してください。そうしないと、セキュリティリスクにさらされます。

他のヒントは通常、NetteがSQLクエリを構築する際に賢い自動検出を使用するため（表の3番目の列を参照）、指定する必要はありません。ただし、たとえば AND の代わりに OR を使用して条件を結合したい場合などに使用できます：

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

特別な値

通常のスカラ型（string、int、bool）に加えて、パラメータとして特別な値を渡すこともできます：

• ファイル：fopen('image.gif', 'r') はファイルのバイナリコンテンツを挿入します
• 日付と時刻：DateTimeオブジェクトはデータベース形式に変換されます
• 列挙型：enumインスタンスはその値に変換されます
• SQLリテラル：Connection::literal('NOW()')を使用して作成されたものは、クエリに直接挿入されます

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

datetimeデータ型をネイティブにサポートしていないデータベース（SQLiteやOracleなど）の場合、DateTimeはデータベース設定のformatDateTime項目で指定された値（デフォルト値はU – Unixタイムスタンプ）に変換されます。

SQLリテラル

場合によっては、値として直接SQLコードを指定する必要がありますが、これは文字列として解釈されず、エスケープされるべきではありません。この目的のために、Nette\Database\SqlLiteralクラスのオブジェクトが使用されます。これらはConnection::literal()メソッドによって作成されます。

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

または代替案：

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

SQLリテラルにはパラメータを含めることができます：

$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) 

これにより、興味深い組み合わせを作成できます：

$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') 

データの取得

SELECTクエリのショートカット

データ取得を簡略化するために、Connectionはquery()の呼び出しとそれに続くfetch*()を組み合わせたいくつかのショートカットを提供します。これらのメソッドはquery()と同じパラメータ、つまりSQLクエリとオプションのパラメータを受け入れます。fetch*()メソッドの完全な説明は以下にあります。

例：

// fetchField() - 最初のセルの値を返します $count = $database->query('SELECT COUNT(*) FROM articles')	->fetchField(); 

foreach – 行の反復処理

クエリを実行した後、ResultSetオブジェクトが返され、これにより結果をいくつかの方法で反復処理できます。クエリを実行して行を取得する最も簡単な方法は、foreachループで反復処理することです。この方法は、データを段階的に返し、一度にメモリに保存しないため、メモリ効率が最も高くなります。

$result = $database->query('SELECT * FROM users'); foreach ($result as $row) {	echo $row->id;	echo $row->name;	// ... } 

ResultSetは一度しか反復処理できません。繰り返し反復処理する必要がある場合は、まずfetchAll()メソッドなどを使用してデータを配列に読み込む必要があります。

fetch(): ?Row

行をRowオブジェクトとして返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

$result = $database->query('SELECT * FROM users'); $row = $result->fetch(); // 最初の行を読み込みます if ($row) {	echo $row->name; } 

fetchAll(): array

ResultSetから残りのすべての行をRowオブジェクトの配列として返します。

$result = $database->query('SELECT * FROM users'); $rows = $result->fetchAll(); // すべての行を読み込みます foreach ($rows as $row) {	echo $row->name; } 

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

結果を連想配列として返します。最初の引数は配列のキーとして使用されるカラム名を指定し、2番目の引数は値として使用されるカラム名を指定します：

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

最初のパラメータのみを指定した場合、値は行全体、つまりRowオブジェクトになります：

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

キーが重複する場合、最後の行の値が使用されます。キーとしてnullを使用すると、配列はゼロから数値でインデックス付けされます（衝突は発生しません）：

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

fetchPairs (Closure $callback): array

あるいは、パラメータとしてコールバックを指定できます。これは、各行に対して値自体、またはキーと値のペアのいずれかを返します。

$result = $database->query('SELECT * FROM users'); $items = $result->fetchPairs(fn($row) => "$row->id - $row->name"); // ['1 - John', '2 - Jane', ...] // コールバックはキーと値のペアを持つ配列を返すこともできます： $names = $result->fetchPairs(fn($row) => [$row->name, $row->age]); // ['John' => 46, 'Jane' => 21, ...] 

fetchField(): mixed

現在の行の最初のフィールドの値を返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

$result = $database->query('SELECT name FROM users'); $name = $result->fetchField(); // 最初の行から名前を読み込みます 

fetchList(): ?array

行をインデックス付き配列として返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

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

getRowCount(): ?int

最後のUPDATEまたはDELETEクエリによって影響を受けた行数を返します。SELECTの場合、これは返された行数ですが、これは不明な場合があり、その場合メソッドはnullを返します。

getColumnCount(): ?int

ResultSet内のカラム数を返します。

クエリ情報

デバッグ目的で、最後に実行されたクエリに関する情報を取得できます：

echo $database->getLastQueryString(); // SQLクエリを出力します $result = $database->query('SELECT * FROM articles'); echo $result->getQueryString(); // SQLクエリを出力します echo $result->getTime(); // 実行時間を秒単位で出力します 

結果をHTMLテーブルとして表示するには、次を使用できます：

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

ResultSetはカラムの型に関する情報を提供します：

$result = $database->query('SELECT * FROM articles'); $types = $result->getColumnTypes(); foreach ($types as $column => $type) {	echo "$column は型 $type->type です"; // 例：'id は型 int です' } 

クエリのロギング

独自のクエリロギングを実装できます。イベントonQueryは、実行された各クエリの後に呼び出されるコールバックの配列です：

$database->onQuery[] = function ($database, $result) use ($logger) {	$logger->info('Query: ' . $result->getQueryString());	$logger->info('Time: ' . $result->getTime());	if ($result->getRowCount() > 1000) {	$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');	} };