PHPから見たPostgreSQLの数値データ型（数値リテラル）

第四企画坂井潔

PostgreSQL Advent Calendarも中盤に入りました。12/11。今回はこの一環として記事を発表します。

数値をクォートで括ることでSQLインジェクション対策とする方法では、その数値はいったんSQL上で文字列として認識されるので、比較するカラムのデータ型（整数型など）への暗黙のキャストが行われます。そのとき、数値精度の劣化や性能低下、ひいては予期せぬ動きを引き起こす可能性があります。このことはエスケープ・クォート編の「数値をSQLに埋め込む」でも少し触れました。

これらの理由から、数値はシングルクォートで囲んだ文字列としてではなく、数値そのもの（数値リテラル）としてSQLに埋め込む方法が主流になりつつあります。ただしその際の、数値範囲チェック・フォーマットチェック、そしてSQL文の組み立てにPHPを使う場合、気を付けなければならないポイントがあります。

PHPでの数値とPostgreSQLでの数値

PHPの32bit版、64bit版のinteger、floatと、PostgreSQLのぞれぞれの数値データ型は、殆どのケースで異なる範囲を持ちます。カラムのデータ型を超えた数値を入力しようとすれば当然エラーになってしまうため、SQLに埋め込む前に、値が範囲内かをあらかじめチェックしておく必要があります。

システムとしての仕様、つまり事前のチェックが、PHPのinteger、そしてカラムが取りうる数値の範囲以内であれば問題はありませんが、仕様として特に制限がないと言った場合には、数値としてのフォーマットやカラムに合った範囲などのチェックが必要になってきます。

PHPのinteger、floatの扱い

PHPで数値を扱う際、まず最初に気を付けなくてはならないポイントは、値がintegerの範囲を超えた途端、<、>、==などの比較演算子を使った大小の比較は正確ではなくなってしまうという点です。例えばコマンドライン上で、以下のような比較を行なってみると、

$ php -r 'var_dump("9223372036854775807" == "9223372036854775999");'
$ php -r 'var_dump( 9223372036854775808  === 9223372036854775999 );'

これらの結果は、私たちの意に反してbool(true)になってしまうのです！

PHPでは比較演算子を使った場合、数値形式の文字が含まれるときは、文字列が数値（integerあるいはfloat）に変換された上で比較されます。上記の例はintegerの範囲を超えてしまう値であるため、左辺、右辺の値は共にfloatに変換されます。ただし、floatの精度はあまり高くないため、ここでは両辺ともに 9.2233720368548E+18 に変換されてしまい、そのため、比較の結果は真になってしまうのです。

32bitシステムか64bitシステムか

PHPが64bitシステム（OS）上で動作しているか、32bitシステム上で動作しているかで、PHPのintegerの範囲（上限・下限）が異なります。32bitシステムの場合であれば、PHPのintegerの範囲は-2,147,483,648から2,147,483,647になりますし、64bitであれば-9,223,372,036,854,775,808から9,223,372,036,854,775,807になります。この判定のためには、コマンドライン上で

$ php -r 'var_dump(PHP_INT_MAX);'

と入力してみてください。出力が int(2147483647) であればPHPは32bitシステム上で、int(9223372036854775807) であれば64bitシステム上で動作している、と判定できます。integerの上限より上、あるいは下限以下の数値は、floatとして認識されます。

$ php -r 'var_dump(9223372036854775808);'

あるいは

$ php -r 'var_dump(2147483648);'

の値を、この前後でいろいろ変化させて試してみることで、PHP上でそれぞれの数値がどのように認識されているかを確認することができます。

詳しくは以下に説明しますが、使用しているPHPが32bitシステム上で動作しているのか、64bitシステム上なのかによって、数値の判定（の範囲）が異なってきます。値がfloatになると比較演算子を使った数値の比較は不正確になってしまうため、注意が必要です。

