blib/lib/DBIx/SecureCGI.pm

filter: Statementstmt 12.9% 51 / 394 Branchbran 3.4% 8 / 234 Conditioncond 4.1% 5 / 120 MC/DCmcdc 0.0% 0 / 74 Subroutinesub 18.1% 8 / 44 Podpod 100% 1 / 1 totaltotal 8.4% 73 / 867 SCAR 100.3
CC
172
Cov
9%
_get_sql
2862.0
Delete
210.0
Select
132.0
CRAP 22793.4
↑ Summary Build »
1 package DBIx::SecureCGI;
2  
3 21 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 21 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 21 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 21 ▶use feature ':5.10';
Subroutine BEGIN: called CC=1 SCAR=0.0
7 21 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 21 ▶use version; our $VERSION = qv('2.0.6'); # REMINDER: update Changes
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 # REMINDER: update dependencies in Build.PL
12 21 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
13  
14  
15 ## no critic (ProhibitPostfixControls Capitalization ProhibitEnumeratedClasses)
16  
17 21 my $PRIVATE = 'private_' . __PACKAGE__;
18 21 my $INT = qr/\A-?\d+\s+(?:SECOND|MINUTE|HOUR|DAY|MONTH|YEAR)\z/msi;
19 21 my $IDENT = qr/((?!__)\w[a-zA-Z0-9]*(?:_(?!_)[a-zA-Z0-9]*)*)/ms;
20 21 my %Func = ();
21  
22  
23 DefineFunc(eq => sub {
24 0 ▶ my ($dbh, $f, $v) = @_;
Subroutine __ANON__: not called CC=7 SCAR=40.3
25 0 my (@val, $null, @expr);
26 0 ▶ @val = ref $v ? @{$v} : $v;
Branch: 1 branch
BranchTrueFalseTotal
ref $v ? : 0 0 0
27 0 $null = grep {!defined} @val;
28 0 @val = grep {defined} @val;
29 0 ▶ push @expr, sprintf '%s IS NULL', $f if $null;
Branch: 1 branch
BranchTrueFalseTotal
if $null 0 0 0
30 0 ▶ push @expr, sprintf '%s = %s', $f, $dbh->quote($val[0]) if @val==1;
Branch: 1 branch
BranchTrueFalseTotal
if @val == 1 0 0 0
31 push @expr, sprintf '%s IN (%s)',
32 0 ▶ $f, join q{,}, map { $dbh->quote($_) } @val if @val>1;
Branch: 1 branch
BranchTrueFalseTotal
if @val > 1 0 0 0
33 0 ▶ push @expr, 'NOT 1' if !@expr;
Branch: 1 branch
BranchTrueFalseTotal
unless @expr 0 0 0
34 0 ▶ return @expr==1 ? $expr[0] : '('.join(' OR ', @expr).')';
Branch: 1 branch
BranchTrueFalseTotal
@expr == 1 ? : 0 0 0
35 21 });
36 DefineFunc(ne => sub {
37 0 ▶ my ($dbh, $f, $v) = @_;
Subroutine __ANON__: not called CC=10 SCAR=47.0
38 0 my (@val, $null, @expr);
39 0 ▶ @val = ref $v ? @{$v} : $v;
Branch: 1 branch
BranchTrueFalseTotal
ref $v ? : 0 0 0
40 0 $null = grep {!defined} @val;
41 0 @val = grep {defined} @val;
42 0 ▶ push @expr, sprintf '%s IS NOT NULL', $f if $null && !@val;
Branch: 1 branch
BranchTrueFalseTotal
if $null and not @val 0 0 0
Condition0%0/3
$null and not @val
!ll&&!rl&&r
000
MC/DC0%0/2
$null and not @val
$null not @val
Truth table0%0/3
A and not(B)
A= $nullB= not @val
ABresult
0X0
100
111
43 0 ▶ push @expr, sprintf '%s IS NULL', $f if !$null && @val;
Branch: 1 branch
BranchTrueFalseTotal
if not $null and @val 0 0 0
Condition0%0/3
not $null and @val
!ll&&!rl&&r
000
MC/DC0%0/2
not $null and @val
not $null @val
Truth table0%0/3
not(A) and B
A= not $nullB= @val
ABresult
0X0
100
111
44 0 ▶ push @expr, sprintf '%s != %s', $f,$dbh->quote($val[0]) if @val==1;
Branch: 1 branch
BranchTrueFalseTotal
if @val == 1 0 0 0
45 push @expr, sprintf '%s NOT IN (%s)', $f,
46 0 ▶ join q{,}, map { $dbh->quote($_) } @val if @val>1;
Branch: 1 branch
BranchTrueFalseTotal
if @val > 1 0 0 0
47 0 ▶ push @expr, 'NOT 0' if !@expr;
Branch: 1 branch
BranchTrueFalseTotal
unless @expr 0 0 0
48 0 ▶ return @expr==1 ? $expr[0] : '('.join(' OR ', @expr).')';
Branch: 1 branch
BranchTrueFalseTotal
@expr == 1 ? : 0 0 0
49 21 });
50 21 DefineFunc(lt => '%s < %s');
51 21 DefineFunc(gt => '%s > %s');
52 21 DefineFunc(le => '%s <= %s');
53 21 DefineFunc(ge => '%s >= %s');
54 21 DefineFunc(like => '%s LIKE %s');
55 21 DefineFunc(not_like => '%s NOT LIKE %s');
56 21 DefineFunc(date_eq => [$INT, '%s = DATE_ADD(NOW(), INTERVAL %s)']);
57 21 DefineFunc(date_ne => [$INT, '%s != DATE_ADD(NOW(), INTERVAL %s)']);
58 21 DefineFunc(date_lt => [$INT, '%s < DATE_ADD(NOW(), INTERVAL %s)']);
59 21 DefineFunc(date_gt => [$INT, '%s > DATE_ADD(NOW(), INTERVAL %s)']);
60 21 DefineFunc(date_le => [$INT, '%s <= DATE_ADD(NOW(), INTERVAL %s)']);
61 21 DefineFunc(date_ge => [$INT, '%s >= DATE_ADD(NOW(), INTERVAL %s)']);
62 DefineFunc(set_date => sub {
63 0 ▶ my ($dbh, $f, $v) = @_;
Subroutine __ANON__: not called CC=3 SCAR=24.8
64 0 ▶ if (uc $v eq 'NOW') {
Branch: 2 branches
BranchTrueFalseTotal
if (uc $v eq 'NOW') { } 0 0 0
elsif ($v =~ /$INT/mos) { } 0 0 0
65 0 return sprintf '%s = NOW()', $f;
66 } elsif ($v =~ /$INT/mso) {
67 0 return sprintf '%s = DATE_ADD(NOW(), INTERVAL %s)', $f, $dbh->quote($v),
68 }
69 0 return;
70 21 });
71 DefineFunc(set_add => sub {
72 0 ▶ my ($dbh, $f, $v) = @_;
Subroutine __ANON__: not called CC=1 SCAR=6.9
73 0 return sprintf '%s = %s + %s', $f, $f, $dbh->quote($v);
74 21 });
75  
76  
77 sub DefineFunc {
78 336 ▶ my ($func, $cmd) = @_;
Subroutine DefineFunc: called CC=12 SCAR=32.6
Pod documented
79 336 ▶ if (!$func || ref $func || $func !~ /\A[A-Za-z]\w*\z/ms) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $func or ref $func or not $func =~ /\A[A-Za-z]\w*\z/ms) 0 336 336
Condition33%2/6
not $func or ref $func or not $func =~ /\A[A-Za-z]\w*\z/ms
l!l&&r!l&&!r
00336
not $func or ref $func
l!l&&r!l&&!r
00336
MC/DC0%0/3
not $func or ref $func or not $func =~ /\A[A-Za-z]\w*\z/ms
not $func ref $func not $func =~ /\A[A-Za-z]\w*\z/ms
Truth table25%1/4
not(A) or B or not(C)
A= not $funcB= ref $funcC= not $func =~ /\A[A-Za-z]\w*\z/ms
ABCresult
1XX1
01X1
0011
0000
80 0 croak "bad function name: $func";
81 }
82 336 ▶ if (!ref $cmd) {
Branch: 3 branches
BranchTrueFalseTotal
if (not ref $cmd) { } 126 210 336
elsif (ref $cmd eq 'ARRAY') { } 126 84 210
elsif (ref $cmd ne 'CODE') { } 0 84 84
83 126 ▶ if (2 != (() = $cmd =~ /%s/msg)) {
Branch: 1 branch
BranchTrueFalseTotal
if (2 != (() = $cmd =~ /%s/gms)) 0 126 126
84 0 croak "bad function: $cmd";
85 }
86 } elsif (ref $cmd eq 'ARRAY') {
87 126 ▶ if (2 != @{$cmd}
Branch: 1 branch
BranchTrueFalseTotal
if (2 != @{$cmd;} or ref $cmd->[0] ne "Regexp" or (ref $cmd->[1] or 2 != (() = $cmd->[1] =~ /%s/gms))) 0 126 126
Condition33%3/9
2 != @{$cmd;} or ref $cmd->[0] ne "Regexp" or (ref $cmd->[1] or 2 != (() = $cmd->[1] =~ /%s/gms))
l!l&&r!l&&!r
00126
2 != @{$cmd;} or ref $cmd->[0] ne "Regexp"
l!l&&r!l&&!r
00126
ref $cmd->[1] or 2 != (() = $cmd->[1] =~ /%s/gms)
l!l&&r!l&&!r
00126
MC/DC0%0/4
2 != @{$cmd;} or ref $cmd->[0] ne "Regexp" or (ref $cmd->[1] or 2 != (() = $cmd->[1] =~ /%s/gms))
2 != @{$cmd;} ref $cmd->[0] ne "Regexp" ref $cmd->[1] 2 != (() = $cmd->[1] =~ /%s/gms)
Truth table20%1/5
A or B or C or D
A= 2 != @{$cmd;}B= ref $cmd->[0] ne "Regexp"C= ref $cmd->[1]D= 2 != (() = $cmd->[1] =~ /%s/gms)
ABCDresult
1XXX1
01XX1
001X1
00011
00000
88 || ref $cmd->[0] ne 'Regexp'
89 || (ref $cmd->[1] || 2 != (() = $cmd->[1] =~ /%s/msg))) {
90 0 croak "bad function: [@$cmd]";
91 }
92 } elsif (ref $cmd ne 'CODE') {
93 0 croak 'bad function';
94 }
95 336 $Func{$func} = $cmd;
96 336 return;
97 }
98  
99 sub _ret {
100 0 ▶ my $cb = shift;
Subroutine _ret: not called CC=3 SCAR=24.8
101 0 ▶ if ($cb) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cb) { } 0 0 0
102 0 return $cb->(@_);
103 } else {
104 0 ▶ return wantarray ? @_ : $_[0];
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 0 0 0
105 }
106 }
107  
108 sub _ret1 {
109 0 ▶ my ($cb, $ret, $h) = @_;
Subroutine _ret1: not called CC=2 SCAR=17.9
110 0 ▶ if ($cb) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cb) { } 0 0 0
111 0 return $cb->($ret, $h);
112 } else {
113 0 return $ret;
114 }
115 }
116  
117 sub _retdo {
118 0 ▶ my ($dbh, $sql, $cb) = @_;
Subroutine _retdo: not called CC=2 SCAR=17.9
119 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
120 0 return $dbh->do($sql);
121 }
122 0 return $dbh->do($sql, undef, $cb);
123 }
124  
125 # Set cache to given HASHREF, if any.
126 # Initialize cache, if needed.
127 # Return current cache.
128 sub DBI::db::SecureCGICache {
129 0 ▶ my ($dbh, $cache) = @_;
Subroutine SecureCGICache: not called CC=4 SCAR=30.0
130 0 ▶ if ($cache && ref $cache eq 'HASH') {
Branch: 1 branch
BranchTrueFalseTotal
if ($cache and ref $cache eq 'HASH') { } 0 0 0
Condition0%0/3
$cache and ref $cache eq "HASH"
!ll&&!rl&&r
000
MC/DC0%0/2
$cache and ref $cache eq "HASH"
$cache ref $cache eq "HASH"
Truth table0%0/3
A and B
A= $cacheB= ref $cache eq "HASH"
ABresult
0X0
100
111
131 0 $dbh->{$PRIVATE} = $cache;
132 } else {
133 0 ▶ $dbh->{$PRIVATE} //= {};
Condition0%0/2
$dbh->{$PRIVATE} //= {}
l!l
00
MC/DC0%0/1
$dbh->{$PRIVATE} //= {}
$dbh->{$PRIVATE}
Truth table0%0/2
A
A= $dbh->{$PRIVATE}
Aresult
11
00
134 }
135 0 return $dbh->{$PRIVATE};
136 }
137  
138 # Ensure $dbh->All("DESC $table") is cached.
139 # Return cached $dbh->All("DESC $table").
140 # On error set $dbh->err and return nothing.
141 sub DBI::db::ColumnInfo {
142 0 ▶ my ($dbh, $table, $cb) = @_;
Subroutine ColumnInfo: not called CC=3 SCAR=24.8
143 0 my $cache = $dbh->SecureCGICache();
144 0 ▶ if ($cache->{$table}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cache->{$table}) 0 0 0
145 0 return _ret($cb, $cache->{$table});
146 }
147  
148 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
149 0 my @desc = $dbh->All('DESC '.$dbh->quote_identifier($table));
150 0 return _set_column_info($dbh, $cache, $table, undef, @desc);
151 }
152 return $dbh->All('DESC '.$dbh->quote_identifier($table), sub {
153 0 ▶ my @desc = @_;
Subroutine __ANON__: not called CC=1 SCAR=6.9
154 0 return _set_column_info($dbh, $cache, $table, $cb, @desc);
155 0 });
156 }
157  
158 sub _set_column_info {
159 0 ▶ my ($dbh, $cache, $table, $cb, @desc) = @_;
Subroutine _set_column_info: not called CC=4 SCAR=30.0
160 0 ▶ if (@desc) {
Branch: 1 branch
BranchTrueFalseTotal
if (@desc) 0 0 0
161 0 my @pk = grep {$desc[$_]{Key} eq 'PRI'} 0 .. $#desc;
162 0 ▶ if (1 != @pk || $pk[0] != 0) {
Branch: 1 branch
BranchTrueFalseTotal
if (1 != @pk or $pk[0] != 0) 0 0 0
Condition0%0/3
1 != @pk or $pk[0] != 0
l!l&&r!l&&!r
000
MC/DC0%0/2
1 != @pk or $pk[0] != 0
1 != @pk $pk[0] != 0
Truth table0%0/3
A or B
A= 1 != @pkB= $pk[0] != 0
ABresult
1X1
011
000
163 0 return _ret($cb, $dbh->set_err($DBI::stderr, "first field must be primary key: $table\n", undef, 'ColumnInfo'));
164 }
165 0 $cache->{$table} = \@desc;
166 }
167 0 return _ret($cb, $cache->{$table});
168 }
169  
170 # Ensure DESC for all $tables cached.
171 # Return $dbh->SecureCGICache().
172 # On error set $dbh->err and return nothing.
173 sub DBI::db::TableInfo {
174 0 ▶ my ($dbh, $tables, $cb) = @_;
Subroutine TableInfo: not called CC=7 SCAR=40.3
175 0 ▶ my @tables = ref $tables eq 'ARRAY' ? @{$tables} : ($tables);
Branch: 1 branch
BranchTrueFalseTotal
ref $tables eq 'ARRAY' ? : 0 0 0
176 0 ▶ if (!@tables || grep {/\A\z|\s/ms} @tables) {
Branch: 1 branch
BranchTrueFalseTotal
if (not @tables or grep {/\A\z|\s/ms;} @tables) 0 0 0
Condition0%0/3
not @tables or grep {/\A\z|\s/ms;} @tables
l!l&&r!l&&!r
000
MC/DC0%0/2
not @tables or grep {/\A\z|\s/ms;} @tables
not @tables grep {/\A\z|\s/ms;} @tables
Truth table0%0/3
not(A) or B
A= not @tablesB= grep {/\A\z|\s/ms;} @tables
ABresult
1X1
011
000
177 0 return _ret($cb, $dbh->set_err($DBI::stderr, "bad tables: [@tables]\n", undef, 'TableInfo'));
178 }
179  
180 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
181 0 ▶ while (@tables) {
Branch: 1 branch
BranchTrueFalseTotal
if (@tables) 0 0 0
182 0 my $desc = $dbh->ColumnInfo(shift @tables);
183 0 ▶ if (!$desc) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($desc) 0 0 0
184 0 return;
185 }
186 }
187 0 return $dbh->SecureCGICache();
188 }
189 0 my $code; $code = sub {
190 0 ▶ my ($desc) = @_;
Subroutine __ANON__: not called CC=3 SCAR=24.8
191 0 ▶ if (!$desc) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($desc) 0 0 0
192 0 undef $code;
193 0 return $cb->();
194 }
195 0 ▶ if (@tables) {
Branch: 1 branch
BranchTrueFalseTotal
if (@tables) 0 0 0
196 0 return $dbh->ColumnInfo(shift @tables, $code);
197 }
198 0 undef $code;
199 0 return $cb->( $dbh->SecureCGICache() );
200 0 };
201 0 return $dbh->ColumnInfo(shift @tables, $code);
202 }
203  
204 sub DBI::db::GetSQL {
205 0 ▶ my ($dbh, $tables, $P, $cb) = @_;
Subroutine GetSQL: not called CC=3 SCAR=24.8
206 # remove possible JOIN info from table names for TableInfo()
207 0 ▶ my @tables = map {my $s=$_;$s=~s/\s.*//ms;$s} ref $tables ? @{$tables} : $tables; ## no critic
Branch: 1 branch
BranchTrueFalseTotal
ref $tables ? : 0 0 0
208 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
209 0 my $cache = $dbh->TableInfo(\@tables);
210 0 return _get_sql($dbh, $cache, $tables, $P);
211 }
212 return $dbh->TableInfo(\@tables, sub {
213 0 ▶ my $cache = shift;
Subroutine __ANON__: not called CC=1 SCAR=6.9
214 0 return _get_sql($dbh, $cache, $tables, $P, $cb);
215 0 });
216 }
217  
218 sub _get_sql { ## no critic (ProhibitExcessComplexity)
219 0 ▶ my ($dbh, $cache, $tables, $P, $cb) = @_;
Subroutine _get_sql: not called CC=53 SCAR=79.6
220 0 ▶ if (!$cache) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cache) 0 0 0
221 0 return _ret($cb);
222 }
223  
224 # Extract JOIN type info from table names
225 0 my (@tables, @jointype);
226 0 ▶ for (ref $tables eq 'ARRAY' ? @{$tables} : $tables) {
Branch: 1 branch
BranchTrueFalseTotal
ref $tables eq 'ARRAY' ? : 0 0 0
227 0 ▶ if (!/\A(\S+)(?:\s+(LEFT|INNER))?\s*\z/msi) {
Branch: 1 branch
BranchTrueFalseTotal
unless (/\A(\S+)(?:\s+(LEFT|INNER))?\s*\z/ism) 0 0 0
228 0 return _ret($cb, $dbh->set_err($DBI::stderr, "unknown join type: $_\n", undef, 'GetSQL'));
229 }
230 0 push @tables, $1;
231 0 ▶ push @jointype, $2 // 'INNER';
Condition0%0/2
$2 // "INNER"
l!l
00
MC/DC0%0/1
$2 // "INNER"
$2
Truth table0%0/2
A
A= $2
Aresult
11
00
232 }
233  
234 my %SQL = (
235 Table => $tables[0],
236 ID => $cache->{ $tables[0] }[0]{Field},
237 0 Select => q{},
238 From => q{},
239 Set => q{},
240 Where => q{},
241 UpdateWhere => q{},
242 Order => q{},
243 Group => q{},
244 Limit => q{},
245 SelectLimit => q{},
246 );
247  
248 # Detect keys which should be used for JOINing tables
249 0 $SQL{From} = $dbh->quote_identifier($tables[0]);
250 0 my @field = map {{ map {$_->{Field}=>1} @{ $cache->{$_} } }} @tables; ## no critic
251 TABLE:
252 0 for my $right (1..$#tables) {
253 ## no critic (ProhibitAmbiguousNames)
254 0 my $rkey = $cache->{ $tables[$right] }[0]{Field};
255 0 for my $left (0..$right-1) {
256 0 my $lkey = $cache->{ $tables[$left] }[0]{Field};
257 my $key = $field[$left]{$rkey} ? $rkey :
258 0 ▶ $field[$right]{$lkey} ? $lkey : next;
Branch: 2 branches
BranchTrueFalseTotal
$field[$left]{$rkey} ? : 0 0 0
$field[$right]{$lkey} ? : 0 0 0
259 $SQL{From} .= sprintf ' %s JOIN %s ON (%s.%s = %s.%s)',
260 $jointype[$right],
261 0 map { $dbh->quote_identifier($_) }
262 $tables[$right], $tables[$right], $key, $tables[$left], $key;
263 0 next TABLE;
264 }
265 0 return _ret($cb, $dbh->set_err($DBI::stderr, "can't join table: $tables[$right]\n", undef, 'GetSQL'));
266 }
267  
268 # Set $SQL{Select} using qualified field names and without duplicates
269 0 my %qualify;
270 0 for my $t (@tables) {
271 0 for my $f (map {$_->{Field}} @{ $cache->{$t} }) {
272 0 ▶ next if $qualify{$f};
Branch: 1 branch
BranchTrueFalseTotal
if $qualify{$f} 0 0 0
273 $qualify{$f} = sprintf '%s.%s',
274 0 map { $dbh->quote_identifier($_) } $t, $f;
275 0 $SQL{Select} .= ', '.$qualify{$f};
276 }
277 }
278 0 $SQL{Select} =~ s/\A, //ms;
279  
280 # Set $SQL{Set}, $SQL{Where}, $SQL{UpdateWhere}
281 0 for my $k (keys %{$P}) {
282 0 ▶ $k =~ /\A$IDENT(?:__(?!_)$IDENT)?\z/ms or next; # ignore non-field keys
Branch: 1 branch
BranchTrueFalseTotal
$k =~ /\A$IDENT(?:__(?!_)$IDENT)?\z/ms or next 0 0 0
283 0 ▶ my $f = $qualify{$1} or next; # ignore non-field keys
Branch: 1 branch
BranchTrueFalseTotal
my $f = $qualify{$1} or next 0 0 0
284 0 ▶ my $func= $2 // q{};
Condition0%0/2
$2 // ""
l!l
00
MC/DC0%0/1
$2 // ""
$2
Truth table0%0/2
A
A= $2
Aresult
11
00
285 0 ▶ my $cmd = $Func{$func || 'eq'};
Condition0%0/2
$func || 'eq'
l!l
00
MC/DC0%0/1
$func || 'eq'
$func
Truth table0%0/2
A
A= $func
Aresult
11
00
286 0 ▶ if (!$cmd) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cmd) 0 0 0
287 0 return _ret($cb, $dbh->set_err($DBI::stderr, "unknown function: $k\n", undef, 'GetSQL'));
288 }
289 0 ▶ if (!$func && ref $P->{$k}) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $func and ref $P->{$k}) 0 0 0
Condition0%0/3
not $func and ref $P->{$k}
!ll&&!rl&&r
000
MC/DC0%0/2
not $func and ref $P->{$k}
not $func ref $P->{$k}
Truth table0%0/3
not(A) and B
A= not $funcB= ref $P->{$k}
ABresult
0X0
100
111
290 0 return _ret($cb, $dbh->set_err($DBI::stderr, "ARRAYREF without function: $k\n", undef, 'GetSQL'));
291 }
292 # WARNING functions `eq' and `ne' must process value array themselves:
293 0 ▶ my $is_list = ref $P->{$k} && $func ne 'eq' && $func ne 'ne';
Condition0%0/6
ref $P->{$k} && $func ne 'eq' && $func ne 'ne'
!ll&&!rl&&r
000
ref $P->{$k} and $func ne "eq"
!ll&&!rl&&r
000
MC/DC0%0/3
ref $P->{$k} && $func ne 'eq' && $func ne 'ne'
ref $P->{$k} $func ne "eq" $func ne 'ne'
Truth table0%0/4
A and B && C
A= ref $P->{$k}B= $func ne "eq"C= $func ne 'ne'
ABCresult
0XX0
10X0
1100
1111
294 0 ▶ for my $v ($is_list ? @{$P->{$k}} : $P->{$k}) {
Branch: 1 branch
BranchTrueFalseTotal
$is_list ? : 0 0 0
295 0 ▶ my $expr
Branch: 2 branches
BranchTrueFalseTotal
ref $cmd eq 'CODE' ? : 0 0 0
ref $cmd eq 'ARRAY' ? : 0 0 0
Condition0%0/3
$v =~ /$cmd->[0]/ms and sprintf $cmd->[1], $f, $v
!ll&&!rl&&r
000
MC/DC0%0/2
$v =~ /$cmd->[0]/ms and sprintf $cmd->[1], $f, $v
$v =~ /$cmd->[0]/ms sprintf $cmd->[1], $f, $v
Truth table0%0/3
A and B
A= $v =~ /$cmd->[0]/msB= sprintf $cmd->[1], $f, $v
ABresult
0X0
100
111
296 = ref $cmd eq 'CODE' ? $cmd->($dbh, $f, $v)
297 : ref $cmd eq 'ARRAY' ? ($v =~ /$cmd->[0]/ms && sprintf $cmd->[1], $f, $v)
298 : sprintf $cmd, $f, $dbh->quote($v);
299 0 ▶ if (!$expr) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($expr) 0 0 0
300 0 return _ret($cb, $dbh->set_err($DBI::stderr, "bad value for $k: $v\n", undef, 'GetSQL'));
301 }
302 0 ▶ $SQL{Set} .= ", $expr" if !$func || $func =~ /\Aset_/ms;
Branch: 1 branch
BranchTrueFalseTotal
if not $func or $func =~ /\Aset_/ms 0 0 0
Condition0%0/3
not $func or $func =~ /\Aset_/ms
l!l&&r!l&&!r
000
MC/DC0%0/2
not $func or $func =~ /\Aset_/ms
not $func $func =~ /\Aset_/ms
Truth table0%0/3
not(A) or B
A= not $funcB= $func =~ /\Aset_/ms
ABresult
1X1
011
000
303 0 ▶ $SQL{Where} .= " AND $expr" if $func !~ /\Aset_/ms;
Branch: 1 branch
BranchTrueFalseTotal
unless $func =~ /\Aset_/ms 0 0 0
304 0 ▶ $SQL{UpdateWhere} .= " AND $expr" if $func && $func !~ /\Aset_/ms;
Branch: 1 branch
BranchTrueFalseTotal
if $func and not $func =~ /\Aset_/ms 0 0 0
Condition0%0/3
$func and not $func =~ /\Aset_/ms
!ll&&!rl&&r
000
MC/DC0%0/2
$func and not $func =~ /\Aset_/ms
$func not $func =~ /\Aset_/ms
Truth table0%0/3
A and not(B)
A= $funcB= not $func =~ /\Aset_/ms
ABresult
0X0
100
111
305 0 ▶ $SQL{UpdateWhere} .= " AND $expr" if $k eq $SQL{ID};
Branch: 1 branch
BranchTrueFalseTotal
if $k eq $SQL{"ID"} 0 0 0
306 }
307 }
308 0 $SQL{Set} =~ s/\A, //ms;
309 0 $SQL{Where} =~ s/\A AND //ms;
310 0 $SQL{UpdateWhere} =~ s/\A AND //ms;
311 0 $SQL{Set} =~ s/\s+IS\s+NULL/ = NULL/msg;
312 0 ▶ $SQL{Where} ||= '1';
Condition0%0/2
$SQL{"Where"} ||= 1
l!l
00
MC/DC0%0/1
$SQL{"Where"} ||= 1
$SQL{"Where"}
Truth table0%0/2
A
A= $SQL{"Where"}
Aresult
11
00
313 0 ▶ $SQL{UpdateWhere} ||= '1';
Condition0%0/2
$SQL{"UpdateWhere"} ||= 1
l!l
00
MC/DC0%0/1
$SQL{"UpdateWhere"} ||= 1
$SQL{"UpdateWhere"}
Truth table0%0/2
A
A= $SQL{"UpdateWhere"}
Aresult
11
00
314  
315 # Set $SQL{Order} and $SQL{Group}
316 0 ▶ for my $order (ref $P->{__order} ? @{$P->{__order}} : $P->{__order}) {
Branch: 1 branch
BranchTrueFalseTotal
ref $P->{'__order'} ? : 0 0 0
317 0 ▶ next if !defined $order;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $order 0 0 0
318 0 ▶ if ($order !~ /\A(\w+)\s*( ASC| DESC|)\z/ms || !$qualify{$1}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($order =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}) 0 0 0
Condition0%0/3
$order =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}
!ll&&!rl&&r
000
MC/DC0%0/2
$order =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}
$order =~ /\A(\w+)\s*( ASC| DESC|)\z/ms $qualify{$1}
Truth table0%0/3
A and B
A= $order =~ /\A(\w+)\s*( ASC| DESC|)\z/msB= $qualify{$1}
ABresult
0X0
100
111
319 0 return _ret($cb, $dbh->set_err($DBI::stderr, "bad __order value: $order\n", undef, 'GetSQL'));
320 }
321 0 $SQL{Order} .= ", $qualify{$1}$2";
322 }
323 0 ▶ for my $group (ref $P->{__group} ? @{$P->{__group}} : $P->{__group}) {
Branch: 1 branch
BranchTrueFalseTotal
ref $P->{'__group'} ? : 0 0 0
324 0 ▶ next if !defined $group;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $group 0 0 0
325 0 ▶ if ($group !~ /\A(\w+)\s*( ASC| DESC|)\z/ms || !$qualify{$1}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($group =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}) 0 0 0
Condition0%0/3
$group =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}
!ll&&!rl&&r
000
MC/DC0%0/2
$group =~ /\A(\w+)\s*( ASC| DESC|)\z/ms and $qualify{$1}
$group =~ /\A(\w+)\s*( ASC| DESC|)\z/ms $qualify{$1}
Truth table0%0/3
A and B
A= $group =~ /\A(\w+)\s*( ASC| DESC|)\z/msB= $qualify{$1}
ABresult
0X0
100
111
326 0 return _ret($cb, $dbh->set_err($DBI::stderr, "bad __group value: $group\n", undef, 'GetSQL'));
327 }
328 0 $SQL{Group} .= ", $qualify{$1}$2";
329 }
330 0 $SQL{Order} =~ s/\A, //ms;
331 0 $SQL{Group} =~ s/\A, //ms;
332  
333 # Set $SQL{Limit}, $SQL{SelectLimit}
334 0 ▶ my @limit = ref $P->{__limit} ? @{$P->{__limit}} : $P->{__limit} // ();
Branch: 1 branch
BranchTrueFalseTotal
ref $P->{'__limit'} ? : 0 0 0
Condition0%0/3
$P->{"__limit"} // ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$P->{"__limit"} // ()
$P->{"__limit"} ()
Truth table0%0/3
A // B
A= $P->{"__limit"}B= ()
ABresult
1X1
011
000
335 0 for (grep {!m/\A\d+\z/ms} @limit) {
336 0 return _ret($cb, $dbh->set_err($DBI::stderr, "bad __limit value: $_\n", undef, 'GetSQL'));
337 }
338 0 ▶ if (@limit == 1) {
Branch: 3 branches
BranchTrueFalseTotal
if (@limit == 1) { } 0 0 0
elsif (@limit == 2) { } 0 0 0
elsif (@limit > 2) { } 0 0 0
339 0 $SQL{Limit} = " $limit[0]"; # make __limit=>0 true value
340 0 $SQL{SelectLimit} = " $limit[0]"; # make __limit=>0 true value
341 }
342 elsif (@limit == 2) {
343 0 $SQL{SelectLimit} = join q{,}, @limit;
344 }
345 elsif (@limit > 2) {
346 0 return _ret($cb, $dbh->set_err($DBI::stderr, "too many __limit values: [@limit]\n", undef, 'GetSQL'));
347 }
348  
349 0 return _ret($cb, \%SQL);
350 }
351  
352 sub DBI::db::Insert {
353 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Insert: not called CC=4 SCAR=30.0
354 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
355  
356 my $sql = sprintf 'INSERT INTO %s SET %s',
357 0 $dbh->quote_identifier($SQL->{Table}), $SQL->{Set};
358  
359 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
360 0 ▶ return $dbh->do($sql) ? $dbh->{mysql_insertid} : undef;
Branch: 1 branch
BranchTrueFalseTotal
$dbh->do($sql) ? : 0 0 0
361 }
362 return $dbh->do($sql, undef, sub {
363 0 ▶ my ($rv, $dbh) = @_; ## no critic (ProhibitReusedNames)
Subroutine __ANON__: not called CC=2 SCAR=17.9
364 0 ▶ return $cb->(($rv ? $dbh->{mysql_insertid} : undef), $dbh);
Branch: 1 branch
BranchTrueFalseTotal
$rv ? : 0 0 0
365 0 });
366 }
367  
368 sub DBI::db::InsertIgnore {
369 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine InsertIgnore: not called CC=2 SCAR=17.9
370 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
371  
372 my $sql = sprintf 'INSERT IGNORE INTO %s SET %s',
373 0 $dbh->quote_identifier($SQL->{Table}), $SQL->{Set};
374 0 return _retdo($dbh, $sql, $cb);
375 }
376  
377 sub DBI::db::Update {
378 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Update: not called CC=6 SCAR=37.4
379 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
380 0 ▶ if ($SQL->{UpdateWhere} eq '1' && !$P->{__force}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($SQL->{"UpdateWhere"} eq 1 and not $P->{"__force"}) 0 0 0
Condition0%0/3
$SQL->{"UpdateWhere"} eq 1 and not $P->{"__force"}
!ll&&!rl&&r
000
MC/DC0%0/2
$SQL->{"UpdateWhere"} eq 1 and not $P->{"__force"}
$SQL->{"UpdateWhere"} eq 1 not $P->{"__force"}
Truth table0%0/3
A and not(B)
A= $SQL->{"UpdateWhere"} eq 1B= not $P->{"__force"}
ABresult
0X0
100
111
381 0 return _ret1($cb, $dbh->set_err($DBI::stderr, "empty WHERE require {__force=>1}\n", undef, 'Update'), $dbh);
382 }
383  
384 my $sql = sprintf 'UPDATE %s SET %s WHERE %s' . ($SQL->{Limit} ? ' LIMIT %s' : q{}),
385 $dbh->quote_identifier($SQL->{Table}), $SQL->{Set}, $SQL->{UpdateWhere},
386 0 ▶ $SQL->{Limit} || ();
Branch: 1 branch
BranchTrueFalseTotal
$SQL->{'Limit'} ? : 0 0 0
Condition0%0/3
$SQL->{'Limit'} || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$SQL->{'Limit'} || ()
$SQL->{'Limit'} ()
Truth table0%0/3
A || B
A= $SQL->{'Limit'}B= ()
ABresult
1X1
011
000
387 0 return _retdo($dbh, $sql, $cb);
388 }
389  
390 sub DBI::db::Replace {
391 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Replace: not called CC=2 SCAR=17.9
392 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
393  
394 my $sql = sprintf 'REPLACE INTO %s SET %s',
395 0 $dbh->quote_identifier($SQL->{Table}), $SQL->{Set};
396 0 return _retdo($dbh, $sql, $cb);
397 }
398  
399 sub _find_tables_for_delete {
400 0 ▶ my ($dbh, $fields, $tables, $P, $cb) = @_;
Subroutine _find_tables_for_delete: not called CC=6 SCAR=37.4
401 0 ▶ if (!@{$tables}) {
Branch: 1 branch
BranchTrueFalseTotal
unless (@{$tables;}) 0 0 0
402 0 return _ret1($cb, undef, $dbh);
403 }
404  
405 0 my $found = [];
406 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
407 0 for my $t (@{$tables}) {
408 0 my $desc = $dbh->ColumnInfo($t);
409 0 ▶ if ($desc) {
Branch: 1 branch
BranchTrueFalseTotal
if ($desc) 0 0 0
410 0 my @columns = map {$_->{Field}} @{$desc};
411 0 my %seen;
412 0 ▶ if (@{$fields} == grep {++$seen{$_}==2} @{$fields}, @columns) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$fields;} == grep({++$seen{$_} == 2;} @{$fields;}, @columns)) 0 0 0
413 0 push @{$found}, $t;
414 }
415 }
416 }
417 0 return $dbh->Delete($found, $P);
418 }
419 0 my $code; $code = sub {
420 0 ▶ my ($desc) = @_;
Subroutine __ANON__: not called CC=4 SCAR=30.0
421 0 my $t = shift @{$tables};
422 0 ▶ if ($desc) {
Branch: 1 branch
BranchTrueFalseTotal
if ($desc) 0 0 0
423 0 my @columns = map {$_->{Field}} @{$desc};
424 0 my %seen;
425 0 ▶ if (@{$fields} == grep {++$seen{$_}==2} @{$fields}, @columns) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$fields;} == grep({++$seen{$_} == 2;} @{$fields;}, @columns)) 0 0 0
426 0 push @{$found}, $t;
427 }
428 }
429 0 ▶ if (@{$tables}) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$tables;}) 0 0 0
430 0 return $dbh->ColumnInfo($tables->[0], $code);
431 }
432 0 undef $code;
433 0 return $dbh->Delete($found, $P, $cb);
434 0 };
435 0 return $dbh->ColumnInfo($tables->[0], $code);
436 }
437  
438 sub DBI::db::Delete { ## no critic (ProhibitExcessComplexity)
439 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Delete: not called CC=14 SCAR=53.5
440  
441 0 ▶ if (!defined $table) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $table) 0 0 0
442 0 ▶ my %fields = map {/\A$IDENT(?:__(?!_)$IDENT)?\z/ms ? ($1=>1) : ()} keys %{$P};
Branch: 1 branch
BranchTrueFalseTotal
/\A$IDENT(?:__(?!_)$IDENT)?\z/ms ? : 0 0 0
443 0 my @fields = keys %fields;
444 0 ▶ if (!@fields) {
Branch: 1 branch
BranchTrueFalseTotal
unless (@fields) 0 0 0
445 0 return _ret1($cb, $dbh->set_err($DBI::stderr, "table undefined, require params\n", undef, 'Delete'), $dbh);
446 }
447 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
448 0 return _find_tables_for_delete($dbh, \@fields, [$dbh->Col('SHOW TABLES')], $P);
449 }
450 return $dbh->Col('SHOW TABLES', sub {
451 0 ▶ my (@tables) = @_;
Subroutine __ANON__: not called CC=1 SCAR=6.9
452 0 return _find_tables_for_delete($dbh, \@fields, \@tables, $P, $cb);
453 0 });
454 }
455  
456 0 ▶ my @tables = ref $table ? @{$table} : $table;
Branch: 1 branch
BranchTrueFalseTotal
ref $table ? : 0 0 0
457 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
458 0 my $res;
459 0 for my $t (@tables) {
460 0 ▶ my $SQL = $dbh->GetSQL($t, $P) or return;
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($t, $P) or return 0 0 0
461 0 ▶ if ($SQL->{Where} eq '1' && !$P->{__force}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($SQL->{"Where"} eq 1 and not $P->{"__force"}) 0 0 0
Condition0%0/3
$SQL->{"Where"} eq 1 and not $P->{"__force"}
!ll&&!rl&&r
000
MC/DC0%0/2
$SQL->{"Where"} eq 1 and not $P->{"__force"}
$SQL->{"Where"} eq 1 not $P->{"__force"}
Truth table0%0/3
A and not(B)
A= $SQL->{"Where"} eq 1B= not $P->{"__force"}
ABresult
0X0
100
111
462 0 return $dbh->set_err($DBI::stderr, "empty WHERE require {__force=>1}\n", undef, 'Delete');
463 }
464 my $sql = sprintf 'DELETE FROM %s WHERE %s' . ($SQL->{Limit} ? ' LIMIT %s' : q{}),
465 0 ▶ $dbh->quote_identifier($SQL->{Table}), $SQL->{Where}, $SQL->{Limit} || ();
Branch: 1 branch
BranchTrueFalseTotal
$SQL->{'Limit'} ? : 0 0 0
Condition0%0/3
$SQL->{'Limit'} || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$SQL->{'Limit'} || ()
$SQL->{'Limit'} ()
Truth table0%0/3
A || B
A= $SQL->{'Limit'}B= ()
ABresult
1X1
011
000
466 0 ▶ $res = $dbh->do($sql) or return;
Branch: 1 branch
BranchTrueFalseTotal
$res = $dbh->do($sql) or return 0 0 0
467 }
468 0 return $res;
469 }
470 0 my $code; $code = sub {
471 0 ▶ my ($SQL) = @_;
Subroutine __ANON__: not called CC=6 SCAR=37.4
472 0 my $t = shift @tables;
473 0 ▶ if (!$SQL) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($SQL) 0 0 0
474 0 undef $code;
475 0 return $cb->(undef, $dbh);
476 }
477 0 ▶ if ($SQL->{Where} eq '1' && !$P->{__force}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($SQL->{"Where"} eq 1 and not $P->{"__force"}) 0 0 0
Condition0%0/3
$SQL->{"Where"} eq 1 and not $P->{"__force"}
!ll&&!rl&&r
000
MC/DC0%0/2
$SQL->{"Where"} eq 1 and not $P->{"__force"}
$SQL->{"Where"} eq 1 not $P->{"__force"}
Truth table0%0/3
A and not(B)
A= $SQL->{"Where"} eq 1B= not $P->{"__force"}
ABresult
0X0
100
111
478 0 undef $code;
479 0 return $cb->($dbh->set_err($DBI::stderr, "empty WHERE require {__force=>1}\n", undef, 'Delete'), $dbh);
480 }
481 my $sql = sprintf 'DELETE FROM %s WHERE %s' . ($SQL->{Limit} ? ' LIMIT %s' : q{}),
482 0 ▶ $dbh->quote_identifier($SQL->{Table}), $SQL->{Where}, $SQL->{Limit} || ();
Branch: 1 branch
BranchTrueFalseTotal
$SQL->{'Limit'} ? : 0 0 0
Condition0%0/3
$SQL->{'Limit'} || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$SQL->{'Limit'} || ()
$SQL->{'Limit'} ()
Truth table0%0/3
A || B
A= $SQL->{'Limit'}B= ()
ABresult
1X1
011
000
483 $dbh->do($sql, sub {
484 0 ▶ my ($res, $dbh) = @_; ## no critic (ProhibitReusedNames)
Subroutine __ANON__: not called CC=3 SCAR=24.8
485 0 ▶ if ($res && @tables) {
Branch: 1 branch
BranchTrueFalseTotal
if ($res and @tables) 0 0 0
Condition0%0/3
$res and @tables
!ll&&!rl&&r
000
MC/DC0%0/2
$res and @tables
$res @tables
Truth table0%0/3
A and B
A= $resB= @tables
ABresult
0X0
100
111
486 0 return $dbh->GetSQL($tables[0], $P, $code);
487 }
488 0 undef $code;
489 0 return $cb->($res, $dbh);
490 0 });
491 0 };
492 0 return $dbh->GetSQL($tables[0], $P, $code);
493 }
494  
495 sub DBI::db::ID {
496 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine ID: not called CC=7 SCAR=40.3
497 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
498  
499 my $sql = sprintf 'SELECT %s.%s FROM %s WHERE %s'
500 . ($SQL->{Order} ? ' ORDER BY %s' : q{})
501 . ($SQL->{SelectLimit} ? ' LIMIT %s' : q{}),
502 0 ▶ (map { $dbh->quote_identifier($_) } $SQL->{Table}, $SQL->{ID}),
Condition0%0/6
$SQL->{'Order'} || ()
l!l&&r!l&&!r
000
$SQL->{'SelectLimit'} || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$SQL->{'Order'} || ()
$SQL->{'Order'} ()
MC/DC0%0/2
$SQL->{'SelectLimit'} || ()
$SQL->{'SelectLimit'} ()
Truth table0%0/3
A || B
A= $SQL->{'Order'}B= ()
ABresult
1X1
011
000
Truth table0%0/3
A || B
A= $SQL->{'SelectLimit'}B= ()
ABresult
1X1
011
000
503 0 ▶ $SQL->{From}, $SQL->{Where}, $SQL->{Order} || (), $SQL->{SelectLimit} || ();
Branch: 2 branches
BranchTrueFalseTotal
$SQL->{'Order'} ? : 0 0 0
$SQL->{'SelectLimit'} ? : 0 0 0
504 0 ▶ return $dbh->Col($sql, $cb // ());
Condition0%0/3
$cb // ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$cb // ()
$cb ()
Truth table0%0/3
A // B
A= $cbB= ()
ABresult
1X1
011
000
505 }
506  
507 sub DBI::db::Count {
508 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Count: not called CC=3 SCAR=24.8
509 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
510  
511 my $sql = sprintf 'SELECT count(*) __count FROM %s WHERE %s',
512 0 $SQL->{From}, $SQL->{Where};
513 0 ▶ return $dbh->Col($sql, $cb // ());
Condition0%0/3
$cb // ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$cb // ()
$cb ()
Truth table0%0/3
A // B
A= $cbB= ()
ABresult
1X1
011
000
514 }
515  
516 sub DBI::db::Select {
517 0 ▶ my ($dbh, $table, $P, $cb) = @_;
Subroutine Select: not called CC=11 SCAR=48.8
518 0 ▶ my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh);
Branch: 1 branch
BranchTrueFalseTotal
my $SQL = $dbh->GetSQL($table, $P) or return _ret1($cb, undef, $dbh) 0 0 0
519  
520 my $sql = sprintf 'SELECT %s'
521 . ($SQL->{Group} ? ', count(*) __count' : q{})
522 . ' FROM %s WHERE %s'
523 . ($SQL->{Group} ? ' GROUP BY %s' : q{})
524 . ($SQL->{Order} ? ' ORDER BY %s' : q{})
525 . ($SQL->{SelectLimit} ? ' LIMIT %s' : q{}),
526 $SQL->{Select}, $SQL->{From}, $SQL->{Where},
527 0 ▶ $SQL->{Group} || (), $SQL->{Order} || (), $SQL->{SelectLimit} || ();
Branch: 4 branches
BranchTrueFalseTotal
$SQL->{'Group'} ? : 0 0 0
$SQL->{'Group'} ? : 0 0 0
$SQL->{'Order'} ? : 0 0 0
$SQL->{'SelectLimit'} ? : 0 0 0
Condition0%0/9
$SQL->{'Group'} || ()
l!l&&r!l&&!r
000
$SQL->{'Order'} || ()
l!l&&r!l&&!r
000
$SQL->{'SelectLimit'} || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$SQL->{'Group'} || ()
$SQL->{'Group'} ()
MC/DC0%0/2
$SQL->{'Order'} || ()
$SQL->{'Order'} ()
MC/DC0%0/2
$SQL->{'SelectLimit'} || ()
$SQL->{'SelectLimit'} ()
Truth table0%0/3
A || B
A= $SQL->{'Group'}B= ()
ABresult
1X1
011
000
Truth table0%0/3
A || B
A= $SQL->{'Order'}B= ()
ABresult
1X1
011
000
Truth table0%0/3
A || B
A= $SQL->{'SelectLimit'}B= ()
ABresult
1X1
011
000
528 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
529 0 ▶ return wantarray ? $dbh->All($sql) : $dbh->Row($sql);
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 0 0 0
530 }
531 0 return $dbh->All($sql, $cb);
532 }
533  
534 sub _is_cb {
535 0 ▶ my $cb = shift;
Subroutine _is_cb: not called CC=2 SCAR=17.9
536 0 my $ref = ref $cb;
537 0 ▶ return $ref eq 'CODE' || $ref eq 'AnyEvent::CondVar';
Condition0%0/3
$ref eq 'CODE' || $ref eq 'AnyEvent::CondVar'
l!l&&r!l&&!r
000
MC/DC0%0/2
$ref eq 'CODE' || $ref eq 'AnyEvent::CondVar'
$ref eq 'CODE' $ref eq 'AnyEvent::CondVar'
Truth table0%0/3
A || B
A= $ref eq 'CODE'B= $ref eq 'AnyEvent::CondVar'
ABresult
1X1
011
000
538 }
539  
540 sub DBI::db::All {
541 0 ▶ my ($dbh, $sql, @bind) = @_;
Subroutine All: not called CC=5 SCAR=34.0
542 0 ▶ my $cb = @bind && _is_cb($bind[-1]) ? pop @bind : undef;
Branch: 1 branch
BranchTrueFalseTotal
@bind && _is_cb($bind[-1]) ? : 0 0 0
Condition0%0/3
@bind and _is_cb($bind[-1])
!ll&&!rl&&r
000
MC/DC0%0/2
@bind and _is_cb($bind[-1])
@bind _is_cb($bind[-1])
Truth table0%0/3
A and B
A= @bindB= _is_cb($bind[-1])
ABresult
0X0
100
111
543 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
544 0 ▶ (my $sth = $dbh->prepare($sql, {async=>0}))->execute(@bind) or return;
Branch: 1 branch
BranchTrueFalseTotal
(my $sth = $dbh->prepare($sql, {"async", 0}))->execute(@bind) or return 0 0 0
545 0 return @{ $sth->fetchall_arrayref({}) };
546 }
547 return $dbh->prepare($sql)->execute(@bind, sub {
548 0 ▶ my ($rv, $sth) = @_;
Subroutine __ANON__: not called CC=2 SCAR=17.9
549 0 ▶ return $cb->(!$rv ? () : @{ $sth->fetchall_arrayref({}) });
Branch: 1 branch
BranchTrueFalseTotal
!$rv ? : 0 0 0
550 0 });
551 }
552  
553 sub DBI::db::Row {
554 0 ▶ my ($dbh, $sql, @bind) = @_;
Subroutine Row: not called CC=1 SCAR=6.9
555 0 return $dbh->selectrow_hashref($sql, undef, @bind);
556 }
557  
558 sub DBI::db::Col {
559 0 ▶ my ($dbh, $sql, @bind) = @_;
Subroutine Col: not called CC=6 SCAR=37.4
560 0 ▶ my $cb = @bind && _is_cb($bind[-1]) ? pop @bind : undef;
Branch: 1 branch
BranchTrueFalseTotal
@bind && _is_cb($bind[-1]) ? : 0 0 0
Condition0%0/3
@bind and _is_cb($bind[-1])
!ll&&!rl&&r
000
MC/DC0%0/2
@bind and _is_cb($bind[-1])
@bind _is_cb($bind[-1])
Truth table0%0/3
A and B
A= @bindB= _is_cb($bind[-1])
ABresult
0X0
100
111
561 0 ▶ if (!$cb) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cb) 0 0 0
562 0 ▶ my @res = @{ $dbh->selectcol_arrayref($sql, undef, @bind) || [] };
Branch: 1 branch
BranchTrueFalseTotal
$dbh->selectcol_arrayref($sql, undef, @bind) or [] 0 0 0
563 0 ▶ return wantarray ? @res : $res[0];
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 0 0 0
564 }
565 return $dbh->selectcol_arrayref($sql, undef, @bind, sub {
566 0 ▶ my ($ary_ref) = @_;
Subroutine __ANON__: not called CC=2 SCAR=17.9
567 0 ▶ return $cb->($ary_ref ? @{ $ary_ref } : ());
Branch: 1 branch
BranchTrueFalseTotal
$ary_ref ? : 0 0 0
568 0 });
569 }
570  
571  
572 21 1; # Magic true value required at end of module
573 __END__
574  
575 =encoding utf8
576  
577 =head1 NAME
578  
579 DBIx::SecureCGI - Secure conversion of CGI params hash to SQL
580  
581  
582 =head1 SYNOPSIS
583  
584 #--- sync
585  
586 use DBIx::SecureCGI;
587  
588 $row = $dbh->Select('Table', \%Q);
589 @rows = $dbh->Select(['Table1','Table2'], {%Q, id_user=>$id});
590 $count = $dbh->Count('Table', {age__gt=>25});
591 $id = $dbh->ID('Table', {login=>$login, pass=>$pass});
592 @id = $dbh->ID('Table', {age__gt=>25});
593 $newid = $dbh->Insert('Table', \%Q);
594 $rv = $dbh->InsertIgnore('Table', \%Q);
595 $rv = $dbh->Update('Table', \%Q);
596 $rv = $dbh->Replace('Table', \%Q);
597 $rv = $dbh->Delete('Table', \%Q);
598 $rv = $dbh->Delete(undef, {id_user=>$id});
599  
600 @rows = $dbh->All('SELECT * FROM Table WHERE id_user=?', $id);
601 $row = $dbh->Row('SELECT * FROM Table WHERE id_user=?', $id);
602 @col = $dbh->Col('SELECT id_user FROM Table');
603  
604 $SQL = $dbh->GetSQL(['Table1','Table2'], \%Q);
605 $cache = $dbh->TableInfo(['Table1','Table2']);
606 $desc = $dbh->ColumnInfo('Table');
607  
608  
609 #--- async
610  
611 use AnyEvent::DBI::MySQL;
612 use DBIx::SecureCGI;
613  
614 $dbh->Select(…, sub { my (@rows) = @_; … });
615 $dbh->Count(…, sub { my ($count) = @_; … });
616 $dbh->ID(…, sub { my (@id) = @_; … });
617 $dbh->Insert(…, sub { my ($newid, $dbh) = @_; … });
618 $dbh->InsertIgnore(…, sub { my ($rv, $dbh) = @_; … });
619 $dbh->Update(…, sub { my ($rv, $dbh) = @_; … });
620 $dbh->Replace(…, sub { my ($rv, $dbh) = @_; … });
621 $dbh->Delete(…, sub { my ($rv, $dbh) = @_; … });
622  
623 $dbh->All(…, sub { my (@rows) = @_; … });
624 $dbh->Row(…, sub { my ($row) = @_; … });
625 $dbh->Col(…, sub { my (@col) = @_; … });
626  
627 $dbh->GetSQL(…, sub { my ($SQL) = @_; … });
628 $dbh->TableInfo(…, sub { my ($cache) = @_; … });
629 $dbh->ColumnInfo(…, sub { my ($desc) = @_; … });
630  
631  
632 #--- setup
633  
634 DBIx::SecureCGI::DefineFunc( $name, '%s op %s' )
635 DBIx::SecureCGI::DefineFunc( $name, [ qr/regexp/, '%s op %s' ] )
636 DBIx::SecureCGI::DefineFunc( $name, sub { … } )
637  
638 $cache = $dbh->SecureCGICache();
639 $dbh->SecureCGICache($new_cache);
640  
641  
642 =head1 DESCRIPTION
643  
644 This module let you use B<hash with CGI params> to make (or just generate)
645 SQL queries to MySQL database in B<easy and secure> way. To make this
646 magic possible there are some limitations and requirements:
647  
648 =over
649  
650 =item * Your app and db scheme must conform to these L</"CONVENTIONS">
651  
652 =item * Small speed penalty/extra queries to load scheme from db
653  
654 =item * No support for advanced SQL, only basic queries
655  
656 =back
657  
658 Example: if all CGI params (including unrelated to db table 'Table') are
659 in C<%Q>, then:
660  
661 @rows = $dbh->Select('Table', \%Q);
662  
663 will execute any simple C<SELECT> query from the table C<Table> (defined
664 by user-supplied parameters in C<%Q>); and this:
665  
666 @user_rows = $dbh->Select('Table', {%Q, id_user=>$id});
667  
668 will make any similar query limited to records with C<id_user> column
669 value C<$id> (thus allowing user to fetch any or B<his own> records).
670  
671 The module is intended for use only with a fairly simple tables and simple
672 SQL queries. More advanced queries usually can be generated manually with
673 help of L</GetSQL> or you can just use plain L<DBI> methods.
674  
675 Also it support B<non-blocking SQL queries> using L<AnyEvent::DBI::MySQL>
676 and thus can be effectively used with event-based CGI frameworks like
677 L<Mojolicious> or with event-based FastCGI servers like L<FCGI::EV>.
678  
679 Finally, it can be used in non-CGI environment, as simplified interface to
680 L<DBI>.
681  
682 =head2 SECURITY OVERVIEW
683  
684 At a glance, generating SQL queries based on untrusted parameters sent by
685 user to your CGI looks very unsafe. But interface of this module designed
686 to make it safe - while you conform to some L</CONVENTIONS> and follow
687 some simple guidelines.
688  
689 =over
690  
691 =item * B<User have no control over query type (SELECT/INSERT/…)>
692  
693 It's defined by method name you call.
694  
695 =item * B<User have no control over tables involved in SQL query>
696  
697 It's defined by separate (first) parameter in all methods, unrelated to
698 hash with CGI parameters.
699  
700 =item * B<User have no direct control over SQL query>
701  
702 All values from hash are either quoted before inserting into SQL, or
703 checked using very strict regular expressions if it's impossible to quote
704 them (like for date/time C<INTERVAL> values).
705  
706 =item * B<You can block/control access to "secure" fields in all tables>
707  
708 Name all such fields in some special way (like beginning with "C<_>") and
709 when receiving CGI parameters immediately B<delete all keys> in hash which
710 match these fields (i.e. all keys beginning with "C<_>"). Later you can
711 analyse user's request and manually add to hash keys for these fields
712 before call method to execute SQL query.
713  
714 =item * B<You can limit user's access to some subset of records>
715  
716 Just instead of using plain C<\%Q> as parameter for methods use
717 something like C<< { %Q, id_user => $id } >> - this way user will be
718 limited to records with C<$id> value in C<id_user> column.
719  
720 =back
721  
722 Within these security limitations user can do anything - select records
723 with custom C<WHERE>, C<GROUP BY>, C<ORDER BY>, C<LIMIT>; set any values
724 (allowed by table scheme, of course) for any fields on C<INSERT> or
725 C<UPDATE>; etc. without any single line of your code - exclusively by
726 using different CGI parameters.
727  
728  
729 =head1 HOW IT WORKS
730  
731 Each CGI parameter belongs to one of three categories:
732  
733 =over
734  
735 =item * B<related to some table's field in db:> C<fieldname>,
736 C<fieldname__funcname>
737  
738 =item * B<control command:> C<__commandname>
739  
740 =item * B<your app's parameter>
741  
742 =back
743  
744 It's recommended to name fields in db beginning with B<lowercase> letter
745 or B<underscore>, and name your app's parameters beginning with
746 B<Uppercase> letter to avoid occasional clash with field name.
747  
748 To protect some fields (like "C<balance>" or "C<privileges>") from
749 uncontrolled access you can use simple convention: name these fields in db
750 beginning with "C<_>"; when receiving CGI params just
751 B<delete all with names beginning with> "C<_>" - thus it won't be possible
752 to access these fields from CGI params. This module doesn't know about
753 these protected fields and handle them just as usual fields. So, you
754 should later add needed keys for these fields into hash before calling
755 methods to execute SQL query. This way all operations on these fields will
756 be controlled by your app.
757  
758 You can use any other similar naming scheme which won't conflict with
759 L</CONVENTIONS> below - DBIx::SecureCGI will analyse db scheme (and
760 cache it for speed) to detect which keys match field names.
761  
762 CGI params may have several values. In hash, keys for such params must
763 have C<ARRAYREF> value. DBIx::SecureCGI support this only for keys which
764 contain "C<__>" (double underscore). Depending on used CGI framework you
765 may need to convert existing CGI parameters into this format.
766  
767 Error handling: all unknown keys will be silently ignored, all other
768 errors (unable to detect key for joining table, field without
769 "C<__funcname>" have C<ARRAYREF> value, unknown "C<__funcname>" function, etc.)
770 will return usual DBI errors (or throw exceptions when C<< {RaiseError=>1} >>.
771  
772 =head2 CONVENTIONS
773  
774 =over
775  
776 =item *
777  
778 Each table's B<first field> must be a C<PRIMARY KEY>.
779  
780 =over
781  
782 MOTIVATION: This module use simplified analyse of db scheme and suppose
783 first field in every table is a C<PRIMARY KEY>. To add support for complex
784 primary keys or tables without primary keys we should first define how
785 L</ID> should handle them and how to automatically join such tables.
786  
787 =back
788  
789 =item *
790  
791 Two tables are always C<JOIN>ed using field which must be C<PRIMARY KEY>
792 at least in one of them and have B<same name in both tables>.
793  
794 =over
795  
796 So, don't name your primary key "C<id>" if you plan to join this table with
797 another - name it like "C<id_thistable>" or "C<thistableId>".
798  
799 =back
800  
801 If both tables have field corresponding to C<PRIMARY KEY> in other table,
802 then key field of B<right table> (in order defined when you make array of
803 tables in first param of method) will be used.
804  
805 If more than two tables C<JOIN>ed, then each table starting from second
806 one will try to join to each of the previous tables (starting at first
807 table) until it find table with suitable field. If it wasn't found
808 DBI error will be returned.
809  
810 =over
811  
812 MOTIVATION: Let this module automatically join tables.
813  
814 =back
815  
816 =item *
817  
818 Field names must not contain "C<__>" (two adjoined underscore).
819  
820 =over
821  
822 MOTIVATION: Distinguish special commands for this module from field names.
823 Also, some methods sometimes create aliases for fields and their names
824 begins with "C<__>".
825  
826 =back
827  
828 =item *
829  
830 Hash with CGI params may contain several values (as C<ARRAYREF>) only for key
831 names containing "C<__>" (keys unrelated to fields may have any values).
832  
833 =over
834  
835 MOTIVATION: Allowing C<< { field => \@values } >> introduce many
836 ambiguities and in fact same as C<< { field__eq => \@values } >>,
837 so it's safer to deny it.
838  
839 =back
840  
841 =back
842  
843 =head2 Hash to SQL convertion rules
844  
845 =head3 __commandname
846  
847 Keys beginning with "C<__>" are control commands. Supported commands are:
848  
849 =over
850  
851 =item B<__order>
852  
853 Define value for C<ORDER BY>. Valid values are:
854  
855 'field_name'
856 'field_name ASC'
857 'field_name DESC'
858  
859 Multiple values can be given as C<ARRAYREF>.
860  
861 =item B<__group>
862  
863 Define value for C<GROUP BY>. Valid values are same as for B<__order>.
864  
865 =item B<__limit>
866  
867 Can have up to two numeric values (when it's C<ARRAYREF>), set C<LIMIT>.
868  
869 =item B<__force>
870  
871 If the value of B<__force> key is true, then it's allowed to run
872 L</Update> and L</Delete> with an empty C<WHERE>. (This isn't a security
873 feature, it's just for convenience to protect against occasional damage on
874 database while playing with CGI parameters.)
875  
876 =back
877  
878 Examples:
879  
880 my @rows = $dbh->Select('Table', {
881 age__ge => 20,
882 age__lt => 30,
883 __group => 'age',
884 __order => ['age DESC', 'fname'],
885 __limit => 5,
886 });
887 $dbh->Delete('Table', { __force => 1 });
888  
889 =head3 fieldname__funcname
890  
891 If the key contains a "C<__>" then it is treated as applying function
892 "C<funcname>" to field "C<fieldname>".
893 If the there is no field with such name in database, this key is ignored.
894 A valid key value - string/number or a reference to an array of
895 strings/numbers.
896 A list of available functions in this version is shown below.
897  
898 Unless special behavior mentioned functions handle C<ARRAYREF> value by
899 applying itself to each value in array and joining with C<AND>.
900  
901 Example:
902  
903 { html__like => ['%<P>%', '%<BR>%'] }
904  
905 will be transformed in SQL to
906  
907 html LIKE '%<P>%' AND html LIKE '%<BR>%'
908  
909 Typically, such keys are used in C<WHERE>, except when "C<funcname>" begins
910 with "C<set_>" - such keys will be used in C<SET>.
911  
912 =head3 fieldname
913  
914 Other keys are treated as names of fields in database.
915 If there is no field with such name, then key is ignored.
916 A valid value for these keys - scalar.
917  
918 Example:
919  
920 { name => 'Alex' }
921
922 will be transformed in SQL to
923  
924 name = 'Alex'
925  
926 Typically, such keys are used in part C<SET>, except for C<PRIMARY KEY>
927 field in L</Update> - it will be used in C<WHERE>.
928  
929  
930 =head1 INTERFACE
931  
932 =head2 Functions
933  
934 =head3 DefineFunc
935  
936 DBIx::SecureCGI::DefineFunc( $name, '%s op %s' );
937 DBIx::SecureCGI::DefineFunc( $name, [ qr/regexp/, '%s op %s' ] );
938 DBIx::SecureCGI::DefineFunc( $name, sub { … } );
939  
940 Define new or replace existing function applied to fields after "C<__>"
941 delimiter.
942  
943 SQL expression for that function will be generated in different ways,
944 depending on how you defined that function - using string, regexp+string
945 or code:
946  
947 $expr = sprintf '%s op %s', $field, $dbh->quote($value);
948 $expr = $value =~ /regexp/ && sprintf '%s op %s', $field, $value;
949 $expr = $code->($dbh, $field, $value);
950  
951 If C<$expr> will be false DBI error will be returned.
952 Here is example of code implementation:
953  
954 sub {
955 my ($dbh, $f, $v) = @_;
956 if (… value ok …) {
957 return sprintf '…', $f, $dbh->quote($v);
958 }
959 return; # wrong value
960 }
961  
962  
963 =head2 Methods injected into DBI
964  
965 =head3 GetSQL
966  
967 $SQL = $dbh->GetSQL( $table, \%Q );
968 $dbh->GetSQL( $table, \%Q, sub { my ($SQL) = @_; … } );
969 $SQL = $dbh->GetSQL( \@tables, \%Q );
970 $dbh->GetSQL( \@tables, \%Q, sub { my ($SQL) = @_; … } );
971  
972 This is helper function which will analyse (cached) database scheme for
973 given tables and generate elements of SQL query for given keys in C<%Q>.
974 You may use it to write own methods like L</Select> or L</Insert>.
975  
976 In C<%Q> keys which doesn't match field names in C<$table> / C<@tables>
977 are ignored.
978  
979 Names of tables and fields in all keys (except C<{Table}> and C<{ID}>)
980 are already quoted, field names qualified with table name (so they're
981 ready for inserting into SQL query). Values of C<{Table}> and C<{ID}>
982 should be escaped with C<< $dbh->quote_identifier() >> before using in SQL
983 query.
984  
985 Returns C<HASHREF> with keys:
986  
987 {Table} first of the used tables
988 {ID} name of PRIMARY KEY field in {Table}
989 {Select} list of all field names which should be returned by
990 'SELECT *' excluding duplicated fields (when field with
991 same name exist in many tables only field from first table
992 will be returned); field names in {Select} are joined with ","
993 {From} all tables joined using chosen JOIN type (INNER by default)
994 {Set} string like "field=value, field2=value2" for all simple
995 "fieldname" keys in %Q
996 {Where} a-la {Set}, except fields joined using "AND" and added
997 "field__function" fields; if there are no fields it will
998 be set to string "1"
999 {UpdateWhere} a-la {Where}, except it uses only "field__function" keys
1000 plus one PRIMARY KEY "fieldname" key (if it exists in %Q)
1001 {Order} string like "field1 ASC, field2 DESC" or empty string
1002 {Group} a-la {Order}
1003 {Limit} set to value of __limit if it contain one number
1004 {SelectLimit} set to value of __limit if it contain one number,
1005 or to values of __limit joined with "," if it contain
1006 two numbers
1007  
1008 Example :
1009  
1010 CREATE TABLE A (
1011 id_a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1012 i INT NOT NULL
1013 );
1014 CREATE TABLE B (
1015 id_b INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1016 id_a INT NOT NULL,
1017 s VARCHAR(255) NOT NULL
1018 );
1019  
1020 $SQL = $dbh->GetSQL(['A', 'B LEFT'], {
1021 id_a => 3,
1022 i => 10,
1023 s => 'str',
1024 id_b__gt => 5,
1025 __group => 'i',
1026 __order => ['s DESC', 'i'],
1027 __limit => [50,10],
1028 });
1029  
1030 # now %$SQL have these values:
1031 # (backticks added by $dbh->quote_identifier() around all table/field
1032 # names omitted for readability)
1033 Table => 'A'
1034 ID => 'id_a'
1035 Select => 'A.id_a, A.i, B.id_b, B.s'
1036 From => 'A LEFT JOIN B ON (B.id_a = A.id_a)'
1037 Set => 'B.s = "str", A.id_a = 3, A.i = 10'
1038 Where => 'B.s = "str" AND A.id_a = 3 AND A.i = 10 AND B.id_b > 5'
1039 UpdateWhere => ' A.id_a = 3 AND B.id_b > 5'
1040 Group => 'A.i'
1041 Order => 'B.s DESC, A.i'
1042 Limit => ''
1043 SelectLimit => '50,10'
1044  
1045  
1046 =head3 Insert
1047  
1048 $newid = $dbh->Insert( $table, \%Q );
1049 $dbh->Insert( $table, \%Q, sub { my ($newid, $dbh) = @_; … } );
1050  
1051 Execute SQL query:
1052  
1053 INSERT INTO {Table} SET {Set}
1054  
1055 Return C<< $dbh->{mysql_insertid} >> on success or C<undef> on error.
1056  
1057 It's B<strongly recommended> to always use
1058  
1059 $dbh->Insert( …, { %Q, …, primary_key_name=>undef }, … )
1060  
1061 because if you didn't force C<primary_key> field to be C<NULL> in SQL (and
1062 thus use C<AUTO_INCREMENT> value) then user may send CGI parameter to set
1063 it to C<-1> or C<4294967295> and this will result in B<DoS> because no
1064 more records can be added using C<AUTO_INCREMENT> into this table.
1065  
1066  
1067 =head3 InsertIgnore
1068  
1069 $rv = $dbh->InsertIgnore( $table, \%Q );
1070 $dbh->InsertIgnore( $table, \%Q, sub { my ($rv, $dbh) = @_; … } );
1071  
1072 Execute SQL query:
1073  
1074 INSERT IGNORE INTO {Table} SET {Set}
1075  
1076 Return C<$rv> (true on success or C<undef> on error).
1077  
1078  
1079 =head3 Update
1080  
1081 $rv = $dbh->Update( $table, \%Q );
1082 $dbh->Update( $table, \%Q, sub { my ($rv, $dbh) = @_; … } );
1083  
1084 Execute SQL query:
1085  
1086 UPDATE {Table} SET {Set} WHERE {UpdateWhere} [LIMIT {Limit}]
1087  
1088 Uses in C<SET> part all fields given as "C<fieldname>", in C<WHERE> part all
1089 fields given as "C<fieldname__funcname>" plus C<PRIMARY KEY> field if it was
1090 given as "C<fieldname>".
1091  
1092 Return C<$rv> (amount of modified records on success or C<undef> on error).
1093  
1094 To use with empty C<WHERE> part require C<< {__force=>1} >> in C<%Q>.
1095  
1096  
1097 =head3 Replace
1098  
1099 $rv = $dbh->Replace( $table, \%Q );
1100 $dbh->Replace( $table, \%Q, sub { my ($rv, $dbh) = @_; … } );
1101  
1102 Execute SQL query:
1103  
1104 REPLACE INTO {Table} SET {Set}
1105  
1106 Uses in C<SET> part all fields given as "C<fieldname>".
1107  
1108 Return C<$rv> (true on success or C<undef> on error).
1109  
1110  
1111 =head3 Delete
1112  
1113 $rv = $dbh->Delete( $table, \%Q );
1114 $dbh->Delete( $table, \%Q, sub { my ($rv, $dbh) = @_; … } );
1115 $rv = $dbh->Delete( \@tables, \%Q );
1116 $dbh->Delete( \@tables, \%Q, sub { my ($rv, $dbh) = @_; … } );
1117 $rv = $dbh->Delete( undef, \%Q );
1118 $dbh->Delete( undef, \%Q, sub { my ($rv, $dbh) = @_; … } );
1119  
1120 Execute SQL query:
1121  
1122 DELETE FROM {Table} WHERE {Where} [LIMIT {Limit}]
1123  
1124 Delete records from C<$table> or (one-by-one) from each table in
1125 C<@tables>. If C<undef> given, then delete records from B<ALL> tables
1126 (except C<TEMPORARY>) which have B<ALL> fields mentioned in C<%Q>.
1127  
1128 To use with empty C<WHERE> part require C<< {__force=>1} >> in C<%Q>.
1129  
1130 Return C<$rv> (amount of deleted records or C<undef> on error).
1131 If used to delete records from more than one table - return C<$rv>
1132 for last table. If error happens it will be immediately returned,
1133 so some tables may not be processed in this case.
1134  
1135  
1136 =head3 ID
1137  
1138 $id = $dbh->ID( $table, \%Q );
1139 @id = $dbh->ID( $table, \%Q );
1140 $dbh->ID( $table, \%Q, sub { my (@id) = @_; … } );
1141 $id = $dbh->ID( \@tables, \%Q );
1142 @id = $dbh->ID( \@tables, \%Q );
1143 $dbh->ID( \@tables, \%Q, sub { my (@id) = @_; … } );
1144  
1145 Return result of executing this SQL query using L</Col>:
1146  
1147 SELECT {ID} FROM {From} WHERE {Where}
1148 [ORDER BY {Order}] [LIMIT {SelectLimit}]
1149  
1150  
1151 =head3 Count
1152  
1153 $count = $dbh->Count( $table, \%Q );
1154 $dbh->Count( $table, \%Q, sub { my ($count) = @_; … } );
1155 $count = $dbh->Count( \@tables, \%Q );
1156 $dbh->Count( \@tables, \%Q, sub { my ($count) = @_; … } );
1157  
1158 Return result of executing this SQL query using L</Col>:
1159  
1160 SELECT count(*) __count FROM {From} WHERE {Where}
1161  
1162  
1163 =head3 Select
1164  
1165 $row = $dbh->Select( $table, \%Q );
1166 @rows = $dbh->Select( $table, \%Q );
1167 $dbh->Select( $table, \%Q, sub { my (@rows) = @_; … } );
1168 $row = $dbh->Select( \@tables, \%Q );
1169 @rows = $dbh->Select( \@tables, \%Q );
1170 $dbh->Select( \@tables, \%Q, sub { my (@rows) = @_; … } );
1171  
1172 Execute one of these SQL queries (depending on using C<__group> command):
1173  
1174 SELECT * FROM {From} WHERE {Where}
1175 [ORDER BY {Order}] [LIMIT {SelectLimit}]
1176 SELECT *, count(*) __count FROM {From} WHERE {Where} GROUP BY {Group}
1177 [ORDER BY {Order}] [LIMIT {SelectLimit}]
1178  
1179 Instead of C<SELECT *> it uses enumeration of all fields qualified using
1180 table name; if same field found in several tables it's included only
1181 one - from first table having that field.
1182  
1183 In C<@tables> you can append C<' LEFT'> or C<' INNER'> to table name to
1184 choose C<JOIN> variant (by default C<INNER JOIN> will be used):
1185  
1186 $dbh->Select(['TableA', 'TableB LEFT', 'TableC'], …)
1187  
1188 Return result of executing SQL query using L</All> when called in list
1189 context or L</Row> when called in scalar context.
1190  
1191  
1192 =head3 All
1193  
1194 @rows = $dbh->All( $sql, @bind )
1195 $dbh->All( $sql, @bind, sub { my (@rows) = @_; … } );
1196  
1197 Shortcut for this ugly but very useful snippet:
1198  
1199 @{ $dbh->selectall_arrayref($sql, {Slice=>{}}, @bind) }
1200  
1201  
1202 =head3 Row
1203  
1204 $row = $dbh->Row( $sql, @bind );
1205 $dbh->Row( $sql, @bind, sub { my ($row) = @_; … } );
1206  
1207 Shortcut for:
1208  
1209 $dbh->selectrow_hashref($sql, undef, @bind)
1210  
1211 If you wonder why it exists, the answer is simple: it was added circa
1212 2002, when there was no C<< $dbh->selectrow_hashref() >> and now it
1213 continue to exists for compatibility and to complement L</All>
1214 and L</Col>.
1215  
1216  
1217 =head3 Col
1218  
1219 $col = $dbh->Col( $sql, @bind );
1220 @col = $dbh->Col( $sql, @bind );
1221 $dbh->Col( $sql, @bind, sub { my (@col) = @_; … } );
1222  
1223 Shortcut for:
1224  
1225 $col = $dbh->selectcol_arrayref($sql, undef, @bind)->[0];
1226 @col = @{ $dbh->selectcol_arrayref($sql, undef, @bind) };
1227  
1228  
1229 =head3 SecureCGICache
1230  
1231 $cache = $dbh->SecureCGICache();
1232 $cache = $dbh->SecureCGICache( $new_cache );
1233  
1234 Fetch (or set when C<$new_cache> given) C<HASHREF> with cached results of
1235 "C<DESC tablename>" SQL queries for all tables used previous in any methods.
1236  
1237 You may need to reset cache (by using C<{}> as C<$new_cache> value) if
1238 you've changed scheme for tables already accessed by any method or if you
1239 changed current database.
1240  
1241 Also in some environments when many different C<$dbh> used simultaneously,
1242 connected to same database (like in event-based environments) it may make
1243 sense to share same cache for all C<$dbh>.
1244  
1245  
1246 =head3 TableInfo
1247  
1248 $cache = $dbh->TableInfo( $table );
1249 $dbh->TableInfo( $table, sub { my ($cache) = @_; … } );
1250 $cache = $dbh->TableInfo( \@tables );
1251 $dbh->TableInfo( \@tables, sub { my ($cache) = @_; … } );
1252  
1253 Ensure "C<DESC tablename>" for all C<$table> / C<@tables> is cached.
1254  
1255 Return same as L</SecureCGICache> on success or C<undef> on error.
1256  
1257  
1258 =head3 ColumnInfo
1259  
1260 $desc = $dbh->ColumnInfo( $table );
1261 $dbh->ColumnInfo( $table, sub { my ($desc) = @_; … } );
1262  
1263 Ensure "C<DESC $table>" is cached.
1264  
1265 Return result of C<< $dbh->All("DESC $table") >> on success or C<undef> on
1266 error.
1267  
1268  
1269 =head2 __funcname functions for fields
1270  
1271 These functions can be added and replaced using L</DefineFunc>.
1272  
1273 Functions which can be used in C<%Q> as "C<fieldname_funcname>":
1274  
1275 =head3 eq, ne, lt, gt, le, ge
1276  
1277 field = value field IS NULL
1278 field != value field IS NOT NULL
1279 field < value
1280 field > value
1281 field <= value
1282 field >= value
1283  
1284 For functions B<eq> or B<ne>:
1285  
1286 eq [] - NOT 1
1287 ne [] - NOT 0
1288 eq only undef - name IS NULL
1289 ne only undef - name IS NOT NULL
1290 eq without undef - name IN (...)
1291 ne without undef - (name IS NULL OR name NOT IN (...))
1292 eq with undef - (name IS NULL OR name IN (...))
1293 ne with undef - name NOT IN (...)
1294  
1295 where
1296  
1297 "[]" : name__func=>[]
1298 "only undef": name__func=>undef or name__func=>[undef]
1299 "without undef": name__func=>$defined or name__func=>[@defined]
1300 "with undef": name__func=>[@defined_and_not_defined]
1301  
1302 =head3 like, not_like
1303  
1304 field LIKE value
1305 field NOT LIKE value
1306  
1307 =head3 date_eq, date_ne, date_lt, date_gt, date_le, date_ge
1308  
1309 field = DATE_ADD(NOW(), INTERVAL value)
1310 field != DATE_ADD(NOW(), INTERVAL value)
1311 field < DATE_ADD(NOW(), INTERVAL value)
1312 field > DATE_ADD(NOW(), INTERVAL value)
1313 field <= DATE_ADD(NOW(), INTERVAL value)
1314 field >= DATE_ADD(NOW(), INTERVAL value)
1315  
1316 value must match:
1317  
1318 /^-?\d+ (?:SECOND|MINUTE|HOUR|DAY|MONTH|YEAR)$/
1319  
1320 =head3 set_add
1321  
1322 field = field + value
1323  
1324 When used in L</Update> it will be in C<SET> instead of C<WHERE>.
1325 It doesn't make sense to use this function with L</Insert>,
1326 L</InsertIgnore> or L</Replace>.
1327  
1328 =head3 set_date
1329  
1330 field = NOW()
1331 field = DATE_ADD(NOW(), INTERVAL value)
1332  
1333 If it's value is (case-insensitive) string C<'NOW'> then it'll use
1334 C<NOW()> else it will use C<DATE_ADD(…)>.
1335  
1336 When used in L</Insert>, L</InsertIgnore>, L</Update> and L</Replace> it
1337 will be in C<SET>.
1338  
1339  
1340 =head1 BUGS AND LIMITATIONS
1341  
1342 No bugs have been reported.
1343  
1344 Only MySQL supported.
1345  
1346 It's impossible to change C<PRIMARY KEY> using L</Update> with:
1347  
1348 { id => $new_id, id__eq => $old_id }
1349  
1350 because both "C<id>" and "C<id__eq>" will be in C<WHERE> part:
1351  
1352 SET id = $new_id WHERE id = $new_id AND id = $old_id
1353  
1354 and if we won't add C<< 'id => $new_id' >> in C<WHERE> part if we have
1355 C< 'id__eq' >, then we'll have do use this
1356  
1357 $dbh->Func($table, {%Q, id_user=>$S{id_user}, id_user__eq=>$S{id_user})
1358  
1359 in B<all> CGI requests to protect against attempt to read someone else's
1360 records or change own records's id_user field by using C<'id_user'>
1361 or C<'id_user__eq'> CGI params.
1362  
1363  
1364 =head1 SUPPORT
1365  
1366 Please report any bugs or feature requests through the web interface at
1367 L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-SecureCGI>.
1368 I will be notified, and then you'll automatically be notified of progress
1369 on your bug as I make changes.
1370  
1371 You can also look for information at:
1372  
1373 =over
1374  
1375 =item * RT: CPAN's request tracker
1376  
1377 L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-SecureCGI>
1378  
1379 =item * AnnoCPAN: Annotated CPAN documentation
1380  
1381 L<http://annocpan.org/dist/DBIx-SecureCGI>
1382  
1383 =item * CPAN Ratings
1384  
1385 L<http://cpanratings.perl.org/d/DBIx-SecureCGI>
1386  
1387 =item * Search CPAN
1388  
1389 L<http://search.cpan.org/dist/DBIx-SecureCGI/>
1390  
1391 =back
1392  
1393  
1394 =head1 AUTHORS
1395  
1396 Alex Efros C<< <powerman@cpan.org> >>
1397  
1398 Nikita Savin C<< <nikita@asdfGroup.com> >>
1399  
1400  
1401 =head1 LICENSE AND COPYRIGHT
1402  
1403 Copyright 2002-2014 Alex Efros <powerman@cpan.org>.
1404  
1405 This program is distributed under the MIT (X11) License:
1406 L<http://www.opensource.org/licenses/mit-license.php>
1407  
1408 Permission is hereby granted, free of charge, to any person
1409 obtaining a copy of this software and associated documentation
1410 files (the "Software"), to deal in the Software without
1411 restriction, including without limitation the rights to use,
1412 copy, modify, merge, publish, distribute, sublicense, and/or sell
1413 copies of the Software, and to permit persons to whom the
1414 Software is furnished to do so, subject to the following
1415 conditions:
1416  
1417 The above copyright notice and this permission notice shall be
1418 included in all copies or substantial portions of the Software.
1419  
1420 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
1421 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
1422 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
1423 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
1424 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
1425 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
1426 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
1427 OTHER DEALINGS IN THE SOFTWARE.
1428  
↑ Summary Build »