written: 2007 .. 2011-04-28

Perl/CGI で MySQL データベースを扱う上での注意点

はじめに

データベースというものは、MySQL のような DBMS［データベース管理システム］そのものと対話して操作することが基本で、その時に用いる操作のためのコマンド体系が SQL というわけです。ただ、自前でサーバーを管理している場合ならば別ですが、Web サーバーで運用するような場合は良くても SSH (Telnet) を通じて遠隔操作することになりますし、レンタルサーバーの場合にはシェルアカウントを開放していない場合も十分にありえます。

MySQL と直接対話しないでデータベースを操作したい場合は、CGI 経由で SQL コマンドを発行する手法が考えられます（いわゆる「ストアド・プロシージャ」の一種）。Perl の場合それを行うためのライブラリとして、Perl DBI 標準モジュールを使って CGI プログラムを組めば実現可能です。

データベースを利用する場合、データをプログラマーが自前でテキストファイルなどの形で管理するのではなく、データベースにデータ管理を委ねる形になります。そのため、データの入出力に関してある程度のことを把握しておいた方が良さそうです。ここでは、僕が実際に、Perl/CGI を通じて MySQL でデータベースをプログラムした際に調べたりしたいくつかの注意点について記しています。

メタ文字の扱い

表データをファイルで入出力する際には、CSV 形式で扱うのが普通と考えられます。この場合においては、メタ文字の扱いに配慮する必要があります。メタ文字として使われるのは、改行と、フィールド区切りと、引用符があります。

「改行」（「CRLF」または「LF」または「CR」）はファイル内における複数のレコード（タプル）を区切るのに使われる。データ内容に改行を含まないような簡潔なデータを扱う表の場合は問題はない。問題は、データ内容に改行を含む場合である。この場合、データ内容としての改行と、レコードの区切りとしての改行との区別をする工夫が必要である。データ内容としての改行である場合は、引用符で囲まれた内側に存在する改行であるはずなので、そのことを利用して識別する（引用符が奇数個しか登場していない状況下で改行が登場した場合は、まだフィールドが終わっていないとみなし、当然レコードの終端でもないと判断する）。
「フィールド区切り」（「,」）は、レコード内の各フィールドを区切るのに使われる。データ内容としてのカンマは、それが引用符で囲まれた内側に存在することで識別される。このフィールドの分解は、最後のフィールドの後ろにカンマを付加した文字列に対して正規表現によるパターンマッチを行うことによって可能である。つまり必ず最初は引用符で始まり、最後は引用符とカンマで終わるようなパターンとなっていることを利用する。
「引用符」（「"」または「'」）は、これで各フィールドを囲うことによって、その内側のメタ文字（改行やフィールド区切り）がメタ文字ではなく、ただのデータ内容として扱われることを識別するために使われる。引用符自身がデータ内容として出現する場合には、引用符を二度重ねて記述することでエスケープ処理する。つまり「"」がフィールド中のデータ内容として出現する場合は「""」と表される。すなわち CSV における引用符は、「1. フィールドの範囲を示す引用符としての機能」と、「2. 引用符自体に対するエスケープ記号としての機能」の 2 つを兼用していることになる。

※参考：大崎博基さん「値に改行コードを含む CSV形式を扱う」

MySQL の場合は（上述のような CSV のメタ文字規則にも対応していますが）基本的にはエスケープ記号として「\」を用いたメタ文字処理を行っています。これは Perl に慣れている人にとってはむしろわかりやすいエスケープ処理です。いかなるメタ機能をもった文字が存在しても、その直前に「\」を記述することによって、直後の文字のメタ機能を無効化します。この無効化機能は、（エスケープ機能をもったメタ文字である）「\」自身も例外ではないので、ただのデータ内容として「\」を記述したい場合は、「\\」とすればいいことになります。

CSV データを MySQL に入力する場合の注意点としては、CSV では「\」はメタ文字とはみなされておらずエスケープ処理はされていないのですが、MySQL への入力時にはメタ文字（エスケープ記号）として扱われるので、データ内容としての「\」が落ちてしまうことになる点です。それを回避するため、入力時には、あらかじめ「\」を「\\」に変換してエスケープ処理を行ってから、MySQL に入力を行う手続が必要です。


$value =~ s/\\/\\\\/g;

CSV データを扱う場合は既に引用符による処理がされていますが、それ以外の場合は引用符のエスケープ処理についても注意する必要があります。SQL 文では数字以外の文字列データは引用符（「"」または「'」）で囲う規則になっています。ですから、文字列データの内容にその引用符が含まれる場合は、CSV のように「""」と二重表記してエスケープするか、エスケープ記号（「\」）を使って「\"」と表記してエスケープするかのどちらかによって示さなければなりません。


$value =~ s/"/""/g;

