Search the CPAN - metacpan.org

Changes	0 5
MANIFEST	0 1
META.json	5 5
META.yml	5 5
README.md	4 39
lib/SQL/Maker/Select.pm	1 1
lib/SQL/Maker.pm	7 52
t/13_strict.t	1 19
t/14_quote_char.t	0 66
9 files changed (This is a version diff)	23193

Changes

@@ -1,5 +1,10 @@
 Revision history for Perl module SQL::Maker
 
+1.21 2014-12-22T06:27:33Z
+
+    Official support for directly passing a SQL::QueryMaker object as $where parameter #42
+    (debug-ito)
+
 1.20 2014-07-31T20:59:30Z
 
     - DateTime is a testing deps

MANIFEST

@@ -29,6 +29,7 @@ t/10_subquery.t
 t/11_set.t
 t/12_insert_empty.t
 t/13_strict.t
+t/14_quote_char.t
 t/condition/01_where.t
 t/condition/02_make_term.t
 t/condition/03_add_raw.t

META.json

@@ -4,7 +4,7 @@
       "Tokuhiro Matsuno <tokuhirom AAJKLFJEF@ GMAIL COM>"
    ],
    "dynamic_config" : 0,
-   "generated_by" : "Minilla/v2.1.1",
+   "generated_by" : "Minilla/v2.2.0",
    "license" : [
       "perl_5"
    ],
@@ -65,7 +65,7 @@
    "provides" : {
       "SQL::Maker" : {
          "file" : "lib/SQL/Maker.pm",
-         "version" : "1.20"
+         "version" : "1.21"
       },
       "SQL::Maker::Condition" : {
          "file" : "lib/SQL/Maker/Condition.pm"
@@ -99,11 +99,11 @@
       },
       "homepage" : "https://github.com/tokuhirom/SQL-Maker",
       "repository" : {
-         "url" : "git://github.com/tokuhirom/SQL-Maker",
+         "url" : "git://github.com/tokuhirom/SQL-Maker.git",
          "web" : "https://github.com/tokuhirom/SQL-Maker"
       }
    },
-   "version" : "1.20",
+   "version" : "1.21",
    "x_authority" : "cpan:TOKUHIROM",
    "x_contributors" : [
       "lestrrat <lestrrat+github@gmail.com>",
@@ -114,7 +114,6 @@
       "issm <issmxx@gmail.com>",
       "Masahiro Chiba <nihen@megabbs.com>",
       "Masahiro Chiba <chiba@everqueue.com>",
-      "Toshio Ito <debug.ito@gmail.com>",
       "Masayuki Matsuki <y.songmu@gmail.com>",
       "Neil Bowers <neil@bowers.com>",
       "Gelu Lupas <gvl@cpan.org>",
@@ -122,6 +121,7 @@
       "soh335 <sugarbabe335@gmail.com>",
       "pokutuna <popopopopokutuna@gmail.com>",
       "Kazuho Oku <kazuhooku@gmail.com>",
+      "Toshio Ito <debug.ito@gmail.com>",
       "Tokuhiro Matsuno <tokuhirom@gmail.com>"
    ]
 }

META.yml

@@ -9,7 +9,7 @@ build_requires:
 configure_requires:
   Module::Build::Tiny: '0.035'
 dynamic_config: 0
-generated_by: 'Minilla/v2.1.1, CPAN::Meta::Converter version 2.141520'
+generated_by: 'Minilla/v2.2.0, CPAN::Meta::Converter version 2.141520'
 license: perl
 meta-spec:
   url: http://module-build.sourceforge.net/META-spec-v1.4.html
@@ -28,7 +28,7 @@ no_index:
 provides:
   SQL::Maker:
     file: lib/SQL/Maker.pm
-    version: '1.20'
+    version: '1.21'
   SQL::Maker::Condition:
     file: lib/SQL/Maker/Condition.pm
   SQL::Maker::Plugin::InsertMulti:
@@ -56,8 +56,8 @@ requires:
 resources:
   bugtracker: https://github.com/tokuhirom/SQL-Maker/issues
   homepage: https://github.com/tokuhirom/SQL-Maker
-  repository: git://github.com/tokuhirom/SQL-Maker
-version: '1.20'
+  repository: git://github.com/tokuhirom/SQL-Maker.git
+version: '1.21'
 x_authority: cpan:TOKUHIROM
 x_contributors:
   - 'lestrrat <lestrrat+github@gmail.com>'
