Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Re: [shann123] upgrade from dbman to dbmansql 1

Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
I've got the basic part of the Secure password lookup mod working now. Still to come is the: Get email (there is something in the forums for that, so it should be pretty easy), Change Email and Change Password part of the mod (See JPDeni's mods).

For the Basic Mod of the secure password lookup:

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 if you do.

Follow JP's instructions down to "Replace subroutine" of sub admin_display {.

Replace with this version:

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~<select name="username"><option> </option>~;
while (@data = $sth->fetchrow_array) {
if ($in{'inquire'} and ($in{'username'} eq $data[0])) {
$user_list .= qq~<option value="$data[0]" SELECTED>$data[0]</option>\n~;
$perm = qq|
View <input type=checkbox name="per_view" value="1" |; ($data[3] and $perm .= "CHECKED"); $perm .= qq|>
Add <input type=checkbox name="per_add" value="1" |; ($data[4] and $perm .= "CHECKED"); $perm .= qq|>
Delete <input type=checkbox name="per_del" value="1" |; ($data[5] and $perm .= "CHECKED"); $perm .= qq|>
Modify <input type=checkbox name="per_mod" value="1" |; ($data[6] and $perm .= "CHECKED"); $perm .= qq|>
Admin <input type=checkbox name="per_admin" value="1" |; ($data[7] and $perm .= "CHECKED"); $perm .= qq|>|;
$password = $data[1];
$email = $data[2];
}
else {
$user_list .= qq~<option value="$data[0]">$data[0]</option>\n~;
}
}
$user_list .= "</select>";
# Build the permissions list if we haven't inquired in someone.
if (!$perm) {
$perm = qq|
View <input type=checkbox name="per_view" value="1" |; ($auth_default_perm[0] and $perm .= "CHECKED"); $perm .= qq|>
Add <input type=checkbox name="per_add" value="1" |; ($auth_default_perm[1] and $perm .= "CHECKED"); $perm .= qq|>
Delete <input type=checkbox name="per_del" value="1" |; ($auth_default_perm[2] and $perm .= "CHECKED"); $perm .= qq|>
Modify <input type=checkbox name="per_mod" value="1" |; ($auth_default_perm[3] and $perm .= "CHECKED"); $perm .= qq|>
Admin <input type=checkbox name="per_admin" value="1" |; ($auth_default_perm[4] and $perm .= "CHECKED"); $perm .= qq|>|;
}
&html_admin_display ($message, $user_list, $password, $perm, $email);
}


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 1 FROM $db_table_user
WHERE username = $username_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 $username_q 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.");

}
$sth->finish;

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);

$message ?
&html_signup_form ($message) :
&html_signup_success();
}

Follow instructions for the HTML.pl subroutines on JP's site. I'll work on the rest of the mods and get them posted here. Smile


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Subject Author Views Date
Thread upgrade from dbman to dbmansql 1 shann123 12279 Jan 19, 2004, 5:06 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
ltillner 12063 Jan 22, 2004, 1:37 PM
Thread Re: [ltillner] upgrade from dbman to dbmansql 1
shann123 12119 Jan 22, 2004, 2:18 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12076 Jan 23, 2004, 6:08 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12097 Jan 24, 2004, 6:52 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12053 Jan 24, 2004, 7:08 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12051 Jan 25, 2004, 4:50 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
ltillner 12078 Jan 26, 2004, 8:37 AM
Thread Re: [ltillner] upgrade from dbman to dbmansql 1
shann123 12045 Jan 26, 2004, 1:08 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12056 Jan 27, 2004, 4:58 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12039 Jan 27, 2004, 6:48 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 12031 Jan 30, 2004, 10:06 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
timbo 12001 Feb 5, 2004, 11:13 AM
Post Re: [timbo] upgrade from dbman to dbmansql 1
shann123 11999 Feb 8, 2004, 12:10 AM
Thread Re: [timbo] upgrade from dbman to dbmansql 1
ltillner 11991 Feb 10, 2004, 11:24 AM
Thread Re: [ltillner] upgrade from dbman to dbmansql 1
shann123 11897 Mar 2, 2004, 3:21 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11906 Mar 2, 2004, 5:54 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11879 Mar 3, 2004, 2:23 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11850 Mar 3, 2004, 6:35 PM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11866 Mar 4, 2004, 4:36 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11862 Mar 6, 2004, 8:17 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11843 Mar 7, 2004, 7:20 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11869 Mar 8, 2004, 4:37 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11856 Mar 10, 2004, 10:24 AM
Thread Re: [shann123] upgrade from dbman to dbmansql 1
shann123 11822 Mar 11, 2004, 11:59 AM
Post Re: [shann123] upgrade from dbman to dbmansql 1
shann123 1697 Mar 16, 2004, 5:44 AM