CGI::Application::PlugCGI::Application::Plugin::Authentication::Driver::DBI(3)NAMECGI::Application::Plugin::Authentication::Driver::DBI - DBI
Authentication driver
VERSION
This document describes
CGI::Application::Plugin::Authentication::Driver::DBI version 0.20
SYNOPSIS
use base qw(CGI::Application);
use CGI::Application::Plugin::Authentication;
__PACKAGE__->authen->config(
DRIVER => [ 'DBI',
DBH => $self->dbh,
TABLE => 'user',
CONSTRAINTS => {
'user.name' => '__CREDENTIAL_1__',
'MD5:user.password' => '__CREDENTIAL_2__'
},
],
);
DESCRIPTION
This Authentication driver uses the DBI module to allow you to
authenticate against any database for which there is a DBD module. You
can either provide an active database handle, or provide the parameters
necessary to connect to the database.
When describing the database structure, you need to specify some or all
of the following parameters: TABLE(S), JOIN_ON, COLUMNS, CONSTRAINTS,
ORDER_BY and LIMIT.
DBH
The DBI database handle to use. Defaults to "$self-"dbh()>, which is
provided and configured through CGI::Application::Plugin::DBH
TABLE(S) (required)
Provide either a single table name, or an array of table names. You
can give the table names aliases which can be referenced in later
columns.
TABLE => 'users',
- or -
TABLES => ['users U', 'domains D'],
JOIN_ON (conditionally required)
If you have specified multiple tables, then you need to provide an SQL
expression that can be used to join those tables.
JOIN_ON => 'user.domainid = domain.id',
- or -
JOIN_ON => 'U.domainid = D.id',
COLUMNS (optional)
This is a hash of columns/values that should be pulled out of the
database and validated locally in perl. Most credentials can be
checked right in the database (example username = ?), but some
parameters may need to be tested locally in perl, so they must be
listed in the COLUMNS option. One example of a value that needs to be
tested in perl is a crypted password. In order to test a crypted
password, you need to take the entered password, and crypt it with the
salt of the already crypted password. But until we actually see the
password that is in the database, we will not know the value of the
salt that was used to encrypt the password. So we pull the value out
using COLUMNS, and the test will be performed automatically in perl.
Any value that matches __CREDENTIAL_n__ (where n is a number) will be
replaced with the corresponding credential that was entered by the
user. For an explanation of what the credentials are and where they
come from, see the section headed with CREDENTIALS in
CGI::Application::Plugin::Authentication.
COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
CONSTRAINTS (optional)
You will most likely always have some constraints to use. These
constraints will be added to the WHERE clause of the SQL query, and
will ideally reduce the number of returned rows to one.
Any value that matches __CREDENTIAL_n__ (where n is a number) will be
replaced with the corresponding credential that was entered by the
user. For an explanation of what the credentials are and where they
come from, see the section headed with CREDENTIALS in
CGI::Application::Plugin::Authentication.
CONSTRAINTS => {
'users.email' => '__CREDENTIAL_1__',
'MD5:users.passphrase' => '__CREDENTIAL_2__',
'users.active' => 1,
}
ORDER_BY (optional)
This option allows you to order the result set, in case the query
returns multiple rows.
ORDER_BY => 'created DESC'
Note: This option is only useful if you also specify the COLUMNS
option.
LIMIT (optional)
In some situations your query may return multiple rows when you only
want it to return one. For example if you insert and date a new row
instead of updating the existing row when the details for an account
change. In this case you want the newest record from the result set,
so it will be important to order the result set and limit it to return
only one row.
LIMIT => 1
Note: This option is only useful if you also specify the COLUMNS
option.
ENCODED PASSWORDS
It is quite common to store passwords in a database in some form that
makes them hard (or virtually impossible) to guess. Most of the time
one way encryption techniques like Unix crypt or MD5 hashes are used to
store the password securely (I would recommend using MD5 or SHA1 over
Unix crypt). If you look at the examples listed above, you can see
that you can mark your columns with an encoding type. Here is another
example:
CONSTRAINTS => {
username => '__CREDENTIAL_1__',
'MD5:password' => '__CREDENTIAL_2__',
}
Here the password field is expected to be stored in the database in MD5
format. In order for the MD5 check to work for all databases, the
password will be encoded using perl, and then checked against the value
in the database. So in effect, the following will be done:
$username = 'test';
$password = '123';
$encoded_password = 'ICy5YqxZB1uWSwcVLSNLcA';
$sth = $dbh->prepare('SELECT count(*) FROM users WHERE username = ? AND password = ?';
$sth->execute($username, $encoded_password);
# I we found a row, then the user credentials are valid and the user is logged in
This is all automatically performed behind the scenes when you specify
that a certain field in the database is encoded.
We have to handle this slightly different when working with Unix crypt.
In order to crypt a password, you need to provide the crypt function
with a 2 character salt value. These are usually just generated
randomly, and when the value is crypted, the first two characters of
the resulting string will be the 2 salt characters. The problem comes
into play when you want to check a password against a crypted password.
You need to know the salt in order to properly test the password. But
in our case, the crypted password is in the DB. This means we can not
generate the crypted test password before we run the query against the
database.
So instead we pull the value of the crypted password out of the
database, and then perform the tests after the query, instead of
before. Here is an example:
CONSTRAINTS => { 'username' => '__CREDENTIAL_1__' },
COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
And here is what will happen behind the scenes:
$username = 'test';
$password = '123';
$sth = $dbh->prepare('SELECT password FROM users WHERE username = ?';
$sth->execute($username);
($encoded_password) = $sth->fetchrow_array;
if ($encoded_password eq crypt($password, $encoded_password)) {
# The credentials are valid and the user is logged in
}
Again, this is all done automatically behind the scenes, but I've
included it here to illustrate how the queries are performed, and how
the comparisons are handled. For more information see the section
labelled ENCODED PASSWORDS in the
CGI::Application::Plugin::Authentication::Driver docs.
EXAMPLE
# using multiple tables
# Here we check three credentials (user, password and domain) across
# two separate tables.
__PACKAGE__->authen->config(
DRIVER => [ 'DBI',
# the handle comes from $self->dbh, via the "DBH" plugin.
TABLES => ['user', 'domain'],
JOIN_ON => 'user.domainid = domain.id',
CONSTRAINTS => {
'user.name' => '__CREDENTIAL_1__',
'user.password' => '__CREDENTIAL_2__',
'domain.name' => '__CREDENTIAL_3__'
}
],
);
- or -
# using filtered fields
# Here the password column contains values that are encoded using unix crypt
# and since we need to know the salt in order to encrypt the password
# properly, we need to pull out the password, and check it locally
__PACKAGE__->authen->config(
DRIVER => [ 'DBI',
DBH => $dbh, # provide your own DBI handle
TABLE => 'user',
CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__' }
COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
],
);
- or -
# extra constraints
# Here we only check users where the 'active' column is true
__PACKAGE__->authen->config(
DRIVER => [ 'DBI',
TABLE => 'user',
CONSTRAINTS => {
'user.name' => '__CREDENTIAL_1__',
'user.password' => '__CREDENTIAL_2__',
'user.active' => 't'
},
],
);
- or -
# all of them combined
# Here the user is required to enter a username and password (which is
# crypted), and a daily code that changes every day (which is encoded using
# an MD5 hash hex format and stored in upper case).
__PACKAGE__->authen->config(
DRIVER => [ 'DBI',
TABLES => ['user U', 'dailycode D'],
JOIN_ON => 'U.userid = D.userid',
CONSTRAINTS => {
'U.name' => '__CREDENTIAL_1__',
'uc:md5_hex:D.code' => '__CREDENTIAL_3__',
'D.date' => 'now'
},
COLUMNS => {
'crypt:U.password' => '__CREDENTIAL_2__'
},
],
);
METHODS
verify_credentials
This method will test the provided credentials against the values found
in the database, according to the Driver configuration.
SEE ALSO
CGI::Application::Plugin::Authentication::Driver,
CGI::Application::Plugin::Authentication, perl(1)LICENCE AND COPYRIGHT
Copyright (c) 2005, SiteSuite. All rights reserved.
This module is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
DISCLAIMER OF WARRANTY
BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY
FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT
WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER
PARTIES PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE
ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH
YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL
NECESSARY SERVICING, REPAIR, OR CORRECTION.
IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING
WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR
REDISTRIBUTE THE SOFTWARE AS PERMITTED BY THE ABOVE LICENCE, BE LIABLE
TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL, OR
CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE
SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING
RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A
FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF
SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
DAMAGES.
perl v5.14.1CGI::Application::Plugin::Authentication::Driver::DBI(3)