1CGI::Application::PlugiUCnsG:eI:r:A:uCAtophnpetlnriticibacutatiteoidno:nP::eP:rlDlurgiDivonec:ru::mA:euDntBthIae(tn3it)oincation::Driver::DBI(3)
2
6 CGI::Application::Plugin::Authentication::Driver::DBI - DBI
7 Authentication driver
8
10 use base qw(CGI::Application);
11 use CGI::Application::Plugin::Authentication;
12 __PACKAGE__->authen->config(
14 DRIVER => [ 'DBI',
15 DBH => $self->dbh,
16 TABLE => 'user',
17 CONSTRAINTS => {
18 'user.name' => '__CREDENTIAL_1__',
19 'MD5:user.password' => '__CREDENTIAL_2__'
20 },
21 ],
22 );
23
25 This Authentication driver uses the DBI module to allow you to
26 authenticate against any database for which there is a DBD module. You
27 can either provide an active database handle, or provide the parameters
28 necessary to connect to the database.
29 When describing the database structure, you need to specify some or all
31 of the following parameters: TABLE(S), JOIN_ON, COLUMNS, CONSTRAINTS,
32 ORDER_BY and LIMIT.
33 DBH
35 The DBI database handle to use. Defaults to "$self-"dbh()>, which is
36 provided and configured through CGI::Application::Plugin::DBH
37 TABLE(S) (required)
39 Provide either a single table name, or an array of table names. You
40 can give the table names aliases which can be referenced in later
41 columns.
42 TABLE => 'users',
44 - or -
46 TABLES => ['users U', 'domains D'],
48 JOIN_ON (conditionally required)
50 If you have specified multiple tables, then you need to provide an SQL
51 expression that can be used to join those tables.
52 JOIN_ON => 'user.domainid = domain.id',
54 - or -
56 JOIN_ON => 'U.domainid = D.id',
58 COLUMNS (optional)
60 This is a hash of columns/values that should be pulled out of the
61 database and validated locally in perl. Most credentials can be
62 checked right in the database (example username = ?), but some
63 parameters may need to be tested locally in perl, so they must be
64 listed in the COLUMNS option. One example of a value that needs to be
65 tested in perl is a crypted password. In order to test a crypted
66 password, you need to take the entered password, and crypt it with the
67 salt of the already crypted password. But until we actually see the
68 password that is in the database, we will not know the value of the
69 salt that was used to encrypt the password. So we pull the value out
70 using COLUMNS, and the test will be performed automatically in perl.
71 Any value that matches __CREDENTIAL_n__ (where n is a number) will be
73 replaced with the corresponding credential that was entered by the
74 user. For an explanation of what the credentials are and where they
75 come from, see the section headed with CREDENTIALS in
76 CGI::Application::Plugin::Authentication.
77 COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
79 CONSTRAINTS (optional)
81 You will most likely always have some constraints to use. These
82 constraints will be added to the WHERE clause of the SQL query, and
83 will ideally reduce the number of returned rows to one.
84 Any value that matches __CREDENTIAL_n__ (where n is a number) will be
86 replaced with the corresponding credential that was entered by the
87 user. For an explanation of what the credentials are and where they
88 come from, see the section headed with CREDENTIALS in
89 CGI::Application::Plugin::Authentication.
90 CONSTRAINTS => {
92 'users.email' => '__CREDENTIAL_1__',
93 'MD5:users.passphrase' => '__CREDENTIAL_2__',
94 'users.active' => 1,
95 }
96 ORDER_BY (optional)
98 This option allows you to order the result set, in case the query
99 returns multiple rows.
100 ORDER_BY => 'created DESC'
102 Note: This option is only useful if you also specify the COLUMNS
104 option.
105 LIMIT (optional)
107 In some situations your query may return multiple rows when you only
108 want it to return one. For example if you insert and date a new row
109 instead of updating the existing row when the details for an account
110 change. In this case you want the newest record from the result set,
111 so it will be important to order the result set and limit it to return
112 only one row.
113 LIMIT => 1
115 Note: This option is only useful if you also specify the COLUMNS
117 option.
118
120 It is quite common to store passwords in a database in some form that
121 makes them hard (or virtually impossible) to guess. Most of the time
122 one way encryption techniques like Unix crypt or MD5 hashes are used to
123 store the password securely (I would recommend using MD5 or SHA1 over
124 Unix crypt). If you look at the examples listed above, you can see
125 that you can mark your columns with an encoding type. Here is another
126 example:
127 CONSTRAINTS => {
129 username => '__CREDENTIAL_1__',
130 'MD5:password' => '__CREDENTIAL_2__',
131 }
132 Here the password field is expected to be stored in the database in MD5
134 format. In order for the MD5 check to work for all databases, the
135 password will be encoded using perl, and then checked against the value
136 in the database. So in effect, the following will be done:
137 $username = 'test';
139 $password = '123';
140 $encoded_password = 'ICy5YqxZB1uWSwcVLSNLcA';
141 $sth = $dbh->prepare('SELECT count(*) FROM users WHERE username = ? AND password = ?';
142 $sth->execute($username, $encoded_password);
143 # I we found a row, then the user credentials are valid and the user is logged in
144 This is all automatically performed behind the scenes when you specify
146 that a certain field in the database is encoded.
147 We have to handle this slightly different when working with Unix crypt.
149 In order to crypt a password, you need to provide the crypt function
150 with a 2 character salt value. These are usually just generated
151 randomly, and when the value is crypted, the first two characters of
152 the resulting string will be the 2 salt characters. The problem comes
153 into play when you want to check a password against a crypted password.
154 You need to know the salt in order to properly test the password. But
155 in our case, the crypted password is in the DB. This means we can not
156 generate the crypted test password before we run the query against the
157 database.
158 So instead we pull the value of the crypted password out of the
160 database, and then perform the tests after the query, instead of
161 before. Here is an example:
162 CONSTRAINTS => { 'username' => '__CREDENTIAL_1__' },
164 COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
165 And here is what will happen behind the scenes:
167 $username = 'test';
169 $password = '123';
170 $sth = $dbh->prepare('SELECT password FROM users WHERE username = ?';
171 $sth->execute($username);
172 ($encoded_password) = $sth->fetchrow_array;
173 if ($encoded_password eq crypt($password, $encoded_password)) {
174 # The credentials are valid and the user is logged in
175 }
176 Again, this is all done automatically behind the scenes, but I've
178 included it here to illustrate how the queries are performed, and how
179 the comparisons are handled. For more information see the section
180 labelled ENCODED PASSWORDS in the
181 CGI::Application::Plugin::Authentication::Driver docs.
182
184 # using multiple tables
185 # Here we check three credentials (user, password and domain) across
186 # two separate tables.
187 __PACKAGE__->authen->config(
188 DRIVER => [ 'DBI',
189 # the handle comes from $self->dbh, via the "DBH" plugin.
190 TABLES => ['user', 'domain'],
191 JOIN_ON => 'user.domainid = domain.id',
192 CONSTRAINTS => {
193 'user.name' => '__CREDENTIAL_1__',
194 'user.password' => '__CREDENTIAL_2__',
195 'domain.name' => '__CREDENTIAL_3__'
196 }
197 ],
198 );
199 - or -
201 # using filtered fields
203 # Here the password column contains values that are encoded using unix crypt
204 # and since we need to know the salt in order to encrypt the password
205 # properly, we need to pull out the password, and check it locally
206 __PACKAGE__->authen->config(
207 DRIVER => [ 'DBI',
208 DBH => $dbh, # provide your own DBI handle
209 TABLE => 'user',
210 CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__' }
211 COLUMNS => { 'crypt:password' => '__CREDENTIAL_2__' },
212 ],
213 );
214 - or -
216 # extra constraints
218 # Here we only check users where the 'active' column is true
219 __PACKAGE__->authen->config(
220 DRIVER => [ 'DBI',
221 TABLE => 'user',
222 CONSTRAINTS => {
223 'user.name' => '__CREDENTIAL_1__',
224 'user.password' => '__CREDENTIAL_2__',
225 'user.active' => 't'
226 },
227 ],
228 );
229 - or -
231 # all of them combined
233 # Here the user is required to enter a username and password (which is
234 # crypted), and a daily code that changes every day (which is encoded using
235 # an MD5 hash hex format and stored in upper case).
236 __PACKAGE__->authen->config(
237 DRIVER => [ 'DBI',
238 TABLES => ['user U', 'dailycode D'],
239 JOIN_ON => 'U.userid = D.userid',
240 CONSTRAINTS => {
241 'U.name' => '__CREDENTIAL_1__',
242 'uc:md5_hex:D.code' => '__CREDENTIAL_3__',
243 'D.date' => 'now'
244 },
245 COLUMNS => {
246 'crypt:U.password' => '__CREDENTIAL_2__'
247 },
248 ],
249 );
250
252 verify_credentials
253 This method will test the provided credentials against the values found
254 in the database, according to the Driver configuration.
255
257 CGI::Application::Plugin::Authentication::Driver,
258 CGI::Application::Plugin::Authentication, perl(1)
259
261 Copyright (c) 2005, SiteSuite. All rights reserved.
262 This module is free software; you can redistribute it and/or modify it
264 under the same terms as Perl itself.
265
267 BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY
268 FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT
269 WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER
270 PARTIES PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND,
271 EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
272 WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE
273 ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH
274 YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL
275 NECESSARY SERVICING, REPAIR, OR CORRECTION.
276 IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING
278 WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR
279 REDISTRIBUTE THE SOFTWARE AS PERMITTED BY THE ABOVE LICENCE, BE LIABLE
280 TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL, OR
281 CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE
282 SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING
283 RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A
284 FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF
285 SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
286 DAMAGES.
287perl v5.28.1 CGI::Applica2t0i1o8n-:0:3P-l0u5gin::Authentication::Driver::DBI(3)