MySQL からの出力時には、「\\」で入力した文字は「\」で表示されるので、特に逆の（「\\」を「\」に直すような）変換を行う必要はありません。引用符の場合も同様です。ただし、特殊なケースにおいては、考慮しなければならない場合もあります。それはデータ型に ENUM 型を使ったような場合です。MySQL では、ENUM 型のデータに「\」や「'」を使った場合、「DESCRIBE table」命令で表の定義を参照して、その選択可能な候補データを読み出そうとするような場合、引用符として「'」を使って囲い、「\」は「\\」で、「'」は「''」でエスケープされた状態で表示されます（「"」は引用符として使われないのでエスケープされず、特に配慮する必要はありません）。ですから場合によっては、それらの値をアンエスケープする必要があるでしょう。

MySQL の利用に、WWW を通じた HTML のフォームを交える場合には、HTML にとってのメタ文字のエスケープ処理も考慮しなければなりません。まず、HTML においては、「&」「<」「>」はメタ文字として機能するので必ず「&」「<」「>」にそれぞれ変換する必要があります。さらに、<input type="text" value="???" /> の ??? のような属性値のデータ内容として利用する場合には、HTML の属性値の引用符「"」と SQL 文で文字列データを記述するための引用符「"」とが競合しないように、データ側の「"」は「"」に変換する必要があります。また、HTML では表示時に改行が無視されるので、「<br />」タグに変換する必要があるでしょう。


$value =~ s/&/&amp;/g;
$value =~ s/</&lt;/g;
$value =~ s/>/&gt;/g;
$value =~ s/"/&quot;/g;
$value =~ s/\x0D\x0A|\x0A|\x0D/<br \/>/g;

表全体のファイル入力とファイル出力

ここでは、バックアップ等の目的のために、表全体をファイルから入力したり、反対にファイルに出力する方法について考えます。

表へファイルからデータを入力する場合は次のような SQL 命令を実行します：


LOAD DATA LOCAL INFILE 'ファイル名' INTO TABLE テーブル名;

このデフォルトの場合のファイルの書式は、TSV（Tab Separated Values）となります。この TSV は、単に CSV（Comma Separated Values）のフィールド区切りの「,」をタブ文字に置き換えただけのものではないのです。CSV よりもシンプルで洗練されていて、かつ、バイナリーデータも扱える完璧な書式です。比較と要点の整理のために TSV と CSV の書式を表にしてみましょう：

	TSV	CSV
フィールド（列）の区切り	「タブ文字」	「`,`」
タプル（行）の区切り	「改行文字」
フィールドの前後の囲い	（何も囲わない）	「`"`」で囲う
フィールド内のデータに対するエスケープ処理	「改行文字」「タブ文字」「`\`」を「`\`」でエスケープする	「`"`」のみ「`"`」でエスケープする
欠点	なし	フィールド内のデータに「改行文字」を使えないそのためバイナリーデータを扱うことができない
NULL の扱い（MySQL の場合）	「`\N`」	「`"`」で囲われない裸の「`NULL`」という文字列

このプログラムでは基本的に TSV を扱うことにしますが、Microsoft の Excel や Access の影響もあってか巷には CSV があまりにも普及しているようなので、CSV も扱えるように対応させておくことにします。CSV の表を入力する SQL 命令は次のようにオプション指定を付けます（参照：LOAD DATA INFILE 構文）：


LOAD DATA LOCAL INFILE 'ファイル名' INTO TABLE テーブル名
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  ESCAPED BY '"' LINES TERMINATED BY '\n';

これらの SQL 入力命令においては、NULL の扱いに関して注意が必要となります。デフォルトの TSV の場合は「\N」、オプション指定を付けた CSV の場合は「"」で囲われない裸の「NULL」という文字列をフィールドの値として使う必要があります。

TSV の場合「NULL」という文字列を使っても NULL として扱われません。また TSV、CSV どちらの場合でも、フィールドに何もデータが存在しない空の状態にして入力した場合においても、やはり NULL として扱われません。ここが要注意です。つまり、NULL を入力するつもりで、実際は NULL として扱われないデータを入力した場合には、NOT NULL 定義は有効に機能しないことになりますし、NULL の場合に予め用意されたデフォルト値を代わりに入力する DEFAULT 定義も有効に機能しないことになります。

──では次に、表をファイルに出力する場合について考えます。

ここで本来は、INFILE 命令と対の関係にある OUTFILE 命令を使いたいところなのですが、OUTFILE 命令を使うには、サーバーソフトウェア上における権限が要求されます。当然ながら、XREA.COM のユーザーに与えられているのはローカル権限に限定されていますので、OUTFILE 命令は使えません。また、INFILE の場合は「LOCAL INFILE」が可能でしたが、OUTFILE の場合に「LOCAL OUTFILE」という命令は用意されていません（MySQL では基本的にそのような使い方を想定していないのが理由のようです）。