この点をふまえたうえで、対象のカラムがPostgreSQL上でどの数値データ型なのかに依って、どのようなチェックをし、どのように埋め込むことが適切なのかを説明します。

※ここ以降は、対象となる項目が文字列であることを前提として話を進めます。これは、PHPでブラウザから渡ってくる値は、全て文字列（と、その配列）であるためです。もしも配列が許されないような場合には、

if(!isset($_REQUEST['num'])) {
  // 項目そのものが未設定（フォームを使用せず直接URLにアクセス、など）の場合の処理
} elseif(is_array($_REQUEST['num'])) {
  // NG処理
}

というように、is_array関数でチェックし、前もってエラーにしてしまいましょう。

整数データ型・連番型（smallint、integer、bigint、serial、bigserial）

型名	格納サイズ	説明	範囲
smallint	2バイト	狭範囲の整数	-32768から+32767
integer	4バイト	典型的に使用する整数	-2147483648から+2147483647
bigint	8バイト	広範囲整数	-9223372036854775808から9223372036854775807
serial	4バイト	自動増分整数	1から2147483647
bigserial	8バイト	広範囲自動増分整数	1から9223372036854775807

smallintはint2、integerはint4 およびint、bigintはint8と同意です。またsequenceを使うという機能を除けば、serialはintegerと、bigserialはbigintと同様の動きをします。これらは小数点以下のない「整数値」です。

serial、bigserialの値のフォーマット・範囲のチェックなどはinteger、bigintと同じですので、以下はそれぞれに読み替えてください。

64bitシステムでの整数値のチェック

smallintとinteger

まず、値が負でない（すなわちマイナス記号がつかない）ことを必須にしてしまってよければ、smallintやintegerのチェックは簡単です。

if(!ctype_digit($_REQUEST['num']) || $_REQUEST['num'] > 32767) {
  // NG処理
}

これはsmallintの場合です。integerの場合には32767を2147483647に書き換えてください。また、PHPのバージョンが5.1.0より前のバージョンでは、$_REQUEST['num']が空文字の場合（$_REQUEST['num'] == ''）でもctype_digit関数はtrueを返してしまいますので、もし古いバージョンのPHPを使用していて、空文字をエラーにしたい場合には

if($_REQUEST['num'] == '') { /* NG */ }

といった条件も加えてください。

$_REQUEST['num']に、負の値も許すような場合には、正規表現を使ってしまったほうが簡単かもしれません。

if(
  !preg_match('/^[+-]?\d+$/', $_REQUEST['num'])
  || $_REQUEST['num'] < -32768 || 32767 < $_REQUEST['num']
) {
  // NG処理
}

これもsmallintの場合ですので、integerの場合は、-32768を-2147483648に、32767を2147483647に書き換えてください。

bigint

bigintのチェックでは、その範囲がPHPのintegerのそれと酷似してくるため、例えば単純に 9223372036854775807 < $_REQUEST['num'] としたような比較演算子での比較では、片方の値（この例の場合は$_REQUEST['num']）がほんの少しでもPHPのintegerの範囲を超えfloatになって時点で、両辺ともの値が、整数としては精度があいまいなfloat（浮動小数点数）に変換されてしまいます。PHPマニュアルの浮動小数点数や比較演算子の説明にもあるように、floatの比較は、その精度や内部的な表現方法の理由で、意図したようには動いてくれません。

bigintの場合、値が負でない（マイナス記号がつかない）ことが必須であれば、このようにします。

if(
  !ctype_digit($_REQUEST['num']) 
  || strnatcmp($_REQUEST['num'], '9223372036854775807') > 0
) {
  // NG処理
}

ここでは自然順アルゴリズムにより文字列比較を行うstrnatcmp関数を使用しました。もしお使いのPHPに、bccomp（BCMath任意精度数学関数の一つで、2つの任意精度数値を比較する）関数がインストールされていれば、以下のようにチェックすることも可能です。

