1
2
3
4
5

-- 危険な方法
SELECT * FROM users WHERE id = '1'

-- 安全な方法（プレースホルダー）
SELECT * FROM users WHERE id = ?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

# 言語: perl
# バージョン: 5.36以上
# 依存: Moo

package QueryBuilder;
use v5.36;
use Moo;

has _table      => (is => 'rw');
has _columns    => (is => 'rw', default => sub { [] });
has _conditions => (is => 'rw', default => sub { [] });
has _orders     => (is => 'rw', default => sub { [] });
has _limit      => (is => 'rw');
has _bind_values => (is => 'rw', default => sub { [] });

sub from ($self, $table) {
    $self->_table($table);
    return $self;
}

sub select ($self, @columns) {
    push $self->_columns->@*, @columns;
    return $self;
}

sub where ($self, $column, $value) {
    push $self->_conditions->@*, { column => $column };
    push $self->_bind_values->@*, $value;  # 値は別配列に保存
    return $self;
}

sub order_by ($self, $column, $dir = 'ASC') {
    push $self->_orders->@*, { column => $column, dir => $dir };
    return $self;
}

sub limit ($self, $count) {
    $self->_limit($count);
    return $self;
}

# SQLを生成（プレースホルダー版）
sub build ($self) {
    my @columns = $self->_columns->@*;
    my $cols = @columns ? CORE::join(', ', @columns) : '*';
    
    my $sql = "SELECT $cols FROM " . $self->_table;
    
    if ($self->_conditions->@*) {
        my @wheres;
        for my $cond ($self->_conditions->@*) {
            push @wheres, "$cond->{column} = ?";  # プレースホルダー
        }
        $sql .= " WHERE " . CORE::join(' AND ', @wheres);
    }
    
    if ($self->_orders->@*) {
        my @orders;
        for my $ord ($self->_orders->@*) {
            push @orders, "$ord->{column} $ord->{dir}";
        }
        $sql .= " ORDER BY " . CORE::join(', ', @orders);
    }
    
    if ($self->_limit) {
        $sql .= " LIMIT " . $self->_limit;
    }
    
    return $sql;
}

# バインド値を取得
sub bind_values ($self) {
    return $self->_bind_values->@*;
}

1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

#!/usr/bin/env perl
# 言語: perl
# バージョン: 5.36以上
# 依存: Moo, DBI, DBD::SQLite

use v5.36;
use lib 'lib';
use QueryBuilder;
use DBI;

my $builder = QueryBuilder->new
    ->from('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->order_by('created_at', 'DESC')
    ->limit(10);

my $sql = $builder->build;
my @bind = $builder->bind_values;

say "SQL: $sql";
say "Bind: " . join(', ', @bind);

1
2

SQL: SELECT * FROM users WHERE status = ? AND role = ? ORDER BY created_at DESC LIMIT 10
Bind: active, admin

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', '', '', {
    RaiseError => 1,
});

# テストデータ作成
$dbh->do('CREATE TABLE users (id INTEGER, status TEXT, role TEXT, created_at TEXT)');
$dbh->do("INSERT INTO users VALUES (1, 'active', 'admin', '2026-01-01')");
$dbh->do("INSERT INTO users VALUES (2, 'active', 'user', '2026-01-02')");
$dbh->do("INSERT INTO users VALUES (3, 'inactive', 'admin', '2026-01-03')");

# クエリビルダーで構築
my $builder = QueryBuilder->new
    ->from('users')
    ->where('status', 'active')
    ->where('role', 'admin');

# 安全に実行
my $sth = $dbh->prepare($builder->build);
$sth->execute($builder->bind_values);

while (my $row = $sth->fetchrow_hashref) {
    say "ID: $row->{id}, Status: $row->{status}, Role: $row->{role}";
}

1
2
3
4
5
6

my $attack_builder = QueryBuilder->new
    ->from('users')
    ->where('username', "' OR '1'='1");

say "SQL: " . $attack_builder->build;
say "Bind: " . join(', ', $attack_builder->bind_values);

1
2

SQL: SELECT * FROM users WHERE username = ?
Bind: ' OR '1'='1