サーバサイドプログラミング 4. PDO (PHPからSQLアクセス) コンテンツメディアプログラミング 演 習 Ⅱ 2014 年 菊 池, 斉 藤
1. PDO 概 要 n PDO (PHP Data Object) q PHP5.1から 採 用 された 汎 用 SQLの 標 準 クラ ス. q オブジェクト 指 向 を 採 用 し,オブジェクトからメ ソッドやクラス 変 数 を 操 作 する. q MySQL, SQLiteなどのサーバソフトに 依 存 せ ず,ほぼ 共 通 のコードでプログラミングできる.
PHPからSQL 文 の 実 行 n PDOオブジェクト 作 成 ( 準 備 ) $ 変 数 = new PDO("ドライバ: host=ホスト; dbname=データベース 名 );» ドライバ = mysql,sqlite など» hostはローカルの 場 合 は 省 略 可 能» "dbname=" を 指 定 しない(MySQLとの 違 い)» $ 変 数 がメソッドなどを 含 むオブジェクト. q 例 ) Mtsデータベースにアクセスする» $db = new PDO("sqlite:mts.sqlite");» $db = new PDO("mysql:host=localhost;dbname=mts", "root", "パスワード"); (MySQLの 場 合 )
SQLのエラーについて n 注 意 q PDOのエラーは 直 接 観 測 できない. 事 前 に sqlite3コマンドで 動 作 を 確 認 しておくこと. q エラーを 表 示 する 設 定 (やらなくても 動 く) $db = new PDO("sqlite:mts.sqlite"); $db- >setattribute(pdo::attr_errmode, PDO::ERRMODE_WARNING);
SQLコマンドの 実 行 n query (クエリ) q 例 ) $ 変 数 ->query(sql 文 );» 任 意 のSQL 文 を 実 行 する.$ 変 数 はPDOオブジェクト.» $db- >query( "INSERT INTO mts(name) VALUES(' 高 尾 山 ')" );» この 場 合 は 返 り 値 はなし(VOID)
検 索 結 果 の 抽 出 (1/3) n fetch ( 物 を 取 に 行 く) $ 検 索 結 果 = $ 変 数 ->query(select 文 ); $ 行 = $ 検 索 結 果 ->fetch();» SELECT 文 は 複 数 の 行 と 列 を 含 むテーブルを 返 す» fetch()はテーブルから1 行 づつ 取 り 出 す.» 返 し 値 = False (もう 行 がない 時 ) 列 から 成 る( 通 常 の) 配 列 列 名 をメンバとする 連 想 配 列 q 例 )» $rows = $db- >query("select * from mts"); テーブルmtsから 全 レコードを $rowsに 取 出 す.» $cols = $rows->fetch(); 1 行 づつ 取 り 出 す.
検 索 結 果 の 抽 出 (2/3) q 例 1) 配 列 で 列 を 取 出 す $cols = $rows- >fetch() print $cols[0]. $cols[1]. $cols[2]. $cols[3]. "\n"; 1 谷 川 岳 05 2 丹 沢 06 q 例 2) 配 列 を "-"で 繋 いで 出 力. print join($cols,"- "). "\n"; 1-1- 谷 川 岳 - 谷 川 岳 - 0-0- 5-5- 1227-1227 q 例 3) 連 想 配 列 で 名 前 と 標 高 のみ 出 力. print $cols['name']."\t". $cols['height']. "\n"; 1 谷 川 岳 05 2 丹 沢 06 q 例 4) 全 ての 行 を 出 力. while($cols = $rows- >fetch()){ print $cols['name']. ","; } 谷 川 岳, 丹 沢, 天 城 山, 八 ヶ 岳, 那 須 岳, 駒 ヶ 岳, 燕 岳, 奥 穂 岳,
検 索 結 果 の 抽 出 (3/3) n fetchall ( 全 ての 行 を 抽 出 ) $ 検 索 結 果 = $ 変 数 ->query(select 文 ); $ 表 = $ 検 索 結 果 ->fetchall();» fetchall()は1 行 づつではなく, 全 行 を 一 度 に 表 から 取 出 す.» 返 し 値 = 列 名 をメンバとする 連 想 配 列 の 配 列 q 例 ) $rows = $db- >query( "SELECT * from mts"); $all = $rows- >fetchall(); print_r($all); 全 行 をまとめて 表 示 Array ( [0] => Array ( [ID] => 1 [0] => 1 [name] => 谷 川 岳 [1] => 谷 川 岳 [day] => 0 [2] => 0 [hour] => 5 [3] => 5
演 習 1 n Mts.sqlite データベースを 用 いて, 標 高 順 に 山 名 を 次 の 様 に 出 力 するmts-top.php を 書 き,ブラウザから 閲 覧 せよ. q ヒント: select でソートを 行 う. 必 要 な 項 目 の み 選 択.Tableタグ 利 用. q UTF-8のデータベース mts-u.sqlite (Webから ダウンロード)
2. データベースの 更 新 n マイ 電 話 帳 q 姓 (lastname), 名 (firstname), TEL(phone)の 列 から 成 る 表 phonebook.sqlite CREATE TABLE users (id integer primary key autoincrement, firstname text, lastname text, phone text)
(1) 全 ての 行 を 表 示 list n phonebook-list.php <html><head> <title> phonebook</title> <meta charset="utf- 8"></head> <body> <h1> マイ 電 話 帳 </h1> <table border=0 cellpadding=0 cellspacing=0> <tr bgcolor=#f87820> <td width=50><br>no</td> <td width=80><br><b> 姓 </b></td> <td width=80><br><b> 名 </b></td> <td width=150><br><b>tel</b></td> <?php $db = new PDO("sqlite:phonebook.sqlite"); $result=$db- >query("select * FROM users"); for($i = 0; $row=$result- >fetch(); ++$i ){ echo "<tr valign=center>"; echo "<td >". $row['id']. "</td>"; echo "<td >". $row['lastname']. "</td>"; echo "<td >". $row['firstname']. "</td>"; echo "<td >". $row['phone']. "</td></tr>";?> } </table> </body></html>
(2) 行 の 追 加 n queryメソッドでinsert 文 実 行 $db->query( "INSERT INTO テーブル VALUES( 値 )");» $dbはデータベースを 含 むPDOオブジェクト (new PDO)» 返 し 値 はない.エラーのない 様 に 値 を 用 意. q 例 )» $firstname=$_get['firstname'];» $db = new PDO("sqlite:phonebook.sqlite");» $db- >query("insert INTO users VALUES(null, '$firstname',null,null)");
FORMからのデータ 受 け 取 り phonebook-add.html n <html> <head><title> phonebook</title> <meta charset="utf- 8"> </head> <body> <h2>エントリー 追 加 </h2> <form action=phonebook- add.php method=get> <table border=0 cellpadding=0 cellspacing=0> <tr><td> 姓 :</td><td><input type=text size=20 name=lastname></td></tr> <tr><td> 名 :</td><td> <input type=text size=20 name=firstname></td></tr> <tr><td>tel:</td><td> <input type=text size=20 name=phone></td></tr> <tr><td> </td><td><input type=submit border=0 value=" 追 加 "></td></tr> </table> </form> </body> </html> n phonebook- add.php <?php $firstname=$_get['firstname']; $lastname=$_get['lastname']; $phone=$_get['phone']; $db = new PDO("sqlite:phonebook.sqlite"); $firstname PHPの 変 数 'firstname' Formのラベル 常 に 同 じでなくてもよい $db- >query("insert INTO users VALUES(null, '$firstname','$lastname','$phone')");?> Added.
(3) 行 の 削 除 n queryメソッドでdelete 文 実 行 $db->query( "DELETE FROM テーブル WHERE 条 件 ");» 条 件 は,id=2 などの 行 を 一 意 に 決 める 式» 返 し 値 はない. q 例 )» $id=$_get['id'];» $db = new PDO("sqlite:phonebook.sqlite");» $db- >query("delete FROM users WHERE id='$id' ");
行 の 削 除 n phonebook-del.html <html> <head><title> phonebook </title> <meta charset="utf- 8"> </ head> <body> <h2>エントリー 削 除 </h2> <form action=phonebook- del.php method=get> ID: <input type=text size=10 name=id> <input type=submit value=" 削 除 "> </form> </body> </html> n phonebook-del.php <?php $id=$_get['id']; $db = new PDO("sqlite:phonebook.sqlite"); $db->query("delete from users where id='$id'");?> deleted.
HtmlとPhpの 統 合 n phonebook- del2.php <?php if(isset($_get['id'])) $id=$_get['id']; $db = new PDO("sqlite:phonebook.sqlite"); if(isset($id)) { $db- >query("delete FROM users WHERE id='$id'"); }?> <html> <head> <title> phonebook </title> <meta charset="utf- 8"> </head> <body> <h2>エントリー 削 除 </h2> <form action=phonebook- del2.php method=get> ID: <input type=text size=10 name=id> <input type=submit value=" 削 除 "> </form> <?php $result=$db- >query("select * FROM users"); print_r($result- >fetchall());?> </body> </html> isset - 最 初 のアクセスの 時 にGET[id]が 未 定 義 で 出 る エラーを 防 止 全 エントリーを 表 示 して 削 除 を 確 認 する
演 習 2 n phonebook.phpを 参 考 にして,ポケモン 図 鑑 pokemon.php を 作 れ. q pokemon.sqlite CREATE TABLE monsters ( id integer primary key autoincrement, name text, hp integer, offense integer, defense integer, speed integer, type text); q 好 きなポケモンを5 匹 追 加 せよ. q http://www.pokemon.co.jp/zukan/
3. セキュリティの 考 慮 n 演 習 2のモンスターの 名 前 に 次 を 入 力 して 何 が 起 きるか 観 察 せよ. 1. <h1> ピカチュー </h1> 2. <script> alert('hello') </script> 3. <body bgcolor=black>
クロスサイトスクリプティング n Cross Site Scripting (XSS) B (おとりサーバ) cookie の 読 出 し 自 動 実 行 A (クライアント) name= <Script> alert('hello') </script> <Script> alert('hello') </script> C ( 脆 弱 なサーバ) 実 行 させたい 攻 撃 コード
SQLインジェクション n データベースへの 攻 撃 q 意 図 しないSQL 文 の 注 入 (injection) q 例 ) 演 習 2のエントリーの 削 除 ( 注 意 ) DELETE FROM monsters WHERE id = $id q $id= 3 OR 1=1 DELETE FROM monsters WHERE id = 3 OR 1=1 ; q 1=1は 恒 等 式 なので, 常 に 成 立 し, 全 ての 行 を 削 除 してしまう.( 全 データを 抽 出 すると 情 報 漏 えい)
対 策 n サニタイジング( 衛 生 化 ) $ 変 換 後 変 数 = htmlspecialchars($ 入 力 変 数 );» 入 力 変 数 に 含 まれるタグをHTMLの 実 体 参 照 に 置 き 換 える. q 例 ) 入 力 : 実 体 参 照 < & " ♨ 表 記 < > <h1>ピカチュー</h1> サニタイズ: <h1> ピカチュー </h1> (ブラウザでの 見 た 目 は 同 じ)
サニタイジングの 準 備 n htmlspecialchars は 長 ったらしい! q そこで 自 前 で 関 数 h を 定 義 してしまおう q 各 PHPプログラムの 冒 頭 に 以 下 の 関 数 定 義 をして 使 う function h($str) { return htmlspecialchars($str, ENT_QUOTES, "UTF- 8"); } q ユーザから 入 力 された 情 報 はそのまま print/ echo 文 で 表 示 するのではなく 必 ず print h($ 変 数 名 ); のように 関 数 h を 介 して 表 示 する
その 他 の 対 策 ( 参 考 ) n n n n サニタイジングだけでは 完 全 にSQLインジェクションを 防 止 で きない. q タグや 特 殊 記 号 を 含 まない 命 令 整 数 化 q $intid = round($id); 文 字 列 を 整 数 のみに( 切 り 捨 て) 正 規 表 現 q preg_match("/[0-9]+/", $ 入 力 変 数 ) 入 力 が 数 字 のみかどうかを 判 定 する Prepared Statement q $ps = $db- >prepare("select * from tb where id=:a"); q $ps- >bindparam(":a", $a); q $ps- >execute(); :Aの 位 置 に$aの 整 数 のみが 代 入.
データベースの 漏 えい n ファイルとアクセスの 関 係 q phpファイル ( 外 部 に 公 開 する 必 要 ) q sqliteファイル ( 内 部 からのみアクセス) n 攻 撃 q http://localhost/pokemon.sqlite でDBを 丸 ご とダウンロードする.( 情 報 漏 えい)
対 策 n 1. SQL DBを 外 部 の フォルダーに. n 2. SQL DBを 直 下 の 別 フォルダーに. webserver htdoc phonebook.php SQL phonebook.mysql n 欠 点 : ファイルの 管 理 htdoc phonebook.php SQL phonebook.mysql.htaccess
アクセス 制 御 ファイル n.htaccess (ドットに 注 意 ) Order deny,allow Deny from all q deny ( 禁 止 ),allow ( 許 可 ) q order は, 優 先 順 序 を 指 定. q このフォルダでは 全 てのウェブからのアクセス を 禁 じる(sqliteコマンドは 別 )
演 習 3 n 演 習 2で 作 成 したpokemon.php を, 次 の2 点 を 考 慮 した 安 全 な pokemon-secure.php に せよ. q 検 査 方 法 (1) エントリー 追 加 で 名 前 <h1> ピカチュー </h1> を 持 つ 行 を 追 加 し,H1の 大 きさで 表 示 されないこと. (2) データベース 本 体 をhttp://localhost/ pokemon.sqlite でブラウザからダウンロードできな いことを 確 認.
演 習 4 n SQLの 課 題 5 (オープンデータ) mydata.sqlite を 用 いて,それにレコード ( 行 )を 追 加 と 削 除 する mydata.php を 書 け. q 追 加 する 行 のデータは 任 意 に 決 めてよい. q 任 意 の 機 能 を 工 夫 した 内 容 を,mydataphp.doc にて 報 告 せよ.
提 出 物 n 提 出 用 ドライブ (Y:) q コンテンツ..II (FMS)\ 第 _ 回 \_ 組 \2- 組 - 番 q 課 題 2. pokemon.php q 課 題 3. pokemon-secure.php, SQL \pokemon.sqlite, SQL\.htaccess q 課 題 4. mydata.sqlite, mydata.php, mydataphp.doc ( 報 告 書 )
まとめ n PDOはオブジェクト 指 向 を 用 いた( )の SQLアクセスライブラリ.( )により 任 意 のSQL 文 を 実 行 する. n PDOから 行 を 追 加 するには,queryメソッドに より,SQLの( )を 実 行 する. n データベースを 操 作 するコマンドにタグが 入 っ ていると( )の 攻 撃 を 受 ける. 防 止 するには,タグをHTMLの 特 殊 文 字 に 置 き 換 える( )がある.