| Subject: | AuthCookieDBI 2.17 breaks with non-MySQL databases |
| Date: | Thu, 15 Jan 2015 15:34:47 +0200 |
| To: | <bug-Apache2-AuthCookieDBI [...] rt.cpan.org> |
| From: | "Peter Gibbs" <peter [...] emkel.co.za> |
Hi,
There was a 'bug fix' make in AuthCookieDBI 2.17 which added backticks
around identifiers in the SQL statements.
This presumably works nicely for mysql, but breaks other databases. For
example, postgresql uses double quotes rather than backticks to quote
identifier names.
The DBI module has a method "quote_identifier" which can be used to
quote identifiers appropriately for the database in use, which would
seem to be a better solution.
For example:
my $sql_query = <<"SQL";
SELECT `$c{'DBI_PasswordField'}`
FROM `$c{'DBI_UsersTable'}`
WHERE `$c{'DBI_UserField'}` = ?
AND (`$c{'DBI_PasswordField'}` != ''
AND `$c{'DBI_PasswordField'}` IS NOT NULL)
SQL
could be rewritten as:
my $PasswordField = $dbh->quote_identifier($c{'DBI_PasswordField'});
my $UsersTable = $dbh->quote_identifier($c{'DBI_UsersTable'});
my $UsersField = $dbh->quote_identifier($c{'DBI_UsersField'});
my $sql_query = <<"SQL";
SELECT $PasswordField
FROM $UsersTable
WHERE $UserField = ?
AND ($PasswordField != ''
AND $PasswordField IS NOT NULL)
SQL
Regards,
Peter Gibbs