When substituting table and column names into SQL statements,
Class::DBI uses them verbatim. This does not work with tables and
columns whose names are SQL reserved keywords unless the database
permits this as an extension (for example, MySQL generally does but
PostgreSQL doesn't). Such names can be used but must be double-
quoted.
The following patch adds quotes in all the cases I could see where
Class::DBI includes such names in statements, but I'm not sure it
covers them all.
Ben.
--- Class/DBI.pm.orig 2006-04-08 17:13:13.000000000 +0100
+++ Class/DBI.pm 2006-04-08 21:29:27.000000000 +0100
@@ -174,7 +174,7 @@
my $table = $class ? $class->table : $self->table;
$cmap{ $alias || $table } = $class || ref $self || $self;
($alias ||= "") &&= " AS $alias";
- return $table . $alias;
+ return "\"$table\"$alias";
};
my $expand_join = sub {
@@ -200,7 +200,7 @@
};
$self->_croak("Don't know how to join $c1 to $c2") unless $col;
- push @sql, sprintf " %s.%s = %s.%s ", $t1, $col, $t2,
+ push @sql, sprintf " \"%s\".\"%s\" = \"%s\".\"%s\" ", $t1, $col, $t2,
$c2->primary_column;
}
return join " AND ", @sql;
@@ -208,11 +208,11 @@
$sql =~ s/__TABLE\(?(.*?)\)?__/$expand_table->($1)/eg;
$sql =~ s/__JOIN\((.*?)\)__/$expand_join->($1)/eg;
- $sql =~ s/__ESSENTIAL__/join ", ", $self->_essential/eg;
+ $sql =~ s/__ESSENTIAL__/join ", ", map "\"$_\"", $self->_essential/eg;
$sql =~
- s/__ESSENTIAL\((.*?)\)__/join ", ", map "$1.$_", $self->_essential/eg;
+ s/__ESSENTIAL\((.*?)\)__/join ", ", map "\"$1\".\"$_\"", $self->_essential/eg;
if ($sql =~ /__IDENTIFIER__/) {
- my $key_sql = join " AND ", map "$_=?", $self->primary_columns;
+ my $key_sql = join " AND ", map "\"$_\"=?", $self->primary_columns;
$sql =~ s/__IDENTIFIER__/$key_sql/g;
}
return $self->SUPER::transform_sql($sql => @args);
@@ -629,7 +629,7 @@
eval {
my @columns = keys %$data;
my $sth = $self->sql_MakeNewObj(
- join(', ', @columns),
+ join(', ', map "\"$_\"", @columns),
join(', ', map $self->_column_placeholder($_), @columns),
);
$self->_bind_param($sth, \@columns);
-- END --