if(
  !ctype_digit($_REQUEST['num']) 
  || bccomp($_REQUEST['num'], '9223372036854775807') > 0
) {
  // NG処理
}

負の値も許す場合は、bccomp関数が使用できる環境では比較的簡単にチェックできます。

if(
  !preg_match('/^[+-]?\d+$/', $_REQUEST['num'])
  || bccomp('-9223372036854775808', $_REQUEST['num']) > 0
  || bccomp($_REQUEST['num'], '9223372036854775807') > 0
) {
  // NG処理
}

strnatcmp関数では自然順アルゴリズムで文字列を比較するため、マイナス記号"-"を負の値を示す記号としては認識してくれません（ハイフンとしての文字要素として認識しているようです）。ですので、strnatcmp関数でのチェックは少し複雑な処理になります。

if(!preg_match('/^([+-])?(\d+)$/', $_REQUEST['num'], $matches)) {
  // NG処理
} else {
  if($matches[1] == '-') {  // 値が負の場合
    if(strnatcmp($matches[2], '9223372036854775808') > 0) {
      // NG処理
    }
  } else { // 値が負でない（ゼロ・正の）場合
    if(strnatcmp($matches[2], '9223372036854775807') > 0) {
      // NG処理
    }
  }
}

正規表現でマッチさせたプラス記号・マイナス記号部分と、数字部分を分けることで、絶対値がそれぞれの範囲以内かをチェックするような処理になります。

整数データ型のSQLへの反映（64bit版）

ここまでで、整数データ型としてのフォーマットのチェックはできていますので、データの埋め込みではそれほど気をつけるべき点はありません。

MDB2を使って

/* プレースホルダ編 */
$mdb2->extended->execParam(
  'UPDATE tbl SET num = :num WHERE id = :id',
  array(
    'num' => $_REQUEST['num'], 
    'id' => $_SESSION['current']['id']
  ),
  array('num' => 'integer', 'id' => 'integer')
  // 64bitシステムでは'integer'でOK
);

/* エスケープ・クォート編 */
$res = $mdb2->query(
  'UPDATE tbl’
  . ' SET num = ' . $mdb2->quote($_REQUEST['num'], 'integer') 
  . ' WHERE id = ' . $mdb2->quote($_SESSION['current']['id'], 'integer')
  // 64bitシステムでは'integer'でOK
);

/* そのまま埋め込み */
$res = $mdb2->query(
  'UPDATE tbl’
  . ' SET num = ' . $_REQUEST['num']
  . ' WHERE id = ' . $_SESSION['current']['id']
);

64bitの場合はチェックさえ済んでいれば、整数型のカラムに対応した数値はPHPのintegerの範囲内になります。なので、'num'のパラメータのタイプは'integer'で問題ありません。エスケープ・クォートを無理やりするのであれば上記のquoteで可能ですが、ここに至るまでに数値としてのフォーマット、範囲のチェックができてさえいれば「そのまま埋め込み」のように何もせず文字列として連結してしまっても構いません。

pg_****を使って

/* プレースホルダ編 */
$res = pg_query_params(
  $dbconn,
  'UPDATE tbl SET num = $1 WHERE id = $2',
  array($_REQUEST['num'], $_SESSION['current']['id'])
);

/* エスケープ・クォート編（そのまま埋め込み） */
$res = pg_query(
  $dbconn,
  'UPDATE tbl SET num = ' . $_REQUEST['num']
  . ' WHERE id = ' . $_SESSION['current']['id']
);

エスケープ・クォートと言っても、pg_****には数値をエスケープするような関数はないので、ここでは数値をクォートせず、そのまま文字列として埋め込んでいます。事前に数値としてのフォーマット・範囲チェックが済んでいることが重要です。

32bitシステムでの整数値のチェック

smallint

前述した64bit版のsmallintと同様のチェックで問題ありません。

integer

