###############################################################################
#### S E C U R E P A S S W O R D L O O K U P ####
#### ####
#### Written by: JPDeni ####
#### Modified: 13 Jun 2000 ####
#### Modified for DBMan SQL version 1 by ####
#### Shannon Geiger ####
#### ####
#### What it does-- ####
#### This is the most secure password lookup modification I can come up ####
#### with. ####
#### ####
#### When a user signs up for an account, he enters his desired username ####
#### and email address. The script sends a random password to the user at ####
#### the email address he entered. This guarantees that email addresses are####
#### valid. ####
#### ####
#### Once the user receives his password by email, he may log in. ####
#### ####
#### If a user loses his password, he enters his email address. The script ####
#### looks for the email address in the password file, generates a new ####
#### random password and sends an email with the user's userid and new ####
#### password. ####
#### ####
#### The password generation routine creates an 8-character "word" which ####
#### consists of 4 consonant/vowel pairs. There are 100 million possible ####
#### combinations of letters. ####
####
#### Be certain that you also choose the appropriate html subroutines
#### for the mod.
#### ####
#### Also available are optional subroutines-- ####
#### get_email ####
#### gets email address from the password file when a record is added ####
#### change_email ####
#### allows user to change his email address in the password file ####
#### also (optionally) changes the email address in the user's record(s)####
#### Note that if a user changes his email address, a new password will ####
#### be sent to him to verify that the address is valid. ####
#### change_password ####
#### allows user to change his password ####
###############################################################################
In your SQL database:
Insert the "Email" field after the "password" field in your SQL database. You could add it at the end, but you'll need to change a few things in these mods if you do.
Make the Email field "VARCHAR" 255 characters and "Not Null".
###############################################################################
# script: db.cfg #
# #
# add new lines #
# #
# Where to put it-- #
# after #
# # Full path and file name of the html routines. #
# require $db_script_path . "/html.pl"; #
###############################################################################
# Full path to sendmail on your system
# Be sure to leave the | character at the beginning!!!
$mailprog = "|/usr/lib/sendmail -t";
# Your email address
$admin_email = 'you@yourserver.com';
###############################################################################
# script: db.cgi #
# sub main #
# #
# add lines #
# #
# Where to add them -- #
# after #
# # If we allow users to signup, and they want to, go to the signup form. #
# elsif ($auth_signup and $in{'signup_form'}) { #
# &html_signup_form; #
# } #
# elsif ($auth_signup and $in{'signup'}) { #
# &signup; #
# } #
###############################################################################
#### Following two lines added for secure_password_lookup mod
elsif ($in{'lookup_form'}) { &html_lookup_form; }
elsif ($in{'lookup'}) { &lookup; }
###############################################################################
# script: db.cgi #
# sub admin_display #
# #
# replace subroutine #
###############################################################################
sub admin_display {
# --------------------------------------------------------
# This displays the current user list.
#
my ($sth, $rc, $query);
my ($insert_names, $insert_values, $message, $username_q, $update, @lines, $line);
# Let's first see if we have anything to do.
if ($in{'new_username'}) {
$insert_names = $insert_values = "";
$in{'username'} = $in{'new_username'};
if (($in{'username'} =~ /^[\w\d]+$/) and (length($in{'username'}) < 12)) {
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
$in{'password'} = crypt($in{'password'}, join '', @salt_chars[rand 64, rand 64]);
if ($in{'email'} eq $email) {
$message .= "email address already exists.";
# last CASE;
}
foreach (qw!username password email per_view per_add per_del per_mod per_admin!) {
$insert_names .= "$_,";
$insert_values .= $DBH->quote($in{$_}) . ",";
}
chop ($insert_names); chop ($insert_values);
$query = qq!
INSERT INTO $db_table_user ($insert_names)
VALUES ($insert_values)
!;
$rc = $DBH->do($query);
$rc ?
($message = "User: $in{'new_username'} created.") :
($message = "Error adding user: $in{'new_username'}. Reason: $DBI::errstr");
}
else {
$message = "Invalid username: '$in{'username'}'. Must only contain letters and numbers and be less then 12 characters.";
}
}
elsif ($in{'delete'}) {
if ($in{'username'}) {
$username_q = $DBH->quote($in{'username'});
$query = qq!
DELETE FROM $db_table_user
WHERE username = $username_q
!;
$rc = $DBH->do($query);
$rc ?
($message = "User: $in{'username'} deleted.") :
($message = "Error deleting user: $in{'username'}. Reason: $DBI::errstr");
}
else {
$message = "No username specified!";
}
}
elsif ($in{'username'} && !$in{'inquire'}) {
$username_q = $DBH->quote($in{'username'});
if (($in{'email'} eq $email) && ($in{'username'} ne $userid)) {
$message .= "email address already exists.";
}
foreach (qw!per_view per_add per_del per_mod per_admin!) {
$update .= $_ . "=" . $DBH->quote($in{$_}) . ",";
}
chop ($update);
$query = qq!
SELECT password FROM $db_table_user
WHERE username = $username_q
!;
my $sth = $DBH->prepare($query);
$sth->execute();
if ($sth->rows) {
my ($orig_pass) = $sth->fetchrow_array();
$orig_pass =~ s/^\s*(\S*)\s*$/$1/;
$in{password} =~ s/^\s*(\S*)\s*$/$1/;
if ($orig_pass ne $in{password}) {
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
$in{'password'} = crypt($in{'password'}, join '', @salt_chars[rand 64, rand 64]);
}
$query = qq!
UPDATE $db_table_user SET $update, password='$in{'password'}', email='$in{'email'}'
WHERE username = $username_q
!;
$rc = $DBH->do($query);
$rc ?
($message = "User: $in{'username'} updated.") :
($message = "Error updating user: $in{'username'}. Reason: $DBI::errstr");
}
else {
$message = "Error, user $username_q not found!";
}
}
else {}
# Now let's load the list of users.
$query = qq!
SELECT username, password, Email, per_view, per_add, per_del, per_mod, per_admin FROM $db_table_user
ORDER BY username
!;
$sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
# If we are inquiring, let's look for the specified user.
# my (@data, $user_list, $perm);
my (@data, $user_list, $perm, $password, $email);
$user_list = qq~";
# Build the permissions list if we haven't inquired in someone.
if (!$perm) {
$perm = qq|
View
Add
Delete
Modify
Admin |;
}
&html_admin_display ($message, $user_list, $password, $perm, $email);
}
###############################################################################
# script: db.cgi #
# sub signup #
# #
# replace subroutine #
# #
###############################################################################
sub signup {
# --------------------------------------------------------
# Allows a user to sign up without admin approval. Must have $auth_signup = 1
# set. The user gets @default_permissions.
#
my ($message,$userid, $pw, $view, $add, $del, $mod, $admin, $email, $password);
# Check to make sure userid is ok, pw ok, and userid is unique.
unless ((length($in{'userid'}) >= 3) and (length($in{'userid'}) <= 20) and ($in{'userid'} =~ /^[a-zA-Z0-9]+$/)) {
$message = "Invalid userid: $in{'userid'}. Must only contain only letters and be less then 20 and greater then 3 characters.";
}
unless ($in{'email'} =~ /.+\@.+\..+/) {
$message = "Invalid email address format: '$in{'email'}'.";
}
if ($message) {
&html_signup_form($message);
return;
}
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
$in{'pw'} = crypt($in{'pw'}, join '', @salt_chars[rand 64, rand 64]);
my $username_q = $DBH->quote($in{'userid'});
$in{'pw'} = &generate_password;
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
my $salt = join '', @salt_chars[rand 64, rand 64];
my $encrypted = crypt($in{'pw'}, $salt);
my $password_q = $DBH->quote($encrypted);
my $email_q = $DBH->quote($in{'email'});
my $permission = join (",", @auth_signup_permissions);
$query = qq!
SELECT * FROM $db_table_user
WHERE username = $username_q OR Email = $email_q
!;
my $sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
if ($sth->rows) {
$message = "Username or email address already exists. Please try another.";
}
else {
$query = qq!
INSERT INTO $db_table_user (username, password, Email, per_view, per_add, per_del, per_mod, per_admin)
VALUES ($username_q, $password_q, $email_q, $permission)
!;
$DBH->do ($query) or ($message = "Username $username_q already exists. Please try another.");
open (MAIL, "$mailprog") || &cgierr("Can't start mail program");
print MAIL "To: $in{'email'}\n";
print MAIL "From: $admin_email\n";
print MAIL "Subject: $html_title Account Created\n\n";
print MAIL "-" x 75 . "\n\n";
print MAIL "Your account at $html_title has been created.\n\n";
print MAIL "Your $html_title User ID is: $in{'userid'}\n";
print MAIL "Your $html_title password is: $in{'pw'}\n\n";
print MAIL "Please keep this email for future reference.\n\n";
print MAIL "To log on, go to\n\n";
print MAIL "$db_script_url?db=$db_setup\n";
print MAIL "and enter your User ID and password.\n\n";
print MAIL "Please contact $html_title support at: $admin_email\n";
print MAIL "if you have any questions.\n\n";
close (MAIL);
}
$sth->finish;
$message ?
&html_signup_form ($message) :
&html_signup_success();
}
###############################################################################
# script: db.cgi #
# sub lookup #
# #
# new subroutine #
# NOTE: This will change the password for ANY email address entered if that #
# Email address matches one that is already in the user table. #
# I think the original mod for the flat file version did the same thing. #
###############################################################################
sub lookup {
# --------------------------------------------------------
# This subroutine added for the secure_password_lookup mod
my ($query, $uid, $sth, $orig_password, $crypt_pass, $rc, $update, $message, $userid, $pw, $view, $add, $del, $mod, $admin, $email, $password, $found, $output, $pass);
# Check to make sure email is ok
unless ($in{'email'} =~ /.+\@.+\..+/) {
$message = "Invalid email address format.";
}
my $email_q = $DBH->quote($in{'email'});
$query = qq!
SELECT * FROM $db_table_user
WHERE Email = $email_q
!;
my $sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
if ($sth->rows) {
$in{'username'} = &get_username2;
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
$in{'pw'} = crypt($in{'pw'}, join '', @salt_chars[rand 64, rand 64]);
$in{'pw'} = &generate_password;
my @salt_chars = ('A' .. 'Z', 0 .. 9, 'a' .. 'z', '.', '/');
my $salt = join '', @salt_chars[rand 64, rand 64];
my $encrypted = crypt($in{'pw'}, $salt);
my $password_q = $DBH->quote($encrypted);
my $email_q = $DBH->quote($in{'email'});
open (MAIL, "$mailprog") || &cgierr("Can't start mail program");
print MAIL "To: $in{'email'}\n";
print MAIL "From: $admin_email\n";
print MAIL "Subject: $html_title New Password\n\n";
print MAIL "-" x 75 . "\n\n";
print MAIL "Here is your new $html_title password.\n\n";
print MAIL "Your $html_title User ID is: $in{'username'}\n";
print MAIL "Your $html_title password is: $in{'pw'}\n\n";
print MAIL "Please keep this email for future reference.\n\n";
print MAIL "To log on, go to\n\n";
print MAIL "$db_script_url?db=$db_setup\n";
print MAIL "and enter your User ID and password.\n\n";
print MAIL "Please contact $html_title support at: $admin_email\n";
print MAIL "if you have any questions.\n\n";
close (MAIL);
&html_lookup_success;
$query = qq!
UPDATE $db_table_user SET password=$password_q
WHERE Email = $email_q
!;
$rc = $DBH->do($query);
$rc ?
($message = "Password sent.") :
($message = "Error updating Password. Reason: $DBI::errstr");
}
else {
$message = "Error, email address $email_q not found!";
&html_lookup_form ("The email address you entered: $in{'email'}, was not found in the database.");
return;
}
}
###############################################################################
# script: db.cgi #
# sub get_username2 #
# #
# new subroutine #
# Required for the sub_lookup mod to grab username for new password email. #
# #
# What it does -- #
# Pulls the email address from the user table. This was the only way I could #
# figure out how to get the username into the new password email. #
###############################################################################
sub get_username2 {
# --------------------------------------------------------
# Pulls the username from the user_table for including in new password email.
my ($message, $userid, $pw, $view, $add, $del, $mod, $admin, $email, $password, $found, $output, $pass, $query);
my $email_q = $DBH->quote($in{'email'});
$query = qq!
SELECT * FROM $db_table_user
WHERE Email = $email_q
!;
my $sth = $DBH->prepare($query);
$sth->execute();
if ($sth->rows) {
while (@data = $sth->fetchrow_array) {
$username = $data[0];
}
}
$sth->finish;
return $username;
}
###############################################################################
# script: db.cgi #
# sub generate_password #
# #
# new subroutine #
# #
# What it does -- #
# Generates an 8-character password, consisting of 4 two-letter "syllables" #
###############################################################################
sub generate_password {
# --------------------------------------------------------
#### Following subroutine added for secure_password_lookup mod
my (@c, @v, $password);
srand( time() ^ ($$ + ($$ << 15)) ); # Seed Random Number
@c = split(/ */, "bcdfghjklmnprstvwxyz");
@v = split(/ */, "aeiou");
for ($i=1; $i<=4; ++$i) {
$password .= $c[int(rand(20))] . $v[int(rand(5))];
}
return $password;
}