# the test / diffusion / acceptance phase; those are marked with flag
# 'LDNOTE' (note by laurent.dami AT free.fr)
-use Carp;
use strict;
use warnings;
+use Carp ();
use List::Util ();
use Scalar::Util ();
# GLOBALS
#======================================================================
-our $VERSION = '1.68';
+our $VERSION = '1.72';
# This would confuse some packagers
$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
sub belch (@) {
my($func) = (caller(1))[3];
- carp "[$func] Warning: ", @_;
+ Carp::carp "[$func] Warning: ", @_;
}
sub puke (@) {
my($func) = (caller(1))[3];
- croak "[$func] Fatal: ", @_;
+ Carp::croak "[$func] Fatal: ", @_;
}
# special operators
$opt{special_ops} ||= [];
+ # regexes are applied in order, thus push after user-defines
push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
# unary operators
$opt{unary_ops} ||= [];
push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
+ # rudimentary saniy-check for user supplied bits treated as functions/operators
+ # If a purported function matches this regular expression, an exception is thrown.
+ # Literal SQL is *NOT* subject to this check, only functions (and column names
+ # when quoting is not in effect)
+
+ # FIXME
+ # need to guard against ()'s in column names too, but this will break tons of
+ # hacks... ideas anyone?
+ $opt{injection_guard} ||= qr/
+ \;
+ |
+ ^ \s* go \s
+ /xmi;
+
return bless \%opt, $class;
}
+sub _assert_pass_injection_guard {
+ if ($_[1] =~ $_[0]->{injection_guard}) {
+ my $class = ref $_[0];
+ puke "Possible SQL injection attempt '$_[1]'. If this is indeed a part of the "
+ . "desired SQL use literal SQL ( \'...' or \[ '...' ] ) or supply your own "
+ . "{injection_guard} attribute to ${class}->new()"
+ }
+}
+
#======================================================================
# INSERT methods
my ($sql, @bind) = $self->$method($data);
$sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
- if (my $ret = $options->{returning}) {
- $sql .= $self->_insert_returning ($ret);
+ if ($options->{returning}) {
+ my ($s, @b) = $self->_insert_returning ($options);
+ $sql .= $s;
+ push @bind, @b;
}
return wantarray ? ($sql, @bind) : $sql;
}
sub _insert_returning {
- my ($self, $fields) = @_;
+ my ($self, $options) = @_;
- my $f = $self->_SWITCH_refkind($fields, {
- ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$fields;},
- SCALAR => sub {$self->_quote($fields)},
- SCALARREF => sub {$$fields},
+ my $f = $options->{returning};
+
+ my $fieldlist = $self->_SWITCH_refkind($f, {
+ ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$f;},
+ SCALAR => sub {$self->_quote($f)},
+ SCALARREF => sub {$$f},
});
- return join (' ', $self->_sqlcase(' returning'), $f);
+ return $self->_sqlcase(' returning ') . $fieldlist;
}
sub _insert_HASHREF { # explicit list of fields and then values
}
}
- $self->debug("Generic unary OP: $op - recursing as function");
+ $self->_debug("Generic unary OP: $op - recursing as function");
+
+ $self->_assert_pass_injection_guard($op);
my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
SCALAR => sub {
},
SCALARREF => sub {
- puke "-$op => \\\$scalar not supported, use -nest => ...";
+ puke "-$op => \\\$scalar makes little sense, use " .
+ ($op =~ /^or/i
+ ? '[ \$scalar, \%rest_of_conditions ] instead'
+ : '-and => [ \$scalar, \%rest_of_conditions ] instead'
+ );
},
ARRAYREFREF => sub {
- puke "-$op => \\[..] not supported, use -nest => ...";
+ puke "-$op => \\[...] makes little sense, use " .
+ ($op =~ /^or/i
+ ? '[ \[...], \%rest_of_conditions ] instead'
+ : '-and => [ \[...], \%rest_of_conditions ] instead'
+ );
},
SCALAR => sub { # permissively interpreted as SQL
- puke "-$op => 'scalar' not supported, use -nest => \\'scalar'";
+ puke "-$op => \$value makes little sense, use -bool => \$value instead";
},
UNDEF => sub {
$op =~ s/^\s+|\s+$//g;# remove leading/trailing space
$op =~ s/\s+/ /g; # compress whitespace
+ $self->_assert_pass_injection_guard($op);
+
# so that -not_foo works correctly
$op =~ s/^not_/NOT /i;
foreach my $val (@$vals) {
my ($sql, @bind) = $self->_SWITCH_refkind($val, {
SCALAR => sub {
- return ($placeholder, $val);
+ return ($placeholder, $self->_bindtype($k, $val) );
},
SCALARREF => sub {
return $$val;
return (
(join $and, @all_sql),
- $self->_bindtype($k, @all_bind),
+ @all_bind
);
},
FALLBACK => sub {
if (@rest or $func !~ /^ \- (.+)/x);
local $self->{_nested_func_lhs} = $k;
$self->_where_unary_op ($1 => $arg);
- }
+ },
+ UNDEF => sub {
+ return $self->_sqlcase('null');
+ },
});
push @all_sql, $sql;
push @all_bind, @bind;
ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$from;},
SCALAR => sub {$self->_quote($from)},
SCALARREF => sub {$$from},
- ARRAYREFREF => sub {join ', ', @$from;},
});
}
return '' unless defined $_[1];
return ${$_[1]} if ref($_[1]) eq 'SCALAR';
- return $_[1] unless $_[0]->{quote_char};
+ unless ($_[0]->{quote_char}) {
+ $_[0]->_assert_pass_injection_guard($_[1]);
+ return $_[1];
+ }
my $qref = ref $_[0]->{quote_char};
my ($l, $r);
SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
+=item injection_guard
+
+A regular expression C<qr/.../> that is applied to any C<-function> and unquoted
+column name specified in a query structure. This is a safety mechanism to avoid
+injection attacks when mishandling user input e.g.:
+
+ my %condition_as_column_value_pairs = get_values_from_user();
+ $sqla->select( ... , \%condition_as_column_value_pairs );
+
+If the expression matches an exception is thrown. Note that literal SQL
+supplied via C<\'...'> or C<\['...']> is B<not> checked in any way.
+
+Defaults to checking for C<;> and the C<GO> keyword (TransactSQL)
+
=item array_datatypes
When this option is true, arrayrefs in INSERT or UPDATE are
$stmt = "WHERE user = ? AND status IS NULL";
@bind = ('nwiger');
+To test if a column IS NOT NULL:
+
+ my %where = (
+ user => 'nwiger',
+ status => { '!=', undef },
+ );
+
=head2 Specific comparison operators
If you want to specify a different type of operator for your comparison,
my %where => (
user => 'nwiger',
- priority => [ {'=', 2}, {'!=', 1} ]
+ priority => [ { '=', 2 }, { '>', 5 } ]
);
Which would generate:
- $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
- @bind = ('nwiger', '2', '1');
+ $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
+ @bind = ('2', '5', 'nwiger');
If you want to include literal SQL (with or without bind values), just use a
scalar reference or array reference as the value:
@bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
-There is also a special C<-nest>
-operator which adds an additional set of parens, to create a subquery.
-For example, to get something like this:
-
- $stmt = "WHERE user = ? AND ( workhrs > ? OR geo = ? )";
- @bind = ('nwiger', '20', 'ASIA');
-
-You would do:
-
- my %where = (
- user => 'nwiger',
- -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
- );
-
-
-Finally, clauses in hashrefs or arrayrefs can be
-prefixed with an C<-and> or C<-or> to change the logic
-inside :
+Clauses in hashrefs or arrayrefs can be prefixed with an C<-and> or C<-or>
+to change the logic inside :
my @where = (
-and => [
user => 'nwiger',
- -nest => [
- -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
- -and => [workhrs => {'<', 50}, geo => 'EURO' ]
+ [
+ -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
+ -or => { workhrs => {'<', 50}, geo => 'EURO' },
],
],
);
That would yield:
- WHERE ( user = ? AND
- ( ( workhrs > ? AND geo = ? )
- OR ( workhrs < ? AND geo = ? ) ) )
-
+ WHERE ( user = ? AND (
+ ( workhrs > ? AND geo = ? )
+ OR ( workhrs < ? OR geo = ? )
+ ) )
=head2 Algebraic inconsistency, for historical reasons
=head2 Literal SQL
-Finally, sometimes only literal SQL will do. If you want to include
-literal SQL verbatim, you can specify it as a scalar reference, namely:
+Finally, sometimes only literal SQL will do.
+To include literal SQL verbatim, you specify it as a scalar reference.
+Consider this only as a last resort. Usually there is a better way.
+
+Literal SQL is the only way to compare 2 columns to one another:
+
+ my %where = (
+ priority => { '<', 2 },
+ requestor => \'= submittor'
+ );
+
+which creates:
+
+ $stmt = "WHERE priority < ? AND requestor = submitter";
+ @bind = ('2');
+
- my $inn = 'is Not Null';
+There is a nicer way to test for NULL, but just for the sake of example:
+
+ my $inn = 'IS NOT NULL';
my %where = (
priority => { '<', 2 },
requestor => \$inn
);
-TMTOWTDI
-
-Conditions on boolean columns can be expressed in the same way, passing
+Conditions on boolean columns can be expressed by passing
a reference to an empty string, however using liternal SQL in this way
is deprecated - the preferred method is to use the boolean operators -
see L</"Unary operators: bool"> :
$stmt = "WHERE priority < ? AND is_ready";
@bind = ('2');
-Literal SQL is also the only way to compare 2 columns to one another:
-
- my %where = (
- priority => { '<', 2 },
- requestor => \'= submittor'
- );
-
-which creates:
-
- $stmt = "WHERE priority < ? AND requestor = submitter";
- @bind = ('2');
-
=head2 Literal SQL with placeholders and bind values (subqueries)
If the literal SQL to be inserted has placeholders and bind values,
my ($sub_stmt, @sub_bind)
= $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
- my %where = (
+ my %where = ( -and => [
foo => 1234,
- -nest => \["EXISTS ($sub_stmt)" => @sub_bind],
- );
+ \["EXISTS ($sub_stmt)" => @sub_bind],
+ ]);
which yields
C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
what we wanted here.
-Another use of the subquery technique is when some SQL clauses need
-parentheses, as it often occurs with some proprietary SQL extensions
-like for example fulltext expressions, geospatial expressions,
-NATIVE clauses, etc. Here is an example of a fulltext query in MySQL :
-
- my %where = (
- -nest => \["MATCH (col1, col2) AGAINST (?)" => qw/apples/]
- );
-
Finally, here is an example where a subquery is used
for expressing unary negation:
$sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
my %where = (
lname => {like => '%son%'},
- -nest => \["NOT ($sub_stmt)" => @sub_bind],
+ \["NOT ($sub_stmt)" => @sub_bind],
);
This yields
use these three modules together to write complex database query
apps in under 50 lines.
+=head1 REPO
+
+=over
+
+=item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
+
+=item * git: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
+
+=back
=head1 CHANGES
Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
Dan Kubb (support for "quote_char" and "name_sep")
Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
- Laurent Dami (internal refactoring, multiple -nest, extensible list of special operators, literal SQL)
+ Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
Oliver Charles (support for "RETURNING" after "INSERT")