.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "CGI::Application::Plugin::Authentication::Driver::DBI 3pm"
.TH CGI::Application::Plugin::Authentication::Driver::DBI 3pm "2024-01-07" "perl v5.36.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
CGI::Application::Plugin::Authentication::Driver::DBI \- DBI Authentication driver
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\& use base qw(CGI::Application);
\& use CGI::Application::Plugin::Authentication;
\&
\& _\|_PACKAGE_\|_\->authen\->config(
\&     DRIVER => [ \*(AqDBI\*(Aq,
\&         DBH         => $self\->dbh,
\&         TABLE       => \*(Aquser\*(Aq,
\&         CONSTRAINTS => {
\&             \*(Aquser.name\*(Aq         => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&             \*(AqMD5:user.password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq
\&         },
\&     ],
\& );
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This Authentication driver uses the \s-1DBI\s0 module to allow you to authenticate against
any database for which there is a \s-1DBD\s0 module.  You can either provide an active
database handle, or provide the parameters necessary to connect to the database.
.PP
When describing the database structure, you need to specify some or all of the
following parameters: \s-1TABLE\s0(S), \s-1JOIN_ON, COLUMNS, CONSTRAINTS, ORDER_BY\s0 and
\&\s-1LIMIT.\s0
.SS "\s-1DBH\s0"
.IX Subsection "DBH"
The \s-1DBI\s0 database handle to use. Defaults to \f(CW\*(C`$self\-\*(C'\fR\fBdbh()\fR>, which is provided and configured
through CGI::Application::Plugin::DBH
.SS "\s-1TABLE\s0(S)  (required)"
.IX Subsection "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.
.PP
.Vb 1
\&     TABLE => \*(Aqusers\*(Aq,
\&
\& \- or \-
\&
\&     TABLES => [\*(Aqusers U\*(Aq, \*(Aqdomains D\*(Aq],
.Ve
.SS "\s-1JOIN_ON\s0  (conditionally required)"
.IX Subsection "JOIN_ON (conditionally required)"
If you have specified multiple tables, then you need to provide an \s-1SQL\s0 expression that
can be used to join those tables.
.PP
.Vb 1
\&     JOIN_ON => \*(Aquser.domainid = domain.id\*(Aq,
\&
\& \- or \-
\&
\&     JOIN_ON => \*(AqU.domainid = D.id\*(Aq,
.Ve
.SS "\s-1COLUMNS\s0  (optional)"
.IX Subsection "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 \s-1COLUMNS\s0 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 \s-1COLUMNS,\s0 and the test will be performed automatically in perl.
.PP
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
\&\s-1CREDENTIALS\s0 in CGI::Application::Plugin::Authentication.
.PP
.Vb 1
\&     COLUMNS => { \*(Aqcrypt:password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq },
.Ve
.SS "\s-1CONSTRAINTS\s0  (optional)"
.IX Subsection "CONSTRAINTS (optional)"
You will most likely always have some constraints to use.  These constraints
will be added to the \s-1WHERE\s0 clause of the \s-1SQL\s0 query, and will ideally reduce
the number of returned rows to one.
.PP
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
\&\s-1CREDENTIALS\s0 in CGI::Application::Plugin::Authentication.
.PP
.Vb 5
\&     CONSTRAINTS => {
\&         \*(Aqusers.email\*(Aq          => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&         \*(AqMD5:users.passphrase\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq,
\&         \*(Aqusers.active\*(Aq         => 1,
\&     }
.Ve
.SS "\s-1ORDER_BY\s0  (optional)"
.IX Subsection "ORDER_BY (optional)"
This option allows you to order the result set, in case the query returns
multiple rows.
.PP
.Vb 1
\&     ORDER_BY => \*(Aqcreated DESC\*(Aq
.Ve
.PP
Note: This option is only useful if you also specify the \s-1COLUMNS\s0 option.
.SS "\s-1LIMIT\s0  (optional)"
.IX Subsection "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.
.PP
.Vb 1
\&     LIMIT => 1
.Ve
.PP
Note: This option is only useful if you also specify the \s-1COLUMNS\s0 option.
.SH "ENCODED PASSWORDS"
.IX Header "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 \s-1MD5\s0 hashes are used to store the password securely (I would recommend
using \s-1MD5\s0 or \s-1SHA1\s0 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:
.PP
.Vb 4
\&    CONSTRAINTS => {
\&        username       => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&        \*(AqMD5:password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq,
\&    }
.Ve
.PP
Here the password field is expected to be stored in the database in \s-1MD5\s0 format.  In order for the
\&\s-1MD5\s0 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:
.PP
.Vb 6
\&    $username = \*(Aqtest\*(Aq;
\&    $password = \*(Aq123\*(Aq;
\&    $encoded_password = \*(AqICy5YqxZB1uWSwcVLSNLcA\*(Aq;
\&    $sth = $dbh\->prepare(\*(AqSELECT count(*) FROM users WHERE username = ? AND password = ?\*(Aq;
\&    $sth\->execute($username, $encoded_password);
\&    # I we found a row, then the user credentials are valid and the user is logged in
.Ve
.PP
This is all automatically performed behind the scenes when you specify that a certain field
in the database is encoded.
.PP
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 \s-1DB.\s0  This means we
can not generate the crypted test password before we run the query against the database.
.PP
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:
.PP
.Vb 2
\&    CONSTRAINTS => { \*(Aqusername\*(Aq       => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq },
\&    COLUMNS     => { \*(Aqcrypt:password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq },
.Ve
.PP
And here is what will happen behind the scenes:
.PP
.Vb 8
\&    $username = \*(Aqtest\*(Aq;
\&    $password = \*(Aq123\*(Aq;
\&    $sth = $dbh\->prepare(\*(AqSELECT password FROM users WHERE username = ?\*(Aq;
\&    $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
\&    }
.Ve
.PP
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 \s-1ENCODED PASSWORDS\s0 in the CGI::Application::Plugin::Authentication::Driver
docs.
.SH "EXAMPLE"
.IX Header "EXAMPLE"
.Vb 10
\& # using multiple tables
\& #  Here we check three credentials (user, password and domain) across
\& #  two separate tables.
\& _\|_PACKAGE_\|_\->authen\->config(
\&     DRIVER => [ \*(AqDBI\*(Aq,
\&         # the handle comes from $self\->dbh, via the "DBH" plugin. 
\&         TABLES      => [\*(Aquser\*(Aq, \*(Aqdomain\*(Aq],
\&         JOIN_ON     => \*(Aquser.domainid = domain.id\*(Aq,
\&         CONSTRAINTS => {
\&             \*(Aquser.name\*(Aq     => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&             \*(Aquser.password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq,
\&             \*(Aqdomain.name\*(Aq   => \*(Aq_\|_CREDENTIAL_3_\|_\*(Aq
\&         }
\&     ],
\& );
\&
\&  \- 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 => [ \*(AqDBI\*(Aq,
\&         DBH         => $dbh,   # provide your own DBI handle
\&         TABLE       => \*(Aquser\*(Aq,
\&         CONSTRAINTS => { \*(Aquser.name\*(Aq      => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq }
\&         COLUMNS     => { \*(Aqcrypt:password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq },
\&     ],
\& );
\&
\& \- or \-
\&
\& # extra constraints
\& #  Here we only check users where the \*(Aqactive\*(Aq column is true
\& _\|_PACKAGE_\|_\->authen\->config(
\&     DRIVER => [ \*(AqDBI\*(Aq,
\&         TABLE       => \*(Aquser\*(Aq,
\&         CONSTRAINTS => {
\&             \*(Aquser.name\*(Aq     => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&             \*(Aquser.password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq,
\&             \*(Aquser.active\*(Aq   => \*(Aqt\*(Aq
\&         },
\&     ],
\& );
\&
\& \- 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 => [ \*(AqDBI\*(Aq,
\&         TABLES      => [\*(Aquser U\*(Aq, \*(Aqdailycode D\*(Aq],
\&         JOIN_ON     => \*(AqU.userid = D.userid\*(Aq,
\&         CONSTRAINTS => {
\&             \*(AqU.name\*(Aq            => \*(Aq_\|_CREDENTIAL_1_\|_\*(Aq,
\&             \*(Aquc:md5_hex:D.code\*(Aq => \*(Aq_\|_CREDENTIAL_3_\|_\*(Aq,
\&             \*(AqD.date\*(Aq            => \*(Aqnow\*(Aq
\&         },
\&         COLUMNS     => {
\&             \*(Aqcrypt:U.password\*(Aq => \*(Aq_\|_CREDENTIAL_2_\|_\*(Aq
\&         },
\&     ],
\& );
.Ve
.SH "METHODS"
.IX Header "METHODS"
.SS "verify_credentials"
.IX Subsection "verify_credentials"
This method will test the provided credentials against the values found in the database,
according to the Driver configuration.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
CGI::Application::Plugin::Authentication::Driver, CGI::Application::Plugin::Authentication, \fBperl\fR\|(1)
.SH "LICENCE AND COPYRIGHT"
.IX Header "LICENCE AND COPYRIGHT"
Copyright (c) 2005, SiteSuite. All rights reserved.
.PP
This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
.SH "DISCLAIMER OF WARRANTY"
.IX Header "DISCLAIMER OF WARRANTY"
\&\s-1BECAUSE 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 \*(L"AS IS\*(R" 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.\s0
.PP
\&\s-1IN 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\s0 (\s-1INCLUDING 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\s0), \s-1EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.\s0