| Subject: | Incompatible with PostgreSQL due to unquoted column names |
Gunnar Wolf wrote:
Show quoted text
> I want to close a pretty old bug [... by] setting the column "user" to a
> SQL-legal name (it seems it was meant for MySQL, which is quite
> relaxed on what it accepts - I don't know if this problem would still
> show up with a current Postgres, but using 'user' as a column name is
> definitively not nice).
You do not have to make an incompatible change. "user" is very well
allowed as a column name in PostgreSQL, you just have to double-quote it
to distinguish it from the "user" keyword:
| CREATE TABLE requests (
| server varchar(127) DEFAULT '' NOT NULL,
| bytes mediumint(9) DEFAULT '0' NOT NULL,
| "user" varchar(15) DEFAULT '' NOT NULL,
| [...]
| );
Perhaps a PostgreSQL-specific note in the docs for the table creation
would be appropriate.
In any case, a code change (though not an incompatible one) is necessary
in order to make Apache::DBILogger actually work with such a table under
PostgreSQL:
The module must properly use SQL quoting, which is supported by DBI. The
module actually quotes the to-be-inserted values already, but not the
column names.
Here is a minimally invasive patch for the required code changes.
diff -ruN libapache-dbilogger-perl-0.93.org/DBILogger.pm libapache-dbilogger-perl-0.93/DBILogger.pm
--- libapache-dbilogger-perl-0.93.org/DBILogger.pm 2005-03-05 18:42:45.000000000 +0100
+++ libapache-dbilogger-perl-0.93/DBILogger.pm 2005-03-05 18:52:01.000000000 +0100
@@ -57,16 +57,12 @@
return DECLINED;
}
- my @valueslist;
-
- foreach (keys %data) {
- $data{$_} = $dbh->quote($data{$_});
- push @valueslist, $data{$_};
- }
-
my $table = $r->dir_config("DBILogger_table") || 'requests';
- my $statement = "insert into $table (". join(',', keys %data) .") VALUES (". join(',', @valueslist) .")";
+ my @columns = map($dbh->quote_identifier($_), keys %data);
+ my @values = map($dbh->quote($_), values %data);
+
+ my $statement = "INSERT INTO $table (" . join(', ', @columns) . ") VALUES (" . join(', ', @values ) . ")";
my $tries = 0;