またさらに、シェルのコマンドラインから SQL 命令を引数として実行して、その結果（標準出力）をリダイレクトするという方法もできなくはないのですが、この場合は、MySQL でデフォルトの TSV 書式のみの出力となり、その上、NULL が「NULL」という文字列として出力されるので、元からフィールド中のデータとして「NULL」という文字列として存在していた場合との区別ができないという問題が生じるので、このやり方も使えません。

従って、表の出力については、Perl DBI の DBD（DataBase Driver）の機能を積極的に利用することになります。ポイントは NULL の扱いです。フィールドが NULL であった場合、Perl DBI では、未定義の変数 undef として扱われます。なので、Perl の defined 関数を使って undef である場合だけ区別して、TSV では「\N」に、CSV では裸の「NULL」として出力するように処理すれば ok です。

次に、CSV の場合は、NULL 以外のデータに対して、数値か、それ以外の（文字やバイナリー）データか、を区別します。この区別は SQL の DESCRIBE 命令を利用して型を判断することによって行います。数値以外のデータは、エスケープ処理の対象となります。次に、数値以外のデータを「"」によって囲います。TSV の場合は、フィールドの前後を囲う必要がないので、NULL 以外のデータに対して特に数値かどうかを区別する必要もなく、一律エスケープ処理すればいいだけです。

また次に、フィールド区切り（「タブ文字」または「,」）を挟んでフィールド同士を接続します。最後に「改行文字」でタプルを接続します。

処理の手順をまとめると、次のようになります：

処理手順		TSV	CSV
1		`defined` 関数によりフィールドが NULL であるかどうかを判別する
2	NULL	「`\N`」にする	裸の「`NULL`」にする
	NULL 以外	「改行文字」「タブ文字」「`\`」を「`\`」でエスケープする	`DESCRIBE` 命令を利用して型を判断し、数値とそれ以外を区別する
			数値	数値以外
			そのまま	「`"`」を「`"`」でエスケープしてから、フィールドの前後を「`"`」で囲う
3		「タブ文字」でフィールドを接続する	「`,`」でフィールドを接続する
4		「改行文字」でタプルを接続する

以上のようにして TSV と CSV によるファイル出力を得られますが、これらの出力されたファイルは Excel 等の一般の表計算ソフトで正しく編集し直すことは期待できません。まず TSV については、「\（バックスラッシュ）」によるエスケープに表計算ソフトが対応していません。次に CSV については、表計算ソフトによる閲覧については問題はありませんが、編集した場合は「NULL」が「"NULL"」になってしまうので、編集後の CSV ファイルを再びデータベースに入力した場合に本来は NULL であるべきものがただの文字列の「NULL」に変わってしまうという問題が発生します。NULL についてどうなっても構わない場合であれば、CSV を表計算ソフトで編集しても問題はありません。

外部キー

MySQL のデフォルトエンジンである MyISAM エンジンには外部キーに関する機能が実装されていません。それに対して InnoDB エンジンには外部キーが実装されています。InnoDB が使える環境であれば、どうしても外部キーを使いたいのであれば、InnoDB を選択すればいいでしょう。ただ一般的に、外部キーは必ずしもデータベースエンジン側で面倒を見る必要はなく、Perl/CGI プログラムの側の入出力の際にチェックするようにすればいいだけの話です。MyISAM と InnoDB の違いは、必ずしも必要ではない機能を極力削ってパフォーマンスを得る MyISAM に対し、トランザクション機能を万全にすることを優先事項にしてパフォーマンスは二の次にする InnoDB との、志向性の違いに帰着すると思います。トランザクション機能に対する要請が低いのであれば、MySQL の圧倒的なパフォーマンスを選んでおけばいいと思います。このあたりのデータベースエンジンの違いについては、藤塚勤也氏の「徹底比較!! MySQLエンジン」という記事などが参考になります。

基本的に MyISAM エンジンを利用することを前提に話を進めますが、外部キーをデータベースエンジン側で面倒を見てくれないので、外部キーの扱いに関してはプログラマが自前で面倒を見る必要があります。外部キーの実現方法として、参照先の表から、「SELECT 外部キー名 FROM 参照先の表名」で外部キーの値の一覧を獲得し、その値を HTML フォームのプルダウンメニューの候補として表示するという方法が考えられます。この場合の注意点として、プルダウンメニューの候補に、空値の候補を用意することを忘れないようにするということです。この外部キーが空値を許可しない「NOT NULL」のフィールドの場合は別ですが、空値を許す場合には空値の候補を用意しておかないと HTML フォームでデータを更新した場合に必ず値が入ってしまうことになります。

※外部キーでなく、ENUM 型のフィールドをプルダウンメニューで入力できるようにする場合も、同じことが言えます。

<SQL>