From: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
Date: Tue, 1 Jul 2014 08:19:59 +0000 (+0100)
Subject: Escape quotes in string values in producers
X-Git-Tag: v0.11021~16^2~5
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=1868ddbee17731eb23de17472c429c6bbf13a037

Escape quotes in string values in producers
---

diff --git a/lib/SQL/Translator/Generator/DDL/SQLServer.pm b/lib/SQL/Translator/Generator/DDL/SQLServer.pm
index 9e35d89..7221229 100644
--- a/lib/SQL/Translator/Generator/DDL/SQLServer.pm
+++ b/lib/SQL/Translator/Generator/DDL/SQLServer.pm
@@ -136,7 +136,7 @@ sub enum_constraint {
   return (
      'CONSTRAINT ' . $self->enum_constraint_name($field_name) .
        ' CHECK (' . $self->quote($field_name) .
-       ' IN (' . join( ',', map qq('$_'), @$vals ) . '))'
+       ' IN (' . join( ',', map $self->quote_string($_), @$vals ) . '))'
   )
 }
 
diff --git a/lib/SQL/Translator/Generator/Role/DDL.pm b/lib/SQL/Translator/Generator/Role/DDL.pm
index e7666c7..83c8647 100644
--- a/lib/SQL/Translator/Generator/Role/DDL.pm
+++ b/lib/SQL/Translator/Generator/Role/DDL.pm
@@ -20,6 +20,7 @@ requires '_build_numeric_types';
 requires '_build_unquoted_defaults';
 requires '_build_sizeless_types';
 requires 'quote';
+requires 'quote_string';
 
 has type_map => (
    is => 'lazy',
@@ -81,7 +82,7 @@ sub field_default {
   if (ref $default) {
       $default = $$default;
   } elsif (!($self->numeric_types->{lc($field->data_type)} && Scalar::Util::looks_like_number ($default))) {
-     $default = "'$default'";
+      $default = $self->quote_string($default);
   }
   return ( "DEFAULT $default" )
 }
diff --git a/lib/SQL/Translator/Generator/Role/Quote.pm b/lib/SQL/Translator/Generator/Role/Quote.pm
index 33725c3..43c54b3 100644
--- a/lib/SQL/Translator/Generator/Role/Quote.pm
+++ b/lib/SQL/Translator/Generator/Role/Quote.pm
@@ -47,6 +47,14 @@ sub quote {
   join $sep, map { (my $n = $_) =~ s/\Q$r/$esc$r/g; "$l$n$r" } ( $sep ? split (/\Q$sep\E/, $label ) : $label )
 }
 
+sub quote_string {
+    my ($self, $string) = @_;
+
+    return $string unless defined $string;
+    $string =~ s/'/''/g;
+    return qq{'$string'};
+}
+
 1;
 
 =head1 AUTHORS
diff --git a/lib/SQL/Translator/Producer.pm b/lib/SQL/Translator/Producer.pm
index ac6d5bd..94a3d43 100644
--- a/lib/SQL/Translator/Producer.pm
+++ b/lib/SQL/Translator/Producer.pm
@@ -15,7 +15,7 @@ sub produce { "" }
 ## They are special per Producer, and provide support for the old 'now()'
 ## default value exceptions
 sub _apply_default_value {
-  my (undef, $field, $field_ref, $exceptions) = @_;
+  my ($self, $field, $field_ref, $exceptions) = @_;
   my $default = $field->default_value;
   return if !defined $default;
 
@@ -41,11 +41,18 @@ sub _apply_default_value {
     # we need to check the data itself in addition to the datatype, for basic safety
       $$field_ref .= " DEFAULT $default";
   } else {
-      $$field_ref .= " DEFAULT '$default'";
+      $default = $self->_quote_string($default);
+      $$field_ref .= " DEFAULT $default";
   }
 
 }
 
+sub _quote_string {
+    my ($self, $string) = @_;
+    $string =~ s/'/''/g;
+    return qq{'$string'};
+}
+
 1;
 
 # -------------------------------------------------------------------
diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm
index 57d09dc..904b294 100644
--- a/lib/SQL/Translator/Producer/MySQL.pm
+++ b/lib/SQL/Translator/Producer/MySQL.pm
@@ -90,6 +90,7 @@ $DEBUG   = 0 unless defined $DEBUG;
 #   http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
 my $DEFAULT_MAX_ID_LENGTH = 64;
 
+use base qw(SQL::Translator::Producer);
 use Data::Dumper;
 use SQL::Translator::Schema::Constants;
 use SQL::Translator::Generator::DDL::MySQL;
@@ -534,8 +535,7 @@ sub create_field
     my @size      = $field->size;
     my %extra     = $field->extra;
     my $list      = $extra{'list'} || [];
-    # \todo deal with embedded quotes
-    my $commalist = join( ', ', map { qq['$_'] } @$list );
+    my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
     my $charset = $extra{'mysql_charset'};
     my $collate = $extra{'mysql_collate'};
 
@@ -627,7 +627,7 @@ sub create_field
     }
 
     # Default?
