XAMPPでMySQLを使うその３

著者：北本敦

公開日：2022/10/21

最終更新日：2022/11/29

カテゴリー：技術情報

タグ：PHP／SQL

北本です。

前回は以下のようなPHPコードを紹介し、致命的な欠陥があると書いて終わりました。今回はその内容に具体的に触れていくことにします。前回からの続きなのでタイトルにはXAMPPやMySQLと書いていますが、今回の内容はそれらに特化したものではなくSQL・PHP全般に該当する内容かと思います。

test.php

<html>
<head><title>TEST</title></head>
<body>

<?php

$prefecture = $_GET["prefecture"];
$population_min = $_GET["population_min"];
$population_max = $_GET["population_max"];

$pdo = new PDO("mysql:host=127.0.0.1;dbname=testdb;charset=utf8mb4;", "root", "");

$sql = "SELECT name, prefecture, population FROM cities";

$where = "";
if(!empty($prefecture)){
	$where .= " prefecture = '" . $prefecture . "'";
}
if(!empty($population_min )){
	if(!empty($where)){
		$where .= " AND";
	}
	$where .= " population >= " . $population_min;
}
if(!empty($population_max)){
	if(!empty($where)){
		$where .= " AND";
	}
	$where .= " population <= " . $population_max;
}

if(!empty($where)){
	$sql .= " WHERE " . $where;
}

$stmt = $pdo->query($sql);
$result = $stmt->fetchAll();

foreach ( $result as $row ) {
    echo "名前: {$row["name"]}, 都道府県: {$row["prefecture"]}, 人口: {$row["population"]}<br>";
}

?>
</body>
</html>

<html>

<body>

<?php

$prefecture = $_GET["prefecture"];

$population_min = $_GET["population_min"];

$population_max = $_GET["population_max"];

$pdo = new PDO("mysql:host=127.0.0.1;dbname=testdb;charset=utf8mb4;", "root", "");

$sql = "SELECT name, prefecture, population FROM cities";

$where = "";

if(!empty($prefecture)){

$where .= " prefecture = '" . $prefecture . "'";

}

if(!empty($population_min )){

if(!empty($where)){

$where .= " AND";

}

$where .= " population >= " . $population_min;

}

if(!empty($population_max)){

if(!empty($where)){

$where .= " AND";

}

$where .= " population <= " . $population_max;

}

if(!empty($where)){

$sql .= " WHERE " . $where;

}

$stmt = $pdo->query($sql);

$result = $stmt->fetchAll();

foreach ( $result as $row ) {

echo "名前: {$row["name"]}, 都道府県: {$row["prefecture"]}, 人口: {$row["population"]}<br>";

}

</body>

</html>

早速ですが、欠陥が明らかになる操作をしてみます。都道府県のテキストボックスに以下の文字列を入力して「送信」ボタンをクリックしてみましょう。

'; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市

1	'; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市

何もない画面に遷移するはずです。

ではcitiesテーブル見てみましょう。

なんと、尼崎市が大阪府に編入されてしまっています！

というのも、以下のようなSELECT文とUPDATE文が実行されてしまったからです。

SELECT name, prefecture, population FROM cities WHERE prefecture = ''; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市'

1	SELECT name, prefecture, population FROM cities WHERE prefecture = ''; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市'

何もない画面が表示されたのは、SELECT文でprefecture = ”に該当するレコードをcitiesから取得しようとして1件も取得できなかったからです。そして、2つ目のUPDATE文が尼崎市のprefectureが大阪府に変更されてしまった原因です。例ではUPDATE文を実行させましたが、やろうと思えばINSERT文やDELETE文を忍び込ませてレコードの追加や削除をしたりテーブル自体を削除してしまったりすることもできるでしょう。

単純に文字列連結でSQL文を生成しているとこのような問題が起きてしまいます。典型的なSQLインジェクションの例です。

これを防ぐには以下のようなコードにします。

test.php

<html>
<head><title>TEST</title></head>
<body>

<?php

$prefecture = $_GET["prefecture"];
$population_min = $_GET["population_min"];
$population_max = $_GET["population_max"];

$pdo = new PDO("mysql:host=127.0.0.1;dbname=testdb;charset=utf8mb4;", "root", "");

