Gossamer Forum
Home : Products : DBMan SQL : Discussion :

upgrade from dbman to dbmansql 1

(Page 1 of 2)
> >
Quote Reply
upgrade from dbman to dbmansql 1
I've just purchased DBManSQL version 1 in hopes of being able to get all the mods that I currently use for DBMan to work the same with the SQL version. But, that doesn't seem to be the case. These are the mods I currently have working on DBMan:

Archive Records

Automatic view/modify search

Multiple file upload (I've managed to get this to upload the files, but it doesn't work when modifying.)

Private mailer

Secure password lookup

Send mail after record added (This appears to work without any mods)

Validate records

Who's online

Has anyone successfully modified these mods to work with the SQL version? I desperately need some help with this. A member of my website donated the money to get the SQL version, and I'd hate to have it go to waste because I'm not familiar enough with Perl and SQL to get it working.

Thanks so much in advance!

Shannon


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Shannon,

I'm in the same situation. Hoping to get DBManSQL working "quickly" and then go to version two with rewrites.

I'm finding the same thing, I have many mods and many config files. All my questions so far have gone unanswered. So, I'm truding through the code one line at a time.

First - make sure you have a working version of DBManSQL in it's default version on your website (This makes sure your site is ok with the code that DBman supplies which should work)

Then, I took each of the new files, .cfg, .cgi, .pl etc and compared them to my highly modified versions.

I have a little shareware program called Compare It! which lets you put the two files on screen side by side and see the differences as well as moving test from one file to the other.

Anyway, I tried to merge these two files and couldn't,

Now I'm moving my mod data over one line at a time. Very Tedious! But I need to see what is causing DBManSQL to give me that Server performed an internal error message.

I think the auth.pl is pretty easy to match to the SQL version, the .cfg file if you only have one, should go quickly too, unfortunately I have 6 db's and 6 .cfg files and figuring that all out will take a while.

From what I've read, the html.pl file shouldn't need many changes (unless like me you have multiple ones of those as well -- I have 6)

and where I'm seeing the most differences (not just added code sections from the mods) is in the DB.CGI.

You aren't writing to flat files but to SQL tables so wherever there is a table read, write, open or close you will have changes to the code. Also calling fields is different look carefully for each of those differences.

I haven't found a faster way, yet. I hope this helps you and I hope someone else will post something more helpful for BOTH of us SOON!
Lynette
Hollister, Ca
Quote Reply
Re: [ltillner] upgrade from dbman to dbmansql 1 In reply to
Thanks for your reply Lynette.

It sounds like you've got it a little rougher than I do since I only have 1 cfg, cgi and pl file to work with. I was able to get one of my other databases converted successfully since I didn't have any mods on it. Even got all the data imported without any problems. So I do know that the sql version does work great! It's just getting it to work with my main database that's causing the headaches.

I know that writing to the tables is different and I see where the script tries to write to the flat file, but not sure how to get them to write to sql.

One example in the validate_records mod is:

