Bug#41890: libapache-dbilogger-perl: Incompatible with PostgreSQL due to unquoted column names

[Julian Mehnle]

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C521E6.0E14B540
Content-Type: text/plain;
	charset="ISO-8859-15"
Content-Transfer-Encoding: 7bit

package libapache-dbilogger-perl
submitter 41890 !
retitle 41890 libapache-dbilogger-perl: Incompatible with PostgreSQL due to unquoted column names
tags 41890 patch
thanks

Gunnar Wolf wrote:
> I want to close a pretty old bug ( #41890! :-O ). It requires a minor
> change in libapache-dbilogger-perl - 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.

------=_NextPart_000_0000_01C521E6.0E14B540
Content-Type: application/octet-stream;
	name="libapache-dbilogger-perl-0.93-SQL-quoting.diff"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="libapache-dbilogger-perl-0.93-SQL-quoting.diff"

diff -ruN libapache-dbilogger-perl-0.93.org/DBILogger.pm =
libapache-dbilogger-perl-0.93/DBILogger.pm=0A=
--- libapache-dbilogger-perl-0.93.org/DBILogger.pm	2005-03-05 =
18:42:45.000000000 +0100=0A=
+++ libapache-dbilogger-perl-0.93/DBILogger.pm	2005-03-05 =
18:52:01.000000000 +0100=0A=
@@ -57,16 +57,12 @@=0A=
   		return DECLINED;=0A=
   	}=0A=
   	=0A=
-  	my @valueslist;=0A=
-  	=0A=
-  	foreach (keys %data) {=0A=
-		$data{$_} =3D $dbh->quote($data{$_});=0A=
-		push @valueslist, $data{$_};=0A=
-	}=0A=
-=0A=
 	my $table =3D $r->dir_config("DBILogger_table") || 'requests';=0A=
 =0A=
-	my $statement =3D "insert into $table (". join(',', keys %data) .") =
VALUES (". join(',', @valueslist) .")";=0A=
+        my @columns =3D map($dbh->quote_identifier($_), keys   %data);=0A=
+        my @values  =3D map($dbh->quote($_),            values %data);=0A=
+        =0A=
+	my $statement =3D "INSERT INTO $table (" . join(', ', @columns) . ") =
VALUES (" . join(', ', @values ) . ")";=0A=
 =0A=
 	my $tries =3D 0;=0A=
 	=0A=

------=_NextPart_000_0000_01C521E6.0E14B540--