$sql = "SELECT name, prefecture, population FROM cities";

$where = "";
if(!empty($prefecture)){
	$where .= " prefecture = :prefecture";
}
if(!empty($population_min )){
	if(!empty($where)){
		$where .= " AND";
	}
	$where .= " population >= :population_min";
}
if(!empty($population_max)){
	if(!empty($where)){
		$where .= " AND";
	}
	$where .= " population <= :population_max";
}

if(!empty($where)){
	$sql .= " WHERE " . $where;
}

$stmt = $pdo->prepare($sql);
if(!empty($prefecture)){
	$stmt->bindValue(":prefecture", $prefecture, PDO::PARAM_STR);
}
if(!empty($population_min)){
	$stmt->bindValue(":population_min", $population_min, PDO::PARAM_INT);
}
if(!empty($population_max)){
	$stmt->bindValue(":population_max", $population_max, PDO::PARAM_INT);
}

$stmt->execute();
$result = $stmt->fetchAll();

foreach ( $result as $row ) {
    echo "名前: {$row["name"]}, 都道府県: {$row["prefecture"]}, 人口: {$row["population"]}<br>";
}

?>
</body>
</html>

<html>

<body>

<?php

$prefecture = $_GET["prefecture"];

$population_min = $_GET["population_min"];

$population_max = $_GET["population_max"];

$pdo = new PDO("mysql:host=127.0.0.1;dbname=testdb;charset=utf8mb4;", "root", "");

$sql = "SELECT name, prefecture, population FROM cities";

$where = "";

if(!empty($prefecture)){

$where .= " prefecture = :prefecture";

}

if(!empty($population_min )){

if(!empty($where)){

$where .= " AND";

}

$where .= " population >= :population_min";

}

if(!empty($population_max)){

if(!empty($where)){

$where .= " AND";

}

$where .= " population <= :population_max";

}

if(!empty($where)){

$sql .= " WHERE " . $where;

}

$stmt = $pdo->prepare($sql);

if(!empty($prefecture)){

$stmt->bindValue(":prefecture", $prefecture, PDO::PARAM_STR);

}

if(!empty($population_min)){

$stmt->bindValue(":population_min", $population_min, PDO::PARAM_INT);

}

if(!empty($population_max)){

$stmt->bindValue(":population_max", $population_max, PDO::PARAM_INT);

}

$stmt->execute();

$result = $stmt->fetchAll();

foreach ( $result as $row ) {

echo "名前: {$row["name"]}, 都道府県: {$row["prefecture"]}, 人口: {$row["population"]}<br>";

}

</body>

</html>

このコードだと、同様の文字列をテキストボックスに入力して送信しても、尼崎市のprefectureは大阪府に変更されないはずです。

変更点を見ていきましょう。
まず、パラメータの文字列をそのまま連結してwhere句を作成していましたが、そのパラメータの箇所を「:prefecture」、「:population_min」、「:population_max」といったプレースホルダに置き換えています。今回は「:」で開始する名前付きプレースホルダを使用していますが、「?」を使うことも可能です（詳細は次回に）。

また、PDOのqueryメソッドでSQL文を即時実行していたのをprepareメソッドを使用する形に変更しています。prepareメソッドを実行するとPDOStatementオブジェクトが返ってきますが、そのオブジェクトのexecuteメソッドを呼ぶとprepareでセットしたSQL文が実行されます。

executeを実行する前には、bindValueメソッドを実行していますが、ここでプレースホルダに値をセットしています。bindValueメソッドの第1引数については、名前付きプレースホルダを使用している場合はその文字列を指定します。第2引数にはプレースホルダにセットする値、第3引数は任意ですが値の型を指定します。

このようにbindValueでパラメータを反映するようにすれば、

'; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市

1	'; UPDATE cities SET prefecture = '大阪府' WHERE name = '尼崎市

のような値がテキストボックスに入力されていたとしても、その全体がシングルクオートで囲われた一つの文字列のように処理されるため、先程のようにUPDATE文が実行されてしまうようなことは起こりません。

次回はこれまでの内容の補足的なことを書こうと考えています。今回同様SQL・PHP全般に該当する内容になると思います。

XAMPPでMySQLを使う その３

XAMPPでMySQLを使うその３