@@ -68,7 +68,6 @@ x_contributors:
   - 'issm <issmxx@gmail.com>'
   - 'Masahiro Chiba <nihen@megabbs.com>'
   - 'Masahiro Chiba <chiba@everqueue.com>'
-  - 'Toshio Ito <debug.ito@gmail.com>'
   - 'Masayuki Matsuki <y.songmu@gmail.com>'
   - 'Neil Bowers <neil@bowers.com>'
   - 'Gelu Lupas <gvl@cpan.org>'
@@ -76,4 +75,5 @@ x_contributors:
   - 'soh335 <sugarbabe335@gmail.com>'
   - 'pokutuna <popopopopokutuna@gmail.com>'
   - 'Kazuho Oku <kazuhooku@gmail.com>'
+  - 'Toshio Ito <debug.ito@gmail.com>'
   - 'Tokuhiro Matsuno <tokuhirom@gmail.com>'

README.md

@@ -62,6 +62,7 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https
         Whether or not the use of unblessed references are prohibited for defining the SQL expressions.
 
         In strict mode, all the expressions must be declared by using blessed references that export `as_sql` and `bind` methods like [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker).
+        See ["STRICT MODE"](#strict-mode) for detail.
 
         Default: undef
 
@@ -99,7 +100,7 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https
     - `\@where`
     - `$where`
 
-        where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
+        where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object, or [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker) object.
 
     - `\%opt`
 
@@ -216,7 +217,7 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https
     - `\@where`
     - `$where`
 
-        where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
+        where clause from hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object, or [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker) object.
 
     - `\%opt`
 
@@ -253,7 +254,7 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https
     - \\@where
     - $where
 
-        where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
+        where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object, or [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker) object.
 
 - `$builder->new_condition()`
 
@@ -263,7 +264,7 @@ SQL::Maker is yet another SQL builder class. It is based on [DBIx::Skinny](https
 - `my ($sql, @binds) = $builder->where(\@where)`
 - `my ($sql, @binds) = $builder->where(\@where)`
 
-    Where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object.
+    Where clause from a hashref or arrayref via [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition), or [SQL::Maker::Condition](https://metacpan.org/pod/SQL::Maker::Condition) object, or [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker) object.
 
 # PLUGINS
 
@@ -273,6 +274,40 @@ SQL::Maker features a plugin system. Write the code as follows:
     use parent qw/SQL::Maker/;
     __PACKAGE__->load_plugin('InsertMulti');
 
+# STRICT MODE
+
+See [http://blog.kazuhooku.com/2014/07/the-json-sql-injection-vulnerability.html](http://blog.kazuhooku.com/2014/07/the-json-sql-injection-vulnerability.html) for why
+do we need the strict mode in the first place.
+
+In strict mode, the following parameters must be blessed references implementing `as_sql` and `bind` methods
+if they are NOT simple scalars (i.e. if they are references of any kind).
+
+- Values in `$where` parameter for `select`, `update`, `delete` methods.
+- Values in `%values` and `%set` parameter for `insert` and `update` methods, respectively.
+
+You can use [SQL::QueryMaker](https://metacpan.org/pod/SQL::QueryMaker) objects for those parameters.
+
+Example:
+
+    use SQL::QueryMaker qw(sql_in sql_raw);
+    
+    ## NG: Use array-ref for values.
+    $maker->select("user", ['*'], { name => ["John", "Tom"] });
+    
+    ## OK: Use SQL::QueryMaker
+    $maker->select("user", ['*'], { name => sql_in(["John", "Tom"]) });
+    
+    ## Also OK: $where parameter itself is a blessed object.
+    $maker->select("user", ['*'], $maker->new_condition->add(name => sql_in(["John", "Tom"])));
+    $maker->select("user", ['*'], sql_in(name => ["John", "Tom"]));
+    
+    
+    ## NG: Use scalar-ref for a raw value.
+    $maker->insert(user => [ name => "John", created_on => \"datetime(now)" ]);
+    
+    ## OK: Use SQL::QueryMaker
+    $maker->insert(user => [name => "John", created_on => sql_raw("datetime(now)")]);
+
 # FAQ
 
 - Why don't you use SQL::Abstract?

lib/SQL/Maker/Select.pm

@@ -290,7 +290,7 @@ sub add_where_raw {
 sub as_sql_where {
     my $self = shift;
 
-    my $where = $self->{where}->as_sql();
+    my $where = $self->{where}->as_sql(undef, sub { $self->_quote($_[0]) });
     $where ? "WHERE $where" . $self->new_line : '';
 }

lib/SQL/Maker.pm

@@ -2,7 +2,7 @@ package SQL::Maker;
 use strict;
 use warnings;
 use 5.008001;
-our $VERSION = '1.20';
+our $VERSION = '1.21';
 use Class::Accessor::Lite 0.05 (
     ro => [qw/quote_char name_sep new_line strict driver select_class/],
 );
@@ -208,7 +208,7 @@ sub make_set_clause {
 sub where {
     my ($self, $where) = @_;
     my $cond = $self->_make_where_condition($where);
-    return ($cond->as_sql(), $cond->bind());
+    return ($cond->as_sql(undef, sub { $self->_quote($_[0]) }), $cond->bind());
 }
 
 sub _make_where_condition {
@@ -233,7 +233,7 @@ sub _make_where_clause {
     return ['', []] unless $where;
 
     my $w = $self->_make_where_condition($where);
-    my $sql = $w->as_sql();
+    my $sql = $w->as_sql(undef, sub { $self->_quote($_[0]) });
     return [$sql ? " WHERE $sql" : '', [$w->bind]];
 }
 
@@ -411,6 +411,7 @@ Default: '\n'
 Whether or not the use of unblessed references are prohibited for defining the SQL expressions.
 
 In strict mode, all the expressions must be declared by using blessed references that export C<as_sql> and C<bind> methods like L<SQL::QueryMaker>.
+See L</STRICT MODE> for detail.
 
 Default: undef
 
@@ -455,7 +456,7 @@ of column and alias names (e.g. C<< ['foo.id' => 'foo_id'] >>).
 
 =item C<< $where >>
 
-where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object.
 
 =item C<< \%opt >>
 
@@ -590,7 +591,7 @@ Table name in scalar.
 
 =item C<< $where >>
 
-where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+where clause from hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object.
 
 =item C<< \%opt >>
 
@@ -637,7 +638,7 @@ Setting values.
 
 =item $where
 
-where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object.
 
 =back
 
@@ -651,7 +652,7 @@ Create new L<SQL::Maker::Condition> object from C< $builder > settings.
 
 =item C<< my ($sql, @binds) = $builder->where(\@where) >>
 
-Where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object.
+Where clause from a hashref or arrayref via L<SQL::Maker::Condition>, or L<SQL::Maker::Condition> object, or L<SQL::QueryMaker> object.
 
 =back
 
@@ -663,6 +664,50 @@ SQL::Maker features a plugin system. Write the code as follows:
     use parent qw/SQL::Maker/;
     __PACKAGE__->load_plugin('InsertMulti');
 
+=head1 STRICT MODE
+
+See L<http://blog.kazuhooku.com/2014/07/the-json-sql-injection-vulnerability.html> for why
+do we need the strict mode in the first place.
+
+In strict mode, the following parameters must be blessed references implementing C<as_sql> and C<bind> methods
+if they are NOT simple scalars (i.e. if they are references of any kind).
+
+=over
+
+=item *
+
+Values in C<$where> parameter for C<select>, C<update>, C<delete> methods.
+
+=item *
+
+Values in C<%values> and C<%set> parameter for C<insert> and C<update> methods, respectively.
+
+=back
+
+You can use L<SQL::QueryMaker> objects for those parameters.
+
+Example:
+
+    use SQL::QueryMaker qw(sql_in sql_raw);
+    
+    ## NG: Use array-ref for values.
+    $maker->select("user", ['*'], { name => ["John", "Tom"] });
+    
+    ## OK: Use SQL::QueryMaker
+    $maker->select("user", ['*'], { name => sql_in(["John", "Tom"]) });
+    
+    ## Also OK: $where parameter itself is a blessed object.
+    $maker->select("user", ['*'], $maker->new_condition->add(name => sql_in(["John", "Tom"])));
+    $maker->select("user", ['*'], sql_in(name => ["John", "Tom"]));
+    
+    
+    ## NG: Use scalar-ref for a raw value.
+    $maker->insert(user => [ name => "John", created_on => \"datetime(now)" ]);
+    
+    ## OK: Use SQL::QueryMaker
+    $maker->insert(user => [name => "John", created_on => sql_raw("datetime(now)")]);
+
+
 =head1 FAQ
 
 =over 4

t/13_strict.t

@@ -31,7 +31,7 @@ subtest "maker->select where" => sub {
 };
 
 subtest "maker->update where" => sub {
-  my ($sql, @binds) = $maker->delete("table", sql_eq(id => 1));
+  my ($sql, @binds) = $maker->update("table", [], sql_eq(id => 1));
   like $sql, qr/WHERE\s+.*id.*\s*=/s;
   is_deeply \@binds, [ 1 ];
 };
@@ -42,6 +42,12 @@ subtest "maker->delete where" => sub {
   is_deeply \@binds, [ 1 ];
 };
 
+subtest "maker->where" => sub {
+  my ($sql, @binds) = $maker->where(sql_eq(id => 1));
+  like $sql, qr/id.*\s*=/s;
+  is_deeply \@binds, [ 1 ];
+};
+
 subtest "maker->new_condition (err)" => checkerr(sub {
     $maker->new_condition->add(
         foo => [1],
@@ -62,12 +68,24 @@ subtest "maker->select (err)" => checkerr(sub {
     $maker->select("user", ['*'], { name => ["John", "Tom" ]});
 });
 
+subtest "maker->select (ok)", sub {
+    $maker->select("user", ['*'], { name => sql_in(["John", "Tom"]) });
+    $maker->select("user", ['*'], $maker->new_condition->add(name => sql_in(["John", "Tom"])));
+    $maker->select("user", ['*'], sql_in(name => ["John", "Tom"]));
+    ok("run without croaking");
+};
+
 subtest "maker->insert (err)" => checkerr(sub {
     $maker->insert(
         user => [ name => "John", created_on => \"datetime(now)" ]
     );
 });
 
+subtest "maker->insert (ok)" => sub {
+    $maker->insert(user => [name => "John", created_on => sql_raw("datetime(now)")]);
+    ok("run without croaking");
+};
+
 subtest "maker->delete (err)" => checkerr(sub {
     $maker->delete(user => [ name => ["John", "Tom"]]);
 });

t/14_quote_char.t

@@ -0,0 +1,66 @@
+use strict;
+use warnings;
+use utf8;
+use Test::More;
+use SQL::Maker;
+use SQL::QueryMaker qw(sql_eq);
+
+my $maker = SQL::Maker->new(
+    driver => 'SQLite',
+    quote_char => ':',
+    name_sep => '-',
+    new_line => ' ',
+);
+
+my @condition_cases = (
+    {
+        label => 'Condition',
+        cond_maker => sub {
+            my $cond = $maker->new_condition;
+            $cond->add('foo-bar' => 'buzz');
+            return $cond;
+        },
+    },
+    {
+        label => 'QueryMaker',
+        cond_maker => sub {
+            return sql_eq('foo-bar', 'buzz');
+        },
+    },
+    {
+        label => 'QueryMaker in Condition',
+        cond_maker => sub {
+            my $cond = $maker->new_condition;
+            $cond->add('foo-bar' => sql_eq('buzz'));
+            return $cond;
+        },
+    },
+);
+
+foreach my $cond_case (@condition_cases) {
+    subtest "select: $cond_case->{label}" => sub {
+        my $cond = $cond_case->{cond_maker}();
+        my ($sql, @binds) = $maker->select("table", ["*"], $cond);
+        like $sql, qr/:foo:-:bar:/;
+    };
+    
+    subtest "update: $cond_case->{label}" => sub {
+        my $cond = $cond_case->{cond_maker}();
+        my ($sql, @binds) = $maker->update("table", ["a" => "A"], $cond);
+        like $sql, qr/:foo:-:bar:/;
+    };
+
+    subtest "delete: $cond_case->{label}" => sub {
+        my $cond = $cond_case->{cond_maker}();
+        my ($sql, @binds) = $maker->delete("table", $cond);
+        like $sql, qr/:foo:-:bar:/;
+    };
+
+    subtest "where: $cond_case->{label}" => sub {
+        my $cond = $cond_case->{cond_maker}();
+        my ($sql, @binds) = $maker->where($cond);
+        like $sql, qr/:foo:-:bar:/;
+    };
+}
+
+done_testing;

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)