PostgreSQLのintegerの範囲は、32bitシステム上のPHPのinteger（整数値）の範囲と同じになります。すなわち上限・下限ギリギリの値の大小のチェックには、>、<と言った比較演算子を使うことはできません。

ですので、32bitシステムの場合には、PostgreSQLのintegerの範囲チェックには、64bit版でbigintの場合と同様に、bccomp、strnatcmpのような関数を使用したチェックをしてください。

bigint

前述した64bit版のbigintと同様のチェックで問題ありません。

整数データ型のSQLへの反映（32bit版）

32bit版で気を付けなくてはならないのは、PostgreSQLのbigintの範囲が、PHPのintegerの範囲を超えてしまうところです。MDB2ではintegerとして、quoteをする場合、あるいはプレースホルダのパラメータとして送信する直前に、一旦PHPのintegerにキャストします。その際、数値はPHPのintegerの範囲内に丸められてしまうのです。32bit版のPHPではbigintのカラムに対してのパラメータのタイプは'decimal'を使用してください。

MDB2を使って

/* プレースホルダ編 */
$mdb2->extended->execParam(
  'UPDATE tbl SET num = :num WHERE id = :id',
  array(
    'num' => $_REQUEST['num'], 
    'id' => $_SESSION['current']['id']
  ),
  array('num' => 'decimal', 'id' => 'integer')
);

/* エスケープ・クォート編 */
$res = $mdb2->query(
  'UPDATE tbl’
  . ' SET num = ' . $mdb2->quote($_REQUEST['num'], 'decimal')
  . ' WHERE id = ' . $mdb2->quote($_SESSION['current']['id'], 'integer')
);

カラムの型がsmallint、integerの場合も（範囲チェックが済んでいれば）値はPHPのintegerの範囲内になりますので、パラメータのタイプは'integer'で構いません。また「そのまま埋め込む」パターンは64bit版と同じです。

pg_****を使って

pg_****を使った場合は、64bit版と同じです。

比較演算子で整数の比較を正確にできる範囲は？

上記では厳密なチェックを紹介しましたが、実際には32bitシステムでも、integerの範囲付近の大小比較は比較演算子（>や<など）を使っても、たまたまではありますが可能なようです。ただ、整数の比較が実質的にできる範囲を調べてみても、それが、floatでも指数表記をしなくてすむ最大の数99,999,999,999,999なのか、あるいはもっと内部的な値、2の53乗マイナス1（＝9,007,199,254,740,991）なのかが今ひとつハッキリしません…。

とはいえ、PHPのマニュアルの比較演算子のページでも「ふたつの float 値が等しいかどうかを調べてはいけません」と警告されています。ですので、32bitシステムでのPHPのintegerの範囲である-2,147,483,648〜2,147,483,647の付近の値の大小、あるいは等しさを比較する際には、上記のbigintの場合のように、bccomp、strnatcmpなどの関数を使った比較にしたほうが安全なようです。

任意の精度を持つ数（decimal、numeric）

型名	格納サイズ	説明	範囲
decimal	可変長	ユーザ指定精度、正確	精度指定なし：小数点前までは131072桁、小数点以降は16383桁精度指定あり：小数点前後あわせて1000桁
numeric	可変長	ユーザ指定精度、正確	精度指定なし：小数点前までは131072桁、小数点以降は16383桁精度指定あり：小数点前後あわせて1000桁

decimalは実際にはnumericと等価です（ですので、これ以降はnumeric型として説明します）。算術演算の動作は上記の整数型や下記の浮動小数点型よりもずっと遅くなりますが、大きな桁数や正しい小数点以下値、あるいは正しい計算結果が求められるような場合にも使用できる型です。金額などを格納するには最適な型です。