open (DB, "<$db_file_name") or &cgierr("error in validate_records.
unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = <DB>;
close DB;


I know that it tries opening the flat file there, and it needs to query the sql database. But why is it opening it in the first place? There's another one further down in that mod that the only difference is in the 4th line :

if ($db_use_flock) { flock(DB, 2); }

Let me know if you figure out anything and I'll do the same here. Smile

Shannon


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Hmmm... Odd. I have to use the basic editor to post here since the advanced editor came up with an error and wouldn't display the text box..

Anyway, I finally got the Delete function to work correctly with the multiple file upload mod for DBMan SQL version 1. To get it to work, use JP's multi file upload mod. Follow her instructions until you get to the "sub delete_records" routine. Add this:

if ($db_upload) {
if (-e "$SAVE_DIRECTORY/$key/") {
opendir (GRAPHIC, "$SAVE_DIRECTORY/$key/") or &cgierr("unable to open directory in delete records: $SAVE_DIRECTORY/$data[$db_key_pos]. Reason: $!");
@files = readdir(GRAPHIC);
closedir (GRAPHIC);
foreach $file (@files) {
unlink ("$SAVE_DIRECTORY/$key/$file");
}
rmdir "$SAVE_DIRECTORY/$key/";
}
}

else { $output .= $line . "\n"; }

After:

$rc = $DBH->do($query);


I've still got to get the modify subroutine to work with the multi file upload, but I will post that here as well if I can get it to work right. Smile


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Modify is working now. Here is what I did:

In sub validate_record:

Make sure quotes are around "$SAVE_DIRECTORY/$newdirname" in this line:

else {
opendir (GRAPHIC, "$SAVE_DIRECTORY/$newdirname") or &cgierr("unable to open directory. Reason: $!");

I had a hell of a time with this since it wasn't in there originally.

Next in sub modify_record, replace:
($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid);

with:
($auth_user_field >= 0) and ($in{$auth_user_field} = $db_userid);

Replace everything between
"# Check to make sure the record passes the reg exp and null checks."
and:
"# Build the SET string to be used in the SQL query. We quote all input and int all"

with:

foreach $key (keys %in) {
if ($in{$key} eq 'delete') {
unlink "$SAVE_DIRECTORY/$in{$db_key}/$key";
}
}

$num_files=0;
if (-e "$SAVE_DIRECTORY/$in{$db_key}") {
opendir (GRAPHIC, "$SAVE_DIRECTORY/$in{$db_key}") or &cgierr("unable to open directory: $SAVE_DIRECTORY/$rec{$db_key}. Reason: $!");
@files = readdir(GRAPHIC);
closedir (GRAPHIC);
foreach $file (@files) {
next if ($file =~ /^\./); # Skip "." and ".." entries..
next if ($file =~ /^index/); # Skip index.htm type files..
++$num_files;
}
}

if ($num_files or $in{'file-to-upload-1'}) { $in{'Graphic'} = 'Yes'; }
else { $in{'Graphic'} = ''; }


$status = &validate_record; # Check to make sure the modifications are ok!

if (($status eq "ok") && ($in{'file-to-upload-1'})) { $status = &validate_upload; } #Validate Pictures

if ($status eq "ok") {


Should be good to go with those changes! Smile
This only took about 6 hours and 4 packs of cigs for this newbie to figure out.. lol (not kidding about the cigs!)

Guess I'm off to try getting the validate mod working.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Just when I thought everything was going good...

I just discovered that when I logged in to an account without admin permissions, I can't modify a record, it works fine when you have admin permissions. This is the error that I get when trying to modify a record without being admin:

CGI ERROR
==========================================
Error Message : Unable to query database. Reason: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(
ID >= 0 ) AND
. Query:
SELECT ID,Name,Playername,Location,Sex,Orientation,Relationship,Age,birthmonth,birthday,icq,aim,yahoo,msn,URL,profession,CorBBits,dressing,alchdrink,dipping,pillow,pets,music,band,movies,TV,car,toothpaste,foods,dessert,chill,visit,icecream,drink,bedtime,shoes,scent,sport,color,cartoon,handed,game,bum,pizza,candy,store,book,boxers,pcomments,comments,Email,Validated,userid,since,Graphic,cap1,cap2,cap3,cap4,cap5,cap6 FROM Items
WHERE
ID >= 0 (
ID >= 0 ) AND
userid = 'test1'

LIMIT 0, 25

Script Location : db.cgi
Perl Version : 5.008
Setup File : db.cfg
User ID : test1.67351

Form Variables
-------------------------------------------
ID : *
db : db
modify_form : 1
uid : test1.67351


Any ideas? I'm lost on this and getting the validate mod to work. *sighs* This is getting really frustrating and I'm about ready to just give up. Unsure


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
I ended up doing a search for the error in the db.cgi file and after some trial & error, found out that the error was coming from the sub query routine. I did some searching in the forums and discovered that 2 lines in the query routine needed to be changed and now it seems to work perfectly!

The lines you're looking for are here:

# If we are only allowed to mod/del/view our own records let's make
# sure we don't pull up anyone else's information.
if ((($type eq 'mod' and $auth_modify_own) or
($type eq 'del' and $auth_modify_own) or
($type eq 'view' and $auth_view_own)) and (!$per_admin)) {
$userid_q = $DBH->quote($db_userid);
$where .= qq! ($where) AND
$auth_user_field = $userid_q!;

Replace:

# $where .= qq! ($where) AND
# $auth_user_field = $userid_q!;

With:

$where = qq! ($where) AND
$auth_user_field = $userid_q!;

I found out that I could manaully make the validate work if I modified the record and changed the Validate field on the record to yes. Only problem with that is it doesn't email the person to let them know their record has been validated. The Validate field doesn't show up for anyone other than admin, so no worries about anyone else changing that. Still, it'd be nice if the validate mod worked, or I could figure out what needed to be changed to make it work. Unsure


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Will this thread help you with the email issue on validation?

http://gossamer-threads.com/perl/gforum/gforum.cgi?post=196674;sb=post_latest_reply;so=ASC;forum_view=forum_view_collapsed;;page=unread#unread
Lynette
Hollister, Ca
Quote Reply
Re: [ltillner] upgrade from dbman to dbmansql 1 In reply to
Emails don't seem to be an issue as I am getting them after I add a record without any problem.
I've also been playing with the secure password lookup mod and managed to get the mod to send the random password when I sign up for an account, but oddly enough, it didn't write it to the database, even though I was able to log in with the username and password that the system sent.
The problem I'm having with the validation mod is figuring out what I've got to change to either update the Validate field and send email, or delete record and send email.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
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.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
sub get_email

Follow JP's instructions for the html.pl part.

Add this subroutine:

sub get_email {
# --------------------------------------------------------
# Pulls the email address from the password file.


my ($message, $userid, $pw, $view, $add, $del, $mod, $admin, $email, $password, $found, $output, $pass, $query);

my $username_q = $DBH->quote($db_userid);

$query = qq!
SELECT * FROM $db_table_user
WHERE username = $username_q
!;
my $sth = $DBH->prepare($query);
$sth->execute();
if ($sth->rows) {
while (@data = $sth->fetchrow_array) {
$email = $data[2];
}
}

$sth->finish;
return $email;
}


Next up, change email mod.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
OK... I need some help on this one. I have been bashing my head on the keyboard trying to figure out how to get the change email mod to work, but unfortunately, all the bumps on my forehead (not to mention the key imprints) haven't helped me figure it out.
What's throwing me is I'm not sure what each of the original open and close commands are doing. I know they are for opening the flat file, but I'm not sure why, so I have no idea where to put the queries etc.
Any ideas on this?


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
I thought I might have a go at upgrading my dbman site to dbman sql 1 (or even 2) sometime, but reading this thread has sure put me off ....!

The mods that JPDeni wrote for the original dbman are so incredibly useful. It's a shame noone has managed to do the same for the sql versions. Maybe GT should get someone to do it!
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] upgrade from dbman to dbmansql 1 In reply to
Quote:
I thought I might have a go at upgrading my dbman site to dbman sql 1 (or even 2) sometime, but reading this thread has sure put me off ....!

Ya know.. I've gotta agree with ya. Doesn't seem like the lack of help would make for a good selling point for DBMan SQL. And since my website is non-profit, I can't afford the $100 per hour for GT to re-write the mods to work with the SQL version.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [timbo] upgrade from dbman to dbmansql 1 In reply to
I've got to agree too. It's sad the way the Web world has become sooo commercialized that here is no longer help for the "little guys"

I am struggling with my conversion from DBMan to DBManSQL version 2 also. The user's manual is less than helpful in the areas I need help. Just tell me what the differences are man. that my variables of $something need to go to <%someth%>

It would have seemed that someone would have wanted to upgrade from DBMan to DBManSQL and need to know the blasted syntax changes without all the try and retry until you finally guess it right!

Oh well, live and learn.

Lynette
Lynette
Hollister, Ca
Quote Reply
Re: [ltillner] upgrade from dbman to dbmansql 1 In reply to
Modified sub signup (for the secure password lookup mod).

I noticed a few problems with the signup subroutine:

1. Emails were sent out with username & generated passwords, no matter what.

2. The previous mod didn't check for duplicate emails.

3. Logging in with username not on file logged you in without any permissions. The fix for that can be found by searching the forums for "login" (should be the first one that comes up). There's a couple of things to change in the auth.pl file.

Replace original sub signup routine with this:

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


NOTE: I wasn't sure how to get it to work to show either username or e-mail address already exsists, so at this time it displays this message:

"Username or email address already exists. Please try another."

There's probably something that can be done different with this:

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.";
}

This seems to work for checking for duplicates for either username or email, so I guess as long as it's doing that, then no worries.

I still plan on working on the "change email", "change password", "private mailer" and "Validate Records" mods. Maybe eventually these mods can be added somewhere so it'll be easier to find the changes. Wink *hint hint*


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)

Last edited by:

shann123: Mar 2, 2004, 3:25 AM
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Since no one else has done it, I've thrown together a page for these mods. Hopefully that will make it easier to add these mods than searching through this Board.

The URL is http://dbmansqlmods.rainbowroomies.com

This is on a subdomain of the website I use DBMan. Please do not create accounts at the regular part of the website.

For a working copy of some of these mods, see http://www.rainbowroomies.com/cgi-bin/dbmansql/db.cgi. Feel free to create accounts, add a record etc.

You may log into the admin account with "admin" for username and password.

This is fully functional copy for demonstrating the mods that I converted to SQL. I have disabled the links to the mods that are not working, but you will be able to see the multiple upload mod, the get email and the basic mod of the secure lookup.

(Do not ask for the full script, I had to buy it and will not give or sell it to anyone)


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Mod finished for the Change Email address part of the Secure password lookup mod. See link below.
Shannon


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
sub lookup {
#------------------

Mod finished for the basic part of the Secure Password Lookup mod.
This mod allows members to have a new password sent to them if they forget it.
It's probably a little clunky, but it does the job. See link below for the mod.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
sub change_password {
#---------------------------

Mod finished! I cut out the original password field from the form and replaced it with their username displayed and highlighted.

Try it here: DBMan SQL version 1 demo

Please do not change the admin password.
Test with username & password "demo". If password does not work, log with "admin" for username and password and change the demo password back to demo in the admin area.

For the mods, click on the link below. (go to the secure password lookup mod)


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)

Last edited by:

shann123: Mar 4, 2004, 4:39 AM
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
sub validate_records {

Mod finished! Once again this took a couple of days of trial & error to get it to work right, but it's working flawlessly!
Next I'm going to try to get the archive mod to work since that is another big one that I use. If I can get it to work, I may add the option to archive to the validate routine. Depends on how hard that's going to be to get it to work. Tongue


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Private Mailer mod finished! There were a few things that needed to be changed, but it is working correctly (as far as the few records I've tested it on goes anyway). For the full mod, see link below.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
Archive Records is ready to go now!


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
I've got a few other mods finished and listed on the dbsqlmods page and have also made a few fixes for some errors that didn't come up during my initial testing. One of them was with the multiple uploads mod. The mod didn't return any errors if the image file was not a valid format or was too large when adding a record (worked ok if they were modifying their record). Fix is on the dbmansql page.

I've tried working with the who's online mod, but so far all I've been able to do was to get it to list 1 of the people that are logged in. Not sure what to do about that yet.

Also, since I haven't been able to find a mod to
change usernames in both the user table and the record, I'm planning on trying to write a mod that will do that. I'll post here if I can get something like that to work.


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)

Last edited by:

shann123: Mar 10, 2004, 10:25 AM
Quote Reply
Re: [shann123] upgrade from dbman to dbmansql 1 In reply to
New mod available!

I've managed to get a mod working to change the username in an account and record.

This works through the admin page by selecting a username, then entering a new username and checking a checkbox next to the new username. It then changes the username in the table_user and in a record with a matching email address. (Works best if you are using the secure password lookup mod along with the get_email part of the secure password lookup.
If the email address isn't in the records, it will not change a userid for the user's record.

This mod could be wrote for difference choices easily. Choices could be: change username in record only, change username in account only. If interesting in those choices, let me know and I'll add that.

This is a very simple mod to add to DBMan SQL v1. Mod is available at dbmansqlmods (link below).
Shannon


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
> >