【PHP】pg_query_params関数を使ったクエリ実行方法を解説

pg_query_params関数の使い方、理解していますか？

pg_query_params関数は、PHPからPostgreSQLに対してSQLコマンドを実行する際に利用する関数です。しかし、どのように関数を利用すればよいのか、他の関数と機能がどのように違うのか理解できていない人もいることでしょう。

そこで今回は、

・pg_query_params関数の機能と使い方
・pg_query関数との違い

について解説します。

1 pg_query_params関数の基本
2 pg_query_params関数の使用例
3 pg_query関数との違い
4 まとめ

pg_query_params関数の基本

pg_query_params関数は、PostgreSQLに対してSQLコマンドを送信するための関数です。引数と戻り値は以下の通りです。

引数	第1引数	コネクションリソース
	第2引数	SQL
	第3引数	パラメータ
戻り値		SQL実行結果

PHPのバージョン5.1.0で実装された関数で、バージョン7.4以降のPostgreSQLに対して動作します。

第1引数：コネクションリソース

第1引数のコネクションリソースは、pg_connect関数などによってPostgreSQLに接続したときに返されるリソース情報のことです。SQLを実行する際は、どのデータベースに対して実行するかをコネクションリソースを用いて設定します。

例えば、2つのデータベースが存在し、各データベースに対してSQLを実行する際は、以下のサンプルコードのように記述します。

<?php
/**
 * データベースごとにSQLを実行するサンプルコード
 * 
 * ※実行する際は、データベース名・テーブル名を適宜書き換えてください。
 */

// データベース「phpdb」への接続文字列
$connstr_phpdb   = "host=localhost port=5432 dbname=phpdb user=postgres password=1234";

// データベース「phpdb_2」への接続文字列
$connstr_phpdb_2 = "host=localhost port=5432 dbname=phpdb_2 user=postgres password=1234";

// データベース「phpdb」への接続
$dbconn_phpdb = pg_connect($connstr_phpdb);
if (!$dbconn_phpdb) die('Failed to connect to phpdb'.pg_last_error());

// データベース「phpdb_2」への接続
$dbconn_phpdb_2 = pg_connect($connstr_phpdb_2);
if (!$dbconn_phpdb_2) die('Failed to connect to phpdb_2'.pg_last_error());

// データベース「phpdb」に対してSQL実行
$res = pg_query_params($dbconn_phpdb, "SELECT * FROM $1;", array('users'));

// データベース「phpdb_2」に対してSQL実行
$res = pg_query_params($dbconn_phpdb_2, "SELECT * FROM $1;", array('users'));

<?php

