PostgreSQL（Perl データベース操作）

SQL インジェクション対策

    DB=> select * from テーブル名 where id = ' ' or 1=1;-- ' and pw = '';

    データベースが解釈可能な文字列「'」「;」「--」によって、常に一致する条件
  （1=1）を where 句に潜り込ませれば、テーブルの全ての行が条件に一致する。
    SQL 文では、「--」以降はコメントとして扱われる。

    1. apache モジュール（mod_security）による、送信データのチェック

    2. プログラム内で、送信データのチェック

    3. SQL 文で、プリペアードクエリを使用

     実行する SQL 文を予めデータベース・サーバに登録し、実行時にパラメータ（値）
    のみ渡す。
     パラメータは、SQL 文への変数として安全に処理されるため SQL インジェクション
    を行う事は不可能。

    quote を使用

      文字列を quote すると、'（シングルクォート）で囲まれる。

      my $str = $dbh->quote( 値 );
      my $sql = "insert into テーブル名( 列名, ... ) values( $str, ... )";

    または、プレースフォルダを使用

      ?（プレースフォルダ）を使用すると、値を埋め込む際に自動的に quote に相当
     する処理が行われる。

      my $sql = "insert into テーブル名( 列名, 列名, ... ) values( ?, ?, ... )";
      my $sth = $dbh->prepare( $sql );
      $sth->execute( 値, 値, ... );

SQL 文の実行準備

    my $sth = $dbh->prepare( SQL 文 );

    得られたステートメント・ハンドルにて、SQL 文の実行、検索結果の取得を行う。

    成功時 : ステートメント・ハンドル
    失敗時 : undef

SQL 文の実行

    my $ret = $sth->execute();

    成功時 : 影響のあった行数
    失敗時 : undef

トランザクション

insert, update, delete 時には、必ずトランザクションを使用する。

    $dbh->begin_work;   # トランザクション開始
    my $sql = "SQL 文";
    my $sth = $dbh->prepare( $sql );   # 実行準備
    my $ret = $sth->execute( );   # 実行（戻り値は、影響のあった行数）
    $sth->finish;   # ステートメント・ハンドルの開放

    if ( $ret ) {   # 成功時
        $dbh->commit;   # 確定
    }
    else {   # 失敗時
        $dbh->rollback;   # 破棄
    }

検索結果の取得

    my @array = $sth->fetchrow_array;

    成功時 : 検索結果の１レコードを格納した配列
    失敗時 : undef

    使用例

    my @data = ( );
    while ( my ( $a, $b ) = $sth->fetchrow_array ) {
        
        my %hash = ( );
        $hash{ a } = $a;
        $hash{ b } = $b;
        ※ fetchrow_hashref を使用すると上記を省略できる
        
        push @data, \%hash;
    }

    my $hash_ref = $sth->fetchrow_hashref;

    成功時 : 検索結果の１レコードを格納したハッシュのリファレンス
    失敗時 : undef

    使用例

    my @data = ( );
    while ( my $hash_ref = $sth->fetchrow_hashref ) {
        push @data, $hash_ref;
    }

    $sth->bind_colums( \$bind_var, \$bind_var, ... );

    \$bind_var : バインドを行う変数のリファレンス

    バインドとはデータと変数を直接結びつけることで、データが取得されると
   対応する変数の値が自動的に更新される。
    データを取得するには、fetchrow_arrayref を呼び出す。

    my $array_ref = $sth->fetchrow_arrayref;

    成功時 : 検索結果の１レコードを格納した配列のリファレンス
    失敗時 : undef

    使用例

    my %hash = ( );
    my @data = ( );
    $sth->bind_columns( undef, \( @hash{@{$sth->{NAME_lc}}} ) );
    while ( $sth->fetchrow_arrayref ) {
        push @data, {%hash};   # { } で囲む、\ だと全て同じになる
    }

    配列のスライスを使用して、ハッシュを作成
    @hash{@{$sth->{NAME_lc}}}   # @hash{ qw( 列名, 列名 ) }
    @{$sth->{NAME_lc}}   # 列名のリスト
    $sth->{NAME_lc}   # 列名（小文字）の取得

#!/usr/bin/env perl

# 検索結果取得のベンチマークテスト

use strict;
use warnings;
use v5.24;
use Carp qw( croak );
use DBI;
use Benchmark qw( cmpthese );

# データベースへの接続
my $dbname = 'データベース名';
my $ds     = 'DBI:Pg:dbname=' . $dbname;   # データソース
my $user   = 'ユーザー名';
my $pw     = 'パスワード';

my $dbh = DBI->connect(
                $ds, $user, $pw,
                { RaiseError => 0, PrintError => 1, AutoCommit => 1 }
            ) or croak qq{can't connect : $!};

# SQL 文
my $sql = "select no, reg_day, name from member_tbl";

# 実行準備
my $sth = $dbh->prepare( $sql );

# fetchrow_array
sub get1 {
    # 実行
    $sth->execute();

    # 検索結果の取得
    my ( $no, $reg_day, $name ) = $sth->fetchrow_array;
}

# fetchrow_hashref
sub get2 {
    # 実行
    $sth->execute();

    # 検索結果の取得
    my $hash_ref = $sth->fetchrow_hashref;
}

# bind_columns + fetchrow_arrayref
sub get3 {
    # 実行
    $sth->execute();

    # 検索結果の取得
    my %hash = ( );
    $sth->bind_columns( undef, \( @hash{@{$sth->{NAME_lc}}} ) );
    $sth->fetchrow_arrayref;
}

# ベンチマークテストを十万回行う
cmpthese ( 100000, {
    fetchrow_array => sub { get1( ) },
    fetchrow_hashref => sub { get2( ) },
    'bind_columns + fetchrow_arrayref' => sub { get3( ) },
} );

# ステートメント・ハンドルの開放
$sth->finish;

# データベースとの切断
$dbh->disconnect;

ステートメント・ハンドルの開放

    全ての処理が終わったら、ステートメント・ハンドルを開放する。

    $sth->finish;

データベースとの切断

    $dbh->disconnect;