-    SQL::Translator::Producer->_apply_default_value(
+    __PACKAGE__->_apply_default_value(
       $field,
       \$field_def,
       [
@@ -636,7 +636,8 @@ sub create_field
     );
 
     if ( my $comments = $field->comments ) {
-        $field_def .= qq[ comment '$comments'];
+        $comments = __PACKAGE__->_quote_string($comments);
+        $field_def .= qq[ comment $comments];
     }
 
     # auto_increment?
@@ -645,6 +646,13 @@ sub create_field
     return $field_def;
 }
 
+sub _quote_string {
+    my ($self, $string) = @_;
+
+    $string =~ s/([\\'])/$1$1/g;
+    return qq{'$string'};
+}
+
 sub alter_create_index
 {
     my ($index, $options) = @_;
diff --git a/lib/SQL/Translator/Producer/Oracle.pm b/lib/SQL/Translator/Producer/Oracle.pm
index 34a9be8..d3f7a12 100644
--- a/lib/SQL/Translator/Producer/Oracle.pm
+++ b/lib/SQL/Translator/Producer/Oracle.pm
@@ -93,6 +93,7 @@ our ( $DEBUG, $WARN );
 our $VERSION = '1.59';
 $DEBUG   = 0 unless defined $DEBUG;
 
+use base 'SQL::Translator::Producer';
 use SQL::Translator::Schema::Constants;
 use SQL::Translator::Utils qw(header_comment);
 
@@ -461,10 +462,9 @@ sub create_table {
         if ( my @table_comments = $table->comments ) {
             for my $comment ( @table_comments ) {
                 next unless $comment;
-                $comment =~ s/'/''/g;
-                push @field_comments, "COMMENT ON TABLE $table_name_q is\n '".
-                $comment . "'" unless $options->{no_comments}
-                ;
+                $comment = __PACKAGE__->_quote_string($comment);
+                push @field_comments, "COMMENT ON TABLE $table_name_q is\n $comment"
+                    unless $options->{no_comments};
             }
         }
 
@@ -550,8 +550,7 @@ sub create_field {
     my @size      = $field->size;
     my %extra     = $field->extra;
     my $list      = $extra{'list'} || [];
-    # \todo deal with embedded quotes
-    my $commalist = join( ', ', map { qq['$_'] } @$list );
+    my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
 
     if ( $data_type eq 'enum' ) {
         $check = "CHECK ($field_name_q IN ($commalist))";
@@ -660,7 +659,7 @@ sub create_field {
                 ) {
             $default = 'SYSDATE';
         } else {
-            $default = $default =~ m/null/i ? 'NULL' : "'$default'"
+            $default = $default =~ m/null/i ? 'NULL' : __PACKAGE__->_quote_string($default);
         }
 
         $field_def .= " DEFAULT $default",
@@ -718,10 +717,10 @@ sub create_field {
     push @field_defs, $field_def;
 
     if ( my $comment = $field->comments ) {
-        $comment =~ s/'/''/g;
+        $comment =~ __PACKAGE__->_quote_string($comment);
         push @field_comments,
-          "COMMENT ON COLUMN $table_name_q.$field_name_q is\n '" .
-            $comment . "';" unless $options->{no_comments};
+          "COMMENT ON COLUMN $table_name_q.$field_name_q is\n $comment;"
+              unless $options->{no_comments};
     }
 
     return \@create, \@field_defs, \@trigger_defs, \@field_comments;
diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index fa618a3..4fffce3 100644
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -460,8 +460,7 @@ sub create_view {
         my $data_type = lc $field->data_type;
         my %extra     = $field->extra;
         my $list      = $extra{'list'} || [];
-        # todo deal with embedded quotes
-        my $commalist = join( ', ', map { qq['$_'] } @$list );
+        my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
 
         if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
             my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
@@ -480,7 +479,7 @@ sub create_view {
         #
         # Default value
         #
-        SQL::Translator::Producer->_apply_default_value(
+        __PACKAGE__->_apply_default_value(
           $field,
           \$field_def,
           [
@@ -798,8 +797,7 @@ sub alter_field
     if(ref $default_value eq "SCALAR" ) {
         $default_value = $$default_value;
     } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
-        $default_value =~ s/'/''/xsmg;
-        $default_value = q(') . $default_value . q(');
+        $default_value = __PACKAGE__->_quote_string($default_value);
     }
 
     push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
diff --git a/t/02mysql-parser.t b/t/02mysql-parser.t
index 63ac5f3..1bc8888 100644
--- a/t/02mysql-parser.t
+++ b/t/02mysql-parser.t
@@ -80,7 +80,7 @@ BEGIN {
               s1 set('a','b','c') default 'b',
               e1 enum("a","b","c") default "c",
               name varchar(30) default NULL,
-              foo_type enum('vk','ck') NOT NULL default 'vk',
+              foo_type enum('vk','c''k') NOT NULL default 'vk',
               date timestamp,
               time_stamp2 timestamp,
               foo_enabled bit(1) default b'0',
@@ -170,12 +170,12 @@ BEGIN {
     my $f8 = shift @fields;
     is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
     is( $f8->data_type, 'enum', 'Type is "enum"' );
-    is( $f8->size, 2, 'Size is "2"' );
+    is( $f8->size, 3, 'Size is "2"' );
     is( $f8->is_nullable, 0, 'Field cannot be null' );
     is( $f8->default_value, 'vk', 'Default value is "vk"' );
     is( $f8->is_primary_key, 0, 'Field is not PK' );
     my %f8extra = $f8->extra;
-    is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
+    is( join(',', @{ $f8extra{'list'} || [] }), 'vk,c\'k', 'List is "vk,c\'k"' );
 
     my $f9 = shift @fields;
     is( $f9->name, 'date', 'Ninth field name is "date"' );
diff --git a/t/03mysql-to-oracle.t b/t/03mysql-to-oracle.t
index 664d4e9..5b64c59 100644
--- a/t/03mysql-to-oracle.t
+++ b/t/03mysql-to-oracle.t
@@ -10,6 +10,7 @@ my $create = q|
 CREATE TABLE random (
     id int auto_increment PRIMARY KEY,
     foo varchar(255) not null default '',
+    bar enum('wibble','wo''bble'),
     updated timestamp
 );
 CREATE UNIQUE INDEX random_foo_update ON random(foo,updated);
@@ -18,7 +19,7 @@ CREATE INDEX random_foo ON random(foo);
 |;
 
 BEGIN {
-    maybe_plan(3,
+    maybe_plan(undef,
         'SQL::Translator::Parser::MySQL',
         'SQL::Translator::Producer::Oracle');
 }
@@ -35,3 +36,6 @@ my $output = $tr->translate(\$create);
 ok( $output, 'Translate MySQL to Oracle' );
 ok( $output =~ /CREATE INDEX random_foo /, 'Normal index definition translated.');
 ok( $output =~ /CREATE UNIQUE INDEX random_foo_update /, 'Unique index definition translated.');
+ok( $output =~ /\QCHECK (bar IN ('wibble', 'wo''bble'))\E/, 'Enum translated and escaped.');
+
+done_testing;
diff --git a/t/38-mysql-producer.t b/t/38-mysql-producer.t
index 0e3f9c6..446186b 100644
--- a/t/38-mysql-producer.t
+++ b/t/38-mysql-producer.t
@@ -117,7 +117,7 @@ schema:
             list:
               - foo
               - bar
-              - baz
+              - ba'z
       indices:
         - type: NORMAL
           fields:
@@ -171,7 +171,7 @@ schema:
             list:
               - foo
               - bar
-              - baz
+              - ba'z
       indices:
         - type: NORMAL
           fields:
@@ -215,7 +215,7 @@ my @stmts = (
   `id` integer NOT NULL,
   `foo` integer NOT NULL,
   `foo2` integer NULL,
-  `bar_set` set('foo', 'bar', 'baz') NULL,
+  `bar_set` set('foo', 'bar', 'ba''z') NULL,
   INDEX `index_1` (`id`),
   INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
   INDEX (`foo`),
@@ -230,7 +230,7 @@ my @stmts = (
   `id` integer NOT NULL,
   `foo` integer NOT NULL,
   `foo2` integer NULL,
-  `bar_set` set('foo', 'bar', 'baz') NULL,
+  `bar_set` set('foo', 'bar', 'ba''z') NULL,
   INDEX `index_1` (`id`),
   INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
   INDEX (`foo`),
diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t
index 8297b81..3e0ac90 100644
--- a/t/47postgres-producer.t
+++ b/t/47postgres-producer.t
@@ -14,7 +14,7 @@ use FindBin qw/$Bin/;
 #=============================================================================
 
 BEGIN {
-    maybe_plan(57,
+    maybe_plan(undef,
         'SQL::Translator::Producer::PostgreSQL',
         'Test::Differences',
     )
@@ -306,18 +306,30 @@ is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
 my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field',
                                                    table => $table,
                                                    data_type => 'enum',
-                                                   extra => { list => [ 'Foo', 'Bar' ] },
+                                                   extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ] },
                                                    is_auto_increment => 0,
                                                    is_nullable => 0,
                                                    is_foreign_key => 0,
                                                    is_unique => 0 );
 
-my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
+my $field5_types = {};
+my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field(
+    $field5,
+    {
+        postgres_version => 8.3,
+        type_defs => $field5_types,
+    }
+);
 
 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
-
-
-
+is_deeply(
+    $field5_types,
+    { mytable_enum_field_type =>
+          "DROP TYPE IF EXISTS mytable_enum_field_type CASCADE;\n" .
+          "CREATE TYPE mytable_enum_field_type AS ENUM ('Foo', 'Bar', 'Ba''z')"
+    },
+    'Create real enum type works'
+);
 
 my $field6 = SQL::Translator::Schema::Field->new(
                                                   name      => 'character',
@@ -438,16 +450,31 @@ is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision');
 my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name',
                                                    table => $table,
                                                    data_type => 'enum',
-                                                   extra => { list => [ 'Foo', 'Bar' ],
+                                                   extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ],
                                                               custom_type_name => 'real_enum_type' },
                                                    is_auto_increment => 0,
                                                    is_nullable => 0,
                                                    is_foreign_key => 0,
                                                    is_unique => 0 );
 
-my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field($field13,{ postgres_version => 8.3 });
+my $field13_types = {};
+my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field(
+    $field13,
+    {
+        postgres_version => 8.3,
+        type_defs => $field13_types,
+    }
+);
 
 is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works');
+is_deeply(
+    $field13_types,
+    { real_enum_type =>
+          "DROP TYPE IF EXISTS real_enum_type CASCADE;\n" .
+          "CREATE TYPE real_enum_type AS ENUM ('Foo', 'Bar', 'Ba''z')"
+    },
+    'Create real enum type works'
+);
 
 
 {
@@ -621,3 +648,5 @@ CREATE VIEW view_foo ( id, name ) AS
 ";
 
 is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct");
+
+done_testing;
diff --git a/t/71-generator-sql_server.t b/t/71-generator-sql_server.t
index 17b0da3..9e31ce4 100644
--- a/t/71-generator-sql_server.t
+++ b/t/71-generator-sql_server.t
@@ -5,6 +5,7 @@ use Test::More;
 
 use SQL::Translator::Generator::DDL::SQLServer;
 use SQL::Translator::Schema::Field;
+use SQL::Translator::Schema::Table;
 
 my $shim = SQL::Translator::Generator::DDL::SQLServer->new();
 
@@ -19,5 +20,19 @@ is $shim->field(SQL::Translator::Schema::Field->new(
    size => 10,
 )), '[nice] varchar(10) NULL', 'sized field is generated correctly';
 
+my $table = SQL::Translator::Schema::Table->new(
+    name => 'mytable',
+);
+
+$table->add_field(
+    name => 'myenum',
+    data_type => 'enum',
+    extra => { list => [qw(foo ba'r)] },
+);
+
+like $shim->table($table),
+     qr/\b\QCONSTRAINT [myenum_chk] CHECK ([myenum] IN ('foo','ba''r'))\E/,
+     'enum constraint is generated and escaped correctly';
+
 done_testing;