/**

* データベースごとにSQLを実行するサンプルコード

* ※実行する際は、データベース名・テーブル名を適宜書き換えてください。

// データベース「phpdb」への接続文字列

$connstr_phpdb = "host=localhost port=5432 dbname=phpdb user=postgres password=1234";

// データベース「phpdb_2」への接続文字列

$connstr_phpdb_2 = "host=localhost port=5432 dbname=phpdb_2 user=postgres password=1234";

// データベース「phpdb」への接続

$dbconn_phpdb = pg_connect($connstr_phpdb);

if (!$dbconn_phpdb) die('Failed to connect to phpdb'.pg_last_error());

// データベース「phpdb_2」への接続

$dbconn_phpdb_2 = pg_connect($connstr_phpdb_2);

if (!$dbconn_phpdb_2) die('Failed to connect to phpdb_2'.pg_last_error());

// データベース「phpdb」に対してSQL実行

$res = pg_query_params($dbconn_phpdb, "SELECT * FROM $1;", array('users'));

// データベース「phpdb_2」に対してSQL実行

$res = pg_query_params($dbconn_phpdb_2, "SELECT * FROM $1;", array('users'));

コネクションリソースは、省略することも可能です。省略した際は、直近で作成されたコネクションリソースをデフォルトで指定するように動作します。

例えば、以下のサンプルコードの場合、SQLはデータベース「phpdb」に対して実行するように動作します。開発するアプリケーション全体に対して、1つのデータベースしか利用しない場合は、コードの記述量を減らすために第1引数を省略するのも一手です。

<?php

// データベース「phpdb」への接続文字列
$connstr_phpdb   = "host=localhost port=5432 dbname=phpdb user=postgres password=1234";

// データベース「phpdb_2」への接続文字列
$connstr_phpdb_2 = "host=localhost port=5432 dbname=phpdb_2 user=postgres password=1234";

// データベース「phpdb_2」への接続
$dbconn_phpdb_2 = pg_connect($connstr_phpdb_2);
if (!$dbconn_phpdb_2) die('Failed to connect to phpdb_2'.pg_last_error());

// データベース「phpdb」への接続
$dbconn_phpdb = pg_connect($connstr_phpdb);
if (!$dbconn_phpdb) die('Failed to connect to phpdb'.pg_last_error());

// 直近で作成されたリソースをデフォルトで指定する
// 今回は「phpdb」に対してSQLを実行する
$res = pg_query_params("SELECT * FROM users WHERE name = $1", array("Sato Taro"));
if(!$res) echo "データの取得に失敗しました";

<?php

// データベース「phpdb」への接続文字列

$connstr_phpdb = "host=localhost port=5432 dbname=phpdb user=postgres password=1234";

// データベース「phpdb_2」への接続文字列

$connstr_phpdb_2 = "host=localhost port=5432 dbname=phpdb_2 user=postgres password=1234";

// データベース「phpdb_2」への接続

$dbconn_phpdb_2 = pg_connect($connstr_phpdb_2);

if (!$dbconn_phpdb_2) die('Failed to connect to phpdb_2'.pg_last_error());

// データベース「phpdb」への接続

$dbconn_phpdb = pg_connect($connstr_phpdb);

if (!$dbconn_phpdb) die('Failed to connect to phpdb'.pg_last_error());

// 直近で作成されたリソースをデフォルトで指定する

// 今回は「phpdb」に対してSQLを実行する

$res = pg_query_params("SELECT * FROM users WHERE name = $1", array("Sato Taro"));

if(!$res) echo "データの取得に失敗しました";

第2引数：SQL

第2引数は、パラメータ化されたSQLを設定します。パラメータ化とは、SQL文の中で「リテラル」として記述される部分を、プレースホルダに置き換えることです。例えば、以下のSQL文は、VALUESキーワードの後に記述されている「1」「’apple’」「130」がリテラルにあたります。

INSERT INTO products(id, name, price)VALUES(1, 'apple', 130);

1	INSERT INTO products(id, name, price)VALUES(1, 'apple', 130);

パラメータ化する際は以下のサンプルコードのように、リテラルの部分を$1、$2……と設定します。

<?php
// （例）productsテーブルにデータを挿入するSQL文
// パラメータの部分は、$1、$2、…… と記述する
$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

<?php

// （例）productsテーブルにデータを挿入するSQL文

// パラメータの部分は、$1、$2、…… と記述する

$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

パラメータ化することによって、第3引数で設定した値が後々挿入されるように動作します。

第3引数：パラメータ

第3引数は、パラメータ化した箇所（$1、$2、…にあたる部分）に実際に当てはめる値を配列形式で設定します。先程示した、productテーブルにデータを挿入するSQLを例にした場合、以下のサンプルコードのように記述します。

<?php
// （例）productsテーブルにデータを挿入するSQL文
$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

// 挿入するデータを配列で定義
$param = array(1, 'apple', 130);

// SQL実行
$res = pg_query_params($dbconn_phpdb, $sql, $param);

<?php

// （例）productsテーブルにデータを挿入するSQL文

$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

// 挿入するデータを配列で定義

$param = array(1, 'apple', 130);

// SQL実行

$res = pg_query_params($dbconn_phpdb, $sql, $param);

以下のサンプルコードのように、パラメータの個数と配列の要素数を合わせていない場合、SQLを正常に実行できないので注意してください。

<?php
// パラメータは3つ
$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

// 挿入するデータの要素数は1つ
$param = array('apple');

// 実行すると以下のエラーが出力される
// Query failed: ERROR: バインドメッセージは1パラメータを提供しましたが、準備された文""では3必要でした
$res = pg_query_params($dbconn_phpdb, $sql, $param);

<?php

// パラメータは3つ

$sql = "INSERT INTO products(id, name, price)VALUES($1, $2, $3)";

// 挿入するデータの要素数は1つ

$param = array('apple');

// 実行すると以下のエラーが出力される

// Query failed: ERROR: バインドメッセージは1パラメータを提供しましたが、準備された文""では3必要でした

$res = pg_query_params($dbconn_phpdb, $sql, $param);

pg_query_params関数の使用例

ここからは、いくつか例を挙げながらpg_query_params関数の使い方を詳しく解説します。なお、これから示すサンプルコードは、データベースに接続する処理を省略しています。実行する際には、データベースへの接続処理を実装することを忘れずに行いましょう。

データの挿入

商品ID、商品名、価格の3つのカラムから構成されるテーブル「products」を考えてみます。

カラム	型	NULL許容
商品ID（id）	serial	×
商品名（name）	char	○
価格（price）	int	○

上記のテーブルに対してデータを複数個挿入するサンプルコードを、以下に示します。サンプルコードでは、挿入対象のデータ配列をforeachによって順に抽出し、商品名と価格を各々のパラメータに当てはめて1つずつSQLを実行しています。ちなみに、idは設定しなくても自動的に採番されるため、サンプルコードでは特に記述していません。

<?php
// 挿入対象のデータ配列
$input_product_list = [
    'りんご'     => 130,
    '消しゴム'   => 320,
    'マスク'     => 1000,
    'Tシャツ'    => 3200,
    'まくら'     => 6500,
    'ヘッドホン' => 12000,
];

// SQL文の定義
$insert_sql = 'INSERT INTO products (name, price) VALUES ($1, $2)';

foreach($input_product_list as $name => $price) {
    // データ挿入
    $res = pg_query_params($dbconn, $insert_sql, array($name, $price));
}

<?php

// 挿入対象のデータ配列

$input_product_list = [

'りんご' => 130,

'消しゴム' => 320,

'マスク' => 1000,

'Tシャツ' => 3200,

'まくら' => 6500,

'ヘッドホン' => 12000,

];

// SQL文の定義

$insert_sql = 'INSERT INTO products (name, price) VALUES ($1, $2)';

foreach($input_product_list as $name => $price) {

// データ挿入

$res = pg_query_params($dbconn, $insert_sql, array($name, $price));

}

上記サンプルコードを実行し、SQL Shellでデータが挿入されているか確認した結果は、以下の通りです。

データの検索

以下のようなテーブル「tasks」を考えてみます。

カラム	型	NULL許容
タスクID（id）	serial	×
開始日（start_date）	date	○
完了日（due_date）	date	○
タスク内容（task_details）	char	○
完了状態（status）	int	○

また、上記テーブルに対して下記のようにデータが保存されているとします。なお、完了状態の値の指す意味は「0 = 未着手」「1 = 完了」「2 = 中止」です。

テーブル「tasks」に対して、開始日で検索し結果を表示するサンプルコードを、以下に示します。サンプルコードでは、SQLのWHERE句で指定する開始日をパラメータ化しています。SQL実行後は、pg_fetch_all関数で取得データを取り出し、リストタグを用いて結果を表示します。

<?php

// SQL文の定義
$sql = 'SELECT start_date, due_date, task_detail
        FROM tasks
        WHERE start_date >= $1 AND start_date <= $2';

// 検索する期間
$date_start = '2021-01-01';
$date_end = '2021-01-31';

// データ取得
$res = pg_query_params($dbconn, $sql, array($date_start, $date_end));

// 取得内容のフェッチ
$task_list = pg_fetch_all($res);
if(count($task_list) === 0) echo '該当データなし';

// 取得結果表示
echo "<ul>";
foreach($task_list as $task){
    echo '<li>開始日:'.$task['start_date']."  期限日:".$task['due_date']." タスク:".$task['task_detail']."</li>";
}
echo "</ul>";

<?php

// SQL文の定義

$sql = 'SELECT start_date, due_date, task_detail

FROM tasks

WHERE start_date >= $1 AND start_date <= $2';

// 検索する期間

$date_start = '2021-01-01';

$date_end = '2021-01-31';

// データ取得

$res = pg_query_params($dbconn, $sql, array($date_start, $date_end));

// 取得内容のフェッチ

$task_list = pg_fetch_all($res);

if(count($task_list) === 0) echo '該当データなし';

// 取得結果表示

echo "<ul>";

foreach($task_list as $task){

echo '<li>開始日:'.$task['start_date']." 期限日:".$task['due_date']." タスク:".$task['task_detail']."</li>";

}

echo "</ul>";

ブラウザで確認した結果は、以下の通りです。

データの更新

引き続き「tasks」テーブルについて考えてみます。

テーブルに保存されているデータの内、開始日が3月のデータ対して、完了状態を「0=未着手」から「1=完了」に更新するサンプルコードを、以下に示します。データの更新は、更新する値と更新対象のデータの条件をパラメータ化します。

<?php

// SQL文の定義
$sql = 'UPDATE tasks
        SET status = $1
        WHERE start_date >= $2 AND start_date <= $3';

// 更新後の値
$updated_status = 1;
// 更新対象データの期間
$date_start = '2021-03-01';
$date_end   = '2021-03-31';

// データ更新
$res = pg_query_params($dbconn, $sql, array($updated_status, $date_start, $date_end));

<?php

// SQL文の定義

$sql = 'UPDATE tasks

SET status = $1

WHERE start_date >= $2 AND start_date <= $3';

// 更新後の値

$updated_status = 1;

// 更新対象データの期間

$date_start = '2021-03-01';

$date_end = '2021-03-31';

// データ更新

$res = pg_query_params($dbconn, $sql, array($updated_status, $date_start, $date_end));

SQL実行前の更新対象データは、以下の通りです。

SQL実行後は、完了状態（status）の値が1に更新されていることが分かります。

pg_query関数との違い

pg_query_params関数と機能が似ている関数として、pg_query関数が存在します。ここでは、pg_query関数とpg_query_params関数の違いについて解説しましょう。

pg_query関数とは？

pg_query関数も、SQLを実行できる関数ですが、引数は2つしか取りません。また、第2引数に設定するSQL文もパラメータ化する必要はありません。

2つの関数の違いを比較するため、従業員の情報を保存するテーブル「employees」から、名前が「Sato Taro」のデータを検索する処理を考えてみます。以下2つのサンプルコードは、pg_query関数を利用した検索処理とpg_query_params関数を利用した検索処理の両方を並べています。いずれの処理を実行しても、同じ結果が返ってきます。

<?php
// 入力値
$input_name = "Sato Taro";

// SQL文の条件として、入力値を直接連結
$sql = "SELECT * FROM employees
        WHERE name = '".$input_name."';";

// pg_query関数でSQL実行
$res = pg_query($dbconn, $sql);

<?php

// 入力値

$input_name = "Sato Taro";

// SQL文の条件として、入力値を直接連結

$sql = "SELECT * FROM employees

WHERE name = '".$input_name."';";

// pg_query関数でSQL実行

$res = pg_query($dbconn, $sql);

<?php
// 入力値
$input_name = "Sato Taro";

// SQL文の条件をパラメータ化
$sql = "SELECT * FROM employees
        WHERE name = $1;";

// pg_query_params関数でSQL実行
$res = pg_query_params($dbconn, $sql, array($input_name));

<?php

// 入力値

$input_name = "Sato Taro";

// SQL文の条件をパラメータ化

$sql = "SELECT * FROM employees

WHERE name = $1;";

// pg_query_params関数でSQL実行

$res = pg_query_params($dbconn, $sql, array($input_name));

2つの関数の違いを理解するためには、SQLインジェクションと事象について理解する必要があります。

SQLインジェクションはプログラムの脆弱性の一種

SQLインジェクションは、意図しないSQLを実行することでデータベースに対して不正な操作を行うことです。SQLインジェクションによって、外部に漏らしてはならないデータが抜き取られたり、データが改ざんされたりする危険があり、対策を講じなければなりません。

pg_query関数は、使い方を誤るとSQLインジェクションによってデータベースが不正に操作されるおそれがあります。先程示したpg_query関数のサンプルコードについて考えてみます。

// 入力値（画面から入力された値が格納されていることを想定）
$input_name = "Sato Taro";

// SQL文の条件として、入力値を直接連結
$sql = "SELECT * FROM employees
        WHERE name = '".$input_name."';";

// 入力値（画面から入力された値が格納されていることを想定）

$input_name = "Sato Taro";

// SQL文の条件として、入力値を直接連結

$sql = "SELECT * FROM employees

WHERE name = '".$input_name."';";

上記サンプルコードは、WHERE句によって条件指定する際に、画面から入力された文字列「Sato Taro」を連結することで、「Sato Taro」に該当するデータを検索するSQL文を作成しています。

ここで、画面から以下の文字列が入力されたとしましょう。

'; DELETE FROM employees --

1	'; DELETE FROM employees --

すると、以下のSQL文が作成されます。

SELECT * FROM employees WHERE name = ''; DELETE FROM employees --';";

1	SELECT * FROM employees WHERE name = ''; DELETE FROM employees --';";

入力された文字列は、employeesテーブルに保存されているデータを削除するSQL文です。本来、名前を入力値として想定しているのにも関わらず、SQL文が入力されることにより、意図しない操作が実行されデータがすべて削除されてしまいます。これがSQLインジェクションです。

2021.02.15sqlインジェクションと対策方法のサニタイジングとは？詳しく解説します

SQLインジェクションを防げるpg_query_params関数

pg_query_params関数は、pg_query関数が持つ脆弱性を克服し、SQLインジェクションを防ぐことができます。SQL文をパラメータ化することによって、入力されたSQL文を通常の文字列として解釈するからです。

例えば以下のサンプルコードのように、パラメータ化SQL文とpg_query_params関数で処理を実装した場合は、名前が「’; DELETE FROM employees –」のデータを検索するSQL文として解釈されます。

<?php
// 入力値
$input_name = "'; DELETE FROM employees --";

// SQL文の条件をパラメータ化
$sql = "SELECT * FROM employees
        WHERE name = $1;";

// SQL実行
$res = pg_query_params($dbconn, $sql, array($input_name));

<?php

// 入力値

$input_name = "'; DELETE FROM employees --";

// SQL文の条件をパラメータ化

$sql = "SELECT * FROM employees

WHERE name = $1;";

// SQL実行

$res = pg_query_params($dbconn, $sql, array($input_name));

データベースを操作する際に、画面から入力された値を基にSQL文を作成する必要がある場合は、pg_query_params関数を利用することが推奨されています。

まとめ

いかがでしたか？

今回は、

・pg_query_params関数の機能と使い方
・pg_query関数との違い

について解説しました。

pg_query_paramsは、SQLコマンドとパラメータを分離でき、直接パラメータを書き込むよりも扱いやすいのが特徴です。他の関数との違いを理解しつつ、積極的に利用しましょう。

2021.02.20データベースとは一体何なのか？初心者の疑問をまとめて解決

ウェブカツは「年収1000万円」を目指すフリーランスITエンジニアを育成するオンライン最大級のプログラミングスクールとして、元医師やBIG4税理士法人会計士など高学歴高経歴な方から、中卒土方、40代主婦、海外在住者など様々な方が通われています。
現役ITエンジニアとして活躍し、富裕層でプロのファイナンシャルプランナーでもある顧問「かずきち」が教える【フリーランスITエンジニアになって10年で1億の資産を築く方法】など他のプログラミングスクールとは全く異なり、「転職をゴール」とはせずに「会社に依存せずに外で稼げる力」を身につけさせています。