PostgreSQL 9.1.xのマニュアルには記載してありますが、この型には最大で、小数点前までは131072桁、小数点以降は16383桁、格納することができます（PostgreSQL 7.3.x以前ではこのとおりではありません）。とは言え、これはnumeric型を宣言するときに、精度に何も指定しなかった場合の最大桁数なので、numeric型を使った場合「計算の途中で一瞬この桁の数値になったとしても正しい計算が行われる」という値にすぎないと考えたほうがよさそうです。精度を指定したとしても、小数点前後を含めて1000桁という（宇宙的な計算をさせるのでなければ）十分な桁数がサポートされているので、通常numeric型のデータ型を定義する場合には、なるべく精度を指定したほうがよさそうです。

ここでは例えば、整数部分が96桁、小数点以下が24桁のnumeric型を定義したとしましょう。

CREATE TABLE tbl_numerics (
  num_no serial PRIMARY KEY, 
  num numeric(120, 24)
);

整数部が96桁、小数点以下が24桁なので、精度としては96+24=120になります。そのうち小数点以下が24桁なので numeric(120, 24) という定義になります。

変数の値が、この範囲に含まれているかのチェックも、やはり正規表現を使うのがよさそうです。

if(
  !(
    preg_match('/^[+-]?\d{0,96}(\.\d{0,24})?$/', $_REQUEST['num'])
    &&
    preg_match('/\d/', $_REQUEST['num'])
  )
) {
  // NG処理
}

今回は小数点以下が含まれるため、少しだけ複雑な正規表現になりました。１つめのpreg_matchの正規表現は、まず正か負かの記号があればそれにマッチ、そして整数部分の数字にマッチ、そして小数点そのものと小数点以下があればそれにマッチ、の３つのパートで構成されています。PostgreSQLでは「4.」「.001」といった値も数値として認めるのでこうしていますが、ただこのままでは「+」「.」「-.」などといった数字が全く含まれない値でもOKになってしまいます。ですので２つめのpreg_matchの正規表現で、少なくとも１つの数字が含まれているという条件を付加しました。これら２つの正規表現が満たされていればOK、いなければNGということになります。

もっと良い正規表現があるという方は、ぜひLet's Postgresのツイッターアカウントにツイートください。

任意の精度を持つ数のSQLへの反映

MDB2を使って

/* プレースホルダ編 */
$mdb2->extended->execParam(
  'UPDATE tbl SET num = :num WHERE id = :id',
  array(
    'num' => $_REQUEST['num'], 
    'id' => $_SESSION['current']['id']
  ),
  array('num' => 'decimal', 'id' => 'integer')
);

/* エスケープ・クォート編 */
$res = $mdb2->query(
  'UPDATE tbl’
  . ' SET num = ' . $mdb2->quote($_REQUEST['num'], 'decimal') 
  . ' WHERE id = ' . $mdb2->quote($_SESSION['current']['id'], 'integer')
);

decimal、numericの場合は、文字通り'decimal'をパラメータのタイプ、クォートのタイプとして使います。「そのまま埋め込む」パターンは整数型の64bit版と同じです。

pg_****を使って

pg_****を使った場合は、整数型の64bit版と同じです。

次回は浮動小数点データ型（real、double precision）に関して説明します。

(2011年12月11日公開)

PHPから見たPostgreSQLの数値データ型（数値リテラル）

PHPから見たPostgreSQLの数値データ型（数値リテラル）

第四企画 坂井 潔

PHPでの数値とPostgreSQLでの数値

PHPのinteger、floatの扱い

32bitシステムか64bitシステムか

整数データ型・連番型（smallint、integer、bigint、serial、bigserial）

64bitシステムでの整数値のチェック

smallintとinteger

bigint

整数データ型のSQLへの反映（64bit版）

MDB2を使って

pg_****を使って

32bitシステムでの整数値のチェック

smallint

integer

bigint

整数データ型のSQLへの反映（32bit版）

MDB2を使って

pg_****を使って

比較演算子で整数の比較を正確にできる範囲は？

任意の精度を持つ数（decimal、numeric）

任意の精度を持つ数のSQLへの反映

MDB2を使って

pg_****を使って

第四企画坂井潔