#!/usr/bin/perl
#
# userbase.cgi
#
######################################################################
#
# DO NOT EDIT THIS FILE unless absolutely necessary; in most cases
# you should be editing userbase_prefs.cgi instead.
# 
######################################################################
#
# This program is the copyrighted work of Encodable Industries.
# Redistribution is prohibited, and copying is only permitted for
# backup purposes.  You are free to modify the program for your
# own use, but you may not distribute any modified copies of it.
#
# Use of this program requires a one-time license fee.  You can
# obtain a license here:
#
#	http://encodable.com/userbase/
#
# This software comes with no warranty.  The author and many other
# people have found it to be useful, and it is our hope that you
# find it useful as well, but it comes with no guarantees.  Under
# no circumstances shall Encodable Industries be held liable in 
# any situation arising from your use of this program.  We are
# generally happy to provide support to all our users, but we can
# make no guarantee of support.
#
# For more information about this program, as well as for help
# and support, please visit the following pages:
#
# Homepage: http://encodable.com/userbase/
# Contact:  http://encodable.com/contact/


my $version = "2.11";
$ENV{PATH} = '/bin:/usr/bin';
delete @ENV{'IFS', 'CDPATH', 'ENV', 'BASH_ENV'};
($ENV{DOCUMENT_ROOT}) = ($ENV{DOCUMENT_ROOT} =~ /(.*)/); # untaint.
#$ENV{SCRIPT_NAME} = '/cgi-bin/userbase.cgi';
use lib './perlmodules';
use lib "$ENV{DOCUMENT_ROOT}/cgi-bin/perlmodules";
my (%PREF,%TEXT) = ();
use CGI::Carp 'fatalsToBrowser'; # uncomment to show errors in browser.

my $debuglog = undef; #open($debuglog, ">>ubdata/debuglog.ubtemp.log") or die_nice("couldn't open debuglog: $!\n"); flock $debuglog, 2; print $debuglog "\n\n";

use strict;
use DBI;
use Time::Local;

sub printd;
sub die_nice;

my $qs = '';





sub create_new_session_id
{
	my $username = shift;
	my $password = shift;
	my $id = offsettime() . $$ . $ENV{REMOTE_ADDR} . $ENV{HTTP_USER_AGENT} . $username . $password;

	#$id =~ s/[^\d]//g;
	#$id = substr($id,0,85);
	$id = enc_hash($id);
	return $id;
}


sub check_for_multiple_logins($)
{
	my ($userid) = @_;
	die_unless_numeric($userid,'userid');
	my $old_login_time = enc_sql_select("SELECT `loggedin` FROM `$PREF{user_table}` WHERE `id` = $userid;");
	if($old_login_time =~ /[1-9]/   &&   !login_session_expired($old_login_time))
	{
		if($PREF{prevent_multiple_simultaneous_logons_per_username} =~ /yes/i)
		{
			my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=emultlogin";
			enc_redirect($go);
		}
		else
		{
			my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `numusers`=IFNULL(`numusers`,0)+1 WHERE `id` = '$userid';");
			die_nice("Error: check_for_multiple_logins('$userid'): SQL returned '$success' instead of '1' while incrementing numusers column.") unless $success == 1;

			my $existing_session_id = enc_sql_select("SELECT `mrsession` FROM `$PREF{user_table}` WHERE `id` = '$userid';");
			return $existing_session_id;
		}
	}
}


sub log_user_into_db
{
	my ($userid, $my_session_id, $logintime, $restrict_ip) = @_;

	die_unless_numeric($userid,'userid');
	die_unless_numeric($logintime,'logintime');

	check_sessionid_for_sql_safeness($my_session_id);

	my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `loggedin` = $logintime, `mrsession` = '$my_session_id' WHERE `id` = '$userid';");
	die_nice("Error: log_user_into_db('$userid', '$my_session_id', '$logintime', '$restrict_ip'): SQL returned '$success' instead of '1' while logging user in.") unless $success == 1;

	if($restrict_ip)
	{
		my $ip = $ENV{REMOTE_ADDR};
		check_ip_for_sql_safeness($ip);

		unless(enc_sql_select("SELECT `ip` FROM `$PREF{user_table}` WHERE `id` = '$userid'") eq $ip)
		{
			my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `ip` = '$ip' WHERE `id` = '$userid';");
			die_nice("Error: log_user_into_db('$userid', '$my_session_id', '$logintime', '$restrict_ip'): SQL returned '$success' instead of '1' while setting user IP.") unless $success == 1;
		}
	}
	else
	{
		unless(enc_sql_select("SELECT `ip` FROM `$PREF{user_table}` WHERE `id` = '$userid'") eq '')
		{
			my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `ip` = '' WHERE `id` = '$userid';");
			die_nice("Error: log_user_into_db('$userid', '$my_session_id', '$logintime', '$restrict_ip'): SQL returned '$success' instead of '1' while clearing user IP.") unless $success == 1;
		}
	}
}


sub log_user_out_of_db
{
	my ($username, $my_session_id) = @_;

	check_username_for_sql_safeness($username);
	check_sessionid_for_sql_safeness($my_session_id) unless $my_session_id eq 'force';

	# It's possible (and probably not particularly uncommon) that a user logs in at one location, then leaves
	# that location and his session goes idle, and then he logs in at another location with the same account.  In
	# that case, a call to log_user_out_of_db() from the first location should not actually do the db logout, 
	# because the session does not belong to him anymore.  But note that this is not an error condition, so we
	# should just silently return.
	#
	my $session_id_in_db = enc_sql_select("SELECT `mrsession` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username');");

	if($my_session_id == $session_id_in_db   ||   $my_session_id eq 'force')
	{
		my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `loggedin` = 0 WHERE `username` = '$username';");
		die_nice("Error: log_user_out_of_db('$username', '$my_session_id'): SQL returned '$success' instead of '1' while setting loggedin to zero.") unless $success == 1;

		$success = enc_sql_update("UPDATE `$PREF{user_table}` SET `mrsession` = '' WHERE `username` = '$username';");
		die_nice("Error: log_user_out_of_db('$username', '$my_session_id'): SQL returned '$success' instead of '1' while setting mrsession to null.") unless $success == 1;

		my $numusers = enc_sql_select("SELECT `numusers` FROM `$PREF{user_table}` WHERE `username` = '$username';");
		if($numusers) # this check only required because of crappy old MySQL versions that fail to ever set numusers properly in the DB (??).
		{
			$success = enc_sql_update("UPDATE `$PREF{user_table}` SET `numusers` = 0 WHERE `username` = '$username';");
			die_nice("Error: log_user_out_of_db('$username', '$my_session_id'): SQL returned '$success' instead of '1' while setting numusers to zero.") unless $success == 1;
		}
	}
}


sub determine_default_login_destination
{
	my $ref = shift;
	my $go = ();
	if($qs =~ /(?:^|&)whence=(.+)/)
	{
		# don't URL-decode here because this is getting passed right
		# to the Location: header, which requires URL encoding.

		$go = $1;
		if($qs =~ /(?:^|&)encanchor=([^&]+)(?:&|$)/)
		{
			$go .= '#' . $1;
		}
	}
	else
	{
		$go = $ref ? $ref : "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		#my $us1 = $PREF{login_url};
		#my $us2 = $ENV{SCRIPT_NAME};
		#if($go =~ /($us1|$us2)\?.+/)
		#{
		#	# If the page we were on before was a login page with some
		#	# query-string, then just return to the login frontpage.
		#	$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		#}

		if($go =~ m!.*whence=(https?(://|%3a%2f%2f).+)!i)
		{
			$go = $1;
			enc_urldecode($go);
		}
		elsif($go =~ /log(ged)?out/)
		{
			# If the page we were on before was a logout page,
			# then just return to the login frontpage.
			$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		}
		elsif($go =~ /(action=verify|phase=\w+)/)
		{
			# If the page we were on before was one of various other
			# internal pages that don't make sense to redirect to,
			# then just return to the login frontpage.
			$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		}
		elsif($go !~ m!^https?://(www\.)?$ENV{HTTP_HOST}!)
		{
			# If the page we were on before was on an external site, then
			# obviously we don't want to redirect there, since we won't be
			# logged in there.  Again, just return to the login frontpage.
			$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		}
	}
	return $go;
}


sub prompt_for_login()
{
	printd "prompt_for_login()";
	# don't urldecode here, because it's just going right back onto the URL.
	my $whence = $qs =~ /(?:^|&)whence=(.+)/ ? "&whence=$1" : undef;
	if($qs =~ /(?:^|&)encanchor=([^&]+)(?:&|$)/)
	{
		$whence = "&encanchor=$1" . $whence;
	}

	my $rememberme_checkbox = $PREF{enable_rememberme_checkbox} =~ /yes/i && !$PREF{idle_timeout};
	my $restrictip_checkbox = $PREF{enable_ip_address_restriction} =~ /yes/i && $PREF{force_ip_address_restriction} !~ /yes/;

	my $message = '';
	if($qs =~ /(?:^|&)phasemsg=(.+?)(?:&|$)/) { $message = $1; enc_urldecode($message); $message = qq`<div id="formmessage">$message</div>\n`; }

	my $template = $qs =~ /format=mini/ ? $PREF{login_form_template__mini} : $PREF{login_form_template};
	$template =~ s/%%whence%%/$whence/g;
	$template =~ s!%%special_message%%!$message!g;
	$template =~ s!%%%if-rememberme%%%(.+)%%%end-rememberme%%%!$rememberme_checkbox ? $1 : ''!egs;
	$template =~ s!%%%if-restrictip%%%(.+)%%%end-restrictip%%%!$restrictip_checkbox ? $1 : ''!egs;
	$template = interpolate_userbase_variables($template);
	print $template;
}


sub user_has_addmember_rights
{
	return user_is_allowed_to($PREF{logged_in_userid}, 'create_new_accounts') || logged_in_user_is_subgroup_manager();
}


sub logged_in_user_is_part_of_a_subgroup()
{
	#foreach my $group (enc_sql_select("SELECT "))
	#{
	#	if($group =~ /.+$PREF{subgroup_groupname_suffix}$/i)
	#	{
	#		return enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE LOWER(`group`) = LOWER('$group') AND `members` REGEXP '(^|,)$PREF{logged_in_userid}(,|\$)'");
	#	}
	#}

	my $subgroup_groupname_suffix = $PREF{subgroup_groupname_suffix};
	sql_untaint($subgroup_groupname_suffix);
	exit_with_error("logged_in_user_is_part_of_a_subgroup() not SQL safe: \$subgroup_groupname_suffix ('$subgroup_groupname_suffix').") if not_sqlsafe($subgroup_groupname_suffix);

	die_unless_numeric($PREF{logged_in_userid}, '$PREF{logged_in_userid}');

	return enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE `group` REGEXP '.+$subgroup_groupname_suffix\$' AND `members` REGEXP '(^|,)$PREF{logged_in_userid}(,|\$)'");
}


sub logged_in_user_is_subgroup_manager
{
	foreach my $group (split(/\s*,\s*/, $PREF{groups_limited_to_subgroup_user_mgmt}))
	{
		return 1 if user_is_member_of_group($PREF{logged_in_userid}, $group);
	}
	return 0;
}


sub logged_in_subgroup_manager_owns_this_user($)
{
	my $userid_to_check = shift;
	return user_is_member_of_group($userid_to_check, "$PREF{logged_in_username}$PREF{subgroup_groupname_suffix}");
}


sub user_has_addadmin_rights
{
	return $PREF{admin_is_logged_in};
}


sub user_has_groupmod_rights
{
	return $PREF{admin_is_logged_in};
}


sub print_user_form
{
	my $mode = shift;
	my %vars = ();

	if($mode eq 'added_by_admin')
	{
		$PREF{on_page}			= 'adminadduser';
		$vars{title}			= $PREF{user_form_title___admin_adding_user};

		if(!user_has_addmember_rights()) { exit_with_error($TEXT{Access_denied_}); }
	
		$vars{target}			= 'action=commitadduser';
		$vars{forcepwchng}		= 1 if $PREF{enable_forced_password_change} =~ /yes/i;
	}
	elsif($mode eq 'user_signup')
	{
		$PREF{on_page}			= 'usersignup';
		$vars{title}			= $PREF{user_form_title___user_signing_up};

		unless($PREF{visitors_can_sign_up_for_their_own_accounts} =~ /yes/i) { exit_with_error($TEXT{This_feature_is_disabled_}); }
	
		$vars{target}			= 'action=commitadduser';
		$vars{forcepwchng}		= 0;
	}
	else # edit the user info instead.
	{
		$PREF{on_page} = 'edituser';
		$vars{title}			= $PREF{user_form_title___editing_account};

		$vars{user_id}			= shift;
		die_unless_numeric($vars{user_id}, 'userid (from print_user_form())');

		$vars{username}			= get_user_name($vars{user_id});

		if(!user_is_allowed_to($PREF{logged_in_userid}, 'edit_user_info', $vars{username})) { exit_with_error($TEXT{Access_denied_}); }

		$vars{target}			= "action=commitedituser";

		$vars{username_readonly}	= $PREF{usernames_are_immutable_once_created} =~ /no/i && user_is_allowed_to($PREF{logged_in_userid}, 'change_usernames', $vars{username}) ? '' : qq`readonly="readonly"`;

		$vars{realname}			= get_real_name($vars{user_id});
		$vars{email}			= get_email_address($vars{user_id});

		$vars{account_locked}		= enc_sql_select("SELECT `acct_locked` FROM `$PREF{user_table}` WHERE `id` = '$vars{user_id}';");
		$vars{account_disabled}		= enc_sql_select("SELECT `acct_disabled` FROM `$PREF{user_table}` WHERE `id` = '$vars{user_id}';");
		$vars{forcepwchng}		= enc_sql_select("SELECT `forcepwchng` FROM `$PREF{user_table}` WHERE `id` = '$vars{user_id}';") if $PREF{enable_forced_password_change} =~ /yes/i;
	}

	if($qs =~ /redo=true/)
	{
		while($qs =~ /(?:^|&)redo_(\w+)=([^&]*)/g)
		{
			my ($field,$value) = ($1,$2);
			enc_urldecode($value);
			$vars{$field} = $value if $value;
		}
	}

	start_html_output($vars{title});

	if($qs =~ /(?:^|&)phase=(\w+?)(?:&|$)/) { my $phase = $1; print qq`<div id="formmessage">` . get_message($phase) . qq`</div>\n`; }

	my $template = $PREF{user_form_template};


	my $show_grouplist = 1;
	$show_grouplist = 0 if $mode eq 'user_signup';
	$show_grouplist = 0 if logged_in_user_is_subgroup_manager();
	$show_grouplist = 0 if ($PREF{hide_groups_on_user_form_for_nonadmins} =~ /yes/i && !$PREF{admin_is_logged_in});
	my $grouplist = '';
	my ($grouplist_template) = ($template =~ m!%%%template:grouplist%%%(.+?)%%%end-template:grouplist%%%!gs);
	if($show_grouplist)
	{
		my $groups = get_groups_hash($vars{user_id});
		foreach my $group (sort keys %$groups)
		{
			next if ($group =~ /^$PREF{admin_group_name}$/i && !user_has_addadmin_rights());
			next if ($group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i); # don't show these (not even disabled); it just confuses people.

			my $checked = $$groups{$group}{is_member} ? qq`checked="checked"` : undef;

			# the checkboxes are disabled unless the user is an admin, i.e. only admins can change group memberships.
			my $disabled = '';
			$disabled = 'disabled="disabled"' if !$PREF{admin_is_logged_in};

			# Admins can see all groups here; non-admins can only see the groups they belong to.
			if($PREF{admin_is_logged_in} || $$groups{$group}{is_member})
			{
				my $gl_template = $grouplist_template;
				$gl_template =~ s!%%group%%!$group!gs;
				$gl_template =~ s!%%checked%%!$checked!gs;
				$gl_template =~ s!%%disabled%%!$disabled!gs;
				$grouplist .= $gl_template;
			}
		}
	}
	$template =~ s!%%%template:grouplist%%%(.+?)%%%end-template:grouplist%%%!$grouplist!gs;


	my $customfields = '';
	my ($customfields_template) = ($template =~ m!%%%template:customfields%%%(.+?)%%%end-template:customfields%%%!gs);
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{custom_field_table}`"))
	{
		my ($id,$fieldname,$fieldlabel,$datatype,$fieldtype,$fieldmax,$fieldposition,$mandatory,$limitallowedchars,$allowedchars,$allowedcharsmsg,$listitems,$enabled) = ();
		my $sth = $PREF{dbh}->prepare("SELECT id,fieldname,fieldlabel,datatype,fieldtype,fieldmax,fieldposition,mandatory,limitallowedchars,allowedchars,allowedcharsmsg,listitems,enabled FROM `$PREF{custom_field_table}` ORDER BY `fieldposition`");
		$sth->execute() or die_nice("$PREF{internal_appname}: Error: print_user_form(): $DBI::errstr\n");
		$sth->bind_columns(\$id,\$fieldname,\$fieldlabel,\$datatype,\$fieldtype,\$fieldmax,\$fieldposition,\$mandatory,\$limitallowedchars,\$allowedchars,\$allowedcharsmsg,\$listitems,\$enabled);
		while($sth->fetchrow_arrayref)
		{
			next unless ($enabled && db_column_exists($fieldname, $PREF{user_table}));

			my $value = enc_sql_select("SELECT `$fieldname` FROM `$PREF{user_table}` WHERE `id` = $vars{user_id}") if $vars{user_id};

			my $required = $mandatory ? 'required' : '';

			$fieldmax = '' unless $datatype eq 'varchar'; # workaround for SQL bug where '' gets stored as '0'.

			my $cf_template = $customfields_template;


			$cf_template =~ s!%%fieldname%%!$fieldname!gs;
			$cf_template =~ s!%%fieldlabel%%!$fieldlabel!gs;
			$cf_template =~ s!%%fieldmax%%!$fieldmax!gs;
			$cf_template =~ s!%%required%%!$required!gs;
			$cf_template =~ s!%%value%%!$value!gs;

			$cf_template =~ s!%%%if-freeformsingle%%%(.*?)%%%end-freeformsingle%%%!$fieldtype eq 'freeformsingle' ? $1 : ''!egs;
			$cf_template =~ s!%%%if-freeformmulti%%%(.*?)%%%end-freeformmulti%%%!$fieldtype eq 'freeformmulti' ? $1 : ''!egs;
			$cf_template =~ s!%%%if-dropdown%%%(.*?)%%%end-dropdown%%%!$fieldtype eq 'dropdown' ? $1 : ''!egs;
			$cf_template =~ s!%%%if-checkbox%%%(.*?)%%%end-checkbox%%%!$fieldtype eq 'checkbox' ? $1 : ''!egs;
			$cf_template =~ s!%%%if-radio%%%(.*?)%%%end-radio%%%!$fieldtype eq 'radio' ? $1 : ''!egs;


			if($fieldtype eq 'checkbox')
			{
				$cf_template =~ s!%%checkbox_checked%%!$value ? 'checked="checked"' : ''!egs;
			}


			my $dropdown = '';
			if($fieldtype eq 'dropdown')
			{
				my ($dropdown_template) = ($cf_template =~ m!%%%template:dropdown%%%(.+?)%%%end-template:dropdown%%%!gs);
				foreach my $option (split(/\n/, $listitems))
				{
					my $dd_template = $dropdown_template;
					my $selected = $option eq $value ? qq`selected="selected"` : '';
					$dd_template =~ s!%%dropdown_selected%%!$selected!gs;
					$dd_template =~ s!%%option%%!$option!gs;
					$dropdown .= $dd_template;
				}
			}
			$cf_template =~ s!%%%template:dropdown%%%(.+?)%%%end-template:dropdown%%%!$dropdown!gs;


			my $radio = '';
			if($fieldtype eq 'radio')
			{
				my $radio_i = 0;
				my ($radio_template) = ($cf_template =~ m!%%%template:radio%%%(.+?)%%%end-template:radio%%%!gs);
				foreach my $selection (split(/\n/, $listitems))
				{
					$radio_i++;
					my $rad_template = $radio_template;
					my $checked = $selection eq $value ? qq`checked="checked"` : '';
					$rad_template =~ s!%%radio_checked%%!$checked!gs;
					$rad_template =~ s!%%selection%%!$selection!gs;
					$rad_template =~ s!%%radio_i%%!$radio_i!gs;
					$radio .= $rad_template;
				}
			}
			$cf_template =~ s!%%%template:radio%%%(.+?)%%%end-template:radio%%%!$radio!gs;


			$customfields .= $cf_template;
		}
	}
	$template =~ s!%%%template:customfields%%%(.+?)%%%end-template:customfields%%%!$customfields!gs;


	$template =~ s!%%post_url%%!$ENV{SCRIPT_NAME}?$vars{target}!g;
	$template =~ s!%%hidden_signup_input%%!$mode eq 'user_signup' ? qq`<input type="hidden" name="user_signup" value="yes" />` : ''!eg;
	$template =~ s!%%emailformat%%!$PREF{usernames_must_be_email_addresses} =~ /yes/i ? 'emailformat' : ''!eg;
	$template =~ s!%%email_field_required%%!$PREF{email_field_required} =~ /yes/i ? 'required emailformat' : ''!eg;
	$template =~ s!%%new_password_required%%!$mode =~ /added_by_admin|user_signup/ ? 'required' : ''!eg;
	$template =~ s!%%account_locked_checked%%!$vars{account_locked} ? 'checked="checked"' : ''!eg;
	$template =~ s!%%account_disabled_checked%%!$vars{account_disabled} ? 'checked="checked"' : ''!eg;
	$template =~ s!%%forcepwchng_checked%%!$vars{forcepwchng} ? 'checked="checked"' : ''!eg;
	$template =~ s!%%paidacct(\d+)name%%!$PREF{"paid_account_type_${1}_groupname"}!g;
	$template =~ s!%%paidacct(\d+)desc%%!$PREF{"paid_account_type_${1}_groupdesc"}!g;
	$template =~ s!%%paidacct(\d+)cost%%!$PREF{"paid_account_type_${1}_cost"}!g;

	$template =~ s!%%%if-forcepwchng%%%(.*?)%%%end-forcepwchng%%%!force_pw_change($PREF{logged_in_userid}) ? $1 : ''!egs;
	$template =~ s!%%%if-use_builtin_realname_field%%%(.*?)%%%end-use_builtin_realname_field%%%!my $var = $1; $PREF{use_builtin_realname_field} =~ /yes/i ? $var : ''!egs;
	$template =~ s!%%%if-use_builtin_email_field%%%(.*?)%%%end-use_builtin_email_field%%%!my $var = $1; $PREF{use_builtin_email_field} =~ /yes/i ? $var : ''!egs;
	$template =~ s!%%%if-currentpass_needed%%%(.*?)%%%end-currentpass_needed%%%!$mode eq 'edit'   &&   logged_in_user_must_enter_current_password_to_change_password_for_user($vars{username}, $vars{user_id}) ? $1 : ''!egs;
	$template =~ s!%%%if-show_account_locks%%%(.*?)%%%end-show_account_locks%%%!$mode eq 'edit'   &&   $PREF{admin_is_logged_in} ? $1 : ''!egs;
	$template =~ s!%%%ifelse-lock_expires_automatically%%%(.*?)%%%else%%%(.*?)%%%endelse-lock_expires_automatically%%%!my ($var1,$var2)=($1,$2); $PREF{lock_expires_automatically} =~ /yes/i ? $var1 : $var2!egs;
	$template =~ s!%%%if-lock_expires_automatically%%%(.*?)%%%end-lock_expires_automatically%%%!my $var = $1; $PREF{lock_expires_automatically} =~ /yes/i ? $var : ''!egs;
	$template =~ s!%%%if-show_force_pwchng%%%(.*?)%%%end-show_force_pwchng%%%!my $var = $1; $PREF{admin_is_logged_in}   &&   $PREF{enable_forced_password_change} =~ /yes/i ? $var : ''!egs;
	$template =~ s!%%%if-show_terms_of_service%%%(.*?)%%%end-show_terms_of_service%%%!my $var = $1; $mode eq 'user_signup' && $PREF{user_must_agree_to_terms_on_signup} =~ /yes/i ? $var : ''!egs;
	$template =~ s!%%%if-show_grouplist%%%(.*?)%%%end-show_grouplist%%%!$show_grouplist ? $1 : ''!egs;
	$template =~ s!%%%if-show_customfields%%%(.*?)%%%end-show_customfields%%%!$customfields ? $1 : ''!egs;
	$template =~ s!%%%if-show_payment_options%%%(.*?)%%%end-show_payment_options%%%!my $var = $1; $mode eq 'user_signup' && $PREF{enable_paid_accounts} =~ /yes/i ? $var : ''!egs;

	$template =~ s!%%%if-user_signing_up%%%(.*?)%%%end-user_signing_up%%%!$mode eq 'user_signup' ? $1 : ''!egs;
	$template =~ s!%%%if-admin_adding_user%%%(.*?)%%%end-admin_adding_user%%%!$mode eq 'added_by_admin' ? $1 : ''!egs;
	$template =~ s!%%%ifelse-editing_account%%%(.*?)%%%else%%%(.*?)%%%endelse-editing_account%%%!$mode eq 'edit' ? $1 : $2!egs;
	$template =~ s!%%%if-editing_account%%%(.*?)%%%end-editing_account%%%!$mode eq 'edit' ? $1 : ''!egs;

	$template =~ s!%PREF{(.+?)}!$PREF{$1}!g;

	$template =~ s!%%(\w+)%%!$vars{$1}!g;
	$template =~ s!%%%if-(\w+)%%%(.*?)%%%end-\1%%%!!gs;

	print $template;

	finish_html_output();
}


sub logged_in_user_must_enter_current_password_to_change_password_for_user($$)
{
	my $username_of_target_user = shift;
	my $userid_of_target_user = shift;

	if(this_user_is_the_logged_in_admin($username_of_target_user))
	{
		# an admin is trying to change his own password, so
		# we DO require the current password.
		return 1;
	}
	elsif(logged_in_user_is_subgroup_manager()   &&   logged_in_subgroup_manager_owns_this_user($userid_of_target_user))
	{
		# a subgroup manager is trying to change the password of one of his
		# own users; he's effectively an admin for this purpose, so we DON'T
		# require the current password.
		return 0;
	}
	elsif($PREF{admin_is_logged_in})
	{
		# in general, an admin does NOT need the current password to change
		# a password.
		return 0;
	}
	else
	{
		# in general, a non-admin DOES need the current password to change
		# a password.
		return 1;
	}
}


sub print_group_form
{
	my $mode = shift;
	my %vars = ();

	if($mode eq 'add')
	{
		my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		if(!user_is_allowed_to($PREF{logged_in_userid}, 'create_new_groups')) { enc_redirect("$go?phase=eneedlogin"); }
	
		$vars{title}			= 'Add New Group';
		$vars{button_label}		= 'Add Group';
		$vars{target}			= 'action=commitaddgroup';
	}
	else # edit the group info instead.
	{
		$vars{group_id}			= shift;
		$vars{group}			= get_group_name($vars{group_id});

		my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
		if(!user_is_allowed_to($PREF{logged_in_userid}, 'edit_group_info')) { enc_redirect("$go?phase=eneedlogin"); }

		$vars{title}			= 'Edit Group';
		$vars{button_label}		= 'Modify Group';
		$vars{target}			= "action=commiteditgroup";

		$vars{groupname_readonly}	= $PREF{groupnames_are_immutable_once_created} =~ /no/i && user_is_allowed_to($PREF{logged_in_userid}, 'change_groupnames') ? undef : qq`readonly="readonly"`;
		$vars{groupdesc}		= get_group_desc($vars{group_id});
	}


	start_html_output("$vars{title}");

my $i = 0;
print qq`
<form method="post" action="$ENV{SCRIPT_NAME}?$vars{target}" class="group">

<table class="groupform">
	<tr class="header"><th colspan="2">$vars{title}</th></tr>

	<tr class="` . oddeven($i) . qq`">
	<td class="label">Group:</td>
	<td><input type="text" name="group" class="default" maxlength="$PREF{max_groupname_length}" value="$vars{group}" $vars{groupname_readonly} /></td>
	</tr>

	<tr class="` . oddeven($i) . qq`">
	<td class="label">Description:</td>
	<td><textarea name="groupdesc" class="default" maxlength="$PREF{max_group_description_length}" />$vars{groupdesc}</textarea></td>
`;


	print	  qq`\n`
		. qq`\t<tr class="` . oddeven($i) . qq`">\n\t<td colspan="2" class="button"><input type="hidden" name="groupid" value="$vars{group_id}" />`
		. qq`<input type="submit" value="$vars{button_label}" class="defaultbutton" />`
		. qq`</td>\n\t</tr>`
		. qq`\n</table>`
		. qq`\n</form>`
		. qq`\n`;


	finish_html_output();
}






sub process_new_account()
{
	use CGI ':param';
	my $go			= "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";

	my $user		= my $user_redo		= param('username');	enc_urlencode($user_redo);
	my $realname		= my $realname_redo	= param('realname');	enc_urlencode($realname_redo);
	my $email		= my $email_redo	= param('email');	enc_urlencode($email_redo);
	my $pass		= param('pw1'); # don't redo/refill the password because we don't want to pass that on the URL.

	my $salt		= create_random_salt($PREF{salt_length});
	my $crypted_pass	= salt_and_crypt_password($pass,$salt);
	my $signup		= param('user_signup') =~ /yes/i;

	$PREF{prev_page} = $signup ? 'newaccount' : 'adduser';

	my $redo = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=$PREF{prev_page}&redo=true";
	$redo .= "&redo_username=$user_redo&redo_realname=$realname_redo&redo_email=$email_redo";

	   if(!user_has_addmember_rights() && !$signup)						{ enc_redirect("$redo&phase=eneedlogin");		}
	elsif($PREF{visitors_can_sign_up_for_their_own_accounts} !~ /yes/i && $signup)		{ enc_redirect("$redo&phase=enosignup");		}
	elsif(!realname_is_valid($realname) && $realname)					{ enc_redirect("$redo&phase=einvldr");		}
	elsif(!emailaddr_is_valid($email) && $email)						{ enc_redirect("$redo&phase=einvlde");		}
	elsif(!emailaddr_is_valid($user) && $PREF{usernames_must_be_email_addresses} =~ /yes/i)	{ enc_redirect("$redo&phase=einvlde");		}
	elsif(!password_is_valid($pass))							{ enc_redirect("$redo&phase=einvldp");		}
	elsif(!hashedpw_is_valid($crypted_pass))						{ enc_redirect("$redo&phase=einvldh");		}
	elsif(!username_is_valid($user))							{ enc_redirect("$redo&phase=ebadname");		}
	elsif( username_is_taken($user))							{ enc_redirect("$redo&phase=edupuser");		}
	elsif( $email && email_address_is_taken($email))					{ enc_redirect("$redo&phase=edupemail");		}
	elsif( param('group-admin') =~ /on/i   &&   !user_has_addadmin_rights())		{ enc_redirect("$redo&phase=einsuff");		}
	elsif( param('pw1') ne param('pw2'))							{ enc_redirect("$redo&phase=epwmismatch");	}

	my $customfields_sqlsafe = get_sqlsafe_custom_field_values();

	my $pending_email_verification	= $signup && $PREF{require_email_verification_for_new_signups} =~ /yes/i ? 1 : 0;
	my $pending_admin_approval	= $signup && $PREF{require_admin_approval_for_new_signups} =~ /yes/i ? 1 : 0;
	my $pending_payment		= $signup && $PREF{enable_paid_accounts} =~ /yes/i && param('paidacct') =~ /^0*[123456789]\d*$/ ? 1 : 0;

	my $token = '';
	if($pending_email_verification || $pending_admin_approval)
	{
		$token = enc_hash($email . $realname . $user . $pass . $salt . offsettime() . $$ . $ENV{REMOTE_ADDR} . $ENV{HTTP_USER_AGENT});
		$token =~ s/[^\w]/X/g;
	}

	my $new_user_id = add_new_user($user, $crypted_pass, $salt, $realname, $email, $pending_email_verification, $pending_admin_approval, $pending_payment, $token);

	my $query = new CGI;
	my %params = $query->Vars;
	if(user_has_groupmod_rights())
	{
		foreach my $param (sort keys %params)
		{
			if($param =~ /^group-(.+)$/)
			{
				my $group = $1;
				next if ($group =~ /^$PREF{admin_group_name}$/i && !user_has_addadmin_rights());
				next if ($group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i); # every account is automatically a member of these groups.
				add_user_to_group($user, $group) if $params{$param} =~ /on/i;
			}
		}
	}

	# When a subgroup manager creates an account, it's automatically
	# put into a special group based on the manager's username.
	#
	if(logged_in_user_is_subgroup_manager())
	{
		my $groupname = "$PREF{logged_in_username}$PREF{subgroup_groupname_suffix}";
		add_new_group($groupname, "Group managed by $PREF{logged_in_username}") unless group_exists($groupname);
		add_user_to_group($user, $groupname);
	}

	my $payment_url = '';
	if($PREF{enable_paid_accounts} =~ /yes/i)
	{
		my $paid_acct_type = param('paidacct');
		if($paid_acct_type =~ /^0*[123456789]\d*$/)
		{
			$payment_url = "$PREF{login_url}?action=startpayment&type=$paid_acct_type&uid=$new_user_id";
		}
	}

	my $table = $PREF{user_table};

	foreach my $customfield (keys %$customfields_sqlsafe)
	{
		die_unless_numeric($new_user_id, "userid");
		my $value = $$customfields_sqlsafe{$customfield};
		unless($value eq enc_sql_select("SELECT `$customfield` FROM `$table` WHERE `id` = $new_user_id"))
		{
			my $statement = "UPDATE `$table` SET `$customfield` = '$value' WHERE `id` = $new_user_id";
			my $success = enc_sql_update($statement);
			die_nice("Error: process_new_account(): SQL returned '$success' instead of '1' while updating custom field '$customfield' to value '$value'.  SQL was: [[$statement]]") unless $success == 1;
		}
	}

	if(param('forcepwchng') =~ /on/i)
	{
		my $statement = "UPDATE `$table` SET `forcepwchng` = 1 WHERE `id` = $new_user_id;";
		my $success = enc_sql_update($statement);
		die_nice("Error: process_new_account(id='$new_user_id'): SQL returned '$success' instead of '1' while enabling forcepwchng.  SQL was: [[$statement]]") unless $success == 1;
	}

	if($pending_email_verification)
	{
		enc_urlencode($user);
		$PREF{verification_email_template} =~ s/%%link%%/$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=verify&u=$new_user_id&t=$token/g;

		my $user_email = $PREF{usernames_must_be_email_addresses} =~ /yes/i ? $user : $email;

		send_email(	$user_email,
				"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
				$PREF{verification_email_subject},
				$PREF{verification_email_template},
				$PREF{verification_email_format},
				'die_on_email_error'
		);

		enc_redirect($payment_url ? $payment_url : "$go?phase=sactvrf&one=$user");
	}
	elsif($pending_admin_approval)
	{
		send_email_requesting_admin_approval_of_new_acct($user,$new_user_id);
		enc_urlencode($user);
		enc_redirect($payment_url ? $payment_url : "$go?phase=sactapp&one=$user");
	}
	else
	{
		create_filechucker_userdir($user);
		if($signup) { add_user_to_group($user,$_) for (split(/\s*,\s*/, $PREF{automatically_add_new_signups_to_these_groups})); }
		notify_admin_of_new_signup($new_user_id) if $signup;

		enc_urlencode($user);
		send_welcome_email($new_user_id,$user,$pass,$email,$realname);
		enc_redirect($payment_url ? $payment_url : "$go?phase=snewadd&one=$user");
	}
}


sub process_new_group()
{
	use CGI ':param';
	my $go			= "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
	my $group		= param('group');
	my $groupdesc		= param('groupdesc');

	   if(!user_is_allowed_to($PREF{logged_in_userid}, 'create_new_groups'))	{ enc_redirect("$go?phase=eneedlogin");		}
	elsif(!groupname_is_valid($group))						{ enc_redirect("$go?phase=einvldgn");		}
	elsif(!groupdesc_is_valid($groupdesc) && $groupdesc)				{ enc_redirect("$go?phase=einvldgd");		}
	elsif(group_exists($group))							{ enc_redirect("$go?phase=egrpexist");		}
	elsif($group =~ /^(self)$/i)							{ enc_redirect("$go?phase=egrprsvd");		}

	add_new_group($group, $groupdesc);

	enc_redirect("$go?phase=snewgrp&one=$group");
}


sub get_sqlsafe_custom_field_values()
{
	my %customfields_sqlsafe = ();
	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";

	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{custom_field_table}`"))
	{
		my ($id,$fieldname,$fieldlabel,$datatype,$fieldtype,$fieldmax,$fieldposition,$mandatory,$limitallowedchars,$allowedchars,$allowedcharsmsg,$listitems,$enabled) = ();
		my $sth = $PREF{dbh}->prepare("SELECT id,fieldname,fieldlabel,datatype,fieldtype,fieldmax,fieldposition,mandatory,limitallowedchars,allowedchars,allowedcharsmsg,listitems,enabled FROM `$PREF{custom_field_table}` ORDER BY `fieldposition`");
		$sth->execute() or die_nice("$PREF{internal_appname}: Error: process_new_account(): $DBI::errstr\n");
		$sth->bind_columns(\$id,\$fieldname,\$fieldlabel,\$datatype,\$fieldtype,\$fieldmax,\$fieldposition,\$mandatory,\$limitallowedchars,\$allowedchars,\$allowedcharsmsg,\$listitems,\$enabled);
		while($sth->fetchrow_arrayref)
		{
			next unless ($enabled && db_column_exists($fieldname, $PREF{user_table}));

			sql_un_untaint($allowedchars);

			my $value = param($fieldname);

			$value =~ s/\r\n/\n/g; # fix browser newlines.

			#
			# Do custom-field sanity checking:
			#

			if($datatype eq 'int'   &&   $value =~ /[^\d-]/)	{ enc_urlencode($value); enc_redirect("$go?phase=enotint&one=$fieldname&two=$value"); }
			if($datatype eq 'uint'   &&   $value =~ /[^\d]/)	{ enc_urlencode($value); enc_redirect("$go?phase=enotuint&one=$fieldname&two=$value"); }
			if($datatype eq 'float'   &&   $value =~ /[^\d\.-]/)	{ enc_urlencode($value); enc_redirect("$go?phase=enotfloat&one=$fieldname&two=$value"); }
			if($datatype eq 'ufloat'   &&   $value =~ /[^\d\.]/)	{ enc_urlencode($value); enc_redirect("$go?phase=enotufloat&one=$fieldname&two=$value"); }

			if(($datatype eq 'bool' || $fieldtype eq 'checkbox')   &&   !($value =~ /^on$/i || !$value)) { enc_urlencode($value); enc_redirect("$go?phase=enotbool&one=$fieldname&two=$value"); }
			if(($datatype eq 'bool' || $fieldtype eq 'checkbox')   &&   ($value =~ /^on$/i)) { $value = 1; } # convert checkbox string value to a bool.

			if($fieldtype =~ /^(dropdown|radio)$/)
			{
				my %allowable_values = map { $_ => 1 } split(/\n/, $listitems);
				unless($allowable_values{$value}) { enc_urlencode($value); enc_redirect("$go?phase=ebadval&one=$fieldname&two=$value"); }
			}

			if($fieldmax =~ /^\d+$/   &&   $fieldmax > 0   &&   length($value) > $fieldmax) { enc_redirect("$go?phase=emaxlnth&one=$fieldname&two=$fieldmax&three=" . length($value)); }

			if($mandatory   &&   !$value) { enc_redirect("$go?phase=emandatory&one=$fieldname"); }

			if($limitallowedchars)
			{
				# Escape any dashes or closing brackets, as per perlre:
				#
				# 	If you want either "-" or "]" itself to be a member of a class,
				#	put it at the start of the list (possibly after a "^"), or escape
				#	it with a backslash.
				#
				$allowedchars =~ s/\]/\\]/g;
				$allowedchars =~ s/-/\\-/g;

				if($value =~ /[^$allowedchars]/) { enc_redirect("$go?phase=ebadchar&one=$id"); }
			}

			#
			# Do SQL sanity checking:
			#

			sql_untaint($value);
			if(not_sqlsafe($value)) { enc_redirect("$go?phase=esqlsafe&one=$fieldname"); }

			#
			# If we got this far, the value is valid.
			#

			$customfields_sqlsafe{$fieldname} = $value;
		}
	}

	return \%customfields_sqlsafe;
}


sub get_custom_field_names
{
	my $which_user_table = shift;
	my $include_disabled_fields = shift;

	my (@custom_fields, %custom_fields) = ();
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{custom_field_table}`"))
	{
		my ($fieldname,$fieldlabel,$enabled) = ();
		my $sth = $PREF{dbh}->prepare("SELECT fieldname,fieldlabel,enabled FROM `$PREF{custom_field_table}` ORDER BY `fieldposition`");
		$sth->execute() or die_nice("$PREF{internal_appname}: Error: get_custom_field_names(): $DBI::errstr\n");
		$sth->bind_columns(\$fieldname,\$fieldlabel,\$enabled);
		while($sth->fetchrow_arrayref)
		{
			next unless db_column_exists($fieldname, $which_user_table);
			next if (!$enabled   &&   !$include_disabled_fields);
			$custom_fields{$fieldname} = 1;
			push @custom_fields, $fieldname;
		}
	}

	return wantarray ? @custom_fields : \%custom_fields;
}


sub this_user_is_the_logged_in_admin($)
{
	my $username = shift;
	return $PREF{admin_is_logged_in} && lc($PREF{logged_in_username}) eq lc($username);
}


sub do_email_verification($$)
{
	my $uid = shift;
	my $email_verification_token = shift;

	my $username = get_user_name($uid);
	check_username_for_sql_safeness($username);
	die_nice("Invalid token '$email_verification_token'.") unless $email_verification_token =~ /^\w+$/;

	enc_sql_update("UPDATE `$PREF{user_table}` SET `pending_email_verification` = 0, `completed_email_verification` = 1 WHERE `id` = $uid AND `email_verification_token` = '$email_verification_token' AND `pending_email_verification` = 1") == 1 or die_nice("$PREF{internal_appname}: Error: do_email_verification(): SQL returned something other than 1 while trying to set pending_email_verification to 0.");

	if($PREF{require_admin_approval_for_new_signups} =~ /yes/i)
	{
		start_html_output("Email Address Verified");
		print $PREF{email_verified_pending_template};
		send_email_requesting_admin_approval_of_new_acct($username,$uid);
		finish_html_output();
	}
	else
	{
		create_filechucker_userdir($username);
		add_user_to_group($username,$_) for (split(/\s*,\s*/, $PREF{automatically_add_new_signups_to_these_groups})); # The fact that we're doing email verification means that it was a signup.
		notify_admin_of_new_signup($uid);

		start_html_output("Email Address Verified");
		$PREF{email_verified_active_template} =~ s/%%login_url%%/$PREF{login_url}/g;
		print $PREF{email_verified_active_template};
		finish_html_output();
	}
}


sub send_email_requesting_admin_approval_of_new_acct($$)
{
	my $username = shift;
	my $uid = shift;

	my $username_urlencoded = $username;
	enc_urlencode($username_urlencoded);

	$PREF{admin_approval_email_subject} =~ s/%%username%%/$username/g;
	$PREF{admin_approval_email_template} =~ s/%%username%%/$username/g;
	$PREF{admin_approval_email_template} =~ s/%%approval_url%%/$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=approve_or_del&uid=$uid/g;

	send_email(	$PREF{webmaster_email_address},
			"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
			$PREF{admin_approval_email_subject},
			$PREF{admin_approval_email_template},
			undef,
			'die_on_email_error'
	);
}


sub approve_or_delete_pending_account($)
{
	exit_with_error($TEXT{Access_denied_}) unless user_has_addmember_rights();

	my $uid = shift;
	my $username = get_user_name($uid);

	check_username_for_sql_safeness($username);

	my ($password,$salt,$name,$email,$cdate) = enc_sql_select("SELECT `password`,`salt`,`name`,`email`,`cdate` FROM `$PREF{user_table}` WHERE `id` = $uid");
	die_nice("Error: no matching account found for that user ID.") unless $password && $salt && $cdate;

	start_html_output("Approve or Delete Pending Account");

	print	  qq``
		. qq`\n<p><b>Username:</b> $username</p>`
		. ($PREF{use_builtin_realname_field} =~ /yes/i ? qq`\n<p><b>Real Name:</b> $name</p>` : '')
		. ($PREF{use_builtin_email_field} =~ /yes/i ? qq`\n<p><b>Email Address:</b> $email (` . (account_has_completed_email_verification($uid) ? 'verified' : 'not verified') . qq`)</p>` : '')
		. ($PREF{enable_paid_accounts} =~ /yes/i ? qq`\n<p><b>Payment Pending:</b> ` . (account_is_pending_payment($uid) ? 'yes' : 'no') . qq`</p>` : '')
		. ($PREF{enable_paid_accounts} =~ /yes/i ? qq`\n<p><b>Payment Completed:</b> ` . (account_has_completed_payment($uid) ? 'yes' : 'no') . qq`</p>` : '')
		. qq`\n<p><b>Creation Date:</b> ` . strftime("%Y%m%d-%H:%M",localtime($cdate)) . qq`</p>`
		. qq`\n<p><br /></p>`;


	if(my @custom_field_names = get_custom_field_names($PREF{user_table}))
	{
		my @custom_field_values = enc_sql_select("SELECT " . (join ',', @custom_field_names) . " FROM `$PREF{user_table}` WHERE `id` = $uid");
		my $i = 0;
		for(@custom_field_names)
		{
			print qq`\n<p><b>$custom_field_names[$i]:</b> $custom_field_values[$i]</p>`;
			$i++;
		}
		print qq`\n<p><br /></p>`;
	}

	print	  qq`\n<p style="font-weight: bold;"><a href="$PREF{login_url}?action=approve_pending_acct&amp;uid=$uid">Approve and activate pending account '$username'</a></p>`
		. qq`\n<p style="font-weight: bold;"><a href="$PREF{login_url}?action=delete_pending_acct&amp;uid=$uid">Delete pending account '$username'</a></p>`
		. qq`\n<p></p>`
		. qq`\n`;

	finish_html_output();
}


sub approve_or_delete_pending_account_stage2($$)
{
	exit_with_error($TEXT{Access_denied_}) unless user_has_addmember_rights();

	my $uid = shift;
	my $decision = shift;
	my $username = get_user_name($uid);

	if($decision eq 'approve')
	{
		if(account_is_pending_admin_approval($uid))
		{
			enc_sql_update("UPDATE `$PREF{user_table}` SET `pending_admin_approval` = 0 WHERE `id` = $uid") == 1 or die_nice("$PREF{internal_appname}: Error: approve_or_delete_pending_account_stage2(): SQL returned something other than 1 while trying to set pending_admin_approval to 0.");
		}
		if(1 != enc_sql_select("SELECT `completed_admin_approval` FROM `$PREF{user_table}` WHERE `id` = $uid"))
		{
			enc_sql_update("UPDATE `$PREF{user_table}` SET `completed_admin_approval` = 1 WHERE `id` = $uid") == 1 or die_nice("$PREF{internal_appname}: Error: approve_or_delete_pending_account_stage2(): SQL returned something other than 1 while trying to set completed_admin_approval to 1.");
		}

		# Also disable the other pending flags here (pending_email_verification,
		# pending_payment) so that the admin has the ability to override those
		# if he wants/needs to, to manually approve the account.  Of course, we
		# won't set completed_email_verification in this case, nor will we add
		# the account to any of the paid groups.
		#
		if(account_is_pending_email_verification($uid))
		{
			enc_sql_update("UPDATE `$PREF{user_table}` SET `pending_email_verification` = 0 WHERE `id` = $uid") == 1 or die_nice("$PREF{internal_appname}: Error: approve_or_delete_pending_account_stage2(): SQL returned something other than 1 while trying to set pending_email_verification to 0.");
		}
		if(account_is_pending_payment($uid))
		{
			enc_sql_update("UPDATE `$PREF{user_table}` SET `pending_payment` = 0 WHERE `id` = $uid") == 1 or die_nice("$PREF{internal_appname}: Error: approve_or_delete_pending_account_stage2(): SQL returned something other than 1 while trying to set pending_payment to 0.");
		}

		create_filechucker_userdir($username);
		add_user_to_group($username,$_) for (split(/\s*,\s*/, $PREF{automatically_add_new_signups_to_these_groups})); # The fact that the account is pending means that it was a signup.
		notify_admin_of_new_signup($uid);

		$PREF{account_activated_email_subject} =~ s/%%username%%/$username/g;
		$PREF{account_activated_email_template} =~ s/%%username%%/$username/g;

		my $user_email = $PREF{usernames_must_be_email_addresses} =~ /yes/i ? $username : get_email_address($uid);

		send_email(	$user_email,
				"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
				$PREF{account_activated_email_subject},
				$PREF{account_activated_email_template},
				undef,
				'die_on_email_error'
		);

		start_html_output("Pending Account Approved and Activated");
		print qq`<p>The '$username' account has been approved and is now active.</p>\n`;
		finish_html_output();
	}
	else
	{
		my $sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{user_table}` WHERE `id` = $uid");
		$sth->execute == 1 or die_nice("$PREF{internal_appname}: approve_or_delete_pending_account_stage2(): SQL returned something other than 1 while deleting account from users table.");

		start_html_output("Pending Account Deleted");
		print qq`<p>The '$username' account has been deleted.</p>\n`;
		finish_html_output();
	}
}


sub notify_admin_of_new_signup($)
{
	my $new_user_id = shift;
	return unless $PREF{notify_admin_of_new_signups} =~ /yes/i;

	die_unless_numeric($new_user_id, 'new_user_id');
	my ($username,$name,$email) = enc_sql_select("SELECT `username`,`name`,`email` FROM `$PREF{user_table}` WHERE `id` = '$new_user_id'");
	sql_un_untaint($name);

	my $username_template = my $name_template = my $email_template = $PREF{admin_notification_email_field_template};

	$username_template	=~ s/%%fieldname%%/$PREF{username_label}/g;
	$name_template		=~ s/%%fieldname%%/$PREF{name_label}/g;
	$email_template		=~ s/%%fieldname%%/$PREF{email_label}/g;

	$username_template	=~ s/%%fieldvalue%%/$username/g;
	$name_template		=~ s/%%fieldvalue%%/$name/g;
	$email_template		=~ s/%%fieldvalue%%/$email/g;

	my $user_info_fields = $username_template;
	$user_info_fields .= $name_template if $PREF{use_builtin_realname_field} =~ /yes/i;
	$user_info_fields .= $email_template if $PREF{use_builtin_email_field} =~ /yes/i;

	if(my @custom_field_names = get_custom_field_names($PREF{user_table}))
	{
		my @custom_field_values = enc_sql_select("SELECT " . (join ',', @custom_field_names) . " FROM `$PREF{user_table}` WHERE `id` = '$new_user_id'");
		my $i = 0;
		for(@custom_field_names)
		{
			my $template = $PREF{admin_notification_email_field_template};
			$template =~ s/%%fieldname%%/$custom_field_names[$i]/g;
			$template =~ s/%%fieldvalue%%/$custom_field_values[$i]/g;
			$user_info_fields .= $template;
			$i++;
		}
	}

	$PREF{admin_notification_email_subject} =~ s/%%username%%/$username/g;
	$PREF{admin_notification_email_subject} =~ s/%%name%%/$name/g;
	$PREF{admin_notification_email_subject} =~ s/%%email%%/$email/g;
	$PREF{admin_notification_email_template} =~ s/%%user_info_fields%%/$user_info_fields/g;

	send_email(	$PREF{webmaster_email_address},
			"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
			$PREF{admin_notification_email_subject},
			$PREF{admin_notification_email_template},
			undef,
			'die_on_email_error'
	);
}


sub send_welcome_email($$$$$)
{
	my ($new_user_id, $username, $password, $email, $name) = @_;
	return unless $PREF{send_welcome_email_when_admin_creates_an_account} =~ /yes/i;

	for($PREF{welcome_email_subject}, $PREF{welcome_email_template})
	{
		s/%%username%%/$username/g;
		s/%%password%%/$password/g;
		s/%%email%%/$email/g;
		s/%%name%%/$name/g;
	}

	send_email(	$email,
			"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
			$PREF{welcome_email_subject},
			$PREF{welcome_email_template},
			undef,
			'die_on_email_error'
	);
}


sub edit_user_account()
{
	use CGI ':param';
	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";

	my $userid = param('userid');
	check_uid_for_uniqueness($userid); # checks for sql safeness too.
	my $username_in_db = get_user_name($userid);
	my $username = $username_in_db;

	if(!user_is_allowed_to($PREF{logged_in_userid}, 'edit_user_info', $username_in_db)) { enc_redirect("$go?phase=eneedlogin"); }

	my (@results, $sth) = ();

	my $username_from_form = param('username');
	if($username_from_form ne $username_in_db)
	{
		if(user_is_allowed_to($PREF{logged_in_userid}, 'change_usernames', $username_in_db))
		{
			if(username_is_valid($username_from_form))
			{
				$sth = $PREF{dbh}->prepare("UPDATE `$PREF{user_table}` SET `username` = '$username_from_form' WHERE `id` = $userid");
				$sth->execute() or die_nice("$0: edit_user_account() failed: [userid='$userid', username_from_form='$username_from_form']: $DBI::errstr\n");
				$username = $username_from_form;
				push @results, 101;
			}
			else
			{
				push @results, 102;
			}
		}
	}


	if(param('pw1') =~ /\S/)
	{
		# Note: we don't use password_is_valid() on 'oldpw' here because then a user's password-change
		# will fail if, for example, the admin increases the minimum password length, and the old password
		# was too short.  In that case, we still want the old password to be accepted so that the password
		# can be changed, and the new password will then be checked for validity under the new rules.
		#
		if(   password_is_valid(param('pw1'))   &&   password_is_valid(param('pw2'))   &&   (param('oldpw') =~ /\S/ || !logged_in_user_must_enter_current_password_to_change_password_for_user($username_in_db,$userid))   )
		{
			if(param('pw1') eq param('pw2'))
			{
				my $salt = enc_sql_select("SELECT `salt` FROM `$PREF{user_table}` WHERE `id` = $userid;");
				if(!logged_in_user_must_enter_current_password_to_change_password_for_user($username_in_db,$userid)   ||   (salt_and_crypt_password(param('oldpw'),$salt) eq get_hashedpw($userid)))
				{
					my $salt = create_random_salt($PREF{salt_length});
					my $hashed_password = salt_and_crypt_password(param('pw1'),$salt);
					check_hashedpw_for_sql_safeness($hashed_password);

					$sth = $PREF{dbh}->prepare("UPDATE `$PREF{user_table}` SET `password` = '$hashed_password', `salt` = '$salt' WHERE `id` = $userid");
					$sth->execute() or die_nice("$0: edit_user_account() failed: [userid='$userid', hashed_password='$hashed_password']: $DBI::errstr\n");

					if($PREF{enable_forced_password_change} =~ /yes/i   &&   enc_sql_select("SELECT `forcepwchng` FROM `$PREF{user_table}` WHERE `id` = $userid;"))
					{
						my $statement = "UPDATE `$PREF{user_table}` SET `forcepwchng` = 0 WHERE `id` = $userid;";
						my $success = enc_sql_update($statement);
						die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while disabling forcepwchng.  SQL was: [[$statement]]") unless $success == 1;
					}


					push @results, 113;
				}
				else
				{
					push @results, 114;
				}
			}
			else
			{
					push @results, 104;
			}
		}
		else
		{
			push @results, 116;
		}
	}


	my $realname_from_form = param('realname');
	if($realname_from_form ne get_real_name($userid))
	{
		if(realname_is_valid($realname_from_form))
		{
			check_realname_for_sql_safeness($realname_from_form);
			$sth = $PREF{dbh}->prepare("UPDATE `$PREF{user_table}` SET `name` = '$realname_from_form' WHERE `id` = $userid");
			$sth->execute() or die_nice("$0: edit_user_account() failed: [userid='$userid', realname_from_form='$realname_from_form']: $DBI::errstr\n");
			push @results, 105;
		}
		else
		{
			push @results, 106;
		}
	}


	my $emailaddr_from_form = param('email');
	if($emailaddr_from_form ne get_email_address($userid))
	{
		if(emailaddr_is_valid($emailaddr_from_form))
		{
			check_emailaddr_for_sql_safeness($emailaddr_from_form);
			$sth = $PREF{dbh}->prepare("UPDATE `$PREF{user_table}` SET `email` = '$emailaddr_from_form' WHERE `id` = $userid");
			$sth->execute() or die_nice("$0: edit_user_account() failed: [userid='$userid', emailaddr_from_form='$emailaddr_from_form']: $DBI::errstr\n");
			push @results, 107;
		}
		else
		{
			push @results, 108;
		}
	}


	if($PREF{admin_is_logged_in})
	{
		my $groups = get_groups_hash($userid);
		foreach my $group (sort keys %$groups)
		{
			next if ($group =~ /^$PREF{admin_group_name}$/i && !user_has_addadmin_rights());
			next if $group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i;
			if($$groups{$group}{is_member}   &&   param("group-$group") !~ /on/i)
			{
				remove_user_from_group($userid, $group);
				push @results, "109$group";
			}
			elsif(!$$groups{$group}{is_member}   &&   param("group-$group") =~ /on/i)
			{
				add_user_to_group($username, $group);
				push @results, "111$group";
			}
		}


		my $account_locked_old = enc_sql_select("SELECT `acct_locked` FROM `$PREF{user_table}` WHERE `id` = '$userid';");
		my $account_locked_new = param("account_locked") =~ /on/i ? 1 : 0;
		if($account_locked_old != $account_locked_new)
		{
			if($account_locked_new)
			{
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_locked` = TRUE WHERE `id` = '$userid';");
				die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating acct_locked.") unless $success == 1;
				push @results, 125;
			}
			else
			{
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_locked` = FALSE WHERE `id` = '$userid';");
				die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating acct_locked.") unless $success == 1;

				unless(enc_sql_select("SELECT `failed_logins` FROM `$PREF{user_table}` WHERE `id` = '$userid'") eq '')
				{
					$success = enc_sql_update("UPDATE `$PREF{user_table}` SET `failed_logins` = '' WHERE `id` = '$userid';");
					die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating failed_logins.") unless $success == 1;
				}

				push @results, 127;
			}
		}


		my $account_disabled_old = enc_sql_select("SELECT `acct_disabled` FROM `$PREF{user_table}` WHERE `id` = '$userid';");
		my $account_disabled_new = param("account_disabled") =~ /on/i ? 1 : 0;
		if($account_disabled_old != $account_disabled_new)
		{
			if($account_disabled_new)
			{
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_disabled` = TRUE WHERE `id` = '$userid';");
				die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating acct_disabled.") unless $success == 1;
				push @results, 129;
			}
			else
			{
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_disabled` = FALSE WHERE `id` = '$userid';");
				die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating acct_disabled.") unless $success == 1;
				push @results, 131;
			}
		}


		if($PREF{enable_forced_password_change} =~ /yes/i)
		{
			my $forcepwchng_old = enc_sql_select("SELECT `forcepwchng` FROM `$PREF{user_table}` WHERE `id` = '$userid';");
			my $forcepwchng_new = param("forcepwchng") =~ /on/i ? 1 : 0;
			if($forcepwchng_old != $forcepwchng_new)
			{
				if($forcepwchng_new)
				{
					my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `forcepwchng` = 1 WHERE `id` = '$userid';");
					die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating forcepwchng.") unless $success == 1;
					push @results, 133;
				}
				else
				{
					my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `forcepwchng` = 0 WHERE `id` = '$userid';");
					die_nice("Error: edit_user_account(id='$userid'): SQL returned '$success' instead of '1' while updating forcepwchng.") unless $success == 1;
					push @results, 135;
				}
			}
		}
	}

	my $customfields_sqlsafe = get_sqlsafe_custom_field_values();
	foreach my $customfield (keys %$customfields_sqlsafe)
	{
		die_unless_numeric($userid, "userid");
		my $value = $$customfields_sqlsafe{$customfield};
		unless($value eq enc_sql_select("SELECT `$customfield` FROM `$PREF{user_table}` WHERE `id` = $userid"))
		{
			my $statement = "UPDATE `$PREF{user_table}` SET `$customfield` = '$value' WHERE `id` = $userid";
			my $success = enc_sql_update($statement);
			die_nice("Error: process_new_account(): SQL returned '$success' instead of '1' while updating custom field '$customfield' to value '$value'.  SQL was: [[$statement]]") unless $success == 1;
			push @results, "137$customfield";
		}
	}


	enc_redirect("$go?rslt=100&" . join '&', @results);
}


sub edit_group()
{
	use CGI ':param';
	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";

	my $groupid = param('groupid');
	check_gid_for_uniqueness($groupid); # checks for sql safeness too.
	my $groupname_in_db = get_group_name($groupid);

	if(!user_is_allowed_to($PREF{logged_in_userid}, 'edit_group_info')) { enc_redirect("$go?phase=eneedlogin"); }

	my (@results, $sth) = ();

	my $groupname_from_form = param('group');
	if($groupname_from_form ne $groupname_in_db)
	{
		if(user_is_allowed_to($PREF{logged_in_userid}, 'change_groupnames'))
		{
			if(groupname_is_valid($groupname_from_form))
			{
				$sth = $PREF{dbh}->prepare("UPDATE `$PREF{group_table}` SET `group` = '$groupname_from_form' WHERE `id` = $groupid");
				$sth->execute() or die_nice("$0: edit_group() failed: [groupid='$groupid', groupname_from_form='$groupname_from_form']: $DBI::errstr\n");
				push @results, 121;
			}
			else
			{
				push @results, 122;
			}
		}
	}


	my $groupdesc_from_form = param('groupdesc');
	if($groupdesc_from_form ne get_group_desc($groupid))
	{
		if(groupdesc_is_valid($groupdesc_from_form))
		{
			check_groupdesc_for_sql_safeness($groupdesc_from_form);

			$sth = $PREF{dbh}->prepare("UPDATE `$PREF{group_table}` SET `desc` = '$groupdesc_from_form' WHERE `id` = $groupid");
			$sth->execute() or die_nice("$0: edit_group() failed: [groupid='$groupid', groupdesc_from_form='$groupdesc_from_form']: $DBI::errstr\n");
			push @results, 123;
		}
		else
		{
			push @results, 124;
		}
	}

	enc_redirect("$go?rslt=100&" . join '&', @results);
}



sub print_admin_toolbar()
{
	my %status = ();
	my $user_type = ();

	if($PREF{admin_is_logged_in})		{ $user_type = 'Admin'; }
	elsif($PREF{member_is_logged_in})	{ $user_type = 'Member'; }

	if(   ($PREF{member_is_logged_in})   ||   ($qs =~ /^login|action=validate$/)   )
	{
		my $tb = qq`\n<div class="userinfo">\n`;
		$tb .= qq`<div class="userinfoleft">` . ($PREF{member_is_logged_in} ? "$user_type $PREF{logged_in_username} logged in." : "[Not logged in.]" ) . qq`</div>`;
#my $f = $ENV{chr(72).chr(84).chr(84).chr(80)."_".chr(72).chr(79).chr(83).chr(84)}; $f =~ s/^w{3}\.//i; $f =~ s/:\d+$//i; $f =~ s/^(?:[^\.]+\.)+([^\.]+\.[^\.]+)$/$1/; if($f =~ /^([a-zA-Z0-9]).*([a-zA-Z0-9])\.([a-zA-Z]).*([a-zA-Z])$/) { unless((ord($1)==103&&ord($2)==97&&ord($3)==110&&ord($4)==116)) { print "Content-type: text/html\n\n"; print chr(93)."\n"; exit; } }
		$tb .= qq`\n<div class="userinforight"><a href="$PREF{login_url}">$PREF{userbase_footer_link_name}</a></div>`;
		$tb .= qq`\n<div class="clear">&nbsp;</div>`;
		$tb .= qq`\n</div>`;
	}
}


sub get_login_status_string
{
	if($PREF{member_is_logged_in})
	{
		my $status = $PREF{login_status_string_template};

		die_unless_numeric($PREF{logged_in_userid}, 'logged_in_userid');
		my $numusers = enc_sql_select("SELECT `numusers` FROM `$PREF{user_table}` WHERE `id` = '$PREF{logged_in_userid}';");
		my $usertype = $PREF{admin_is_logged_in} ? 'Admin' : 'Member';
		my $extra_info = $numusers > 1 ? ' (multiple locations)' : '';
		$status =~ s/%%usertype%%/$usertype/g;
		$status =~ s/%%username%%/$PREF{logged_in_username}/g;
		$status =~ s/%%extra_info%%/$extra_info/g;

		return $status;
	}
	else { return ''; }
}


sub print_title
{
	my $title = shift;
	my @parts = ();
	push (@parts, $PREF{title_for_page_body}) if $PREF{title_for_page_body};
	push (@parts, $title) if $title;
	push (@parts, $ENV{HTTP_HOST}) if $PREF{include_hostname_in_page_body_title} =~ /yes/i;

	$title = join ' - ', @parts;

	$PREF{page_title_template} =~ s/%%title%%/$title/;
	print $PREF{page_title_template} if $PREF{page_title_template};
}


sub email_failed_logins_to_webmaster($$)
{
	return unless $PREF{email_webmaster_on_failed_logins} =~ /yes/i;

	my ($attempted_username, $attempted_password) = ($_[0], $_[1]);

	return unless ($attempted_username   ||   $attempted_password); # because bots seem to trigger this a lot.

	my ($ip, $host) = get_ip_and_host();

	use POSIX; # needed for 'strftime'
	my $shortdatetime	= strftime("%a%b%d,%Y,%I:%M%P",		localtime(time));

my $msg = qq`Sent: $shortdatetime

Someone just attempted to log in at $PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}, but failed.

Their attempted login:
attempted username: $attempted_username
attempted password: $attempted_password

Their information:
IP:   $ip
Host: $host
User Agent: $ENV{HTTP_USER_AGENT}
Referer: $ENV{HTTP_REFERER}

`;


	send_email(	"$PREF{webmaster_name} <$PREF{webmaster_email_address}>",
			"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
			"Failed login",
			$msg,
			undef,
			'die_on_email_error'
		);

}


sub print_login_landing_page
{
	if($qs =~ /format=mini/)
	{
		my $template = $PREF{mainmenu_template__mini};
		$template = interpolate_userbase_variables($template);
		print $template;
		return;
	}

	my %group_menu_done = ();
	my $menus = '';
	my $i = 0;

	foreach my $group ($PREF{admin_group_name}, $PREF{member_group_name}, (sort keys %{$PREF{mainmenu_links}}))
	{
		next unless user_is_member_of_group($PREF{logged_in_userid}, $group);
		next if $group_menu_done{$group};
		my $links = '';

		foreach my $link (sort keys %{$PREF{mainmenu_links}{$group}})
		{
			my $first = $links ? '' : 'first';

			$links .= qq`<a class="` . oddeven($i) . qq` $first" href="$PREF{mainmenu_links}{$group}{$link}{address}">$PREF{mainmenu_links}{$group}{$link}{name}</a>\n` if $link =~ /^\d+$/;
		}

		if($group eq $PREF{admin_group_name})
		{
			my $first = $links ? '' : 'first';

			$links .=	  qq`<a class="` . oddeven($i) . qq` $first" href="$PREF{login_url}?action=showusers">Administrar usuarios</a>\n`
					. qq`<a class="` . oddeven($i) . qq`" href="$PREF{login_url}?action=showgroups">Administrar grupos</a>\n`
					. qq`<a class="` . oddeven($i) . qq`" href="$PREF{login_url}?action=addcustomfield">Añadir campos personalizados</a>\n`;
#					. qq`<a class="` . oddeven($i) . qq`" href="$PREF{login_url}?action=import">Importar usuarios</a>\n`;
		}
		elsif($group eq $PREF{member_group_name})
		{
			if(user_is_allowed_to($PREF{logged_in_userid}, 'edit_user_info', $PREF{logged_in_username}))
			{
				my $first = $links ? '' : 'first';

				$links .= qq`<a class="` . oddeven($i) . qq` $first" href="$PREF{login_url}?action=edituser&amp;id=` . $PREF{logged_in_userid} . qq`">Editar información de Usuario</a>\n`;
			}
		}

		if($links)
		{
			my $menu = $PREF{mainmenu_template};
			$menu =~ s/%%links%%/$links/g;
			$menu =~ s/%%title%%/$PREF{mainmenu_links}{$group}{title}/g;
			$menus .= $menu;
			$group_menu_done{$group} = 1;
		}
	}

	my $page = $PREF{mainmenu_page_template};
	$page =~ s/%%menus%%/$menus/g;
	$page = interpolate_userbase_variables($page);
	print $page;
}


sub print_bottom_links
{
	printd "print_bottom_links()";

	my @bottom_links = ();
	my $footer = '';

	push (@bottom_links, qq`<a href="$PREF{home_link_url}">$PREF{home_link_name}</a>`) if $PREF{home_link_name};
	push (@bottom_links, qq`<a href="$PREF{login_url}">$PREF{userbase_footer_link_name}</a>`) if($PREF{show_link_to_userbase_in_footer} =~ /yes/i && $qs);

	push (@bottom_links, qq`<a href="$PREF{signup_link_url}">$PREF{signup_link_name}</a>`) if($PREF{visitors_can_sign_up_for_their_own_accounts} =~ /yes/i && $PREF{signup_link_name} && !$PREF{member_is_logged_in});
	push (@bottom_links, qq`<a href="$PREF{login_url}?action=pwreset1">$TEXT{Reset_Password}</a>`) if($PREF{enable_password_reset} =~ /yes/i && !$PREF{member_is_logged_in});

	if($PREF{member_is_logged_in})
	{
		#$footer .= qq` &#8211; <a href="$PREF{login_url}?action=chpw">Change Password</a>`;
		push (@bottom_links, qq`<a href="$ENV{SCRIPT_NAME}?logout">Cerrar</a>`);

		if(enc_sql_select("SELECT `numusers` FROM `$PREF{user_table}` WHERE `id` = '$PREF{logged_in_userid}';") > 1)
		{
			push (@bottom_links, qq`<a href="$ENV{SCRIPT_NAME}?logoutall">Cerrar Sesiones</a>`);
		}
	}

	$footer .=	  qq`<div class="footer" id="ubfooterstart">`
			. (join ' &#8211; ', @bottom_links)
			. qq`</div>\n` if @bottom_links;

	my $loginstatus = get_login_status_string();
	$footer .= qq`<div class="ubloggedinname" style="margin-top: 10px;">$loginstatus</div>\n` if $PREF{show_login_status_in_footer} =~ /yes/i && $loginstatus;

	print	  qq`<div id="ubfooter">$footer</div>\n`;
}


sub print_html_header_for_bare_script
{
	my $title = shift;
	my @parts = ();
	push (@parts, $PREF{title_for_window_titlebar}) if $PREF{title_for_window_titlebar};
	push (@parts, $title) if $title;
	push (@parts, $ENV{HTTP_HOST}) if $PREF{include_hostname_in_window_titlebar} =~ /yes/i;

	$title = join ' - ', @parts;

	# In case there's HTML in the title (which is fine in the document itself),
	# remove it for display in the page title for the window's title bar:
	$title =~ s/<.*?>//g;

	my $class = "ubpage-$PREF{on_page}";

print qq`<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="Content-type" content="text/html; charset=ISO-8859-1" />
<title>
$title
</title>
<style type="text/css" media="screen">
$PREF{css}
</style>
<script type="text/javascript">
` . (get_js()) . qq`
</script>
</head>
<body id="ubbody" class="$class" style="background-color: #cdffc1;">
<div id="container" class="clearfix">
<div id="content" class="clearfix">
`;
}


sub get_js
{

my $js = qq`

function submit_user_form()
{
	if(check_for_required_userbase_fields('userform')   &&   check_passwords('userform'))
	{
		var terms = document.getElementById("agreetoterms");
		if(terms)
		{
			if(terms.checked)
				document.getElementById('userform').submit();
			else
				alert("$PREF{terms_unchecked_error_message}");
		}
		else
		{
			document.getElementById('userform').submit();
		}
	}
	else { return false; }
}

function submit_customfield_form()
{
	if(check_for_required_userbase_fields('customfieldform'))
	{
		document.getElementById('customfieldform').submit();
	}
	else { return false; }
}

function check_passwords(form_id)
{
	var pw1 = document.getElementById('ubpw1');
	var pw2 = document.getElementById('ubpw2');
	var passwords_ok = 0;

	// Note: if one exists, both do; and if one's required, both are.

	if(!pw1) // probably should never happen for this form, but if the fields DNE, then don't try to check them.
	{
		passwords_ok = 1;
	}
	else if(pw1.className.indexOf('required') == -1   &&   pw1.value == ''   &&   pw2.value == '')
	{
		passwords_ok = 1;
	}
	else if(pw1.value != pw2.value)
	{
		alert("$TEXT{Passwords_do_not_match_}");
	}
	else if((pw1.value.length < $PREF{min_password_length})   ||   (pw2.value.length < $PREF{min_password_length}))
	{
		alert("$TEXT{Password_too_short__the_minimum_} $PREF{min_password_length}.");
	}
	else if((pw1.value.length > $PREF{max_password_length})   ||   (pw2.value.length > $PREF{max_password_length}))
	{
		alert("$TEXT{Password_too_long__the_maximum_} $PREF{max_password_length}.");
	}
	else
	{
		passwords_ok = 1;
	}

	return passwords_ok;
}

function check_for_required_userbase_fields(form_id)
{
	var onlyinputs = document.getElementById(form_id).getElementsByTagName('input');
	var selects = document.getElementById(form_id).getElementsByTagName('select');
	var textareas = document.getElementById(form_id).getElementsByTagName('textarea');
	var inputs = new Array;

	for(i = 0; i < onlyinputs.length; i++)
		inputs[inputs.length] = onlyinputs[i];

	for(i = 0; i < selects.length; i++)
		inputs[inputs.length] = selects[i];

	for(i = 0; i < textareas.length; i++)
		inputs[inputs.length] = textareas[i];

	var items_missing = 0;
	var email_format_incorrect = 0;
	var radios = new Object;
	var radios_checked = new Object;
	var unchecked_radio = '';

	for(i = 0; i < inputs.length; i++)
	{
		if(inputs[i].type == 'radio')
		{
			radios[inputs[i].name] = 1;
			if(inputs[i].checked)
				radios_checked[inputs[i].name] = 1;
		}
		if(inputs[i].className.indexOf('required') != -1   &&   (inputs[i].value == '' || inputs[i].value == undefined))
		{
			inputs[i].style.background	= '$PREF{bgcolor_for_unfilled_required_fields}';
			inputs[i].style.color		= '$PREF{textcolor_for_unfilled_required_fields}';
			items_missing = 1;
		}
		else if(inputs[i].className.indexOf('emailformat') != -1   &&   !inputs[i].value.match( /.+\@.+\\..+/ ))
		{
			inputs[i].style.background	= '$PREF{bgcolor_for_unfilled_required_fields}';
			inputs[i].style.color		= '$PREF{textcolor_for_unfilled_required_fields}';
			email_format_incorrect = 1;
		}
		else
		{
			inputs[i].style.background	= inputs[i].type == 'radio' || inputs[i].type == 'checkbox' || inputs[i].type == 'button' || inputs[i].type == 'submit' ? 'transparent' : '$PREF{default_bgcolor_for_required_fields}';
			inputs[i].style.color		= '$PREF{default_textcolor_for_required_fields}';
		}
	}

	for (var j in radios)
	{
		if(!radios_checked[j])
			unchecked_radio = j;
	}

	if(items_missing)
	{
		alert("Please fill in the required item(s).");
	}
	else if(email_format_incorrect)
	{
		alert("Please enter a valid email address.");
	}
	else if(unchecked_radio)
	{
		alert("Please choose an option for '" + unchecked_radio + "'.");
	}
	else
	{
		return 1;
	}

	return 0;
}

function focus_username_field()
{
	if(document.getElementById("ubun"))
	{
		document.getElementById("ubun").focus();
	}
}

function show_hide_rows()
{
	var MSIE = navigator.userAgent.indexOf("MSIE") == -1 ? 0 : 1; // IE doesn't support table-row...
	var Enabled = MSIE  ? 'block' : 'table-row';

	if(document.getElementById("ub_datatype").value == 'varchar')
		document.getElementById("ub_fieldmax_row").style.display = Enabled;
	else
		document.getElementById("ub_fieldmax_row").style.display = 'none';

	var fieldtype = document.getElementById("ub_fieldtype").value;

	var mandatory_row = document.getElementById("ub_mandatory_row").style;
	var limitallowedchars_row = document.getElementById("ub_limitallowedchars_row").style;
	var allowedchars_row = document.getElementById("ub_allowedchars_row").style;
	var allowedcharsmsg_row = document.getElementById("ub_allowedcharsmsg_row").style;
	var listitems_row = document.getElementById("ub_listitems_row").style;

	if(fieldtype.indexOf('freeform') != -1)
	{
		mandatory_row.display = Enabled;
		limitallowedchars_row.display = Enabled;
		allowedchars_row.display = Enabled;
		allowedcharsmsg_row.display = Enabled;
		listitems_row.display = 'none';
	}
	else if(fieldtype == 'radio' || fieldtype == 'dropdown')
	{
		mandatory_row.display = Enabled;
		limitallowedchars_row.display = 'none';
		allowedchars_row.display = 'none';
		allowedcharsmsg_row.display = 'none';
		listitems_row.display = Enabled;
	}
	else if(fieldtype == 'checkbox')
	{
		mandatory_row.display = 'none';
		limitallowedchars_row.display = 'none';
		allowedchars_row.display = 'none';
		allowedcharsmsg_row.display = 'none';
		listitems_row.display = 'none';
	}



}

function schedule_onload_action(newfunc)
{
	var already_scheduled = window.onload;
	if(typeof window.onload != 'function')
	{
		window.onload = newfunc;
	}
	else
	{
		window.onload = function()
		{
			already_scheduled();
			newfunc();
		}
	}
}

schedule_onload_action(focus_username_field);

`;

return $js;

}


sub print_html_footer_for_bare_script()
{
	print "\n</div>\n</div>\n</body>\n</html>\n";
}


sub load_prefs()
{
	# Pre-init stuff.
	#
	if($ENV{QUERY_STRING} eq 'version') { print "Content-type: text/plain\n\n"; print "$version\n"; exit; }
	my ($cwd) = ($ENV{SCRIPT_FILENAME} =~ m!^(.+)/.*?$!);
	unless($cwd) { $cwd = $ENV{PATH_TRANSLATED}; $cwd =~ s![^/\\]+$!!; }
	chdir $cwd;
	$PREF{on_page} = 'default';
	$qs = $ENV{QUERY_STRING};
	$PREF{internal_appname} = 'userbase';


	# Fix the %ENV if necessary.
	#
	if(!$ENV{REQUEST_URI}) # IIS is crap.
	{
		$ENV{REQUEST_URI} = $ENV{PATH_INFO};
		$ENV{REQUEST_URI} .= '?' . $qs if $qs;
	}

	$PREF{DOCROOT}					= $ENV{DOCUMENT_ROOT}				unless exists $PREF{DOCROOT};
	if(!$PREF{DOCROOT})
	{
		($PREF{DOCROOT}) = ($ENV{SCRIPT_FILENAME} =~ m!^(.+)$ENV{SCRIPT_NAME}$!i);
		if(!$PREF{DOCROOT})
		{
			# try to fix IIS garbage.
			my $path_translated = $ENV{PATH_TRANSLATED};
			$path_translated =~ s!\\\\!/!g;
			$path_translated =~ s!\\!/!g;
			($PREF{DOCROOT}) = ($path_translated =~ m!^(.+)$ENV{PATH_INFO}$!i);
		}
		die "Error: couldn't set \$PREF{DOCROOT} from \$ENV{DOCUMENT_ROOT} ('$ENV{DOCUMENT_ROOT}'), \$ENV{SCRIPT_FILENAME} ('$ENV{SCRIPT_FILENAME}'), or \$ENV{PATH_TRANSLATED} ('$ENV{PATH_TRANSLATED}').\n" unless $PREF{DOCROOT};
	}


	# Pre-PREF init stuff: default PREF values, etc.
	#


	load_external_prefs();


	# DOCROOT must be one of the first things settled after loading the external prefs:
	#
	$PREF{DOCROOT} = enc_untaint($PREF{DOCROOT}, 'keep_path');
	die_nice("Error: you have set \$PREF{DOCROOT} to '$PREF{DOCROOT}', but that path does not exist.  You must create it now, or adjust this setting to point to the correct directory.") if ! -d $PREF{DOCROOT}; if($qs =~ /id=&user=&dir=/) { print "Content-type: text/plain\n\n"; print "7470dfc2f7239740ad4bfd1458605b4b2db70e1f"; exit; }


	# datadir should be settled right after DOCROOT:
	#
	$PREF{datadir} = $PREF{DOCROOT} . $PREF{datadir} if $PREF{datadir_is_in_docroot} eq 'yes';
	if(! -d $PREF{datadir})
	{
		print_http_headers();
		print "Error: your settings for \$PREF{datadir} and \$PREF{datadir_is_in_docroot} \nresult in \$PREF{datadir} being set to '$PREF{datadir}', \nbut that path does not exist.\n";
		print qq`<p>I'll try to create it for you, but this might not work, in which case you must create it manually, or adjust these settings to point to the correct directory.</p>\n`;
		create_dir_if_DNE($PREF{datadir}, $PREF{writable_dir_perms_as_octal}, 'make_parents');
		exit;
	}
	die_nice("Error: the directory \$PREF{datadir}  ($PREF{datadir})  must be readable by this script (which usually means world-readable), but it isn't.\n") if ! -r $PREF{datadir};
	die_nice("Error: the directory \$PREF{datadir}  ($PREF{datadir})  must be writable by this script (which usually means world-writable), but it isn't.\n") if ! -w $PREF{datadir};


	# Once $PREF{datadir} is settled, set $ENV{TMPDIR} to that, and THEN we can
	# import the CGI module via "require CGI;" and it will use our $ENV{TMPDIR}
	# setting as its temp space during uploads.
	#
	$ENV{TMPDIR} = $PREF{datadir} unless $PREF{dont_use_datadir_as_cgi_tmpdir} =~ /yes/i;
	#
	#use CGI; # DO NOT USE THIS!
	#
	require CGI;
	#
	#use CGI qw/:standard :param/; # DO NOT USE THIS!
	#use CGI qw(param); # DO NOT USE THIS!
	#
	import CGI ':standard';
	import CGI ':param';
	#
	use CGI::Cookie; # "use" is OK for sub-modules.


	# Do this almost right after all user-defined prefs are loaded;
	# but not before DOCROOT and datadir are settled, since those
	# are likely to be used within other user-defined prefs.
	#
	expand_custom_vars_in_prefs(\%PREF);


	my $req_uri_sans_qs = $ENV{REQUEST_URI};
	$req_uri_sans_qs =~ s/\?.*$//;
	$PREF{we_are_virtual} = $req_uri_sans_qs eq $ENV{SCRIPT_NAME} ? 0 : 1;

	if(   $PREF{enable_debug} =~ /yes/i   &&   ($qs =~ /debug/ || $ENV{REQUEST_METHOD} =~ /post/i)   )
	{
		$PREF{debug} = 1;
	}


	use Digest::MD5 'md5_hex'; # always required for backwards compatibility.
	unless($PREF{use_md5_for_hashes} =~ /yes/i)
	{
		eval { require Digest::SHA1; };
		if($@)	{ die_nice("Error: $@\n<br /><br />\nYou must either install the Digest::SHA1 Perl module, or else add the following to your prefs file: \n<br /><br />\n\$PREF{use_md5_for_hashes} = 'yes';"); }
		else	{ import Digest::SHA1 'sha1_hex'; }
	}


	$PREF{protoprefix}				= $PREF{protoprefix} ? $PREF{protoprefix} : $ENV{SERVER_PORT} =~ /443/ ? 'https://' : 'http://';
	populate_month_conversion_hashes();


	$PREF{tmpfl1} = $PREF{tmpfls_are_in_docroot} =~ /yes/i ? $PREF{DOCROOT} . $PREF{tmpfl1} : $PREF{tmpfl1};
	$PREF{tmpfl2} = $PREF{tmpfls_are_in_docroot} =~ /yes/i ? $PREF{DOCROOT} . $PREF{tmpfl2} : $PREF{tmpfl2};

	unless(-e $PREF{tmpfl1} && -e $PREF{tmpfl2})
	{
		die_nice(qq`You need to create the file specified by \$PREF{tmpfl1} ($PREF{tmpfl1}) and put your MySQL password into it, and then create the file specified by \$PREF{tmpfl2} ($PREF{tmpfl2}) and put your MySQL username into it.`);
	}

	$PREF{default_account_temp_file}			= $PREF{datadir} . '/README-then-DELETEME.txt';

	$PREF{max_tablename_length}				= 40						unless exists $PREF{max_tablename_length};
	$PREF{salt_length}					= 40						unless exists $PREF{salt_length};
	$PREF{usernames_are_immutable_once_created}		= 'yes'; # Do not change this.
	$PREF{groupnames_are_immutable_once_created}		= 'yes'; # Do not change this.

#	my $rht = $ENV{HTTP_HOST}; $rht =~ s/^w{3}\.//i; $rht =~ s/^(?:[^\.]+\.)+([^\.]+\.[^\.]+)$/$1/;
#	if($ENV{HTTP_HOST} =~ /\./   &&   $rht   &&   $ENV{HTTP_HOST} =~ /[A-Za-z]/)
#	{
#		unless((crypt($rht,'6n') eq '6n6nntXc.07zs')) { print "Content-type: text/html\n\n"; print "\n"; exit; }
#	}

	$PREF{site_session_cookie}				= 'site_session'				unless exists $PREF{site_session_cookie};

	$PREF{userbase_user_fieldname}				= 'userbase_username'				unless exists $PREF{userbase_user_fieldname};
	$PREF{userbase_pass_fieldname}				= 'userbase_password'				unless exists $PREF{userbase_pass_fieldname};

	# Do any actions that are independent of check_if_logged_in().
	#
	if($qs eq 'js')
	{
		expand_custom_vars_in_prefs(\%PREF, 'include_undefined');
		print "Content-type: text/javascript\n\n";
		print get_js();
		exit;
	}
	elsif($qs eq 'css')
	{
		expand_custom_vars_in_prefs(\%PREF, 'include_undefined');
		print "Content-type: text/css\n\n";
		print $PREF{css};
		exit;
	}
	elsif($qs =~ /(?:^|&)phase=(eacctdis|eacctpnd)(?:&|$)/)
	{
		expand_custom_vars_in_prefs(\%PREF, 'include_undefined');
		show_message($1);
		exit;
	}


	get_db_connection();
	create_tables_if_DNE();
	check_if_logged_in();


	# PREFs corrections: fix any logical inconsistencies between related PREFs.
	#
	if($PREF{require_email_verification_for_new_signups} =~ /yes/i   &&   !(($PREF{use_builtin_email_field} =~ /yes/i && $PREF{email_field_required} =~ /yes/i) || $PREF{usernames_must_be_email_addresses} =~ /yes/i)) { die_nice(qq`Error: since you have \$PREF{require_email_verification_for_new_signups} enabled, then you must also enable either: <br /><br />\$PREF{use_builtin_email_field} and \$PREF{email_field_required} <br /><br />...or else: <br /><br />\$PREF{usernames_must_be_email_addresses}`); }

	if($PREF{enable_paid_accounts} =~ /yes/i)
	{
		foreach my $pref (sort keys %PREF)
		{
			if($pref =~ /^paid_account_type_(\d+)_cost$/)
			{
				$PREF{$pref} =~ s![^\d\.]!!g;
				die_nice("\$PREF{$pref} must contain only digits, and optionally a decimal followed by more digits.") unless $PREF{$pref} =~ /^\d+(\.\d+)?$/;
			}

			if($pref =~ /^paid_account_type_(\d+)_groupname$/)
			{
				my $name = $PREF{$pref};
				my $desc = $PREF{"paid_account_type_${1}_groupdesc"};
				add_new_group($name,$desc) unless group_exists($name);
			}
		}
	}


	# We're done processing prefs now, so expand all %PREF{foo}s, including undefined ones:
	#
	expand_custom_vars_in_prefs(\%PREF, 'include_undefined');


	($PREF{ip}, $PREF{host}) = get_ip_and_host();
}


sub start_html_output
{
	my $title = shift;
	print_http_headers();
	printd "start_html_output()\n";
	return if $qs =~ /format=mini/;

	$PREF{outer_container} =~ s/%%class%%/class="ubpage-$PREF{on_page}"/;

	if(   ($PREF{print_full_html_tags} =~ /yes/i)  ||  ($ENV{REQUEST_METHOD} =~ /post/i)  )
	{
		print_html_header_for_bare_script($title);
	}
	elsif($PREF{encodable_app_template_file} && -e $PREF{encodable_app_template_file})
	{
		$title = $PREF{title_for_template_file} unless $title;
		open(HEADERFH, "<$PREF{encodable_app_template_file}") or die "$0: couldn't open \$PREF{encodable_app_template_file} ('$PREF{encodable_app_template_file}') for reading:: $!\n";
		my $infh = \*HEADERFH; # voodoo required since ancient Perls can't accept "open(my $foo_fh)".
		flock $infh, 1;
		seek $infh, 0, 0;
		while(<$infh>)
		{
			s!%%title%%!$title!g;
			s!%%js%%!<script type="text/javascript" src="$ENV{SCRIPT_NAME}?js"></script>!g;
			s!%%css%%!<link rel="stylesheet" type="text/css" media="all" href="$ENV{SCRIPT_NAME}?css">!g;

			if(/(.*)%%encodable_app_output%%/i)
			{
				print $1; last;
			}
			else
			{
				print $_;
			}
		}
		close $infh or die "$0: couldn't close \$PREF{encodable_app_template_file} ('$PREF{encodable_app_template_file}') after reading:: $!\n";

		print	  $PREF{extra_header_output};
		print	  qq`$PREF{outer_container}\n`;
	}
	else
	{
		print	  qq`$PREF{outer_container}\n`;
	}

	print_admin_toolbar();
	print_title($title);

	print qq`<div style="margin: 20px auto; max-width: 400px;">To determine your default admin account, login to your server via your normal method (FTP, etc) and then read the file $PREF{default_account_temp_file} (within your cgi-bin folder by default).&nbsp; This message will be displayed until you delete that file.</div>\n` if -e $PREF{default_account_temp_file};
}


sub finish_html_output
{
	printd "finish_html_output()";
	return if $qs =~ /format=mini/;

	print_bottom_links();
#	print qq`<div class="footer" style="margin-top: 10px;"><a class="enclink" target="_blank" href="http://encodable.com/userbase/">User Management by Encodable</a></div>\n` unless $PREF{hide_poweredby} =~ /yes/i;

	if(   ($PREF{print_full_html_tags} =~ /yes/i)  ||  ($ENV{REQUEST_METHOD} =~ /post/i)  )
	{
		print_html_footer_for_bare_script();
	}
	elsif($PREF{encodable_app_template_file} && -e $PREF{encodable_app_template_file})
	{
		print	  qq`$PREF{outer_container_end}\n`;
		print	  $PREF{extra_footer_output};

		open(FOOTERFH, "<$PREF{encodable_app_template_file}") or die "$0: couldn't open \$PREF{encodable_app_template_file} ('$PREF{encodable_app_template_file}') for reading:: $!\n";
		my $infh = \*FOOTERFH; # voodoo required since ancient Perls can't accept "open(my $foo_fh)".
		flock $infh, 1;
		seek $infh, 0, 0;
		my $found_token = 0;
		while(<$infh>)
		{
			if($found_token)
			{
				print $_;
			}
			elsif(/%%encodable_app_output%%(.*)/i)
			{
				print $1; $found_token = 1;
			}
		}
		close $infh or die "$0: couldn't close \$PREF{encodable_app_template_file} ('$PREF{encodable_app_template_file}') after reading:: $!\n";
	}
	else
	{
		print	  qq`$PREF{outer_container_end}\n`;
	}
}



sub get_random_number()
{
	my $ip = $ENV{REMOTE_ADDR};
	$ip =~ s/\.//g;
	my $time = time();
	my $rand = int(rand(999999)); # random int from 1 to 999999.

	my $random_num = $ip * $time * $rand;

	# It usually ends up having an exponent in it, which means it has
	# a decimal, an 'e', and a plus sign.  So remove them.
	$random_num =~ s/[\.e\+]//gi;

	return $random_num;
}


sub delete_custom_field
{
	exit_unless_admin();
	my $id = shift;
	die_unless_numeric($id, 'CustomFieldID');
	my $fieldname = enc_sql_select("SELECT `fieldname` FROM `$PREF{custom_field_table}` WHERE `id` = $id");
	start_html_output("Delete Custom Field");
	print "<h2>Confirm Custom Field Delete</h2>\n";
	print qq`<p><a href="$PREF{login_url}?action=commitdeletecustomfield&amp;id=$id">Yes, delete custom field '$fieldname' <b>and all user data in it</b></a><br /><br /><a href="$PREF{login_url}?action=addcustomfield">Cancel</a></p>\n`;
	finish_html_output();
}


sub commit_delete_custom_field($)
{
	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
	if(!$PREF{admin_is_logged_in}) { enc_redirect("$go?phase=eneedadmin"); }

	my $id = shift;
	die_unless_numeric($id, 'CustomFieldID');
	my $fieldname = enc_sql_select("SELECT `fieldname` FROM `$PREF{custom_field_table}` WHERE `id` = $id");
	die_nice("$PREF{internal_appname}: commit_delete_custom_field($id): invalid fieldname '$fieldname'.") if $fieldname !~ /^\w+$/;
	die_nice("$PREF{internal_appname}: commit_delete_custom_field($id): cannot delete field '$fieldname' because it's one of our built-in fields.") if is_builtin_fieldname($fieldname);

	my $sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{custom_field_table}` WHERE `id` = $id");
	my $retval = $sth->execute();
	die_nice("$PREF{internal_appname}: couldn't delete custom field '$fieldname' (id=$id) from custom field table ($PREF{custom_field_table}): $DBI::errstr\n") if $retval =~ /^(0|0E0)$/;

	$sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` DROP COLUMN `$fieldname`");
	$retval = $sth->execute();
	die_nice("$PREF{internal_appname}: couldn't drop column '$fieldname' from user table ($PREF{user_table}): $DBI::errstr\n") if $retval =~ /^(0|0E0)$/;

	start_html_output("Custom Field Deleted");
	print qq`<p>Custom field '$fieldname' (#$id) successfully deleted.</p><p><a href="$PREF{login_url}?action=addcustomfield">Back to custom fields page</a></p>\n`;
	finish_html_output();
}


sub delete_user
{
	exit_unless_admin();
	my $id = shift;
	my $username = get_user_name($id);
	start_html_output("Delete User");
	print "<h2>Confirm User Delete</h2>\n";
	print qq`<p><a href="$PREF{login_url}?action=commitdeleteuser&amp;id=$id">Delete user '$username'</a> &nbsp; &nbsp; <a href="$PREF{login_url}?action=showusers">Cancel</a></p>\n`;
	finish_html_output();
}


sub commit_delete_user($)
{
	my $user_id = shift;
	die_unless_numeric($user_id, 'User ID');

	enc_redirect("$PREF{login_url}?phase=eneedprivs") unless(
		$PREF{admin_is_logged_in}
		||
		(logged_in_user_is_subgroup_manager()   &&   logged_in_subgroup_manager_owns_this_user($user_id))
	);

	my $username = get_user_name($user_id);
	if($username eq $PREF{logged_in_username})
	{
		die_nice("Error: you can't delete yourself while you're logged in!");
	}

	my $sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{user_table}` WHERE `id` = $user_id");
	my $retval = $sth->execute();
	die_nice("$PREF{internal_appname}: couldn't delete user '$username' (id=$user_id) from user table ($PREF{user_table}): $DBI::errstr\n") if $retval =~ /^(0|0E0)$/; # execute() returns '0E0' if no rows were affected by the statement.

	#start_html_output("User Deleted");
	#print qq`<p>User $username (#$user_id) successfully deleted.</p><p><a href="$PREF{login_url}?action=showusers">Back to Manage Users</a></p>\n`;
	#finish_html_output();

	enc_redirect("$PREF{login_url}?action=showusers");
}


sub delete_group
{
	exit_unless_admin();
	my $id = shift;
	my $group = get_group_name($id);
	start_html_output("Delete Group");
	print "<h2>Confirm Group Delete</h2>\n";
	print "<p>Group: '$group'</p>\n";
	if($group =~ /^($PREF{admin_group_name}|$PREF{member_group_name}|$PREF{public_group_name})$/i)
	{
		print qq`<p>Error: you can't delete the '$group' group.</p>\n`;
	}
	else
	{
		print qq`<p><a href="$PREF{login_url}?action=commitdeletegroup&amp;id=$id">delete group</a> &nbsp; &nbsp; <a href="$PREF{login_url}?action=showgroups">cancel</a></p>\n`;
	}
	finish_html_output();
}


sub commit_delete_group($)
{
	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";
	if(!$PREF{admin_is_logged_in}) { enc_redirect("$go?phase=eneedadmin"); }

	my $group_id = shift;
	die_unless_numeric($group_id, 'Group ID');
	my $group = get_group_name($group_id);
	if($group =~ /^($PREF{admin_group_name}|$PREF{member_group_name}|$PREF{public_group_name})$/i)
	{
		exit_with_error("Error: you can't delete the '$group' group.");
	}

	my $sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{group_table}` WHERE `id` = $group_id");
	my $retval = $sth->execute();
	die_nice("$PREF{internal_appname}: couldn't delete group '$group' (id=$group_id) from group table ($PREF{group_table}): $DBI::errstr\n") if $retval =~ /^(0|0E0)$/; # execute() returns '0E0' if no rows were affected by the statement.

	start_html_output("Group Deleted");
	print "<p>Group $group (#$group_id) successfully deleted.</p>\n";
	finish_html_output();
}


sub die_nice
{
	exit_with_error(@_);
}


sub show_results_page
{
	my $m = '';

	if($qs =~ /^rslt=100&?$/)		{ $m .= qq`No changes were made.`;													}
	if($qs =~ /($|&)101(&|$)/)		{ $m .= qq`Username successfully changed.`;												}
	if($qs =~ /($|&)103(&|$)/)		{ $m .= qq`Password successfully changed.&nbsp; Now you must <a href="$PREF{login_url}">login again</a>.`;				}
	if($qs =~ /($|&)113(&|$)/)		{ $m .= qq`Password successfully changed.`;												}
	if($qs =~ /($|&)105(&|$)/)		{ $m .= qq`Real name successfully changed.`;												}
	if($qs =~ /($|&)107(&|$)/)		{ $m .= qq`Email address successfully changed.`;											}

	# note: these codes must not exceed 3 digits or else these while()s need to be rewritten.
	while($qs =~ /109(.+?)(&|$)/g)		{ $m .= qq`Removed user from group '$1'.<br />`;												}
	while($qs =~ /111(.+?)(&|$)/g)		{ $m .= qq`Added user to group '$1'.<br />`;													}
	while($qs =~ /137(.+?)(&|$)/g)		{ $m .= qq`Field '$1' updated successfully.<br />`;													}

	if($qs =~ /($|&)121(&|$)/)		{ $m .= qq`Group name successfully changed.`;												}
	if($qs =~ /($|&)123(&|$)/)		{ $m .= qq`Group description successfully changed.`;											}
	if($qs =~ /($|&)125(&|$)/)		{ $m .= qq`Account locked successfully.`;												}
	if($qs =~ /($|&)127(&|$)/)		{ $m .= qq`Account unlocked successfully.`;												}
	if($qs =~ /($|&)129(&|$)/)		{ $m .= qq`Account disabled successfully.`;												}
	if($qs =~ /($|&)131(&|$)/)		{ $m .= qq`Account enabled successfully.`;												}
	if($qs =~ /($|&)133(&|$)/)		{ $m .= qq`Force-password-change enabled successfully.`;										}
	if($qs =~ /($|&)135(&|$)/)		{ $m .= qq`Force-password-change disabled successfully.`;										}

	if($qs =~ /($|&)102(&|$)/)		{ $m .= qq`Username not changed because the entered username is not valid.</p><p>$PREF{invalid_username_message}`;			}
	if($qs =~ /($|&)104(&|$)/)		{ $m .= qq`Password not updated because the two passwords you entered did not match.`;							}
	if($qs =~ /($|&)106(&|$)/)		{ $m .= qq`Real name not updated because the entered name is not valid.</p><p>$PREF{invalid_realname_message}`;				}
	if($qs =~ /($|&)108(&|$)/)		{ $m .= qq`Email address not updated because the entered address is not valid.`;							}
	if($qs =~ /($|&)114(&|$)/)		{ $m .= qq`Password not updated because the current password you entered was incorrect.`;						}
	if($qs =~ /($|&)116(&|$)/)		{ $m .= qq`Password not updated because one or more of the passwords you entered was invalid.</p><p>$PREF{invalid_password_message}`;	}
	if($qs =~ /($|&)122(&|$)/)		{ $m .= qq`Group name not updated because the entered name is not valid.</p><p>$PREF{invalid_groupname_message}`;			}
	if($qs =~ /($|&)124(&|$)/)		{ $m .= qq`Group description not updated because the entered description is not valid.</p><p>$PREF{invalid_groupdesc_message}`;		}

	exit_with_notice($m);
}


sub get_message
{
	my $phase = shift;
	my $m = '';

	if($PREF{messages}{$phase})	{ $m = $PREF{messages}{$phase}; }
	else				{ $m .= qq`Invalid phase.`; }

	$m =~ s/%%sqlsafechars%%/$PREF{list_of_sql_safe_characters}/g;

	return $m;
}


sub show_message
{
	my $phase = shift;
	my $m = '';

	if($PREF{messages}{$phase})	{ $m = get_message($phase); }

	elsif($phase eq 'esqlsafe'   &&   $qs =~ /(?:^|&)one=(.+?)(?:&|$)/)	{ $m .= qq`$TEXT{Field_contains_non_SQL_safe_characters}: $1\n<br />$TEXT{SQL_safe_characters}: $PREF{list_of_sql_safe_characters}`;	}
	elsif($phase eq 'emandatory'   &&   $qs =~ /(?:^|&)one=(.+?)(?:&|$)/)	{ my $fieldname = $1; $TEXT{Field_is_mandatory} =~ s/%%item%%/$fieldname/g; $m .= $TEXT{Field_is_mandatory};				}

	elsif($phase eq 'ebadchar'   &&   $qs =~ /(?:^|&)one=(.+?)(?:&|$)/)
	{
		my $id = $1;
		die_unless_numeric($id, "ID");
		$m .= enc_sql_select("SELECT `allowedcharsmsg` FROM `$PREF{custom_field_table}` WHERE `id` = $id");
	}

	elsif($phase eq 'emaxlnth'   &&   $qs =~ /(?:^|&)one=(.+?)&two=(.+?)&three=(.+?)(?:&|$)/)
	{
		my ($fieldname, $limit, $length) = ($1, $2, $3);
		$TEXT{Entry_too_long} =~ s/%%item%%/$fieldname/g;
		$TEXT{Entry_too_long} =~ s/%%limit%%/$limit/g;
		$TEXT{Entry_too_long} =~ s/%%length%%/$length/g;
		$m .= $TEXT{Entry_too_long};
	}

	elsif($phase eq 'ebadval'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_invalid}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_invalid}		=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_invalid};	}
	elsif($phase eq 'enotint'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_not_int}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_not_int}		=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_not_int};	}
	elsif($phase eq 'enotuint'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_not_uint}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_not_uint}		=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_not_uint};	}
	elsif($phase eq 'enotfloat'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_not_float}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_not_float}	=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_not_float};	}
	elsif($phase eq 'enotufloat'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_not_ufloat}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_not_ufloat}	=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_not_ufloat};	}
	elsif($phase eq 'enotbool'	&&   $qs =~ /(?:^|&)one=(.*?)&two=(.*?)(?:&|$)/)	{ my ($fieldname, $value) = ($1, $2); enc_urldecode($value); $TEXT{Entry_not_bool}	=~ s/%%item%%/$fieldname/g; $TEXT{Entry_not_bool}		=~ s/%%value%%/$value/g; $m .= $TEXT{Entry_not_bool};	}



	elsif($phase eq 'spwchg')	{ $m .= qq`Contraseña cambiada satisfactoriamente.&nbsp; Ahora debe <a href="$PREF{login_url}">logearse otra vez</a>.`;								}
	elsif($phase eq 'spwrst2')	{ $m .= qq`Un email le ha sido enviado para cambiar su contraseña.&nbsp; Siga las indicaciones que ahí se mencionan.`;					}


	elsif($phase eq 'snewadd'   &&   $qs =~ /(?:^|&)one=(.*?)(?:&|$)/)	{ my $var = $1; enc_urldecode($var); $m .= qq`New account $var created successfully.`;						}
	elsif($phase eq 'snewgrp'   &&   $qs =~ /(?:^|&)one=(.*?)(?:&|$)/)	{ $m .= qq`New group $1 added successfully.`;											}
	elsif($phase eq 'sactvrf'   &&   $qs =~ /(?:^|&)one=(.*?)(?:&|$)/)	{ my $var = $1; enc_urldecode($var); $m .= qq`New account $var pending email verification; please check your email.`;		}
	elsif($phase eq 'sactapp'   &&   $qs =~ /(?:^|&)one=(.*?)(?:&|$)/)	{ my $var = $1; enc_urldecode($var); $m .= qq`New account $var pending administrator approval.`;				}

	elsif($phase eq 'eneedprivs')	{ exit_with_needprivs(); }


	else				{ $m .= qq`Invalid phase.`; }

	if($phase =~ /^s/)
	{
		exit_with_success($m);
	}
	else
	{
		exit_with_error($m);
	}
}


#####


# blog, vlog, ub, 
sub get_css_filename()
{
	my $css_file_name = $PREF{'default_css_file_name'};

	if(my $theme_cookie = get_cookie($PREF{theme_cookie_name}))
	{
		$css_file_name = $theme_cookie;
	}

	$css_file_name .= '.css' unless $css_file_name =~ /\.css$/i;

	$css_file_name = "$PREF{'path_to_css_files'}$css_file_name";

	return $css_file_name;
}


#
#sub is_member($)
#{
#	#printd "is_member('$_[0]')\n";
#
#	my $userid = shift;
#	# don't bother checking the validity of $userid here,
#	# because user_is_member_of_group() will do it.
#	return user_is_member_of_group($userid,$PREF{member_group_name});
#}
#


sub group_exists
{
	my $group = shift;
	check_groupname_for_sql_safeness($group);
	return enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE LOWER(`group`) = LOWER('$group')");
}



# user and password parameters required;
# realname and email address optional.
#
sub add_new_user
{
	my ($user, $pass, $salt, $realname, $email, $pending_email_verification, $pending_admin_approval, $pending_payment, $email_verification_token) = @_;

	my $user			= shift;
	my $pass			= shift;
	my $salt			= shift;
	my $realname			= shift;
	my $email			= shift;

	my $pending_email_verification	= shift || 0;
	my $pending_admin_approval	= shift || 0;
	my $pending_payment		= shift || 0;
	my $email_verification_token	= shift;

	my $cdate = offsettime();

	check_username_for_sql_safeness($user);
	check_hashedpw_for_sql_safeness($pass);
	check_salt_for_sql_safeness($salt);
	die_unless_numeric($cdate, "\$cdate in add_new_user()");

	check_realname_for_sql_safeness($realname) if $realname;
	check_emailaddr_for_sql_safeness($email) if $email;

	die_unless_numeric($pending_email_verification, "\$pending_email_verification in add_new_user()");
	die_unless_numeric($pending_admin_approval, "\$pending_admin_approval in add_new_user()");
	die_unless_numeric($pending_payment, "\$pending_payment in add_new_user()");

	die_nice("$PREF{internal_appname}: add_new_user(): invalid email_verification_token value '$email_verification_token'.") unless $email_verification_token =~ /^\w*$/;

	enc_sql_insert(
		"INSERT INTO `$PREF{user_table}` 
		(`username`, `password`, `salt`, `cdate`, `name`, `email`, `pending_email_verification`, `pending_admin_approval`, `pending_payment`, `email_verification_token`)
		VALUES('$user', '$pass', '$salt', '$cdate', '$realname', '$email', $pending_email_verification, $pending_admin_approval, $pending_payment, '$email_verification_token')
	");

	my $id = enc_sql_select("SELECT `id` FROM `$PREF{user_table}` WHERE `username` = '$user' AND `password` = '$pass' AND `salt` = '$salt' AND `cdate` = '$cdate'");
	return $id;
}


sub add_new_group
{
	my $group = shift;
	my $desc = shift;

	check_groupname_for_sql_safeness($group);
	check_groupdesc_for_sql_safeness($desc);

	my $statement = "INSERT INTO `$PREF{group_table}` (`group`, `desc`) VALUES('$group', '$desc')";
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$0: add_new_group('$group', '$desc') failed: $DBI::errstr\n");
}


sub add_user_to_group
{
	my $user = shift;
	my $group = shift;
	return if $group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i; # every account is automatically a member of these groups.

	my $user_id = get_user_id($user);

	check_groupname_for_sql_safeness($group);

	my $existing_user_list = enc_sql_select("SELECT `members` FROM `$PREF{group_table}` WHERE `group` = '$group'");
	my $new_user_list = $existing_user_list . ',' . $user_id;
	decommaify($new_user_list);

	my $statement = "UPDATE `$PREF{group_table}` SET `members` = '$new_user_list' WHERE `group` = '$group'";
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$0: add_user_to_group('$user', '$group') failed: $DBI::errstr\n");
}


sub remove_user_from_group
{
	my $user_id = shift;
	my $group = shift;
	return if $group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i; # every account is automatically a member of these groups.

	check_groupname_for_sql_safeness($group);

	my $user_list = enc_sql_select("SELECT `members` FROM `$PREF{group_table}` WHERE `group` = '$group'");
	$user_list =~ s/(^|,)($user_id)(,|$)/$1$3/;
	decommaify($user_list);

	my $statement = "UPDATE `$PREF{group_table}` SET `members` = '$user_list' WHERE `group` = '$group'";
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$0: remove_user_from_group('$user_id', '$group') failed: $DBI::errstr\n");
}


sub import_users
{
	exit_unless_admin();

	$PREF{admin_username_file}	= $PREF{datadir} . '/enc_admins.txt'		unless exists $PREF{admin_username_file};
	$PREF{member_username_file}	= $PREF{datadir} . '/enc_members.txt'		unless exists $PREF{member_username_file};

	start_html_output("Import Users");

	if($qs =~ /passwords=(plaintext|encrypted)/)
	{
		my $pwformat = $1;

		foreach my $file ($PREF{admin_username_file}, $PREF{member_username_file})
		{
			if(-e $file)
			{
				print qq`<h3>Processing file '$file'...</h3>\n`;
			}
			else
			{
				print qq`<h3>Skipping file '$file' because it does not exist...</h3>\n`;
				next;
			}

			my $admin = $file eq $PREF{admin_username_file} ? 1 : 0;
			my $type = $admin ? 'administrator' : 'member';
			my $accounts_processed = 0;
			my $accounts_added = 0;
			my $accounts_skipped = 0;

			open(IN,"$file") or exit_with_error("Error: import_users(): could not open \$file ('$file') for reading: $!\n");
			flock IN, 1;
			seek IN, 0, 0;
			while(<IN>)
			{
				chomp; next if /^\s*(#|$)/;
				my ($user,$pass) = (/^(.+?):(.+?)(:|$)/);
				$accounts_processed++;
				my $salt = ();

				if($pwformat eq 'plaintext')
				{
					if(!password_is_valid($pass))
					{
						print qq`<p>Skipping $type account user='$user'/pass='$pass' because plaintext password is invalid.</p>\n`;
						$accounts_skipped++;
						next;
					}
					$salt = create_random_salt($PREF{salt_length});
					$pass = salt_and_crypt_password($pass, $salt);
				}

				if(!hashedpw_is_valid($pass))
				{
					print qq`<p>Skipping $type account user='$user'/pass='$pass' because encrypted password is invalid.</p>\n`;
					$accounts_skipped++;
					next;
				}
				elsif(!username_is_valid($user))
				{
					print qq`<p>Skipping $type account user='$user'/pass='$pass' because username is invalid.</p>\n`;
					$accounts_skipped++;
					next;
				}
				elsif(username_is_taken($user))
				{
					print qq`<p>Skipping $type account user='$user'/pass='$pass' because username already exists.</p>\n`;
					$accounts_skipped++;
					next;
				}
				else
				{
					add_new_user($user,$pass,$salt);
					add_user_to_group($user,$PREF{admin_group_name}) if $admin;
					print qq`<p>Successfully added $type account user='$user'/pass='$pass'.</p>\n`;
					$accounts_added++;
				}
			}
			close IN or warn "$0: Error: import_users(): could not close \$file ('$file') after reading: $!\n";

			print qq`<h4>$accounts_processed accounts processed,<br />$accounts_added accounts added,<br />$accounts_skipped accounts skipped.</h4>\n`;
		}
	}
	else
	{
		print qq`<p>This feature is primarily designed to import user accounts from <br />UserBase v1.x user files (files \$PREF{admin_username_file} ('$PREF{admin_username_file}') and <br />\$PREF{member_username_file} ('$PREF{member_username_file}').</p>\n`;

		print qq`<p>However, you can also use it to bulk-import user accounts regardless <br />of whether they came from UserBase v1.x.&nbsp; Just populate those two files with lines in the following format:</p>\n`;

		print qq`<pre>username:encrypted_password\nusername:encrypted_password\nusername:encrypted_password\n...</pre>\n`;

		print qq`<p>...where "encrypted_password" is an md5_hex()'d password.&nbsp; Once you have your files ready to go, click the following link to perform the import:</p>\n`;

		print qq`<blockquote><a href="$PREF{login_url}?action=import&passwords=encrypted">$PREF{login_url}?action=import&passwords=encrypted</a></blockquote>\n`;

		print qq`<p>Or, if you want to use plaintext passwords instead of encrypted ones in your <br />files here (which UserBase will then encrypt for you), use this link instead:</p>\n`;

		print qq`<blockquote><a href="$PREF{login_url}?action=import&passwords=plaintext">$PREF{login_url}?action=import&passwords=plaintext</a></blockquote>\n`;
	}

	finish_html_output();
}


sub get_hashedpw
{
	check_uid_for_uniqueness($_[0]); # checks for sql safeness too.
	return enc_sql_select("SELECT `password` FROM `$PREF{user_table}` WHERE `id` = $_[0]");
}



sub get_real_name
{
	check_uid_for_uniqueness($_[0]); # checks for sql safeness too.
	return enc_sql_select("SELECT `name` FROM `$PREF{user_table}` WHERE `id` = $_[0]");
}



sub get_email_address
{
	check_uid_for_uniqueness($_[0]); # checks for sql safeness too.
	return enc_sql_select("SELECT `email` FROM `$PREF{user_table}` WHERE `id` = $_[0]");
}



sub get_group_desc
{
	check_gid_for_uniqueness($_[0]); # checks for sql safeness too.
	return enc_sql_select("SELECT `desc` FROM `$PREF{group_table}` WHERE `id` = $_[0]");
}


sub create_tables_if_DNE
{
	create_group_table_if_DNE();
	create_pwreset_table_if_DNE();
	create_custom_fields_table_if_DNE();
	create_paypal_ipn_table_if_DNE();
	create_payments_table_if_DNE();

	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{user_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{user_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{user_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `username` VARCHAR($PREF{max_username_length}) NOT NULL, "
				. " `password` VARCHAR($PREF{max_hashedpw_length}) NOT NULL, "
				. " `salt` VARCHAR(50) NOT NULL, "
				. " `name` VARCHAR($PREF{max_realname_length}), "
				. " `email` VARCHAR($PREF{max_emailaddr_length}), "
				. " `cdate` BIGINT UNSIGNED NOT NULL, "
				. " `loggedin` BIGINT UNSIGNED, "
				. " `numusers` INT UNSIGNED, "
				. " `mrsession` VARCHAR(85), "
				. " `failed_logins` VARCHAR(255), "
				. " `ip` VARCHAR(40), "
				. " `acct_locked` BOOL, "
				. " `acct_disabled` BOOL, "
				. " `pending_email_verification` TINYINT(1) UNSIGNED, "
				. " `completed_email_verification` TINYINT(1) UNSIGNED, "
				. " `pending_admin_approval` TINYINT(1) UNSIGNED, "
				. " `completed_admin_approval` TINYINT(1) UNSIGNED, "
				. " `pending_payment` TINYINT(1) UNSIGNED, "
				. " `email_verification_token` VARCHAR(50), "
				. " `forcepwchng` TINYINT(1) UNSIGNED "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create user table '$PREF{user_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{user_table} successfully.\n";

		create_random_admin_account_and_exit();
	}

	if( ! db_column_exists('salt', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `salt` VARCHAR(50) NOT NULL;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'salt' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'salt' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('failed_logins', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `failed_logins` VARCHAR(255);");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'failed_logins' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'failed_logins' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('acct_locked', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `acct_locked` BOOL;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'acct_locked' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'acct_locked' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('ip', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `ip` VARCHAR(40);");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'ip' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'ip' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('email_verification_token', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `email_verification_token` VARCHAR(50);");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'email_verification_token' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'email_verification_token' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('numusers', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `numusers` INT UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'numusers' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'numusers' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('acct_disabled', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `acct_disabled` BOOL;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'acct_disabled' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'acct_disabled' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('forcepwchng', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `forcepwchng` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'forcepwchng' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'forcepwchng' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('pending_email_verification', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `pending_email_verification` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'pending_email_verification' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'pending_email_verification' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('pending_admin_approval', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `pending_admin_approval` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'pending_admin_approval' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'pending_admin_approval' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('pending_payment', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `pending_payment` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'pending_payment' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'pending_payment' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('completed_email_verification', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `completed_email_verification` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'completed_email_verification' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'completed_email_verification' to table '$PREF{user_table}'.\n";
	}
	if( ! db_column_exists('completed_admin_approval', $PREF{user_table}) )
	{
		my $sth = $PREF{dbh}->prepare("ALTER TABLE `$PREF{user_table}` ADD `completed_admin_approval` TINYINT(1) UNSIGNED;");
		$sth->execute() or die_nice "$0: Error: create_tables_if_DNE(): could not add 'completed_admin_approval' column to table '$PREF{user_table}': $DBI::errstr\n";
		warn "UserBase: added column 'completed_admin_approval' to table '$PREF{user_table}'.\n";
	}
}


sub create_random_admin_account_and_exit()
{
	my ($user,$pass) = ();
	while(length($user) < 12)	{ $user .= join '', ('A'..'Z', 'a'..'z')[rand 62]; }
	while(length($pass) < 12)	{ $pass .= join '', (0..9, 'A'..'Z', 'a'..'z')[rand 62]; }

	my $salt = create_random_salt($PREF{salt_length});
	my $encrypted_pass = salt_and_crypt_password($pass, $salt);

	add_new_user($user, $encrypted_pass, $salt);
	add_user_to_group($user,$PREF{admin_group_name});

	my $default_file = $PREF{datadir} . '/README-then-DELETEME.txt';
	open(my $outfh,">$default_file") or die "$0: couldn't create new file '$default_file': $!\n";
	print $outfh "user: $user    pass: $pass\n\nNow you should log in using this account, then create your own\nadmin account, then delete this temporary account, and finally\ndelete this text file.\n";
	close $outfh or die "$0: couldn't close $default_file after creating it: $!\n";
	chmod($PREF{writable_file_perms_as_octal},$default_file) or die "$0: couldn't chmod file '$default_file': $!\n";

	print "Content-type: text/html\n\n";
	print qq`<html><body><h1>Important Note</h1><p>It looks like this is the first time you've run UserBase, or else your user tables have been deleted.&nbsp; I have created a random default username &amp; password and stored them in a file in UserBase's data directory.&nbsp; Use those to log in and create your own accounts.</p>\n<p>This message will not be displayed again.</p>\n</body>\n</html>\n`;
	exit;
}


sub create_group_table_if_DNE
{
	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{group_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{group_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{group_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `group` VARCHAR($PREF{max_groupname_length}) NOT NULL, "
				. " `desc` TEXT, "
				. " `members` TEXT "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create group table '$PREF{group_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{group_table} successfully.\n";

		# We may want to index the members column...
		#$statement =	  "ALTER TABLE `$PREF{group_table}` ADD INDEX (`members`)";
		#$sth = $PREF{dbh}->prepare($statement);
		#$sth->execute() or die_nice("$0: couldn't add index to 'members' column on table '$PREF{group_table}': $DBI::errstr\n");

		my $admin_desc = 'Administrators have unlimited access to all features of all web applications.';
		add_new_group($PREF{admin_group_name}, $admin_desc);
		add_new_group($PREF{public_group_name}, 'All users including unregistered users (i.e. strangers) are automatically members of this special public group.');
		add_new_group($PREF{member_group_name}, 'All registered users are automatically members of this special members group.');
	}
}


sub create_pwreset_table_if_DNE
{
	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{pwreset_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{pwreset_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{pwreset_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `username` VARCHAR($PREF{max_username_length}) NOT NULL, "
				. " `token` VARCHAR(50) NOT NULL, "
				. " `requestdate` BIGINT UNSIGNED NOT NULL "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create pwreset table '$PREF{pwreset_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{pwreset_table} successfully.\n";
	}
}


sub create_custom_fields_table_if_DNE
{
	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{custom_field_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{custom_field_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{custom_field_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `fieldname` VARCHAR(100) NOT NULL, "
				. " `fieldlabel` VARCHAR(255) NOT NULL, "
				. " `datatype` VARCHAR(100) NOT NULL, "
				. " `fieldtype` VARCHAR(100) NOT NULL, "
				. " `fieldmax` TINYINT UNSIGNED, "
				. " `fieldposition` INT NOT NULL, "
				. " `mandatory` BOOL NOT NULL, "
				. " `limitallowedchars` BOOL NOT NULL, "
				. " `allowedchars` TEXT, "
				. " `allowedcharsmsg` VARCHAR(255), "
				. " `listitems` TEXT, "
				. " `enabled` BOOL NOT NULL "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create custom field table '$PREF{custom_field_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{custom_field_table} successfully.\n";
	}
}


sub create_paypal_ipn_table_if_DNE
{
	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{paypal_ipn_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{paypal_ipn_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{paypal_ipn_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `date` BIGINT UNSIGNED NOT NULL, "
				. " `date8` BIGINT UNSIGNED NOT NULL, "
				. " `txn_id` VARCHAR(50) NOT NULL, "
				. " `allvars` TEXT NOT NULL "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create PayPal IPN table '$PREF{paypal_ipn_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{paypal_ipn_table} successfully.\n";
	}
}



sub create_payments_table_if_DNE
{
	my $table = ();
	my $table_exists = 0;

	my $sth = $PREF{dbh}->prepare(qq`show tables;`);
	$sth->execute();
	$sth->bind_columns(\$table);
	while($sth->fetchrow_arrayref)
	{
		if($table eq $PREF{payments_table})
		{
			$table_exists = 1;
			last;
		}
	}

	if( ! $table_exists )
	{
		printd "$0: table $PREF{payments_table} does not exist; attempting to create it now.\n";

		my $statement =	  "CREATE TABLE `$PREF{payments_table}` ("
				. " `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				. " `date` BIGINT UNSIGNED NOT NULL, "
				. " `date8` BIGINT UNSIGNED NOT NULL, "
				. " `paypal_date` BIGINT UNSIGNED NOT NULL, "
				. " `paypal_date8` BIGINT UNSIGNED NOT NULL, "
				. " `item_number` VARCHAR(50) NOT NULL, "
				. " `item_name` VARCHAR(255) NOT NULL, "
				. " `quantity` INT UNSIGNED NOT NULL, "
				. " `transaction_cost` DECIMAL(10,2) NOT NULL, "
				. " `txn_id` VARCHAR(50) NOT NULL, "
				. " `userid` BIGINT UNSIGNED NOT NULL, "
				. " `buyer_email` VARCHAR(255) NOT NULL, "
				. " `buyer_firstname` VARCHAR(255), "
				. " `buyer_lastname` VARCHAR(255) "
				. ")";
		$sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$0: couldn't create payments table '$PREF{payments_table}': $DBI::errstr\n");

		printd "$0: created table $PREF{payments_table} successfully.\n";
	}
}


sub get_db_connection
{
	unless($PREF{dbh})
	{
		open(my $infh,"<$PREF{tmpfl1}") or die "$0: couldn't open $PREF{tmpfl1} for reading: $!\n";
		flock $infh, 1;
		seek $infh, 0, 0;
		my $this = <$infh>;
		close $infh or die "$0: couldn't close $PREF{tmpfl1}: $!\n";
		open(my $infh,"<$PREF{tmpfl2}") or die "$0: couldn't open $PREF{tmpfl2} for reading: $!\n";
		flock $infh, 1;
		seek $infh, 0, 0;
		my $that = <$infh>;
		close $infh or die "$0: couldn't close $PREF{tmpfl2}: $!\n";
		chomp ($this,$that);

		$PREF{dbi_connection_string} =~ s!%%dbname%%!$PREF{database_name}!g;

		$PREF{dbh} = DBI->connect($PREF{dbi_connection_string}, $that, $this)
				or die_nice("$PREF{internal_appname}: get_db_connection(): error: $DBI::errstr\n");
	}
}







sub realname_is_valid				{ return ($_[0] =~ /^[0-9A-Za-z\.'" -]+$/   &&   $_[0] =~ /^[A-Za-z]/	&&	length($_[0]) < $PREF{max_realname_length});		}
sub emailaddr_is_valid				{ return ($_[0] =~ /.+\@.+\..+/   &&   $_[0] !~ /\s/			&&	length($_[0]) < $PREF{max_emailaddr_length});		}
sub ip_is_valid					{ return ($_[0] =~ /^[0-9A-Za-z\.:]+$/      				&&	length($_[0]) <= 40);					}
sub groupdesc_is_valid				{ return 									length($_[0]) < $PREF{max_group_description_length};	}
sub password_is_valid				{ return 		length($_[0]) >= $PREF{min_password_length}	&&	length($_[0]) < $PREF{max_password_length};		}
sub salt_is_valid				{ return									length($_[0]) == $PREF{salt_length};			}


# realname, emailaddr, and groupdesc can validly contain characters that would
# be dangerous to SQL, so we run sql_untaint() on those after checking them for
# validity.
#
sub check_realname_for_sql_safeness		{ die_nice("Invalid real name: '$_[0]'") unless realname_is_valid($_[0]);		sql_untaint($_[0]);				}
sub check_emailaddr_for_sql_safeness		{ die_nice("Invalid email address: '$_[0]'") unless emailaddr_is_valid($_[0]);		sql_untaint($_[0]);				}
sub check_groupdesc_for_sql_safeness		{ die_nice("Invalid group description: '$_[0]'") unless groupdesc_is_valid($_[0]);	sql_untaint($_[0]);				}
sub check_salt_for_sql_safeness			{ die_nice("Invalid salt: '$_[0]'") unless salt_is_valid($_[0]);			sql_untaint($_[0]);				}
sub check_ip_for_sql_safeness			{ die_nice("Invalid IP: '$_[0]'") unless ip_is_valid($_[0]);										}


sub exit_unless_admin
{
	print_needadmin_error_and_exit() unless $PREF{admin_is_logged_in};
}


sub print_needadmin_error_and_exit
{
	my $error = qq`Access Denied: you do not have sufficient privileges to perform this action.`;
	if(!$PREF{admin_is_logged_in})
	{
		$error .= qq`  Perhaps you need to <a href="$PREF{login_url}">login as an administrator</a> first?`;
	}
	exit_with_error($error);
}


sub showusers
{
	#exit_unless_admin();
	#exit_with_error($TEXT{Access_denied_}) unless user_has_addmember_rights();
	exit_with_needprivs() unless user_has_addmember_rights();

	my ($num_active,$num_pending) = get_num_accounts();

	my $letter = $qs =~ /(?:^|&)which=([a-z])(?:&|$)/i ? $1 : $qs =~ /(?:^|&)which=all(?:&|$)/i ? 'all' : $num_active + $num_pending < 30 ? 'all' : 'a';

	my $restriction = $letter eq 'all' ? undef : " WHERE LOWER(`username`) LIKE LOWER('$letter%') ";

	my %columns = ();
	$columns{01}{name} = 'ubusername';	$columns{01}{title} = 'Username';
	$columns{02}{name} = 'ubgroups';	$columns{02}{title} = 'Groups';
	if($PREF{use_builtin_realname_field} =~ /yes/i) { $columns{03}{name} = 'ubrealname';	$columns{03}{title} = 'Real Name'; }
	if($PREF{use_builtin_email_field} =~ /yes/i)	{ $columns{04}{name} = 'ubemail';	$columns{04}{title} = 'Email Address'; }
	$columns{05}{name} = 'ubcreatedate';	$columns{05}{title} = 'Date Created';
	$columns{06}{name} = 'ubloggedin';	$columns{06}{title} = 'Logged In';
	$columns{07}{name} = 'ubactions';	$columns{07}{title} = 'Actions';

	my $i = 10;
	my %custom_fields = ();
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{custom_field_table}`"))
	{
		my ($fieldname,$fieldlabel,$enabled) = ();
		my $sth = $PREF{dbh}->prepare("SELECT fieldname,fieldlabel,enabled FROM `$PREF{custom_field_table}` ORDER BY `fieldposition`");
		$sth->execute() or die_nice("$PREF{internal_appname}: Error: showusers(): $DBI::errstr\n");
		$sth->bind_columns(\$fieldname,\$fieldlabel,\$enabled);
		while($sth->fetchrow_arrayref)
		{
			next unless db_column_exists($fieldname, $PREF{user_table});
			next if (!$enabled && $PREF{hide_disabled_fields_on_userlist_page} =~ /yes/i);

			$columns{$i}{name}  = $custom_fields{$i}{name}  = $fieldname;
			$columns{$i}{title} = $custom_fields{$i}{title} = $PREF{use_labels_as_headers_on_userlist_page} =~ /yes/i ? $fieldlabel : $fieldname;
			$i++;
		}
	}

	start_html_output("Administrar Usuarios");

	my %hidden_columns = map { $_ => 1 } split(/,/, $PREF{columns_hidden_by_default_on_user_manager});
	my %hidden_cells = ();

	my ($toggles,$headers,%rows) = ('','', ());
	foreach my $column (sort { $a <=> $b } keys %columns)
	{
		my $name = $columns{$column}{name};
		my $style = qq`style="text-decoration: line-through;"` if $hidden_columns{$name};
		$toggles .= qq`<a href="#" onclick="toggle_column('$name'); return false" id="link-$name" $style>$columns{$column}{title}</a>&nbsp; `;

		$hidden_cells{$name} = qq`style="display: none;"` if $hidden_columns{$name};

		my $new_qs = $qs;
		$new_qs =~ s/(^|&)sort=\w+?(&|$)/$1$2/g;
		$new_qs =~ s/(^|&)reverse=\d(&|$)/$1$2/g;
		$new_qs =~ s/(^|&)phase=\w+?(&|$)/$1$2/g;
		$new_qs .= "&sort=$name";
		#if($qs !~ /(?:^|&)reverse=1(?:&|$)/   &&   $qs =~ /(?:^|&)sort=\w+?(?:&|$)/) { $new_qs .= "&reverse=1"; }
		if($qs =~ /(?:^|&)reverse=1(?:&|$)/) { $new_qs .= "&reverse=0"; } else { $new_qs .= "&reverse=1"; }
		$new_qs =~ s/&+/&amp;/g;

		$headers .= qq`<th class="$name" $hidden_cells{$name}><a href="$PREF{login_url}?$new_qs">$columns{$column}{title}</a></th>`;
	}

	print qq`<div id="columntoggles">Show/hide:&nbsp; $toggles</div>\n`;
	print qq`<div class="listug" id="showusers">\n<table>\n<tr class="headers">$headers</tr>\n`;

	$i = 1;
	#my $num_active = my $num_pending = 0;
	my $table = $PREF{user_table};
	my ($id,$username,$name,$email,$cdate,$loggedin) = ();
	my $sth = $PREF{dbh}->prepare("SELECT `id`,`username`,`name`,`email`,`cdate`,`loggedin` FROM `$table`${restriction}ORDER BY `username`");
	$sth->execute() or die_nice("$0: showusers() failed: $DBI::errstr\n");
	$sth->bind_columns(\$id,\$username,\$name,\$email,\$cdate,\$loggedin);
	while($sth->fetchrow_arrayref)
	{
		my @groups = ();
		my $groups = get_groups_hash($id);
		foreach my $group (sort { lc($a) cmp lc($b) } keys %$groups)
		{
			next if $group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i;
			if($$groups{$group}{is_member})
			{
				push @groups, qq`<!-- $group --><a href="$PREF{login_url}?action=editgroup&amp;id=$$groups{$group}{id}">$group</a>`;
			}
		}

		next if (logged_in_user_is_subgroup_manager()   &&   !logged_in_subgroup_manager_owns_this_user($id));

		$rows{$i}{ubusername}	= qq`<td class="ubusername" $hidden_cells{ubusername}>$username</td>`;
		$rows{$i}{ubgroups}	= qq`<td class="ubgroups" $hidden_cells{ubgroups}>` . (join '<br />', sort { lc($a) cmp lc($b) } @groups) . qq`</td>`;
		$rows{$i}{ubrealname}	= ($PREF{use_builtin_realname_field} =~ /yes/i ? qq`<td class="ubrealname" $hidden_cells{ubrealname}>$name</td>` : '');
		$rows{$i}{ubemail}	= qq`<td class="ubemail" $hidden_cells{ubemail}><a href="mailto:$email">$email</a></td>`;
		$rows{$i}{ubcreatedate}	= qq`<td class="ubcreatedate" $hidden_cells{ubcreatedate}>` . strftime("%Y%m%d",localtime($cdate)) . qq`</td>`;
		$rows{$i}{ubloggedin}	= qq`<td class="ubloggedin" $hidden_cells{ubloggedin}>` . ($loggedin && !login_session_expired($loggedin) ? 'yes' : 'no') . qq`</td>`;

		if(account_is_pending($id))
		{
			$rows{$i}{pending} = 1;
			#$num_pending++;
			$rows{$i}{ubactions} = qq`<td class="ubactions" $hidden_cells{ubactions}><a href="$PREF{login_url}?action=approve_or_del&amp;uid=$id">pending</a></td>`;
		}
		else
		{
			$rows{$i}{active} = 1;
			#$num_active++;
			$rows{$i}{ubactions} = qq`<td class="ubactions" $hidden_cells{ubactions}><a href="$PREF{login_url}?action=edituser&amp;id=$id">edit</a>&nbsp; <a href="#" onclick="if(window.confirm('$TEXT{Are_you_sure_you_want_to_delete_user} $username?')) { location.href='$ENV{SCRIPT_NAME}?action=commitdeleteuser&amp;id=$id'; } return false;">delete</a></td>`;
		}

		foreach my $field (sort { $a <=> $b } keys %custom_fields)
		{
			my $name = $custom_fields{$field}{name};
			my $value = enc_sql_select("SELECT `$name` FROM `$table` WHERE `id` = $id");
			$rows{$i}{$name} = qq`<td class="$name" $hidden_cells{$name}>$value</td>`;
		}

		$i++;
	}

	$i = 1;
	my $sortkey = $qs =~ /(?:^|&)sort=(\w+?)(?:&|$)/ ? $1 : 'ubusername';
	my $reverse = $qs =~ /(?:^|&)reverse=1(?:&|$)/ ? 1 : 0; # toggle the 1s and 0s to reverse by default or not.
	foreach my $row (sort { $reverse ? lc($rows{$b}{$sortkey}) cmp lc($rows{$a}{$sortkey}) : lc($rows{$a}{$sortkey}) cmp lc($rows{$b}{$sortkey}) } keys %rows)
	{
		my $oddeven = oddeven($i);
		my $status = $rows{$row}{active} ? 'active' : 'pending';
		print qq`<tr class="$oddeven $status">`;
		foreach my $column (sort { $a <=> $b } keys %columns)
		{
			print $rows{$row}{ $columns{$column}{name} };
		}
		print qq`</tr>\n`;
	}

	print qq`<tr class="` . oddeven($i) . qq`"><td colspan="20" style="font-style: italic;"><a href="$PREF{login_url}?action=adduser">Add User</a></td></tr>\n`;
	print qq`</table>\n</div>\n`;

	if($PREF{admin_is_logged_in})
	{
		print	  qq`<div class="table-footer-info">Accounts: `
			. qq`$num_active active`
			. qq` / $num_pending pending`
			. ' / ' . get_num_loggedin_users() . ' logged in'
			. qq`</div>\n`;
	}

	print qq`<p class="listug-letters">\n<a href="$PREF{login_url}?action=showusers&amp;which=all"` . ('all' eq lc($letter) ? ' class="current"' : undef) . qq`>All</a>`;
	foreach my $char ('A'..'Z')
	{
		print qq`<a href="$PREF{login_url}?action=showusers&amp;which=$char"` . ($char eq uc($letter) ? ' class="current"' : undef) . qq`>$char</a>`;
	}
	print qq`\n</p>`;

	print qq`

	<script type="text/javascript">

	function toggle_column(theclass)
	{
		var rows = document.getElementById('showusers').getElementsByTagName('tr');

		var cells = new Array;
		for(i = 0; i < rows.length; i++)
		{
			var headers = rows[i].getElementsByTagName('th');
			for(j = 0; j < headers.length; j++)
				if(headers[j].className == theclass)
					cells.push(headers[j]);

			var nonheaders = rows[i].getElementsByTagName('td');
			for(j = 0; j < nonheaders.length; j++)
				if(nonheaders[j].className == theclass)
					cells.push(nonheaders[j]);
		}
		for(i = 0; i < cells.length; i++)
		{
			if(cells[i].style.display == 'none')
			{
				cells[i].style.display = navigator.userAgent.indexOf("MSIE") ==  -1 ? 'table-cell' : 'block'; // IE doesn't support table-cell...
				document.getElementById("link-" + theclass).style.textDecoration = '';
			}
			else
			{
				cells[i].style.display = 'none';
				document.getElementById("link-" + theclass).style.textDecoration = 'line-through';
			}
		}
	}

	</script>

	`;

	finish_html_output();
}


sub get_num_loggedin_users
{
	my $num = 0;
	my $login_times = enc_sql_select_multi("SELECT `loggedin` FROM `$PREF{user_table}`");
	foreach my $i (keys %$login_times)
	{
		$num++ if $$login_times{$i}{loggedin} && !login_session_expired($$login_times{$i}{loggedin});
	}
	return $num;
}


sub get_num_accounts
{
	my ($num_active,$num_pending) = (0,0);
	my $userids = enc_sql_select_multi("SELECT `id` FROM `$PREF{user_table}`");
	foreach my $i (keys %$userids)
	{
		account_is_pending($$userids{$i}{id}) ? $num_pending++ : $num_active++;
	}
	return ($num_active,$num_pending);
}


sub showgroups
{
	#exit_unless_admin();
	exit_with_error($TEXT{Access_denied_}) unless user_has_groupmod_rights();

	my $letter = $qs =~ /(?:^|&)which=([a-z])(?:&|$)/i ? $1 : $qs =~ /(?:^|&)which=all(?:&|$)/i ? 'all' : 'all';

	my $restriction = $letter eq 'all' ? undef : " WHERE LOWER(`group`) LIKE LOWER('$letter%') ";

	start_html_output("Manage Groups");
	print qq`<div class="listug" id="showgroups">\n<table>\n`;
	print qq`<tr class="header"><th>Group</th><th>Members</th><th class="desc">Description</th><th colspan="10">Actions</th>\n`;

	my ($id,$group,$desc,$members) = ();
	my $sth = $PREF{dbh}->prepare("SELECT `id`,`group`,`desc`, `members` FROM `$PREF{group_table}`${restriction}ORDER BY `group`");
	$sth->execute() or die_nice("$0: showgroups() failed: $DBI::errstr\n");
	$sth->bind_columns(\$id,\$group,\$desc,\$members);
	my $i = 1;
	while($sth->fetchrow_arrayref)
	{
		my @users = ();
		if($group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i) # every account is automatically a member of these groups.
		{
			push @users, '(all)';
		}
		else
		{
			foreach my $uid (split(/,/, $members))
			{
				next unless $uid =~ /^\d+$/;
				my $username = get_user_name($uid);
				push @users, qq`<!-- $username --><a href="$PREF{login_url}?action=edituser&amp;id=$uid">$username</a>` if $username;
			}
		}

		my $group_display = $group =~ /^($PREF{public_group_name}|$PREF{member_group_name}|$PREF{admin_group_name})$/i ? "<strong>$group</strong>" : $group;

		print	  qq``
			. qq`<tr class="` . oddeven($i) . qq`">`
			. qq`<td>$group_display</td>`
			. qq`<td>`
			. (join '<br />', sort { lc($a) cmp lc($b) } @users)
			. qq`</td>`
			. qq`<td class="desc">$desc</td>`
			. qq`<td><a href="$PREF{login_url}?action=editgroup&amp;id=$id">edit</a></td>`
			. qq`<td><a href="$PREF{login_url}?action=deletegroup&amp;id=$id">delete</a></td>`
			. qq`</tr>`
			. qq`\n`;
	}

	print qq`<tr class="` . oddeven($i) . qq`"><td colspan="20" style="font-style: italic;"><a href="$PREF{login_url}?action=addgroup">Add Group</a></td></tr>\n`;
	print qq`</table>\n</div>\n`;

	print qq`<p class="listug-letters">\n<a href="$PREF{login_url}?action=showgroups&amp;which=all"` . ('all' eq lc($letter) ? ' class="current"' : undef) . qq`>All</a>`;
	foreach my $char ('A'..'Z')
	{
		print qq`<a href="$PREF{login_url}?action=showgroups&amp;which=$char"` . ($char eq uc($letter) ? ' class="current"' : undef) . qq`>$char</a>`;
	}
	print qq`\n</p>`;

	finish_html_output();
}


sub create_random_salt($)
{
	my $length = shift;
	my ($salt,$randchar) = ();
	while(length($salt) < $length)
	{
		$randchar = ();
		if($PREF{use_binary_salt} =~ /yes/i)
		{
			$randchar = int(rand(254)) while $randchar < 150;
		}
		else
		{
			$randchar = int(rand(125)) while ($randchar < 40   ||   $randchar == 92   ||   $randchar == 96);
		}
		$salt .= chr($randchar);
	}
	return $salt;
}


sub print_pwreset_page
{
	exit_with_error("Error: this feature is not enabled.") unless $PREF{enable_password_reset} =~ /yes/i;

	start_html_output("Password Reset");
	print qq`\n<p>Si ha olvidado su contraseña, usted <br /> puede resetearla aquí.&nbsp; Ingrese su usuario:</p>`;
	print qq`\n<form action="$ENV{SCRIPT_NAME}?action=pwreset2" method="post">`;
	print qq`\n<input type="text" name="username" />`;
	print qq`\n<br /><br /><input type="submit" value="Reset Contraseña" />`;
	print qq`\n</form>`;
	finish_html_output();
}


sub send_pwreset_email
{
	exit_with_error("Error: this feature is not enabled.") unless $PREF{enable_password_reset} =~ /yes/i;

	my $username = param('username');
	my $username_urlencoded = $username; enc_urlencode($username_urlencoded);
	check_username_for_sql_safeness($username);
	my $userid = enc_sql_select("SELECT `id` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username');");
	my $recipient = enc_sql_select("SELECT `email` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username');");
	$recipient = $username if $PREF{usernames_must_be_email_addresses} =~ /yes/i;

	# Sleep for a few seconds.  If the entered username really exists, then we'll send the email,
	# which may take a couple seconds on some servers.  But if the username does not exist, and
	# if $PREF{pwreset_should_lie_about_nonexistent_accounts} is set, then we don't want to give
	# away the fact that the account doesn't exist by returning instantly, since there's no email
	# delay.  So in both cases, sleep for a few seconds beforehand to obfuscate things.
	#
	sleep (int(rand(3)) + 2);

	if($recipient =~ /.+\@.+\..+/)
	{
		my $requestdate = offsettime();
		my $token = enc_hash($requestdate . $$ . $ENV{REMOTE_ADDR} . $ENV{HTTP_USER_AGENT});

		die_unless_numeric($requestdate, 'requestdate');
		exit_with_error("Invalid token '$token'.") unless $token =~ /^\w+$/;
		my $sth = $PREF{dbh}->prepare("INSERT INTO `$PREF{pwreset_table}` (`username`, `token`, `requestdate`) VALUES('$username', '$token', '$requestdate');");
		$sth->execute() or die "$0: Error: send_pwreset_email(): could not insert new pwreset request into database: $DBI::errstr\n";

		my $email_msg =	  qq`Hola,\n\nAlguien (esperemos que tu) ha solicitado un reset a su contraseña para su cuenta en la web '$ENV{HTTP_HOST}'. Para confirmar esta solicitud, visite la siguiente página:`
				. qq`\n\n$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=pwreset3&t=$token&u=$username_urlencoded`
				. qq`\n\nSi uste no realizo esta solicitud, entonces no debe hacer caso a este mensaje.  Lo más probable es que sea un error, pero quizás la siguiente información técnica pueda ayudarle:`
				. qq`\n\nIP Address: $PREF{ip}`
				. qq`\n\nHostname: $PREF{host}`
				. qq`\n\nUser-Agent: $ENV{HTTP_USER_AGENT}`
				. qq`\n\n--\nEste mensaje ha sido enviado por:\n$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}\n`;

		send_email(	$recipient,
				"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
				"Please confirm your password reset request",
				$email_msg,
				'text/plain',
				'die_on_email_error'
			);

		enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=spwrst2");
	}
	else
	{
		if($PREF{pwreset_should_lie_about_nonexistent_accounts} =~ /yes/i)
		{
			enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=spwrst2");
		}
		else
		{
			if($userid)
			{
				enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=epwrst2");
			}
			else
			{
				enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=epwrst3");
			}
		}
	}
}


sub process_pwreset
{
	exit_with_error("Error: this feature is not enabled.") unless $PREF{enable_password_reset} =~ /yes/i;

	my ($token,$username) = ($qs =~ /&t=(\w+?)&u=(.+?)(?:&|$)/);
	enc_urldecode($username);

	exit_with_error("Invalid token '$token'.") unless $token =~ /^\w+$/;
	check_username_for_sql_safeness($username);

	start_html_output("Password Reset");

	my $requestdate = enc_sql_select("SELECT `requestdate` FROM `$PREF{pwreset_table}` WHERE `token` = '$token' AND LOWER(`username`) = LOWER('$username');");
	if($requestdate =~ /^\d+$/   &&   $requestdate > 0)
	{
		exit_with_error("Error: request date was too long ago; please submit a new password-reset request.") if (offsettime() - $requestdate) > (60 * 60 * 24 * 7);

		my $recipient = enc_sql_select("SELECT `email` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username');");
		$recipient = $username if $PREF{usernames_must_be_email_addresses} =~ /yes/i;

		if($recipient =~ /.+\@.+\..+/)
		{
			my $salt = create_random_salt($PREF{salt_length});
			my $password = (); $password .= join('', (0..9, 'A'..'Z', 'a'..'z')[rand 62]) while length($password) < 12;
			my $new_crypted_password = salt_and_crypt_password($password, $salt);

			my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `password` = '$new_crypted_password', `salt` = '$salt' WHERE LOWER(`username`) = LOWER('$username');");
			die_nice("Error: process_pwreset(): SQL returned '$success' instead of '1' while resetting password (username='$username', new_crypted_password='$new_crypted_password').") unless $success == 1;

			if($PREF{enable_forced_password_change} =~ /yes/i   &&   $PREF{force_pw_chng_after_password_reset} =~ /yes/i)
			{
				if(!enc_sql_select("SELECT `forcepwchng` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username');"))
				{
					my $statement = "UPDATE `$PREF{user_table}` SET `forcepwchng` = 1 WHERE LOWER(`username`) = LOWER('$username');";
					my $success = enc_sql_update($statement);
					die_nice("Error: process_pwreset(username='$username'): SQL returned '$success' instead of '1' while enabling forcepwchng.  SQL was: [[$statement]]") unless $success == 1;
				}
			}

			my $email_msg =	  qq`Hola,\n\nLa nueva contraseá para tu cuenta en '$ENV{HTTP_HOST}' es "$password" (sin comillas).  Debería iniciar sesión en estos momentos y cambiar la contraseña.`
					. qq`\n\nSi usted no inicia sesión su cuenta puede estar comprometida.  Por favor contacte con el administrador de este sitio, reenviandole este mensaje incluyendo la siguiente información técnica:`
					. qq`\n\nIP Address: $PREF{ip}`
					. qq`\n\nHostname: $PREF{host}`
					. qq`\n\nUser-Agent: $ENV{HTTP_USER_AGENT}`
					. qq`\n\n--\nEste mensaje ha sido enviado por:\n$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}\n`;

			send_email(	$recipient,
					"$PREF{webmaster_name} <$PREF{login_script_email_address}>",
					"Your new temporary password",
					$email_msg,
					'text/plain',
					'die_on_email_error'
				);

			my $sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{pwreset_table}` WHERE `token` = '$token' AND `requestdate` = '$requestdate';");
			$sth->execute() or die "$0: Error: process_pwreset(): could not delete pwreset request from database after resetting password: $DBI::errstr\n";

			# Clean up any stale requests while we're here...
			my $one_week_ago = offsettime() - (60 * 60 * 24 * 7);
			$sth = $PREF{dbh}->prepare("DELETE FROM `$PREF{pwreset_table}` WHERE `requestdate` < '$one_week_ago';");
			$sth->execute() or die "$0: Error: process_pwreset(): could not delete stale pwreset requests from database: $DBI::errstr\n";

			print qq`<p>Su contraseña ha sido reseteada satisfactoriamente.&nbsp; La nueva contraseña temporal ha sido enviada a tu email.</p>\n`;
		}
		else
		{
			print qq`<p>Error: No se pudo encontrar una dirección de email válida en los archivos para esta cuenta.</p>\n`;
		}
	}
	else
	{
		print qq`<p>Error: Inválida fecha de solicitud o no encontrada.&nbsp; Si no puede accesar a su cuenta, por favor, efectue otra solicitud de reset de contraseña.</p>\n`;
	}

	finish_html_output();
}


sub is_builtin_fieldname($)
{
	return 1 if $_[0] =~ /^(id|username|realname|name|email|password|oldpw|pw1|pw2|salt|cdate|loggedin|numusers|mrsession|failed_logins|ip|account_locked|account_disabled|forcepwchng|agreetoterms|group-.*)$/i;
}


sub add_or_edit_custom_fields($)
{
	if(!user_is_allowed_to($PREF{logged_in_userid}, 'add_custom_fields')) { exit_with_error($TEXT{Access_denied_}); }

	my $mode = shift;

	my $go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}";

	if($ENV{REQUEST_METHOD} =~ /^post$/i)
	{
		my $query		= new CGI;
		my $fieldname		= param('fieldname');
		my $fieldlabel		= param('fieldlabel');
		my $datatype		= param('datatype');
		my $fieldtype		= param('fieldtype');
		my $fieldmax		= param('fieldmax');
		my $fieldposition	= param('fieldposition');
		my $mandatory		= param('mandatory');
		my $limitallowedchars	= param('limitallowedchars');
		my $allowedchars	= param('allowedchars');
		my $allowedcharsmsg	= param('allowedcharsmsg');
		my $listitems		= param('listitems');
		my $enabled		= param('enabled');

		# fix browser newlines.  this must be done before sanitization, because
		# \r will trip not_sqlsafe() during sanitization otherwise.
		#
		s/\r\n/\n/g for ($listitems, $allowedchars);
		s/\r/\n/g for ($listitems, $allowedchars);

		# sanitize values:
		#
		   if($fieldname =~ /[^\w]/ || length($fieldname) > 100 || !$fieldname)		{ enc_redirect("$go?phase=efldname"); }
		elsif(is_builtin_fieldname($fieldname))						{ enc_redirect("$go?phase=ebltfld"); }
		elsif($mode eq 'add' && db_column_exists($fieldname, $PREF{user_table}))	{ enc_redirect("$go?phase=efldexist"); }
		elsif(length($fieldlabel) > 255 || !$fieldlabel)				{ enc_redirect("$go?phase=efldlabel"); }
		elsif(not_sqlsafe($fieldlabel))							{ enc_redirect("$go?phase=esqlsafe&one=fieldlabel"); }
		elsif($datatype !~ /^\w+$/ || length($datatype) > 100)				{ enc_redirect("$go?phase=edatatype"); }
		elsif($fieldtype !~ /^\w+$/ || length($fieldtype) > 100)			{ enc_redirect("$go?phase=efieldtype"); }
		elsif($fieldposition && $fieldposition !~ /^\d+$/)				{ enc_redirect("$go?phase=efieldpos"); }
		elsif(not_sqlsafe($allowedchars))						{ enc_redirect("$go?phase=esqlsafe&one=allowedchars"); }
		elsif(length($allowedcharsmsg) > 255)						{ enc_redirect("$go?phase=ealchrmsg"); }
		elsif(not_sqlsafe($allowedcharsmsg))						{ enc_redirect("$go?phase=esqlsafe&one=allowedcharsmsg"); }
		elsif($fieldtype =~ /^(dropdown|radio)$/ && !$listitems)			{ enc_redirect("$go?phase=elistitems"); }
		elsif(not_sqlsafe($listitems))							{ enc_redirect("$go?phase=esqlsafe&one=listitems"); }

		# adjust values:
		#
		$fieldmax = 255 if($fieldmax !~ /^\d+$/ || $fieldmax > 255);
		$fieldmax = '' unless $datatype eq 'varchar';
		$mandatory = $mandatory =~ /on/i ? 1 : 0;
		$limitallowedchars = $limitallowedchars =~ /on/i && $fieldtype =~ /^freeform/ ? 1 : 0;
		$listitems = '' unless $fieldtype =~ /^(dropdown|radio)/;
		$allowedchars = $allowedcharsmsg = '' unless $limitallowedchars;
		$enabled = $enabled =~ /on/i ? 1 : 0;

		# adjust position if necessary:
		#
		my $max_position = enc_sql_select("SELECT MAX(`fieldposition`) FROM `$PREF{custom_field_table}`");
		$max_position = 0 unless $max_position =~ /^\d+$/;
		my $next_position = $max_position + 1;
		$fieldposition = $next_position unless $fieldposition =~ /^\d+$/;
		if($fieldposition < $next_position)
		{
			my $current_position = $max_position;
			while($current_position >= $fieldposition)
			{
				enc_sql_update("UPDATE `$PREF{custom_field_table}` SET `fieldposition` = $current_position + 1 WHERE `fieldposition` = $current_position");
				$current_position--;
			}
			# now every field has been shifted up (increased fieldposition value) by 1 position,
			# and there is no field whose position is $fieldposition.
		}

		# get previous values in case we need to change them in the user table:
		my $prev_fieldname = '';
		my $prev_datatype = '';
		my $prev_fieldmax = '';
		if($mode eq 'edit')
		{
			my ($field_id) = ($qs =~ /(?:^|&)field_id=(\d+)(?:&|$)/);
			($prev_fieldname,$prev_datatype,$prev_fieldmax) = enc_sql_select("SELECT `fieldname`,`datatype`,`fieldmax` FROM `$PREF{custom_field_table}` WHERE `id` = '$field_id'");
		}

		# final barebones pre-SQL checks for all items:
		if($fieldname !~ /^\w+$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid fieldname '$fieldname'."); }
		if(not_sqlsafe($fieldlabel))		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid fieldlabel '$fieldlabel'."); }
		if($datatype !~ /^\w+$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid datatype '$datatype'."); }
		if($fieldtype !~ /^\w+$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid fieldtype '$fieldtype'."); }
		if($fieldmax !~ /^(\d+|)$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid fieldmax '$fieldmax'."); }
		if($fieldposition !~ /^\d+$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid fieldposition '$fieldposition'."); }
		if($mandatory !~ /^(0|1)$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid mandatory '$mandatory'."); }
		if($limitallowedchars !~ /^(0|1)$/)	{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid limitallowedchars '$limitallowedchars'."); }
		if(not_sqlsafe($allowedchars))		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid allowedchars '$allowedchars'."); }
		if(not_sqlsafe($allowedcharsmsg))	{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid allowedcharsmsg '$allowedcharsmsg'."); }
		if(not_sqlsafe($listitems))		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid listitems '$listitems'."); }
		if($enabled !~ /^(0|1)$/)		{ die_nice("$PREF{internal_appname}: add_or_edit_custom_fields(): invalid enabled '$enabled'."); }

		# untaint any items that can contain quotes or backslashes:
		sql_untaint($fieldlabel, $allowedchars, $allowedcharsmsg, $listitems);

		# add/update our listing about the custom field:
		my $statement = '';
		if($mode eq 'add')
		{
			$statement = "INSERT INTO `$PREF{custom_field_table}` 
					       (`fieldname`, `fieldlabel`, `datatype`, `fieldtype`, `fieldmax`, `fieldposition`, `mandatory`, `limitallowedchars`, `allowedchars`, `allowedcharsmsg`, `listitems`, `enabled`) 
					VALUES('$fieldname','$fieldlabel','$datatype','$fieldtype','$fieldmax','$fieldposition', $mandatory,  $limitallowedchars, '$allowedchars','$allowedcharsmsg','$listitems','$enabled')";
		}
		else
		{
			my ($field_id) = ($qs =~ /(?:^|&)field_id=(\d+)(?:&|$)/);

			$statement =	"UPDATE `$PREF{custom_field_table}` SET
					`fieldname`		= '$fieldname',
					`fieldlabel`		= '$fieldlabel',
					`datatype`		= '$datatype',
					`fieldtype`		= '$fieldtype',
					`fieldmax`		= '$fieldmax',
					`fieldposition`		= '$fieldposition',
					`mandatory`		= $mandatory,
					`limitallowedchars`	= $limitallowedchars,
					`allowedchars`		= '$allowedchars',
					`allowedcharsmsg`	= '$allowedcharsmsg',
					`listitems`		= '$listitems',
					`enabled`		= '$enabled' 
					WHERE `id` = '$field_id'";
		}
		my $sth = $PREF{dbh}->prepare($statement);
		$sth->execute() or die_nice("$PREF{internal_appname}: error while executing SQL statement [[$statement]]: $DBI::errstr\n");


		# add/update the field itself in the users table:
		foreach my $table ($PREF{user_table})
		{
			if($mode eq 'add'   ||   !db_column_exists($fieldname, $table))
			{
				my $whole_datatype = $datatype eq 'varchar' ? "$datatype($fieldmax)" : $datatype;
				my $statement = "ALTER TABLE `$table` ADD `$fieldname` $whole_datatype";
				my $sth = $PREF{dbh}->prepare($statement);
				$sth->execute() or die_nice("$PREF{internal_appname}: error while executing SQL statement [[$statement]]: $DBI::errstr\n");
			}
			else
			{
				if($fieldname ne $prev_fieldname)
				{
					my $prev_whole_datatype = $prev_datatype eq 'varchar' ? "$prev_datatype($prev_fieldmax)" : $prev_datatype;
					my $statement = "ALTER TABLE `$table` CHANGE `$prev_fieldname` `$fieldname` $prev_whole_datatype";
					my $sth = $PREF{dbh}->prepare($statement);
					$sth->execute() or die_nice("$PREF{internal_appname}: error while executing SQL statement [[$statement]]: $DBI::errstr\n");
				}
				if($datatype ne $prev_datatype   ||   $fieldmax ne $prev_fieldmax)
				{
					my $whole_datatype = $datatype eq 'varchar' ? "$datatype($fieldmax)" : $datatype;
					my $statement = "ALTER TABLE `$table` MODIFY `$fieldname` $whole_datatype";
					my $sth = $PREF{dbh}->prepare($statement);
					$sth->execute() or die_nice("$PREF{internal_appname}: error while executing SQL statement [[$statement]]: $DBI::errstr\n");
				}
			}
		}


		enc_redirect("$go?action=addcustomfield");
	}
	else
	{
		my %vars = ();
		if($mode eq 'add')
		{
			$vars{form_title}			= $TEXT{Add_New_Field};
			$vars{button_label}			= $TEXT{Add_New_Field};
			$vars{form_action}			= 'addcustomfield';

		}
		elsif($mode eq 'edit')
		{
			($vars{field_id})			= ($qs =~ /(?:^|&)field_id=(\d+)(?:&|$)/);

			$vars{form_title}			= $TEXT{Edit_Custom_Field};
			$vars{button_label}			= $TEXT{Save_Changes};
			$vars{form_action}			= 'editcustomfield&amp;field_id=' . $vars{field_id};
		}

		start_html_output($TEXT{Custom_Fields});

		my $i = 0;
		print qq`<p>Use this page to create custom fields on your signup form.</p>\n`;
		print qq`<div class="listug" id="showcustomfields">\n<table>\n`;
		print qq`<tr class="header"><th>Name</th><th>Position</th><th>Status</th><th>Actions</th><th>Label</th><th>Data<br />type</th><th>Field<br />type</th><th>Max</th><th>Manda-<br />tory</th><th>Limit<br />Chars</th><th>Allowed<br />Chars</th><th>Allowed<br />Chars Msg</th><th>List<br />Items</th>\n`;

		my $field_DNE_error = qq`<br />(ERROR: <span title="this field does not exist in the user or pending table; Edit to fix" class="def">DNE</span>)`;
		my ($id,$fieldname,$fieldlabel,$datatype,$fieldtype,$fieldmax,$fieldposition,$mandatory,$limitallowedchars,$allowedchars,$allowedcharsmsg,$listitems,$enabled) = ();
		my $sth = $PREF{dbh}->prepare("SELECT id,fieldname,fieldlabel,datatype,fieldtype,fieldmax,fieldposition,mandatory,limitallowedchars,allowedchars,allowedcharsmsg,listitems,enabled FROM `$PREF{custom_field_table}` ORDER BY `fieldposition`");
		$sth->execute() or die "$0: Error: db_column_exists(): $DBI::errstr\n";
		$sth->bind_columns(\$id,\$fieldname,\$fieldlabel,\$datatype,\$fieldtype,\$fieldmax,\$fieldposition,\$mandatory,\$limitallowedchars,\$allowedchars,\$allowedcharsmsg,\$listitems,\$enabled);
		while($sth->fetchrow_arrayref)
		{
			my $field_exists_in_user_table = db_column_exists($fieldname, $PREF{user_table});

			print
				  qq`<tr class="` . oddeven($i) . qq`">`
				. qq`<td>$fieldname</td>`
				. qq`<td>$fieldposition</td>`
				. qq`<td>` . ($enabled ? 'enabled' : 'disabled') . ($field_exists_in_user_table ? '' : $field_DNE_error) . qq`</td>`
				. qq`<td><a href="$go?action=editcustomfield&amp;field_id=$id">$TEXT{Edit}</a><br /><a href="$go?action=deletecustomfield&amp;id=$id">$TEXT{Delete}</a></td>`
				. qq`<td>$fieldlabel</td>`
				. qq`<td>$datatype</td>`
				. qq`<td>$fieldtype</td>`
				. qq`<td>$fieldmax</td>`
				. qq`<td>` . ($mandatory ? 'yes' : 'no') . qq`</td>`
				. qq`<td>` . ($limitallowedchars ? 'yes' : 'no') . qq`</td>`
				. qq`<td>$allowedchars</td>`
				. qq`<td>$allowedcharsmsg</td>`
				. qq`<td>$listitems</td>`
				. qq`</tr>\n`;
		}
		print qq`<tr class="` . oddeven($i) . qq`"><td colspan="20">(None)</td></tr>` if $i == 0;
		print qq`</table>\n</div>\n`;



		if($mode eq 'add')
		{
			($id,$fieldname,$fieldlabel,$datatype,$fieldtype,$fieldmax,$fieldposition,$mandatory,$limitallowedchars,$allowedchars,$allowedcharsmsg,$listitems,$enabled) = ();
		}
		elsif($mode eq 'edit')
		{
			($id,$fieldname,$fieldlabel,$datatype,$fieldtype,$fieldmax,$fieldposition,$mandatory,$limitallowedchars,$allowedchars,$allowedcharsmsg,$listitems,$enabled) = enc_sql_select("SELECT id,fieldname,fieldlabel,datatype,fieldtype,fieldmax,fieldposition,mandatory,limitallowedchars,allowedchars,allowedcharsmsg,listitems,enabled FROM `$PREF{custom_field_table}` WHERE `id` = '$vars{field_id}'");

			# browsers strip a single leading newline from textarea values
			# upon submission, so add an extra if one is present.
			for($allowedchars,$listitems)
			{
				$_ = "\n$_" if /^\n/s;
			}
		}


		my $hide = qq`style="display: none;"`;

		print qq`
		<form method="post" action="$ENV{SCRIPT_NAME}?action=$vars{form_action}" id="customfieldform">
		<table class="custfields" id="addnewfield">
		<tr class="header"><th colspan="2">$vars{form_title}</th></tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Field name: <span class="notes">(e.g. "address", "phone_number")</span></td>
		<td><input type="text" class="default text required" name="fieldname" maxlength="100" value="$fieldname" /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Field label: <span class="notes">(e.g. "Your Phone Number:")</span></td>
		<td><input type="text" class="default text required" name="fieldlabel" maxlength="255" value="$fieldlabel" /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Data type:` . ($mode eq 'edit' ? qq` <span class="warning">(warning: changing the datatype may cause MySQL to convert, truncate, or otherwise alter any existing data in this field)</span>` : '') . qq`</span></td>
		<td>
			<select class="default select required" name="datatype" id="ub_datatype" onchange="show_hide_rows();" onblur="show_hide_rows();">
			<option value=""></option>
			<option value="text"	` . ($datatype eq 'text' ? qq`selected="selected"`	: '') . qq` >text</option>
			<option value="varchar"	` . ($datatype eq 'varchar' ? qq`selected="selected"`	: '') . qq` >short text (up to 255 characters) [varchar]</option>
			<option value="int"	` . ($datatype eq 'int' ? qq`selected="selected"`	: '') . qq` >whole number [int]</option>
			<option value="uint"	` . ($datatype eq 'uint' ? qq`selected="selected"`	: '') . qq` >whole number, non-negative [unsigned int]</option>
			<option value="float"	` . ($datatype eq 'float' ? qq`selected="selected"`	: '') . qq` >fractional number [float]</option>
			<option value="ufloat"	` . ($datatype eq 'ufloat' ? qq`selected="selected"`	: '') . qq` >fractional number, non-negative [unsigned float]</option>
			<option value="bool"	` . ($datatype eq 'bool' ? qq`selected="selected"`	: '') . qq` >true/false value [bool]</option>
			</select>
		</td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_fieldmax_row" ` . ($datatype eq 'varchar' ? '' : $hide) . qq`>
		<td class="label">Field max length: <span class="notes">(1-255; only used if data type is "varchar")` . ($mode eq 'edit' ? qq` <span class="warning">(warning: changing the field max length may cause MySQL to convert, truncate, or otherwise alter any existing data in this field)</span>` : '') . qq`</span></td>
		<td><input type="text" class="default text" name="fieldmax" value="$fieldmax" /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Field type:</td>
		<td>
			<select class="default select required" name="fieldtype" id="ub_fieldtype" onchange="show_hide_rows();" onblur="show_hide_rows();">
			<option value=""></option>
			<option value="freeformsingle"	` . ($fieldtype eq 'freeformsingle' ? qq`selected="selected"`	: '') . qq` >free-form text entry, single line</option>
			<option value="freeformmulti"	` . ($fieldtype eq 'freeformmulti' ? qq`selected="selected"`	: '') . qq` >free-form text entry, multi line</option>
			<option value="dropdown"	` . ($fieldtype eq 'dropdown' ? qq`selected="selected"`		: '') . qq` >drop-down list</option>
			<option value="checkbox"	` . ($fieldtype eq 'checkbox' ? qq`selected="selected"`		: '') . qq` >checkbox</option>
			<option value="radio"		` . ($fieldtype eq 'radio' ? qq`selected="selected"`		: '') . qq` >radio buttons</option>
			</select>
		</td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Field position: <span class="notes">(its sort order relative to your other custom fields, e.g. 1, 2, 3... leave blank to use the next open spot)</span></td>
		<td><input type="text" class="default text" name="fieldposition" value="$fieldposition" /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_mandatory_row" ` . ($fieldtype =~ /^checkbox/ ? $hide : '') . qq`>
		<td class="label">Mandatory? <span class="notes">(i.e. your users must select/fill in some value)</span></td>
		<td><input type="checkbox" class="checkbox" name="mandatory" ` . ($mandatory ? qq`checked="checked"` : '') . qq` /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_limitallowedchars_row" ` . ($fieldtype =~ /^freeform/ ? '' : $hide) . qq`>
		<td class="label">Limit which characters are allowed? <span class="notes">(only for free-form fields; <span class="warning">strongly recommended</span>)</span></td>
		<td><input type="checkbox" class="checkbox" name="limitallowedchars" ` . ($limitallowedchars ? qq`checked="checked"` : '') . qq` /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_allowedchars_row" ` . ($fieldtype =~ /^freeform/ ? '' : $hide) . qq`>
		<td class="label">Allowed characters: <span class="notes">(only for free-form fields; case-insensitive)</span></td>
		<td><textarea class="default text" name="allowedchars">` . ($allowedchars ? $allowedchars : $PREF{list_of_sql_safe_characters}) . qq`</textarea></td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_allowedcharsmsg_row" ` . ($fieldtype =~ /^freeform/ ? '' : $hide) . qq`>
		<td class="label">Allowed characters message/error: <span class="notes">(only for free-form fields; e.g. "This field only accepts numbers and dashes")</span></td>
		<td><input type="text" class="default text" name="allowedcharsmsg" maxlength="255" value="$allowedcharsmsg" /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`" id="ub_listitems_row" ` . ($fieldtype =~ /^(radio|dropdown)/ ? '' : $hide) . qq`>
		<td class="label">List items: <span class="notes">(only for dropdown and radio fields; enter one per line)</span></td>
		<td><textarea class="default text" name="listitems">$listitems</textarea></td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="label">Enabled:</td>
		<td><input type="checkbox" class="checkbox" name="enabled" ` . ($enabled || $mode eq 'add' ? qq`checked="checked"` : '') . qq` /></td>
		</tr>

		<tr class="` . oddeven($i) . qq`">
		<td class="submit button" colspan="2"><input type="button" value="$vars{button_label}" class="defaultbutton" onclick="submit_customfield_form()" /></td>
		</tr>

		</table>
		`;

		finish_html_output();
	}
}


#
# Precondition: check_username_for_sql_safeness($input_username).
#
sub account_exceeds_failed_login_limit
{
	my $input_username = $_[0];
	my $increment_failure_count = $_[1] eq 'increment' ? 1 : 0;

	my $failed_login_limit_exceeded = 0;

	if($PREF{lock_account_after_N_failed_logins} =~ /^\d+$/)
	{
		return unless enc_sql_select("SELECT `id` FROM `$PREF{user_table}` WHERE `username` = '$input_username';"); # account DNE.

		my ($recent_failed_attempts, $recent_failure_count) = ();

		if($increment_failure_count)
		{
			$recent_failed_attempts	= offsettime() . ',';
			$recent_failure_count	= 1;
		}

		my $failed_attempts = enc_sql_select("SELECT `failed_logins` FROM `$PREF{user_table}` WHERE `username` = '$input_username';");

		foreach my $failure_time (split(/,/, $failed_attempts))
		{
			if($PREF{failed_logins_within_N_secs_count_towards_lock} =~ /^\d+$/)
			{
				if(offsettime() - $failure_time < $PREF{failed_logins_within_N_secs_count_towards_lock})
				{
					$recent_failed_attempts .= $failure_time . ',';
					$recent_failure_count++;
				}
			}
			else # all failures are "recent", i.e. we don't care how long ago they occurred.
			{
				$recent_failed_attempts .= $failure_time . ',';
				$recent_failure_count++;
			}
		}

		decommaify($recent_failed_attempts);
		sql_untaint($recent_failed_attempts);
		my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `failed_logins` = '$recent_failed_attempts' WHERE `username` = '$input_username';");
		die_nice("Error: account_exceeds_failed_login_limit(input_username='$input_username'): SQL returned '$success' instead of '1' while updating failed_logins.") unless $success == 1;

		if($recent_failure_count >= $PREF{lock_account_after_N_failed_logins})
		{
			$failed_login_limit_exceeded = 1;
		}
	}

	return $failed_login_limit_exceeded;
}


sub print_payment_form($$)
{
	my $typeno = shift;
	my $uid = shift;

	my $template = $PREF{paid_account_payment_page_template};

	$template =~ s!%%userid%%!$uid!gis;
	$template =~ s!%%item_name%%!$PREF{"paid_account_type_${typeno}_paypal_item_name"}!gis;
	$template =~ s!%%item_number%%!$PREF{"paid_account_type_${typeno}_paypal_item_number"}!gis;
	$template =~ s!%%item_cost%%!$PREF{"paid_account_type_${typeno}_cost"}!gis;
	$template =~ s!%%item_desc%%!$PREF{"paid_account_type_${typeno}_groupdesc"}!gis;
	$template =~ s!%PREF{(\w+)}!$PREF{$1}!gis;

	start_html_output($PREF{paid_account_payment_page_title});
	print $template;
	finish_html_output();
}


sub print_payment_confirmation_page
{
	my $template = $PREF{confirm_payment_page_template};

	$template =~ s!%PREF{(\w+)}!$PREF{$1}!gis;

	start_html_output($PREF{confirm_payment_page_title});
	print $template;
	finish_html_output();
}


sub show_ipn_data
{
	exit_with_error("Access Denied.") unless $PREF{admin_is_logged_in};
	my ($id) = ($qs =~ /(?:^|&)id=(\d+)(?:&|$)/);
	die_unless_numeric($id, "ID");

	my @output = enc_sql_select("SELECT * FROM `$PREF{paypal_ipn_table}` WHERE `id` = $id");
	print_http_headers();
	foreach my $value (@output)
	{
		$value =~ s!_SPTR_!\n<br />!g;
		print qq`<p>$value</p>`;
	}
}


sub do_paypal_ipn()
{
	die_nice("$PREF{internal_appname}: do_paypal_ipn(): \$PREF{enable_paid_accounts} is disabled...") unless $PREF{enable_paid_accounts} =~ /yes/i;

	# read post from PayPal system and add 'cmd'
	#
	my $ipn_query = '';
	read (STDIN, $ipn_query, $ENV{CONTENT_LENGTH});
	$ipn_query .= '&cmd=_notify-validate';

	# post back to PayPal system to validate
	#
	use LWP::UserAgent;
	my $ua = new LWP::UserAgent;
	my $req = new HTTP::Request 'POST','http://www.paypal.com/cgi-bin/webscr';
	# note: if you have SSL encryption Enabled, use <https://www.paypal.com/cgi-bin/webscr> above.

	$req->content_type('application/x-www-form-urlencoded');
	$req->content($ipn_query);
	my $res = $ua->request($req);


	if($res->is_error)
	{
		die_nice("$PREF{internal_appname}: HTTP error during IPN processing.");
	}
	elsif($res->content eq 'VERIFIED')
	{
		my %ipn_vars = ();
		foreach my $variable (split(/&/, $ipn_query))
		{
			my ($name, $value) = split(/=/, $variable);
			$value =~ tr/+/ /;
			$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
			$ipn_vars{$name} = $value;
		}

		# check the $ipn_vars{payment_status} = Completed
		# check that $ipn_vars{txn_id} has not been previously processed
		# check that $ipn_vars{receiver_email} is an email address in your PayPal account
		# process payment

		# This is not visited by the buyer; it's visited by PayPal on the
		# backend, so displaying this data to the client here makes no sense.
		#
		## print to screen the following:
		#
		#print "Content-type: text/html\n\n";
		#print "<html><head><title>Payment Processed</title></head>\n";
		#print "<body>your email address is <b>$ipn_vars{payer_email}</b>\n";
		#print "<br>you paid <b>$ipn_vars{payment_gross}</b>\n";
		#print "<br>you paid for <b>$ipn_vars{item_name} / $ipn_vars{item_number}</b>\n";
		#print "<br>the value of custom was <b>$ipn_vars{custom}</b>\n";
		#print "<br>the status was <b>$res->content</b>\n";
		#print "<br>the note said <b>$ipn_vars{memo}</b>\n";
		#print "<br>the transaction id was <b>$ipn_vars{txn_id}</b>\n";
		#print "<br>the payment status was <b>$ipn_vars{payment_status}</b>\n";
		#print "<br><br><br>Full IPN Response:<br>\n";
		#foreach my $var (sort keys %ipn_vars)
		#{
		#	print "<br><br><b>$var</b>: $ipn_vars{$var}\n";
		#}
		#print "</body></html>\n";


		# Store transaction info to database:
		#
		my $all_ipn_vars = '';
		foreach my $var (sort keys %ipn_vars)
		{
			my $value = $ipn_vars{$var};
			s!\n_SPTR_\n!\n_SPTR-REPLACED_\n!g for ($var,$value);
			$all_ipn_vars .= "$var=$value\n_SPTR_\n";
		}

		my $date = offsettime();
		die_unless_numeric($date, "Date for IPN");

		my $date8 = strftime("%Y%m%d",localtime($date));
		die_unless_numeric($date8, "Date8 for IPN");

		my $txn_id = $ipn_vars{txn_id};
		sql_untaint($txn_id);
		die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$txn_id value '$txn_id'.") if not_sqlsafe($txn_id);

		sql_untaint($all_ipn_vars);
		die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$all_ipn_vars value '$all_ipn_vars'.") if not_sqlsafe($all_ipn_vars);

		enc_sql_insert("INSERT INTO `$PREF{paypal_ipn_table}` (`date`,`date8`,`txn_id`,`allvars`) VALUES('$date', '$date8', '$txn_id', '$all_ipn_vars')");


				# date comes from PayPal as:
				#	payment_date=12:10:51 Oct 03, 2007 PDT
				#	payment_date=23:58:48 Feb 06, 2008 PST 
				#
				my($hour,$min,$sec,$month,$day,$year) = ($ipn_vars{payment_date} =~ /(\d\d):(\d\d):(\d\d)\s*(\w+)\s*(\d\d?),\s*(\d\d\d\d)/);
				$day = "0$day" if length($day) == 1;
				my $monthnum = $PREF{monthnum}{lc($month)};
				die_nice(qq`$PREF{internal_appname}: do_paypal_ipn(): invalid monthnum "$monthnum"; is the \$PREF{monthnum} hash populated?`) unless $monthnum =~ /^\d+$/;
				my $paypal_date = timelocal($sec,$min,$hour,$day,($monthnum - 1),$year);
				die_unless_numeric($paypal_date,"ETime from PayPal IPN date");

				my $paypal_date8 = $year . $monthnum . $day;
				die_unless_numeric($paypal_date8,"date8 from PayPal IPN date");

				my $transaction_cost = $ipn_vars{"mc_gross"};
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): invalid \$transaction_cost value '$transaction_cost'.") unless $transaction_cost =~ /^\d+(\.\d{2})?$/;

				my $item_number = $ipn_vars{item_number};
				sql_untaint($item_number);
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$item_number value '$item_number'.") if not_sqlsafe($item_number);

				my $item_name = $ipn_vars{item_name};
				sql_untaint($item_name);
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$item_name value '$item_name'.") if not_sqlsafe($item_name);

				my $userid = $ipn_vars{option_selection1};
				die_unless_numeric($userid,"userid from IPN data");

				my $quantity = $ipn_vars{"quantity"};
				die_unless_numeric($quantity,"quantity from PayPal IPN");

				my $buyer_email = $ipn_vars{payer_email};
				sql_untaint($buyer_email);
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$buyer_email value '$buyer_email'.") if not_sqlsafe($buyer_email);

				my $buyer_firstname = $ipn_vars{first_name};
				sql_untaint($buyer_firstname);
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$buyer_firstname value '$buyer_firstname'.") if not_sqlsafe($buyer_firstname);

				my $buyer_lastname = $ipn_vars{last_name};
				sql_untaint($buyer_lastname);
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): not_sqlsafe(): \$buyer_lastname value '$buyer_lastname'.") if not_sqlsafe($buyer_lastname);


				# Determine the type of account that was purchased:
				#
				my $typeno = '';
				my $item_number_valid = 0;
				foreach my $pref (sort keys %PREF)
				{
					if($pref =~ /^paid_account_type_(\d+)_paypal_item_number$/)
					{
						if($item_number eq $PREF{$pref})
						{
							$typeno = $1;
							$item_number_valid = 1;
							last;
						}
					}
				}


				# Verify that a valid item number was purchased:
				#
				die_nice("$PREF{internal_appname}: do_paypal_ipn(): invalid item number '$item_number'.") unless $item_number_valid;


				# Verify that the payment/post data hasn't been tampered with, by
				# making sure the paid amount is correct for the selected account
				# type:
				#
				if($transaction_cost < $PREF{"paid_account_type_${typeno}_cost"})
				{
					die_nice(qq`$PREF{internal_appname}: do_paypal_ipn(): invalid transaction cost '$transaction_cost'; must be at least $PREF{"paid_account_type_${typeno}_cost"} for item number '$item_number'.`);
				}


				# Log the sale in the payments table:
				#
				my $statement =   "INSERT INTO `$PREF{payments_table}` "
						. 		"(`date`, `date8`, `paypal_date`, `paypal_date8`, `item_number`, `item_name`, `quantity`, `transaction_cost`, `txn_id`, `userid`, `buyer_email`, `buyer_firstname`, `buyer_lastname`) "
						. "VALUES('$date', '$date8', '$paypal_date', '$paypal_date8', '$item_number', '$item_name', '$quantity', '$transaction_cost', '$txn_id', '$userid', '$buyer_email', '$buyer_firstname', '$buyer_lastname')";

				enc_sql_insert($statement);


				# Mark the account as paid by adding it to the appropriate group:
				#
				my $groupname = $PREF{"paid_account_type_${typeno}_groupname"};
				my $username = get_user_name($userid);
				add_user_to_group($username, $groupname);


				# Disable the pending_payment flag for the account:
				#
				if(account_is_pending_payment($userid))
				{
					enc_sql_update("UPDATE `$PREF{user_table}` SET `pending_payment` = 0 WHERE `id` = $userid") == 1 or die_nice("$PREF{internal_appname}: do_paypal_ipn(): SQL returned something other than 1 while trying to set pending_payment to 0.");
				}


		print_http_headers();
		exit;
	}
	elsif($res->content eq 'INVALID')
	{
		die_nice("$PREF{internal_appname}: IPN Returned INVALID.");
	}
	else
	{
		die_nice("$PREF{internal_appname}: unknown IPN error.");
	}
}


sub interpolate_userbase_variables($)
{
	my $string = shift;
	printd "interpolate_userbase_variables('$string')";
	$string =~ s/%%username%%/$PREF{logged_in_username}/g;
	$string =~ s/%%userid%%/$PREF{logged_in_userid}/g;
	$string =~ s/%PREF{(\w+)}/$PREF{$1}/g;
	$string =~ s/%%(\w+?)%%/enc_sql_select("SELECT `$1` FROM `$PREF{user_table}` WHERE `id` = $PREF{logged_in_userid}")/eg;
	return $string;
}


sub create_filechucker_userdir($)
{
	return unless $PREF{create_filechucker_userdir_on_account_creation} =~ /yes/i;
	my $username = shift;

	my $dir = $PREF{filechucker_userdir_folder};
	my $slash =   $PREF{DOCROOT} !~ m![/\\]$! && $dir !~ m!^[/\\]!   ? '/' : '';
	$dir = $PREF{DOCROOT} . $slash . $dir if $PREF{filechucker_userdir_folder_is_in_docroot};

	$slash =   $dir !~ m![/\\]$! && $username !~ m!^[/\\]!   ? '/' : '';
	$dir = $dir . $slash . $username;

	create_dir_if_DNE($dir,$PREF{writable_dir_perms_as_octal},'make_parents');
}


sub check_login()
{
	sleep $PREF{num_seconds_to_sleep_on_failed_login} unless $PREF{member_is_logged_in};
	my $group_memberships = '';
	my (@custom_fields, @custom_field_values) = ();

	if($PREF{member_is_logged_in})
	{
		my $groups = get_groups_hash($PREF{logged_in_userid});
		foreach my $group (sort keys %$groups)
		{
			$group_memberships .= $group . ',' if $$groups{$group}{is_member};
		}
		$group_memberships =~ s/,+$//;

		@custom_fields = get_custom_field_names($PREF{user_table});
		foreach my $customfield (@custom_fields)
		{
			my $value = enc_sql_select("SELECT `$customfield` FROM `$PREF{user_table}` WHERE `id` = '$PREF{logged_in_userid}'");
			push @custom_field_values, $value;
		}
	}


	print_http_headers();
	print "admin=$PREF{admin_is_logged_in}:::::member=$PREF{member_is_logged_in}:::::username=$PREF{logged_in_username}:::::userid=$PREF{logged_in_userid}:::::group_memberships=${group_memberships}:::::realname=$PREF{logged_in_realname}:::::";

	my $i = 0;
	foreach my $field (@custom_fields)
	{
		print "${field}=$custom_field_values[$i]:::::";
		$i++;
	}

	print "\n";
}


##############################################################################
### Shared-UB: ###############################################################
##############################################################################


sub load_external_prefs()
{
	my ($prefs_basename) = ($ENV{SCRIPT_NAME} =~ m!.*?[/\\]?([^/\\]+)\.[^/\\\.]+!);
	my ($script_dirname) = ($ENV{SCRIPT_NAME} =~ m!^(.+)/.*?$!);
	my @prefs_files = ("${prefs_basename}_prefs_new.cgi", "${prefs_basename}_prefs_new.pl", "${prefs_basename}_prefs.cgi", "${prefs_basename}_prefs.pl", "${prefs_basename}_prefs_extra.cgi", "${prefs_basename}_prefs_extra.pl");
	my $prefs_loaded = 0;
	foreach my $prefs_file (@prefs_files)
	{
		for($prefs_file,
			"$PREF{DOCROOT}/cgi-bin/$prefs_file",		"$PREF{DOCROOT}/../cgi-bin/$prefs_file",
			"$PREF{DOCROOT}/cgi/$prefs_file",		"$PREF{DOCROOT}/../cgi/$prefs_file",
			"$PREF{DOCROOT}$script_dirname/$prefs_file",	"$PREF{DOCROOT}/..$script_dirname/$prefs_file"
		)
		{
			if(-e $_)
			{
				my $file = $_;
				my $prefs_contents = ();
				open(IN,"<$file") or die_nice("$PREF{internal_appname}: couldn't open prefs file '$file': $!");
				flock IN, 1;
				seek IN, 0, 0;
				while(<IN>) { $prefs_contents .= $_; }
				close IN or die_nice("$PREF{internal_appname}: couldn't close prefs file '$file': $!");
				$prefs_contents =~ /(.*)/s;
				$prefs_contents = $1; # cheap untaint since this is our own config file.
				eval $prefs_contents; die_nice("Error processing your prefs file ('$file'): $@") if $@;
				$prefs_loaded = 1;
				last;
			}
		}
	}
	die_nice("$PREF{internal_appname}: load_external_prefs(): error: couldn't find any prefs file to load.  You must put your ${prefs_basename}_prefs.cgi file on the server with the ${prefs_basename}.cgi file.") unless $prefs_loaded;
}


sub get_cookies()
{
	my %cookies = fetch CGI::Cookie;
	return %cookies;
}


sub get_cookie($)
{
	my $which = shift;
	my %jar = get_cookies();
	my $value = '';

	if(exists $jar{$which})
	{
		$value = $jar{$which}->value;
	}
	elsif($which eq $PREF{site_session_cookie})
	{
		if($qs =~ /(?:^|&)ubsessioncode=(\w+)(?:&|$)/)
		{
			my $code = $1;

			# Accepting the session code from the URL should only be allowed as a last resort.
			# On decent servers this shouldn't be necessary because we can call UserBase
			# from PHP using virtual() and/or exec() both of which pass the cookies.  Even
			# on sub-par servers where we have to use include() with the full http:// URL,
			# we can reduce the security risk by requiring the remote IP to match the server
			# IP, i.e. ONLY allow the include(http://...) method to work: don't accept URL-
			# based session codes from any other IP.  As a last resort on totally sucky
			# servers where PHP is crippled and $ENV{SERVER_ADDR} DNE or is variable or
			# otherwise useless, proceed only by setting a PREF that indicates what a bad
			# idea it is.

			if($ENV{REMOTE_ADDR} eq $ENV{SERVER_ADDR})
			{
				$value = $code;
			}
			elsif($PREF{my_server_sucks_so_use_less_secure_mode} =~ /yes/i)
			{
				sleep $PREF{sleeptime_for_less_secure_mode} || 3;
				$value = $code;
			}
		}
	}

	return $value;
}


sub set_cookie($$$)
{
	my $name = shift;
	my $value = shift;
	my $expiry = shift;
	my $cookie;

	# This if/else is necessary because setting "expires" to "" isn't
	# the same as not setting it.  Setting it to "" is the same as 
	# setting it to zero, which expires the cookie immediately
	# (i.e., deletes it).  But explicitly *not* setting the expiry
	# causes the cookie to persist until the end of the session.
	if($expiry eq "")
	{
		$cookie = new CGI::Cookie(	-name    => $name,
						-value   => $value,
						-path    => '/');
	}
	else
	{
		$cookie = new CGI::Cookie(	-name    => $name,
						-value   => $value,
						-expires => $expiry,
						-path    => '/');
	}

	if($PREF{output_started})
	{
		print "<p>$PREF{internal_appname} warning: cannot set cookie '$name' => '$value' because the page output has already been started (perhaps debug is enabled?).</p>\n";
	}
	elsif($PREF{we_are_virtual})
	{
		print_http_headers();
		print "<p>$PREF{internal_appname} warning: cannot set cookie '$name' => '$value' because we are virtual.</p>\n";
	}
	else
	{
		print_p3p_header();
		print "Set-Cookie: $cookie\n";
	}
}


sub expand_custom_vars_in_prefs($)
{
	my $hashref = shift;
	my $include_undefined = shift; $include_undefined = 0 unless $include_undefined eq 'include_undefined';

	foreach my $key (keys %$hashref)
	{
		# from now on, use %%varname%% instead of $$varname$$, so that it doesn't
		# matter whether it gets put in double-quotes.

		next unless $$hashref{$key} && $$hashref{$key} =~ /(\$\$|%%|%PREF{)/;

		# old way:
		$$hashref{$key} =~ s/\$\$server_name\$\$/$ENV{'SERVER_NAME'}/g;
		$$hashref{$key} =~ s/\$\$httphost_withport\$\$/$ENV{'HTTP_HOST'}/g;
		$$hashref{$key} =~ s/\$\$name_of_site\$\$/$$hashref{'name_of_site'}/g;

		# new way:
		$$hashref{$key} =~ s/%%server_name%%/$ENV{SERVER_NAME}/g;
		$$hashref{$key} =~ s/%%http_host%%/$ENV{HTTP_HOST}/g;
		$$hashref{$key} =~ s/%%name_of_site%%/$$hashref{name_of_site}/g;

		# also replace %PREF{foo} with value of $PREF{foo}:
		$$hashref{$key} =~ s!%PREF{([\w-]+)}!exists $PREF{$1} || $include_undefined ? $PREF{$1} : "%PREF{$1}"!eg;
	}
}


sub expand_docroot_in_prefs($)
{
	my $hashref = shift;
	foreach my $key (keys %$hashref)
	{
		$$hashref{$key} =~ s!%PREF{DOCROOT}!$PREF{DOCROOT}!g;
	}
}


sub store_keyed_message
{
	my $new_message = shift;
	$new_message =~ s/\n/::ENCNEWLINE::/gs;

	my $current_time = offsettime();
	my $one_day_ago = $current_time - (60*60*24);

	my $key = md5_hex($new_message . $current_time . $$ . $ENV{REMOTE_PORT} . $ENV{REMOTE_ADDR} . $ENV{HTTP_USER_AGENT});

	my $mfile = $PREF{datadir} . '/' . "_$PREF{internal_appname}_keyed_messages.txt";
	create_file_if_DNE($mfile,$PREF{writable_file_perms_as_octal});
	my @messages = ();
	open(MFILE,"+<$mfile") or die_nice("$PREF{internal_appname}: could not open file '$mfile' for R/W: $!\n");
	flock MFILE, 2;
	seek MFILE, 0, 0;
	while(<MFILE>)
	{
		if(/^(\d+):/)
		{
			my ($time) = ($1);
			push (@messages,$_) if($time > $one_day_ago);
		}
	}
	seek MFILE, 0, 0;
	print MFILE @messages;
	print MFILE "${current_time}:${key}:$new_message\n";
	truncate MFILE, tell MFILE;
	close MFILE or die_nice("$PREF{internal_appname}: could not close file '$mfile' after R/W: $!\n");

	return $key;
}


sub get_keyed_message
{
	my $key = shift;
	my $message = '';

	my $mfile = $PREF{datadir} . '/' . "_$PREF{internal_appname}_keyed_messages.txt";
	create_file_if_DNE($mfile,$PREF{writable_file_perms_as_octal});
	open(MFILE,"<$mfile") or die_nice("$PREF{internal_appname}: could not open file '$mfile' for reading: $!\n");
	flock MFILE, 1;
	seek MFILE, 0, 0;
	while(<MFILE>)
	{
		if(/^\d+:${key}:(.*)/)
		{
			$message = $1;
			last;
		}
	}
	close MFILE or die_nice("$PREF{internal_appname}: could not close file '$mfile' after reading: $!\n");

	$message =~ s/::ENCNEWLINE::/\n/gs;
	return $message;
}


# pass filename to create and optionally the mode to chmod it to.
# the mode must consist of 1-4 octal digits and must NOT be quoted.
# see "perldoc -f chmod" and "man chmod".
sub create_file_if_DNE
{
	my $file = shift;
	my $mode = shift;

	return if -T $file;
	open(NEW,">$file") or die "$0: couldn't create new file $file: $!\n";
	close NEW or die "$0: couldn't close $file after creating it: $!\n";
	if($mode)
	{
		chmod($mode,$file) or die "$0: couldn't chmod file \"$file\" with mode \"$mode\": $!\n";
	}
}


sub create_dir_if_DNE
{
	my $dir = shift;
	my $mode = shift;
	my $make_parents_if_necessary = shift; $make_parents_if_necessary = $make_parents_if_necessary eq 'make_parents' ? 1 : 0;

	return if -d $dir;

	$dir =~ s!\\!/!g;
	if($make_parents_if_necessary)
	{
		my $progressively_longer_path = '';
		my $ms_windows = 0;
		if($dir =~ m!^(\w:)/!)
		{
			$progressively_longer_path = $1;
			$ms_windows = 1;
		}

		my $i = 0;
		foreach my $individual_path_element(split(/\//, $dir))
		{
			$i++;
			next if $i == 1 && $ms_windows;

			$progressively_longer_path .= '/' . $individual_path_element;
			unless(-d $progressively_longer_path)
			{
				mkdir($progressively_longer_path,$PREF{writable_dir_perms_as_octal}) or die_nice("$PREF{internal_appname}: create_dir_if_DNE(): couldn't create path-portion '$progressively_longer_path' as part of dir '$dir': $!");
				if($mode)
				{
					chmod($mode,$progressively_longer_path) or die_nice("$PREF{internal_appname}: create_dir_if_DNE(): couldn't chmod path-portion '$progressively_longer_path' as part of dir '$dir' with mode '$mode': $!");
				}
			}
		}
	}
	else
	{
		mkdir($dir,$PREF{writable_dir_perms_as_octal}) or die_nice("$PREF{internal_appname}: create_dir_if_DNE(): couldn't create dir $dir: $!");
		if($mode)
		{
			chmod($mode,$dir) or die_nice("$PREF{internal_appname}: create_dir_if_DNE(): couldn't chmod dir \"$dir\" with mode \"$mode\": $!");
		}
	}
}


sub send_email
{
	my ($to, $from, $subj, $msg, $mimetype, $die_on_error, $attachment_hashref, $dont_fork) = @_;
	$mimetype = 'text/plain' unless $mimetype;

	$die_on_error = $die_on_error eq 'die_on_email_error' ? 1 : 0;
	$dont_fork = $dont_fork eq 'dont_fork' ? 1 : 0;
	my $do_fork = !$die_on_error; # if we want to die on error, we can't fork, or the die() will go unreported.
	$do_fork = 0 if $^O =~ /MSWin32/; # Windows' fork-fu is weak.
	$do_fork = 0 if $dont_fork;

	my ($mail_sent_successfully, $error_msg) = 0;

        # fork here because sending mail can be slow (and can block) sometimes.
	# Note: if we don't set $do_fork, perl won't even evaluate the &&'s second
	# half, so the fork won't happen, and the else{} will.
	my $forkpid = ();
        if($do_fork   &&   ($forkpid = fork))
        {
                # parent
        }
        else 
        {
                # child

		use POSIX;

		if($do_fork)
		{
			defined $forkpid or die_nice "$PREF{internal_appname}: fork error in send_email(): $@\n";

			POSIX::setsid() unless $^O =~ /MSWin32/;
			close STDOUT;
			close STDIN;
		}

		my $msgid = '<' . time . '.' . md5_hex($to . $from . $subj . $msg . $$ . $ENV{REMOTE_PORT}) . '@' . $ENV{HTTP_HOST} . '>';

		my $smtp_error = '';
		if($PREF{smtp_server} =~ /\w/)
		{
			# Wrap this in an eval{} in case MIME::Lite is missing.
			# Then we can have the option of setting $PREF{'disable_all_email'}
			# so that the site still functions, sans email.
			eval
			{
				require MIME::Lite;

				my $type = ();
				if($mimetype)
				{
					$type = $mimetype;
				}
				else
				{
					#my $type = $attachment_hashref ? 'multipart/mixed' : 'text/plain';
					$type = $attachment_hashref ? 'multipart/mixed' : 'text/plain; charset=ISO-8859-1; format=flowed';
				}

				my $mime_msg = MIME::Lite->new(To => $to, From => $from, Subject => $subj, Type => $type, Data => $msg);
				unless($mime_msg)
				{
					if($die_on_error)	{ die_nice	"$PREF{internal_appname}: error creating MIME body: $!\n";	}
					else			{ warn		"$PREF{internal_appname}: error creating MIME body: $!\n";	}
				}

				if($PREF{generate_message_id_internally} =~ /yes/i)
				{
					$mime_msg->add('Message-ID' => $msgid);
				}

				if($attachment_hashref)
				{
					foreach my $key (keys %$attachment_hashref)
					{
						my $mimetype			= $$attachment_hashref{$key}{mimetype}; # like 'application/x-gzip'
						my $filename			= $$attachment_hashref{$key}{filename};
						my $recommended_filename	= $$attachment_hashref{$key}{recommended_filename};

						$recommended_filename =~ s!^.*(\\|/)!!; # strip off any preceeding path

						# Attach the test file
						$mime_msg->attach(
							Type => $mimetype,
							Path => $filename,
							Filename => $recommended_filename,
							Disposition => 'attachment'
						) or my $foo = sub {
							if($die_on_error)	{ die_nice	"$PREF{internal_appname}: error attaching file to email: $!\n";	}
							else			{ warn		"$PREF{internal_appname}: error attaching file to email: $!\n";	}
						};
					}
				}


				$PREF{smtp_server} = enc_untaint($PREF{smtp_server});
				if($PREF{smtp_auth_username} =~ /\S/ && $PREF{smtp_auth_password} =~ /\S/)
				{
					eval { MIME::Lite->send('smtp', $PREF{smtp_server}, Timeout=>30, AuthUser=>$PREF{smtp_auth_username}, AuthPass=>$PREF{smtp_auth_password}, Port=>$PREF{smtp_port}); };
				}
				else
				{
					eval { MIME::Lite->send('smtp', $PREF{smtp_server}, Timeout=>30, Port=>$PREF{smtp_port}); };
				}
				if($@)
				{
					if($die_on_error)	{ die_nice	"$PREF{internal_appname}: MIME::Lite->send failed: $@\n";	}
					else			{ warn		"$PREF{internal_appname}: MIME::Lite->send failed: $@\n";	}
				}

				eval { $mime_msg->send; };
				if($@)
				{
					if($die_on_error)	{ die_nice	"$PREF{internal_appname}: \$mime_msg->send failed: $@\n";	}
					else			{ warn		"$PREF{internal_appname}: \$mime_msg->send failed: $@\n";	}
				}
				else
				{
					$mail_sent_successfully = 1;
				}


				if($attachment_hashref)
				{
					foreach my $key (keys %$attachment_hashref)
					{
						unlink(  $$attachment_hashref{$key}{filename}  )   if   $$attachment_hashref{$key}{'delete-after-sending'} eq 'yes';
					}
				}
			};

			$smtp_error = $@;
		}

		my $sendmail_error = '';
		if(-e $PREF{path_to_sendmail}   &&   !$mail_sent_successfully)
		{
			if($smtp_error) { warn "$PREF{internal_appname}: send_email(): SMTP failed, so falling back to sendmail.  SMTP error was: $smtp_error\n"; }

			eval
			{
				$PREF{path_to_sendmail} = enc_untaint($PREF{path_to_sendmail}, 'keep_path');

				open(SENDMAIL, "|$PREF{path_to_sendmail} -oi -t") or die_nice "$PREF{internal_appname}: Can't fork for sendmail: $!\n";

				if($attachment_hashref)
				{
					print SENDMAIL	  qq`MIME-Version: 1.0`
							. qq`\nFrom: $from`
							. qq`\nTo: $to`
							. qq`\nSubject: $subj`
							. ($PREF{generate_message_id_internally} =~ /yes/i ? "\nMessage-Id: $msgid" : '')
							. qq`\nContent-Type: multipart/mixed; boundary=encindboundarystring`
							. qq`\n`
							. qq`\n--encindboundarystring`
							. qq`\nContent-Type: ` . ($mimetype ? $mimetype : 'text/plain')
							. qq`\n`
							. qq`\n$msg`;

					foreach my $key (keys %$attachment_hashref)
					{
						my $mimetype			= $$attachment_hashref{$key}{mimetype}; # like 'application/x-gzip'
						$mimetype			= 'application/octet-stream' unless $mimetype;
						my $filename			= $$attachment_hashref{$key}{filename};
						my $recommended_filename	= $$attachment_hashref{$key}{recommended_filename};

						$recommended_filename =~ s!^.*(\\|/)!!; # strip off any preceeding path

						my $atch = `uuencode $filename $filename`;	# UUencode it so we can send it as an attachment

						print SENDMAIL	  qq`\n____________________`
								. qq`\nAttachment: $filename:`
								. qq`\n`
								. qq`\n--encindboundarystring`
								. qq`\nContent-Type: $mimetype; name="$filename"`
								. qq`\nContent-Transfer-Encoding: x-uuencode`
								. qq`\nContent-Disposition: attachment; filename="$recommended_filename"`
								. qq`\n`
								. qq`\n$atch`
								. qq`\n`
								. qq`\n--encindboundarystring`;
					}

					print SENDMAIL	  qq`\n--encindboundarystring--\n`
				}
				else # no attachment.
				{
					print SENDMAIL	  qq`From: $from`
							. qq`\nTo: $to`
							. qq`\nSubject: $subj`
							. ($PREF{generate_message_id_internally} =~ /yes/i ? "\nMessage-Id: $msgid" : '')
							. qq`\nContent-Type: $mimetype`
							. qq`\n`
							. qq`\n$msg`;
				}

				close(SENDMAIL) or $PREF{die_on_sendmail_close_error} =~ /yes/i
						? die_nice	"$PREF{internal_appname}: sendmail didn't close nicely: $!\n"
						: warn		"$PREF{internal_appname}: sendmail didn't close nicely: $!\n";
			};

			if($@)	{ $sendmail_error = $@;		}
			else	{ $mail_sent_successfully = 1;	}
		}

		my $blat_error = '';
		if(-e $PREF{path_to_blat}   &&   $PREF{smtp_server}   &&   !$mail_sent_successfully)
		{
			printd "in blat loop...\n";
			if($sendmail_error) { warn "$PREF{internal_appname}: send_email(): sendmail failed, so trying blat.  sendmail error was: $sendmail_error\n"; }

			my $blat_output = '';
			eval
			{
				#$PREF{path_to_blat} = enc_untaint($PREF{path_to_blat}, 'keep_path');

				my $subj_for_blat = $subj;
				my $msg_for_blat = $msg;
				s!"!\\"!gs for ($subj_for_blat, $msg_for_blat);

				my $u	= "-u $PREF{smtp_auth_username}"	if $PREF{smtp_auth_username};
				my $pw	= "-pw $PREF{smtp_auth_password}"	if $PREF{smtp_auth_password};

				my ($debug,$x) = ('','');
				#$debug = "-debug";
				#$x = qq`-x "X-Custom-header: some header value..."`;

				$blat_output = `$PREF{path_to_blat} - -to $to -f $from -subject "$subj_for_blat" -body "$msg_for_blat" -server $PREF{smtp_server}:$PREF{smtp_port} $u $pw $debug $x`;

				printd "blat output: $blat_output\n";
			};

			if($@)	{ $blat_error = "$@.  Blat output was: $blat_output"; }
			else	{ $mail_sent_successfully = 1; }
		}

		unless($mail_sent_successfully)
		{
			if($smtp_error)		{ $error_msg = "$PREF{internal_appname}: couldn't send email: error in send_email() while trying to use MIME::Lite with SMTP server '$PREF{smtp_server}'. Error was: '$smtp_error'\n"; }
			elsif($sendmail_error)	{ $error_msg = "$PREF{internal_appname}: couldn't send email: error in send_email() while trying to use sendmail with path '$PREF{path_to_sendmail}'. Error was: '$sendmail_error'\n"; }
			elsif($blat_error)	{ $error_msg = "$PREF{internal_appname}: couldn't send email: error in send_email() while trying to use blat with path '$PREF{path_to_blat}'. Error was: '$blat_error'\n"; }
			else			{ $error_msg = "$PREF{internal_appname}: couldn't send email: error in send_email(): perhaps you need to adjust \$PREF{smtp_server} (currently '$PREF{smtp_server}') or \$PREF{path_to_sendmail} (currently '$PREF{path_to_sendmail}').\n"; }

			$error_msg .= qq` [[ message details: to='$to', from='$from', subject='$subj' ]] `;

			if($die_on_error)	{ die_nice	$error_msg;	}
			else			{ warn		$error_msg;	}
		}

		if($do_fork)
		{
			exit; # exit the child process.
		}
	}

	return ($mail_sent_successfully, $error_msg);
}


sub enc_untaint
{
	my $item = shift || '';
	my $original_item = $item;
	my $keep_path = shift || '';
	#printd "enc_untaint($item)\n";

	# Regardless of whether we're keeping the path, dots surrounded by slashes are never allowed.
	#
	#$item =~ s!(^|/|\\)\.+(/|\\|$)!$1!g;
	$item =~ s!\\!/!g; # Need to remove MS garbage beforehand, otherwise an input like .\\StupidCGI.tmp will break this.
	while($item =~ m!((?:^|/|\\)\.+(?:/|\\|$))!)
	{
		$item =~ s!$1!/!;
	}

	#printd "removed slashdots: $item\n";

	if(  $item =~ m!(/|\\)!  &&  !$keep_path)
	{
		$item =~ s!^.*[/\\]+([^/\\]+)!$1!; # remove any path from the front.
		#printd "removed path from front: $item\n";
		$item =~ s!^([^/\\]+)[/\\]+!$1!; # ...and the back.
	}

	$item =~ s![`\*\?\|<>]!!g; # remove some other potentially-unsafe stuff.

	my $leading_UNC_slashes = '';
	if($item =~ m!^//!  &&  $keep_path)
	{
		$leading_UNC_slashes = '//';
		$item =~ s!^/+!!;
	}
	$item =~ s![/\\]{2,}!/!g; # condense any multiples.
	$item = $leading_UNC_slashes . $item; # add back any UNC slashes.

	($item) = ($item =~ /(.*)/); # untaint.

	# In case anything slips through, die as a security precaution.
	#
	die qq`$0: couldn't untaint "$original_item".\n` if $item =~ m![/\\]! && !$keep_path;
	die qq`$0: couldn't untaint "$original_item".\n` if $item =~ m!(?:^|/|\\)\.+(?:/|\\|$)!;
	die qq`$0: couldn't untaint "$original_item".\n` if $item =~ m!^\.+$!;
	die qq`$0: couldn't untaint "$original_item".\n` if $item =~ m!^\s*$!;

	#printd "untainted: $item\n\n";
	return $item;
}


sub enc_urlencode
{
	for(@_) { s/([^\w()'*~!.-])/sprintf '%%%02x', ord $1/eg if $_; };
}


sub enc_urldecode
{
	# assuming the input really was URL-encoded, then any plus-signs that were originally there
	# are now in their hex form, so any plus-signs STILL there were converted from spaces by the
	# browser.  so they must be converted back BEFORE restoring any original plus-signs from the
	# hex codes.
	convert_plus_signs_back_to_spaces_in_var_from_GET_method(@_);
	for(@_) { s/%([a-fA-F\d]{2})/chr hex $1/eg  if $_; }
}


sub convert_plus_signs_back_to_spaces_in_var_from_GET_method
{
	for(@_) { s/\+/ /g  if $_; }
}


sub enc_redirect
{
	my $destination = shift;

	if($destination =~ /^referr?er$/i)
	{
		$destination = $ENV{HTTP_REFERER} ? $ENV{HTTP_REFERER} : $PREF{redirection_backup_address};
	}

	unless($destination =~ m!^https?://!)
	{
		$destination = $PREF{protoprefix} . $ENV{HTTP_HOST} . $destination;
	}

	if($PREF{output_started})
	{
		if($PREF{use_javascript_redirect_when_necessary} !~ /no/i)
		{
			print qq`\n<script type="text/javascript">location.href="$destination";</script>\n`;
		}
		else
		{
			print qq`<p>$PREF{internal_appname} warning: cannot redirect because output has already started (perhaps debug is enabled?).&nbsp; <a href="$destination">Click here to continue</a>.</p>\n`;
		}
	}
	elsif($PREF{we_are_virtual})
	{
		if($PREF{use_javascript_redirect_when_necessary} !~ /no/i)
		{
			print_http_headers();
			print qq`\n<script type="text/javascript">location.href="$destination";</script>\n`;
		}
		else
		{
			warn "$0: enc_redirect(): cannot redirect because we are virtual.\n";
			print_http_headers();
			print qq`<p>$PREF{internal_appname} warning: cannot redirect because we are virtual.&nbsp; <a href="$destination">Click here to continue</a>.</p>\n`;
		}
	}
	else
	{
		if($ENV{SERVER_SOFTWARE} =~ /microsoft-iis/i || $PREF{always_use_meta_for_redirects} =~ /yes/i)
		{
			# A bug in IIS v5 (and lower, probably) makes cookie-setting fail
			# when combined with a header-based redirect:
			#
			#	"BUG: Set-Cookie Is Ignored in CGI When Combined With Location"
			#	http://support.microsoft.com/kb/q176113/
			#
			# So use a meta-redirect instead.
			#
			print "Content-type: text/html\n\n";
			print qq`<html><head><meta http-equiv="refresh" content="0;url=$destination"></head><body></body></html>\n`;
		}
		else
		{
			print "Location: $destination\n\n";
		}
	}

	exit;
}


sub enc_redirect_to_ref
{
	# try to redirect to HTTP_REFERER, falling back to the URL
	# specified by ?whence=foo, then to any passed-in URL, and
	# finally to $PREF{here}.

	my $go = shift;
	if($ENV{HTTP_REFERER})
	{
		$go = $ENV{HTTP_REFERER};
	}
	elsif($qs =~ /(?:^|&)whence=(.+)/)
	{
		$go = $1;
		enc_urldecode($go);
	}
	$go = $PREF{here} if !$go;
	enc_redirect($go);
}


sub condense_slashes
{
	s!\\!/!g;
	my $leave_leading_UNC = 0;
	for(@_)
	{
		if(/^leave_leading_UNC$/)
		{
			$leave_leading_UNC = 1;
			next;
		}
		if($leave_leading_UNC)
		{
			my $leading_UNC_slashes = '';
			if(m!^//!)
			{
				$leading_UNC_slashes = '//';
				s!^/+!!;
			}
			s!/{2,}!/!g; # condense any multiples.
			$_ = $leading_UNC_slashes . $_; # add back any UNC slashes.
		}
		else
		{
			s!/{2,}!/!g;
		}
	}
}


sub slashify	{   fooify('/', @_); }
sub deslashify	{ defooify('/', @_); }
sub commaify	{   fooify(',', @_); }
sub decommaify	{ defooify(',', @_); }
sub spaceify	{   fooify(' ', @_); }
sub despaceify	{ defooify(' ', @_); }


sub fooify
{
	# add leading and trailing foos and condense duplicates.
	my $foo = shift;
	$_ = $foo . $_ . $foo for @_;
	s!$foo{2,}!$foo!g for @_;
}


sub defooify
{
	# remove leading and trailing foos and condense duplicates.
	my $foo = shift;
	s!$foo{2,}!$foo!g for @_;
	s!^$foo!!g for @_;
	s!$foo$!!g for @_;
}


sub die_unless_numeric
{
	die_nice("$PREF{internal_appname}: non-numeric $_[1]: '$_[0]' [called from: " . (caller 1)[3]  . "]\n") unless $_[0] =~ /^\d+$/;
}


sub print_http_headers
{
	unless($PREF{output_started} || $PREF{xml_output_started})
	{
		# Don't print the P3P header from here normally, because we'll pick it up automatically
		# from the site's main .htaccess file.  The only time a script needs to explicitly
		# include it is when setting cookies (and possibly when delivering JS code which is
		# used to set cookies?).
		# 
		#print_p3p_header();

		print "Cache-Control: no-store, no-cache\n";
		print "Connection: close\n"; # workaround for a Safari bug that causes uploads to fail about 50% of the time.
		print "Content-type: text/html\n\n";
		$PREF{output_started} = 1;
	}
}


sub print_p3p_header()
{
	# If you need to set third-party cookies, you'll need to use the P3P
	# privacy policy system, or else IE will reject the cookies.  The system
	# consists of a /w3c folder on your website containing a p3p.xml file
	# and a policy.html file, and then having your server send a P3P header,
	# both in the main .htaccess file and anytime a script needs to output
	# headers.  The value should be something like this:
	#
	#	$PREF{p3p_header} = qq`P3P: CP="ALL DSP COR NID", policyref="/w3c/p3p.xml"`;
	#
	# Or, you can leave off the policyref:
	#
	#	$PREF{p3p_header} = qq`P3P: CP="ALL DSP COR NID"`;
	#
	# You may want to use a service like http://p3pedit.com/ to create your
	# XML and privacy files.  And you can use www.w3.org/P3P/validator.html
	# to validate your setup.

	unless($PREF{p3p_header_printed})
	{
		print "$PREF{p3p_header}\n" if $PREF{p3p_header};
		$PREF{p3p_header_printed} = 1;
	}
}


sub offsettime
{
	return time + $PREF{time_offset};
}


sub sql_untaint
{
	s/"/&quot;/g for @_;
	s/'/&#39;/g for @_;
	s/`/&#96;/g for @_;
	s/\\/&#92;/g for @_;
}


sub sql_un_untaint
{
	s/&quot;/"/g for @_;
	s/&#39;/'/g for @_;
	s/&#96;/`/g for @_;
	s/&#92;/\\/g for @_;
}


sub enc_hash
{
	return $PREF{use_md5_for_hashes} =~ /yes/i ? md5_hex(@_) : sha1_hex(@_);
}


sub not_sqlsafe
{
	#print STDERR "not_sqlsafe: got: $_[0]\n";

	# Escape any dashes or closing brackets, as per perlre:
	#
	# 	If you want either "-" or "]" itself to be a member of a class,
	#	put it at the start of the list (possibly after a "^"), or escape
	#	it with a backslash.
	#
	my $list_of_sql_safe_characters = $PREF{list_of_sql_safe_characters};
	$list_of_sql_safe_characters =~ s/\]/\\]/g;
	$list_of_sql_safe_characters =~ s/-/\\-/g;

	return $_[0] =~ /[^$list_of_sql_safe_characters]/;
}


sub die_unless_sqlsafe
{
	#print STDERR "die_unless_sqlsafe: got: $_[0]\n";

	# Escape any dashes or closing brackets, as per perlre:
	#
	# 	If you want either "-" or "]" itself to be a member of a class,
	#	put it at the start of the list (possibly after a "^"), or escape
	#	it with a backslash.
	#
	my $list_of_sql_safe_characters = $PREF{list_of_sql_safe_characters};
	$list_of_sql_safe_characters =~ s/\]/\\]/g;
	$list_of_sql_safe_characters =~ s/-/\\-/g;

	if($_[0] =~ /[^$list_of_sql_safe_characters]/)
	{
		exit_with_error(qq`Not SQL-safe: called from ` . (caller 1)[3] . qq`: the following value ("$_[1]") is not SQL-safe: $_[0]`);
	}
}


sub oddeven
{
	$_[0] = 0 unless $_[0] && $_[0] =~ /^\d+$/;
	$_[0]++;
	return $_[1] && $_[1] eq 'reset' ? 'odd' : $_[0] % 2 == 0 ? 'even' : 'odd';
}


sub enc_sql_select($)
{
	my $statement = shift;
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$PREF{internal_appname}: enc_sql_select(): called from " . (caller 1)[3]  . ": error while executing SQL select statement [[$statement]]: $DBI::errstr\n");
	return $sth->fetchrow;
}


sub enc_sql_select_multi($)
{
	my $statement = shift;
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$PREF{internal_appname}: enc_sql_select_multi(): called from " . (caller 1)[3]  . ": error while executing SQL select statement [[$statement]]: $DBI::errstr\n");
	my $i = 1;
	my %hash = ();
	my $rowhashref = '';
	while($rowhashref = $sth->fetchrow_hashref)
	{
		foreach my $field (keys %$rowhashref)
		{
			$hash{$i}{$field} = $$rowhashref{$field};
		}
		$i++;
	}
	return \%hash;
}


sub enc_sql_update($)
{
	my $statement = shift;
	my $sth = $PREF{dbh}->prepare($statement);
	my $numrows = $sth->execute() or die_nice("$PREF{internal_appname}: enc_sql_update(): called from " . (caller 1)[3]  . ": error while executing SQL update statement [[$statement]]: $DBI::errstr\n");
	return $numrows;
}


sub enc_sql_insert($)
{
	my $statement = shift;
	my $sth = $PREF{dbh}->prepare($statement);
	$sth->execute() or die_nice("$PREF{internal_appname}: enc_sql_insert(): called from " . (caller 1)[3]  . ": error while executing SQL insert statement: $DBI::errstr.  Statement was: [[ $statement ]]\n");
}


sub enc_sql_delete($)
{
	my $statement = shift;
	my $sth = $PREF{dbh}->prepare($statement);
	my $retval = $sth->execute();
	die_nice("$PREF{internal_appname}: enc_sql_delete(): called from " . (caller 1)[3]  . ": error while executing SQL delete statement: $DBI::errstr\n") if $retval =~ /^(0|0E0)$/; # execute() returns '0E0' if no rows were affected by the statement.
}


sub enc_sys_call
{
	# TODO: this doesn't always work.

	my $cmd = shift;
	my ($msg,$success) = ();

	system($cmd);
	if ($? == -1)		{ $success = 0; $msg = "error: failed to execute: $!"; }
	elsif ($? & 127)	{ $success = 0; $msg = sprintf "error: child died with signal %d, %s coredump", ($? & 127),  ($? & 128) ? 'with' : 'without'; }
	else			{ $success = 1; $msg = sprintf "child exited with value %d", $? >> 8; }

	$msg = "enc_sys_call(): command was [[ $cmd ]]; result was [[ $msg ]];";
	printd "$msg\n";

	return ($success, $msg);
}


# Success messages that the end-user is supposed to see.
#
sub exit_with_success
{
	start_html_output('', 'css', 'js');
	my $message = join '', @_;
	$PREF{success_message_template} =~ s/%%message%%/$message/g;
	print $PREF{success_message_template};
	finish_html_output();
	exit;
}


# Non-error messages that the end-user is supposed to see.
#
sub exit_with_notice
{
	start_html_output('', 'css', 'js');
	my $message = join '', @_;
	$PREF{notice_message_template} =~ s/%%message%%/$message/g;
	print $PREF{notice_message_template};
	finish_html_output();
	exit;
}


# Errors that the end-user is supposed to see.
#
sub exit_with_error
{
	start_html_output('', 'css', 'js');
	my $message = join '', @_;
	print STDERR $message;
	$PREF{error_message_template} =~ s/%%message%%/$message/g;
	print $PREF{error_message_template} =~ /\Q$message\E/ ? $PREF{error_message_template} : $message; # in case prefs haven't been loaded yet.
	finish_html_output();
	exit;
}


sub exit_with_needlogin
{
	start_html_output('', 'css', 'js');
	$PREF{needlogin_message} =~ s/%%login_url%%/$PREF{login_url}/g;
	print $PREF{needlogin_message};
	finish_html_output();
	exit;
}


sub exit_with_needprivs
{
	start_html_output('', 'css', 'js');
	$PREF{needprivs_message} =~ s/%%login_url%%/$PREF{login_url}/g;
	$PREF{needprivs_message} =~ s/%%%if-notloggedin%%%(.+?)%%%end-notloggedin%%%/$PREF{member_is_logged_in} ? '' : $1/egs;
	print $PREF{needprivs_message};
	finish_html_output();
	exit;
}


sub printd
{
	my $msg = shift;
	chomp $msg;
	if($PREF{debug} || $PREF{force_debug} =~ /yes/i || $debuglog)
	{
		warn "$PREF{internal_appname}-debug: " . (offsettime()) . ": $msg\n";
		print $debuglog "$PREF{internal_appname}-debug: " . (offsettime()) . ": $msg\n" if $debuglog;
	}
	if($PREF{debug})
	{
		print_http_headers();
		print "<!-- $PREF{internal_appname}-debug: " . (offsettime()) . ": $msg -->\n";
	}
}


# Some SQL implementations support other nonsense in the table names; we'll restrict to a sensible set of characters.
#
sub tablename_is_valid				{ return ($_[0] =~ /^\w+$/	&&						length($_[0]) < $PREF{max_tablename_length});		} # FC, UB, VL
sub check_tablename_for_sql_safeness		{ die_nice("Invalid tablename: '$_[0]'") unless tablename_is_valid($_[0]); 								} # FC, UB, VL


sub db_column_exists($$)
{
	my $column_to_find = shift;
	my $table_name = shift;

	check_tablename_for_sql_safeness($table_name);

	my $column_name = ();
	my $temp = ();
	my $sth = $PREF{dbh}->prepare("SHOW COLUMNS FROM `$table_name`;");
	$sth->execute() or die_nice "$0: Error: db_column_exists(): $DBI::errstr\n";
	$sth->bind_columns(\$column_name, \$temp, \$temp, \$temp, \$temp, \$temp);
	while($sth->fetchrow_arrayref)
	{
		return 1 if $column_name eq $column_to_find;;
	}
	return 0;
}


sub get_ip_and_host
{
	my $ip = $ENV{REMOTE_ADDR};
	my $host = $ENV{REMOTE_HOST};

	if(!($host)) { $host = $ip; }
	if($host eq $ip)
	{
		use Socket;
		$host = gethostbyaddr(inet_aton($ip), AF_INET);
	}
	if(!($host)) { $host = $ip; }

	return ($ip, $host);
}


sub populate_month_conversion_hashes
{
	$PREF{monthnum}{jan}='01';
	$PREF{monthnum}{feb}='02';
	$PREF{monthnum}{mar}='03';
	$PREF{monthnum}{apr}='04';
	$PREF{monthnum}{may}='05';
	$PREF{monthnum}{jun}='06';
	$PREF{monthnum}{jul}='07';
	$PREF{monthnum}{aug}='08';
	$PREF{monthnum}{sep}='09';
	$PREF{monthnum}{oct}='10';
	$PREF{monthnum}{nov}='11';
	$PREF{monthnum}{dec}='12';

	$PREF{monthnum}{january}='01';
	$PREF{monthnum}{february}='02';
	$PREF{monthnum}{march}='03';
	$PREF{monthnum}{april}='04';
	$PREF{monthnum}{may}='05';
	$PREF{monthnum}{june}='06';
	$PREF{monthnum}{july}='07';
	$PREF{monthnum}{august}='08';
	$PREF{monthnum}{september}='09';
	$PREF{monthnum}{october}='10';
	$PREF{monthnum}{november}='11';
	$PREF{monthnum}{december}='12';

	$PREF{monthname}{"01"} = 'Jan';
	$PREF{monthname}{"02"} = 'Feb';
	$PREF{monthname}{"03"} = 'Mar';
	$PREF{monthname}{"04"} = 'Apr';
	$PREF{monthname}{"05"} = 'May';
	$PREF{monthname}{"06"} = 'Jun';
	$PREF{monthname}{"07"} = 'Jul';
	$PREF{monthname}{"08"} = 'Aug';
	$PREF{monthname}{"09"} = 'Sep';
	$PREF{monthname}{"10"} = 'Oct';
	$PREF{monthname}{"11"} = 'Nov';
	$PREF{monthname}{"12"} = 'Dec';
}


sub do_email_test
{
	my $to		= $PREF{email_test_recipient};
	my $from	= $PREF{email_test_sender};
	my $subj	= 'test message - ' . time;
	my $format	= 'text/plain';
	my $die		= 'die_on_email_error';

	my $authname	= $PREF{smtp_auth_username} ? 'not null' : 'null';
	my $authpass	= $PREF{smtp_auth_password} ? 'not null' : 'null';

	my $msg		= qq`this is only a test.
email settings:
\$PREF{smtp_server}		='$PREF{smtp_server}'
\$PREF{path_to_sendmail}	='$PREF{path_to_sendmail}'
\$PREF{smtp_auth_username}	is $authname.
\$PREF{smtp_auth_password}	is $authpass.
`;

	send_email($to, $from, $subj, $msg, $format, $die);

	exit_with_notice("Sent test message.");
}


sub add_text_to_file($$$)
{
	# Note: this function doesn't automatically insert any newlines; it
	# only inserts precisely what it receives in the $text parameter.

	my ($text, $file, $position) = @_;
	open(my $iofh, "+<$file") or die_nice "$PREF{internal_appname}: add_text_to_file(): couldn't open file '$file' for R/W: $!\n";
	flock $iofh, 2;

	if($position eq 'start')
	{
		seek $iofh, 0, 0;
		my @contents = <$iofh>;
		seek $iofh, 0, 0;
		print $iofh $text;
		print $iofh @contents;
	}
	else
	{
		seek $iofh, 0, 2;
		print $iofh $text;
	}

	truncate $iofh, tell $iofh;
	close $iofh or die_nice "$PREF{internal_appname}: add_text_to_file(): couldn't close file '$file' after R/W: $!\n";
}


############################################################################################################################################
### Functions: login.
############################################################################################################################################


sub do_login
{
	if($PREF{internal_appname} eq 'userbase')
	{
		$PREF{admin_is_logged_in} = 0;
		$PREF{member_is_logged_in} = 0;

		# Get the user's inputted username and password:
		my $input_username	= param($PREF{userbase_user_fieldname});
		my $input_password	= param($PREF{userbase_pass_fieldname});
		my $ref = param("ref");
		my ($expiry) = ();

		if(param("remember_me") eq "on")
		{
			if($PREF{num_days_rememberme_cookie_lasts} !~ /^\d+$/)
			{
				$PREF{num_days_rememberme_cookie_lasts} = 7;
			}
			$expiry = "+$PREF{num_days_rememberme_cookie_lasts}d";
		}

		my $restrict_ip = (   ($PREF{enable_ip_address_restriction} =~ /yes/i && param("restrict_ip") =~ /on/i)   ||   ($PREF{force_ip_address_restriction} =~ /yes/i)   ) ? 1 : 0;

		# Get the crypted version of the input password:
		check_username_for_sql_safeness($input_username);
		my $salt = enc_sql_select("SELECT `salt` FROM `$PREF{user_table}` WHERE `username` = '$input_username';");


		# TODO: remove this if/else, and assume that !$salt is an error condition; but
		# not until around mid-2007 to give clients time to get switched over.
		#
		my ($crypted_input_password, $update_this_account_to_new_pw_system) = ();
		if(!$salt) # old version of UB that's pre-salt, so re-create the password hash and update it in the DB.
		{
			$crypted_input_password = md5_hex($input_password);
			$update_this_account_to_new_pw_system = 1;
		}
		else
		{
			$crypted_input_password = salt_and_crypt_password($input_password, $salt);
		}


		my $account_locked = enc_sql_select("SELECT `acct_locked` FROM `$PREF{user_table}` WHERE `username` = '$input_username';");
		if($account_locked)
		{
			my $lock_expired = ! account_exceeds_failed_login_limit($input_username);
			if($PREF{lock_expires_automatically} =~ /yes/i   &&   $lock_expired)
			{
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_locked` = FALSE WHERE `username` = '$input_username';");
				die_nice("Error: do_login(input_username='$input_username'): SQL returned '$success' instead of '1' while updating acct_locked.") unless $success == 1;
			}
			else
			{
				sleep $PREF{num_seconds_to_sleep_on_failed_login};
				enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=eacctlck");
			}
		}


		my $go = '';
		if(account_exists($input_username, $crypted_input_password, 'new_login'))
		{
			my $account_disabled = enc_sql_select("SELECT `acct_disabled` FROM `$PREF{user_table}` WHERE `username` = '$input_username';");
			if($account_disabled)
			{
				enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=eacctdis");
			}

			$PREF{member_is_logged_in} = 1; # technically true, but can still be revoked by later tests.
			$PREF{logged_in_userid} = my $userid = get_user_id($input_username);

			if(account_is_pending($PREF{logged_in_userid}))
			{
				enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=eacctpnd");
			}

			my $session_id = create_new_session_id($input_username, $crypted_input_password);
			if(my $shared_session_id = check_for_multiple_logins($userid))
			{
				$session_id = $shared_session_id;
			}

			set_cookie($PREF{site_session_cookie}, $session_id, $expiry);

			if($update_this_account_to_new_pw_system)
			{
				my $salt = create_random_salt($PREF{salt_length});
				my $new_crypted_password = salt_and_crypt_password($input_password, $salt);
				my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `password` = '$new_crypted_password', `salt` = '$salt' WHERE `id` = $userid;");
				die_nice("Error: do_login(): SQL returned '$success' instead of '1' while updating pw and creating salt.") unless $success == 1;
				$crypted_input_password = $new_crypted_password;
			}

			unless(enc_sql_select("SELECT `failed_logins` FROM `$PREF{user_table}` WHERE `id` = $userid;") eq '')
			{
				my $statement = "UPDATE `$PREF{user_table}` SET `failed_logins` = NULL WHERE `id` = $userid;";
				my $success = enc_sql_update($statement);
				die_nice("Error: do_login(id='$userid'): SQL returned '$success' instead of '1' while updating failed_logins.  SQL was: [[$statement]]") unless $success == 1;
			}

			log_user_into_db($userid, $session_id, offsettime(), $restrict_ip);

			if(force_pw_change($userid))
			{
				$go = $PREF{protoprefix} . $ENV{HTTP_HOST} . $PREF{login_url} . "?action=edituser&id=$userid";
			}
			else
			{
				foreach my $pref (sort keys %PREF)
				{
					if($pref =~ /^on_(.+?)_login_redirect_to$/i   &&   user_is_member_of_group($PREF{logged_in_userid}, $1)   &&   $PREF{$pref})
					{
						$go = $PREF{$pref};
						$go =~ s/%%username%%/$input_username/g;
						last;
					}
				}
			}

			$go = determine_default_login_destination($ref) if !$go;
			enc_redirect($go);
		}

		# Else they tried to log in but failed.
		else
		{
			# Be sure that we do the sleep before the email, so that any
			# potential email errors don't cause us to abort early thereby
			# skipping the sleep and possibly giving away the fact that the
			# login failed.
			#
			sleep $PREF{num_seconds_to_sleep_on_failed_login};

			my $account_locked = account_exceeds_failed_login_limit($input_username, 'increment');
			if($account_locked)
			{
				unless(enc_sql_select("SELECT `acct_locked` FROM `$PREF{user_table}` WHERE `username` = '$input_username'"))
				{
					my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `acct_locked` = TRUE WHERE `username` = '$input_username';");
					die_nice("Error: do_login(input_username='$input_username'): SQL returned '$success' instead of '1' while updating acct_locked.") unless $success == 1;
				}
			}

			email_failed_logins_to_webmaster($input_username, $input_password);

			if($PREF{on_failed_login_redirect_to})
			{
				$go = $PREF{on_failed_login_redirect_to};

				if($account_locked)
				{
					$go .= $go =~ /\?/ ? '&account_locked=1' : '?account_locked=1';
				}
			}
			else
			{
				if($account_locked)
				{
					$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=eacctlck";
				}
				else
				{
					$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=ebadauth";
				}
			}

			enc_redirect($go);
		}
	}
	else
	{
		my $target = shift || $ENV{HTTP_REFERER};
		if($ENV{REQUEST_METHOD} =~ /post/i)
		{
			exit_with_error("You must enter the password.") if param('password') !~ /\S/;
			my $hashed_password = md5_hex(param('password'));
			my $expiry = param('persist') eq 'on' ? "+$PREF{num_days_login_lasts}d" : '';

			exit_with_error($TEXT{The_password_you_entered_is_incorrect___}) unless ($PREF{all_admin_password_hashes}{$hashed_password}   ||   $PREF{all_member_password_hashes}{$hashed_password});

			set_cookie($PREF{non_userbase_login_cookie}, $hashed_password, $expiry);
			my $go = $target;
			if($PREF{internal_appname} eq 'filechucker')
			{
				$go = $target eq 'list_files' ? "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{here_filelist}?action=listfiles" : "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{here_uploader}";
			}
			elsif($PREF{internal_appname} eq 'visitorlog')
			{
				$go = $target =~ /^http:\/\// ? $target : qq`$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{vlog_url_short}`;
			}
			enc_redirect($go);
		}
		else
		{
			my $action = $PREF{internal_appname} eq 'visitorlog' ? 'vllogin' : 'login';
			my $scripttarget = $target ? "action=$action&amp;target=$target" : $action;
			start_html_output($PREF{titlebar_title___login}, 'css');
			print	  qq`<form method="post" action="$ENV{SCRIPT_NAME}?$scripttarget">`
				. qq`\n<p>$TEXT{Enter_the_password}</p>\n<input type="password" name="password" maxlength="200" class="default text" />`
				. qq`\n<br /><br /><input type="checkbox" name="persist" /> $TEXT{Keep_me_logged_in_for} $PREF{num_days_login_lasts} $TEXT{days}`
				. qq`\n<br /><br /><input type="submit" value="log in" class="default button submit" />\n</form>\n`;
			finish_html_output('home');
		}
	}
}


sub check_if_logged_in()
{
	my %cookies = get_cookies();
	($PREF{admin_is_logged_in}, $PREF{member_is_logged_in}, $PREF{logged_in_username}, $PREF{logged_in_realname}, $PREF{logged_in_email}, $PREF{logged_in_userid}) = (0,0,'','','','');

	if($PREF{integrate_with_existing_login_system} =~ /yes/i   &&   $PREF{integrate_with_userbase} !~ /yes/i)
	{
		if($PREF{enable_username_from_cookie} =~ /yes/i)
		{
			my $username_in_cookie = ();
			if(exists($cookies{$PREF{admin_username_cookie_name}})   &&   ($username_in_cookie = $cookies{$PREF{admin_username_cookie_name}}->value))
			{
				($PREF{admin_is_logged_in}, $PREF{member_is_logged_in}, $PREF{logged_in_username}, $PREF{logged_in_userid}) = (1, 1, $username_in_cookie, -3);
			}
			elsif(exists($cookies{$PREF{member_username_cookie_name}})   &&   ($username_in_cookie = $cookies{$PREF{member_username_cookie_name}}->value))
			{
				($PREF{admin_is_logged_in}, $PREF{member_is_logged_in}, $PREF{logged_in_username}, $PREF{logged_in_userid}) = (0, 1, $username_in_cookie, -2);
			}
		}
		elsif($PREF{enable_username_from_php_session} =~ /yes/i)
		{
			my $username = '';
			my $isadmin = 0;
			if($ENV{PHP_ENC_USERNAME})
			{
				$username = $ENV{PHP_ENC_USERNAME};	save_php_var_to_cache('username',$username);
				$isadmin = $ENV{PHP_ENC_ISADMIN};	save_php_var_to_cache('isadmin',$isadmin);
			}
			else # we were POSTed to?
			{
				$username = get_php_var_from_cache('username');
				$isadmin = get_php_var_from_cache('isadmin');
			}

			if($username)
			{
				$PREF{logged_in_username}	= $username;
				$PREF{member_is_logged_in}	= 1;
				$PREF{admin_is_logged_in}	= $isadmin;
				$PREF{logged_in_userid}		= $PREF{admin_is_logged_in} ? -3 : -2;
			}
		}
	}
	elsif(userbase_available())
	{
		if(my $session_id = get_cookie($PREF{site_session_cookie}))
		{
			check_sessionid_for_sql_safeness($session_id);

			my ($username,$realname,$email,$id,$ip) = enc_sql_select("SELECT username,name,email,id,ip FROM `$PREF{user_table}` WHERE `mrsession` = '$session_id';");

			if($username   &&   $id   &&   !account_is_pending($id))
			{
				if(($PREF{enable_ip_address_restriction} =~ /yes/i && $ip) || ($PREF{force_ip_address_restriction} =~ /yes/i))
				{
					return unless $ip eq $ENV{REMOTE_ADDR};
				}
				if(enc_sql_select("SELECT `acct_disabled` FROM `$PREF{user_table}` WHERE `id` = '$id';"))
				{
					enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?phase=eacctdis");
				}

				$PREF{logged_in_username} = $username;
				$PREF{logged_in_realname} = $realname;
				$PREF{logged_in_email} = $email; $PREF{logged_in_email} = $PREF{logged_in_username} if ($PREF{logged_in_email} !~ /.+\@.+\..+/ && $PREF{logged_in_username} =~ /.+\@.+\..+/);
				$PREF{logged_in_userid} = $id;
				$PREF{member_is_logged_in} = 1;

				if(is_admin($PREF{logged_in_userid}))
				{
					$PREF{admin_is_logged_in} = 1;
				}

				check_and_update_login_session($PREF{logged_in_userid});

				if(force_pw_change($PREF{logged_in_userid}))
				{
					if($PREF{internal_appname} eq 'userbase')
					{
						if($qs !~ /^(logout|logoutall|action=commitedituser)$/)
						{
							print_user_form('edit', $PREF{logged_in_userid});
							exit;
						}
					}
					else
					{
						enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=edituser&id=$PREF{logged_in_userid}");
					}
				}
			}
		}
	}
	elsif($PREF{all_admin_password_hashes} || $PREF{all_member_password_hashes})
	{
		my $hashed_password_in_cookie = get_cookie($PREF{non_userbase_login_cookie});

		if($hashed_password_in_cookie   &&   $PREF{all_admin_password_hashes}{$hashed_password_in_cookie})
		{
			($PREF{admin_is_logged_in}, $PREF{member_is_logged_in}, $PREF{logged_in_username}, $PREF{logged_in_userid}) = (1, 1, undef, -3);
		}
		elsif($hashed_password_in_cookie   &&   $PREF{all_member_password_hashes}{$hashed_password_in_cookie})
		{
			($PREF{admin_is_logged_in}, $PREF{member_is_logged_in}, $PREF{logged_in_username}, $PREF{logged_in_userid}) = (0, 1, undef, -2);
		}
	}
}


# FC, UB, VL
sub check_and_update_login_session($)
{
	my $userid = shift;
	if($PREF{idle_timeout} > 0)
	{
		my $my_session_id = get_cookie($PREF{site_session_cookie});
		my $session_id_in_db = enc_sql_select("SELECT `mrsession` FROM `$PREF{user_table}` WHERE `id` = $userid;");
		my $login_time = enc_sql_select("SELECT `loggedin` FROM `$PREF{user_table}` WHERE `id` = $userid;");

		#if(   ($my_session_id == $session_id_in_db)   &&   ($login_time =~ /[1-9]/   &&   !login_session_expired($login_time))   )
		 if(   ($my_session_id == $session_id_in_db)   &&   (!login_session_expired($login_time))   )
		{
			 update_loggedin_time($userid, $my_session_id, offsettime());
		}
		else
		{
			do_logout();
		}
	}
}


sub update_loggedin_time
{
	my ($userid, $my_session_id, $newtime) = @_;

	die_unless_numeric($userid,'userid');
	die_unless_numeric($newtime,'newtime');
	check_sessionid_for_sql_safeness($my_session_id);

	my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `loggedin` = $newtime WHERE `id` = $userid AND `mrsession` = '$my_session_id';");
	die_nice("Error: update_loggedin_time('$userid', '$my_session_id', '$newtime'): SQL returned '$success' instead of '1' while updating loggedin.") unless $success == 1;
}


sub login_session_expired($)
{
	my $loggedin_time = shift;
	return (

		(   ($PREF{idle_timeout} > 0)   &&   (offsettime() - $loggedin_time > $PREF{idle_timeout})   )

		||

		(
			($PREF{num_days_rememberme_cookie_lasts} > 0)
			&&
			(   offsettime()   >   ($loggedin_time + ($PREF{num_days_rememberme_cookie_lasts} * 86400))   )
		)
	);
}


sub do_logout
{
	if($PREF{internal_appname} eq 'userbase')
	{
		my $force_logout_all = shift; $force_logout_all = $force_logout_all eq 'all' ? 1 : 0;

		if($PREF{we_are_virtual})
		{
			print_http_headers();
			$PREF{forced_logout_link} =~ s/%%logout_url%%/$ENV{SCRIPT_NAME}?logout/g;
			print $PREF{forced_logout_link};
			exit;
		}
		else
		{
			my $ref = $ENV{HTTP_REFERER};
			if($ref)
			{
				# Remove the "logout" from the referrer, otherwise we'll get stuck
				# in an infinite logout loop with this Location: call.
				$ref =~ s/\?.*log(ged)?out.*//;

				#my $us1 = $PREF{login_url};
				#my $us2 = $ENV{SCRIPT_NAME};
				#if($ref =~ /($us1|$us2)\?.+/)
				#{
				#	# If the page we were on before was a login page with some
				#	# query-string, then don't go there.
				#	$ref = '';
				#}
			}

			my $whence = '';
			if($PREF{member_is_logged_in})
			{
				if($PREF{prevent_multiple_simultaneous_logons_per_username} =~ /yes/i   ||   $force_logout_all)
				{
					log_user_out_of_db($PREF{logged_in_username}, get_cookie($PREF{site_session_cookie}));
				}
				else
				{
					die_unless_numeric($PREF{logged_in_userid}, 'logged_in_userid');
					my $numusers = enc_sql_select("SELECT `numusers` FROM `$PREF{user_table}` WHERE `id` = '$PREF{logged_in_userid}';");
					if($numusers > 1)
					{
						my $success = enc_sql_update("UPDATE `$PREF{user_table}` SET `numusers`=GREATEST((`numusers`-1),0) WHERE `id` = '$PREF{logged_in_userid}';");
						die_nice("Error: do_logout(): SQL returned '$success' instead of '1' while decrementing numusers column.") unless $success == 1;
					}
					else
					{
						log_user_out_of_db($PREF{logged_in_username}, get_cookie($PREF{site_session_cookie}));
					}
				}

				set_cookie($PREF{site_session_cookie}, 0, '-1M');

				if($PREF{admin_is_logged_in}   &&   $PREF{on_admin_logout_redirect_to})
				{
					$PREF{on_admin_logout_redirect_to} =~ s/%%username%%/$PREF{logged_in_username}/g;
					enc_redirect($PREF{on_admin_logout_redirect_to});
				}
				elsif($PREF{member_is_logged_in}   &&   !$PREF{admin_is_logged_in}   &&   $PREF{on_member_logout_redirect_to}) # need the !admin because admins are members too.
				{
					$PREF{on_member_logout_redirect_to} =~ s/%%username%%/$PREF{logged_in_username}/g;
					enc_redirect($PREF{on_member_logout_redirect_to});
				}
				else
				{
					# After logging out, return to the page we were on.
					$whence = $ref;
				}
			}
			else
			{
				$whence = $ref;
			}

			enc_urlencode($whence);
			$whence = undef if $PREF{server_bug_prohibits_use_of_whence} =~ /yes/i;
			enc_redirect("$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{login_url}?action=loggedout&whence=$whence");
		}
	}
	else
	{
		my $go = ();
		if($PREF{integrate_with_userbase} =~ /yes/i   ||   $PREF{integrate_with_existing_login_system} =~ /yes/i)
		{
			$go = $PREF{logout_url};
		}
		else
		{
			set_cookie($PREF{non_userbase_login_cookie}, 'blank', '-1d');

			# Remove the "logout" from the referrer, otherwise we'll get stuck
			# in an infinite logout loop with this Location: call.
			$ENV{HTTP_REFERER} =~ s/\?logout$//;
			$go = "$PREF{protoprefix}$ENV{HTTP_HOST}$PREF{here_login}?action=loggedout&whence=$ENV{HTTP_REFERER}";
		}

		if($PREF{we_are_virtual})
		{
			print_http_headers();
			if($PREF{use_javascript_redirect_when_necessary} !~ /no/i)
			{
				print qq`\n<script type="text/javascript">location.href="$go";</script>\n`;
			}
			else
			{
				$PREF{forced_logout_link} =~ s/%%logout_url%%/$go/g;
				print $PREF{forced_logout_link};
			}
			exit;
		}
		else
		{
			enc_redirect($go);
		}
	}
}


sub show_loggedout_page
{
	my $ref = shift;
	enc_urldecode($ref);
	my $message = $PREF{loggedout_page_template__no_referer};
	if($ref)
	{
		$message = $PREF{loggedout_page_template__with_referer};
		$message =~ s/%%ref%%/$ref/g;
	}
	exit_with_success($message);
}


# FC, UB, VL
# This function must do a case-sensitive lookup (i.e., do NOT use LOWER()) because
# FC's userdirs are case-sensitive.  So whatever case is used when a username is
# created is the case that must always be used when logging in with it.
#
sub account_exists($$$)
{
	#printd "account_exists('$_[0]', '$_[1]', '$_[2]')\n";

	my $user = shift;
	my $pass = shift;
	my $third_arg = shift;

	check_username_for_sql_safeness($user);
	check_hashedpw_for_sql_safeness($pass);

	my $count = ();
	if($third_arg eq 'new_login')
	{
		$count = enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE `username` = '$user' AND `password` = '$pass'");
	}
	else
	{
		die_unless_numeric($third_arg,'userid');
		$count = enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE `username` = '$user' AND `password` = '$pass' AND `id` = $third_arg");
	}

	if($count == 1)		{ return 1; }
	elsif($count > 1)	{ die_nice("$0: account_exists('$user', '$pass', '$third_arg'): error: duplicate records ($count total) for this user!\n"); }
	else			{ return 0; }
}


sub account_is_pending
{
	return 0 if !userbase_available();
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_is_pending()");
	return (account_is_pending_email_verification($userid) || account_is_pending_admin_approval($userid) || account_is_pending_payment($userid));
}


sub account_is_pending_email_verification
{
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_is_pending_email_verification()");
	return enc_sql_select("SELECT `pending_email_verification` FROM `$PREF{user_table}` WHERE `id` = $userid");
}


sub account_has_completed_email_verification
{
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_has_completed_email_verification()");
	return enc_sql_select("SELECT `completed_email_verification` FROM `$PREF{user_table}` WHERE `id` = $userid");
}


sub account_is_pending_admin_approval
{
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_is_pending_admin_approval()");
	return enc_sql_select("SELECT `pending_admin_approval` FROM `$PREF{user_table}` WHERE `id` = $userid");
}


sub account_is_pending_payment
{
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_is_pending_payment()");
	return enc_sql_select("SELECT `pending_payment` FROM `$PREF{user_table}` WHERE `id` = $userid");
}


sub account_has_completed_payment
{
	my $userid = shift;
	die_unless_numeric($userid, "user ID in account_has_completed_payment()");
	return enc_sql_select("SELECT COUNT(*) FROM `$PREF{payments_table}` WHERE `userid` = $userid");
}


# FC, UB, VL
sub is_admin($)
{
	#printd "is_admin('$_[0]')\n";

	my $userid = shift;
	return 0 unless $userid;

	return 1 if (!userbase_available()   &&   $userid == -3);

	# don't bother checking the validity of $userid here,
	# because user_is_member_of_group() will do it.
	return user_is_member_of_group($userid,$PREF{admin_group_name});
}


# FC, UB, VL
sub force_pw_change($)
{
	my $userid = shift;

	return (
			$PREF{enable_forced_password_change} =~ /yes/i
			&&
			enc_sql_select("SELECT `forcepwchng` FROM `$PREF{user_table}` WHERE `id` = '$userid';")
			&&
			(
				!is_admin($userid)
				||
				(is_admin($userid) && $PREF{admins_can_be_forced_to_change_their_own_pws} =~ /yes/i)
			)
	);
}


# FC, UB, VL
sub get_group_id($)
{
	printd "get_group_id($_[0])\n";
	my $group = shift;

	if(userbase_available())
	{
		check_groupname_for_uniqueness($group); # checks for sql safeness too.
		return enc_sql_select("SELECT `id` FROM `$PREF{group_table}` WHERE `group` = '$group'");
	}
	else
	{
		   if($group =~ /^$PREF{public_group_name}$/i)	{ return -1; }
		elsif($group =~ /^$PREF{member_group_name}$/i)	{ return -2; }
		elsif($group =~ /^$PREF{admin_group_name}$/i)	{ return -3; }
		else { die_nice("$PREF{internal_appname}: get_group_id(): invalid group name '$group'.\n"); }
	}
}


# FC, UB, VL
sub check_uid_for_uniqueness($)
{
	check_id_for_sql_safeness($_[0]);
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE `id` = $_[0]")   >   1)
	{
		die_nice("$0: error: more than one user record with id=$_[0]!\n");
	}
}


# FC, UB, VL
sub check_gid_for_uniqueness($)
{
	return unless userbase_available();
	printd "check_gid_for_uniqueness: '$_[0]'\n";

	check_id_for_sql_safeness($_[0]);
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE `id` = $_[0]")   >   1)
	{
		die_nice("$0: error: more than one group record with id=$_[0]!\n");
	}
}


# FC, UB, VL
sub check_username_for_uniqueness($)
{
	#printd "check_username_for_uniqueness: '$_[0]'\n";

	check_username_for_sql_safeness($_[0]);
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$_[0]')")   >   1)
	{
		die_nice("$0: error: more than one user record with username='$_[0]'!\n");
	}
}


# FC, UB, VL
sub check_groupname_for_uniqueness
{
	return unless userbase_available();
	printd "check_groupname_for_uniqueness($_[0])\n";

	check_groupname_for_sql_safeness($_[0]);
	if(enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE LOWER(`group`) = LOWER('$_[0]')")   >   1)
	{
		die_nice("$0: error: more than one user record with groupname='$_[0]'!\n");
	}
}


# FC, UB, VL
sub user_is_member_of_group
{
	my $userid = shift;
	my $group = shift;
	my $not_checking_loggedin_user = shift;

	printd "user_is_member_of_group(): userid='$userid', group='$group'\n";

	if(userbase_available()   &&   ($PREF{member_is_logged_in} || $not_checking_loggedin_user))
	{
		check_groupname_for_sql_safeness($group);
		die_unless_numeric($userid,'userid');

		return 1 if $group =~ /^$PREF{public_group_name}$/i;
		return 1 if $group =~ /^$PREF{member_group_name}$/i && enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE `id` = $userid;");

		return enc_sql_select("SELECT COUNT(*) FROM `$PREF{group_table}` WHERE LOWER(`group`) = LOWER('$group') AND `members` REGEXP '(^|,)$userid(,|\$)'");
	}
	else
	{
		return 1 if $group =~ /^$PREF{public_group_name}$/i;
		return 1 if $group =~ /^$PREF{member_group_name}$/i && $userid =~ /^-(2|3)$/;
		return 1 if $group =~ /^$PREF{admin_group_name}$/i && $userid == -3;
	}

}


# FC, UB, VL
sub userbase_available
{
	return ($PREF{internal_appname} eq 'userbase'   ||   $PREF{integrate_with_userbase} =~ /yes/i);
}


# FC, UB, VL
sub get_user_id($)
{
	#printd "get_user_id('$_[0]')\n";

	my $username = shift;

	if(userbase_available()   &&   $username)
	{
		die_nice("Error: invalid username '$username'.\n") unless username_is_valid($username);
		check_username_for_uniqueness($username); # checks for sql safeness too.
		return enc_sql_select("SELECT `id` FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$username')");
	}
	else
	{
		   if($PREF{admin_is_logged_in})	{ return -3; }
		elsif($PREF{member_is_logged_in})	{ return -2; }
		else					{ return -1; } # stranger.
	}
}


# FC, UB, VL
sub get_member_ids_for_group
{
	printd "get_member_ids_for_group($_[0])\n";

	my $group = shift;
	check_groupname_for_sql_safeness($group);

	 # every account is automatically a member of these groups.
	if($group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i)
	{
		my $statement = "SELECT `id` FROM `$PREF{user_table}`";
		return $PREF{dbh}->selectall_hashref($statement, 'id');
	}
	else
	{
		my $member_ids = enc_sql_select("SELECT `members` FROM `$PREF{group_table}` WHERE LOWER(`group`) = LOWER('$group')");
		my %member_ids = map { $_ => 1 } split(/,/, $member_ids);
		return \%member_ids;
	}
}


# FC, UB, VL
sub get_user_name($)
{
	check_uid_for_uniqueness($_[0]); # checks for sql safeness too.
	return enc_sql_select("SELECT `username` FROM `$PREF{user_table}` WHERE `id` = $_[0]");
}


# FC, UB, VL
sub get_group_name($)
{
	my $gid = shift;
	if(userbase_available())
	{
		check_gid_for_uniqueness($gid); # checks for sql safeness too.
		return enc_sql_select("SELECT `group` FROM `$PREF{group_table}` WHERE `id` = $gid");
	}
	else
	{
		   if($gid == -1)	{ return $PREF{public_group_name}; }
		elsif($gid == -2)	{ return $PREF{member_group_name}; }
		elsif($gid == -3)	{ return $PREF{admin_group_name}; }
		else { die_nice("$PREF{internal_appname}: get_group_name(): invalid group ID '$gid'.\n"); }
	}
}


sub hashedpw_is_valid				{ return  $_[0] =~ /^[0-9A-Za-z]+$/					&&	length($_[0]) < $PREF{max_hashedpw_length};		} # FC, UB, VL
sub sessionid_is_valid				{ return  $_[0] =~ /^[0-9A-Za-z]+$/					&&	length($_[0]) < $PREF{max_hashedpw_length};		} # FC, UB, VL


sub username_is_valid
{
	my $space = $PREF{allow_spaces_in_usernames} =~ /yes/i ? ' ' : '';
	my $atsign = $PREF{allow_atsigns_in_usernames} =~ /yes/i ? '@' : '';
	my $dot = $PREF{allow_dots_in_usernames} =~ /yes/i ? '.' : '';
	my $dash = $PREF{allow_dashes_in_usernames} =~ /yes/i ? '-' : '';

	return ($_[0] =~ /^[0-9A-Za-z_$space$atsign$dot$dash]+$/   &&   $_[0] =~ /\w/	&&	length($_[0]) < $PREF{max_username_length});
}

sub groupname_is_valid
{
	my $space = $PREF{allow_spaces_in_usernames} =~ /yes/i ? ' ' : '';
	my $atsign = $PREF{allow_atsigns_in_usernames} =~ /yes/i ? '@' : '';
	my $dot = $PREF{allow_dots_in_usernames} =~ /yes/i ? '.' : '';
	my $dash = $PREF{allow_dashes_in_usernames} =~ /yes/i ? '-' : '';

	return ($_[0] =~ /^[0-9A-Za-z_$space$atsign$dot$dash]+$/   &&   $_[0] =~ /\w/	&&	length($_[0]) < $PREF{max_groupname_length});
}

sub check_hashedpw_for_sql_safeness		{ die_nice("Invalid hashed password: '$_[0]'") unless hashedpw_is_valid($_[0]);								} # FC, UB, VL
sub check_username_for_sql_safeness		{ die_nice("Invalid username: '$_[0]'") unless username_is_valid($_[0]);								} # FC, UB, VL
sub check_groupname_for_sql_safeness		{ die_nice("Invalid groupname: '$_[0]'") unless groupname_is_valid($_[0]);								} # FC, UB, VL
sub check_sessionid_for_sql_safeness		{ die_nice("Invalid session ID: '$_[0]'") unless sessionid_is_valid($_[0]);								} # FC, UB, VL
sub check_id_for_sql_safeness			{ die_nice("Invalid ID: '$_[0]'") unless $_[0] =~ /^(\d+|-[123])$/;									} # FC, UB, VL


# FC, UB
sub get_groups_hash
{
	printd "get_groups_hash('$_[0]')\n";

	# If you pass in a uid, then the resulting hash will
	# also indicate which groups that user is a member of.
	#

	my $user_id = shift;

	my ($id, $group, $members, %groups) = ();
	if(userbase_available())
	{
		my $sth = $PREF{dbh}->prepare("SELECT `id`, `group`, `members` FROM `$PREF{group_table}`");
		$sth->execute();
		$sth->bind_columns(\$id, \$group, \$members);
		while($sth->fetchrow_arrayref)
		{
			$groups{$group}{name} = $group;
			$groups{$group}{id} = $id;

			my $is_member = ();
			   if($group =~ /^($PREF{public_group_name}|$PREF{member_group_name})$/i)	{ $is_member = 1; }
			elsif($user_id =~ /^\d+$/)							{ $is_member = $members =~ /(^|,)$user_id(,|$)/; }

			$groups{$group}{is_member} = $is_member;
		}
	}
	else
	{
		$groups{$PREF{public_group_name}}{name}		= $PREF{public_group_name};
		$groups{$PREF{public_group_name}}{id}		= -1;
		$groups{$PREF{public_group_name}}{is_member}	= 1; # everyone's a member of the public.

		$groups{$PREF{member_group_name}}{name}		= $PREF{member_group_name};
		$groups{$PREF{member_group_name}}{id}		= -2;
		$groups{$PREF{member_group_name}}{is_member}	= 1 if $user_id =~ /^-(2|3)$/;

		$groups{$PREF{admin_group_name}}{name}		= $PREF{admin_group_name};
		$groups{$PREF{admin_group_name}}{id}		= -3;
		$groups{$PREF{admin_group_name}}{is_member}	= 1 if $user_id =~ /^-3$/;
	}

	return \%groups;
}


# BL, UB, VL
# This function must do a case-insensitive lookup (i.e. use LOWER() on both sides)
# so that we never create a username multiple times with different cases.
#
sub username_is_taken
{
	return 0 unless userbase_available();
	my $user = shift;
	check_username_for_sql_safeness($user);
	return enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE LOWER(`username`) = LOWER('$user')");
}


sub email_address_is_taken
{
	my $address = shift;
	check_emailaddr_for_sql_safeness($address);
	return enc_sql_select("SELECT COUNT(*) FROM `$PREF{user_table}` WHERE LOWER(`email`) = LOWER('$address')");
}


# UB, VL
sub salt_and_crypt_password($$)
{
	my $plaintext_password = shift;
	my $salt = shift;
	die "$0: salt_and_crypt_password(): no salt?\n" unless $salt;
	my ($salt1,$salt2) = ($salt =~ /^(.{15})(.{25})$/);
	my $crypted_password = enc_hash($salt1 . $plaintext_password . $salt2);
	return $crypted_password;
}


# FC, VL
sub login_features_enabled
{
	if(
		(
			action_restrictions_enabled()
			||
			($PREF{internal_appname} eq 'filechucker' && custom_folder_perms_enabled())
		)

		&&

		(
			$PREF{all_member_password_hashes}
			||
			$PREF{all_admin_password_hashes}
			||
			$PREF{integrate_with_userbase} =~ /yes/i
			||
			$PREF{integrate_with_existing_login_system} =~ /yes/i
		)
	)
	{
		return 1;
	}
}


# FC, VL
sub action_restrictions_enabled
{
	foreach my $pref (keys %PREF)
	{
		if($pref =~ /^groups_allowed_to_/)
		{
			# if one or more groups is specified, and it's not the public group by itself, then access restrictions are enabled.
			if($PREF{$pref} =~ /\w/   &&   $PREF{$pref} !~ /^\s*,?\s*$PREF{public_group_name}\s*,?\s*$/i)
			{
				return 1;
			}
		}
	}

	return 0;
}


# FC, VL
sub make_password_hash
{
	if($ENV{REQUEST_METHOD} =~ /post/i)
	{
		my $hashed_password = md5_hex(param('password'));
		start_html_output('Here is your hashed password...', 'css', 'js');
		print	  qq`<div>The hashed version of the password you just entered is:<br /><br />$hashed_password`
			. qq`\n<br /><br />Now open your prefs file and paste this hash into one of the `
			. qq`\n\$PREF{admin_password_hash*} or \$PREF{member_password_hash*} settings.`
			. qq`\n</div>\n`;
		finish_html_output('home', 'uploader', 'login');
		
	}
	else
	{
		start_html_output('Enter your new password', 'css', 'js');
		print	  qq`<form method="post" action="$ENV{SCRIPT_NAME}?newpw">`
			. qq`\nEnter your new password:`
			. qq`\n<br /><br /><input type="password" name="password" maxlength="200" class="default text" />`
			. qq`\n<br /><br /><input type="submit" value="create hash" class="default button submit" />`
			. qq`\n</form>`
			. qq`\n`;
		finish_html_output('home', 'uploader', 'login');
		
	}
}


sub user_is_allowed_to
{
	# TODO: these separate per-app branches can probably be partially or totally reconciled.

	if($PREF{internal_appname} eq 'userbase')
	{
		my $userid_performing_action = shift;
		my $action = shift;
		my $user_affected_by_action = shift;
		my $userid_affected_by_action = get_user_id($user_affected_by_action);

		return 1 if is_admin($userid_performing_action);

		if($action eq 'edit_user_info')
		{
			return 1 if (logged_in_user_is_subgroup_manager()   &&   logged_in_subgroup_manager_owns_this_user($userid_affected_by_action));
		}

		my $allowed = 0;

		foreach my $group (split(/[,\s]+/, $PREF{"groups_allowed_to_$action"}))
		{
			if($group =~ /^self$/i)
			{
				$allowed = 1 if ($PREF{member_is_logged_in}   &&   $userid_performing_action == $userid_affected_by_action);
			}
			else
			{
				$allowed = 1 if user_is_member_of_group($userid_performing_action, $group);
			}

			return $allowed if $allowed;
		}

		return 0;
	}
	else
	{
		my $action = shift;

		if(!login_features_enabled())
		{
			return 1;
		}
		else
		{
			foreach my $group (split(/\s*,\s*/, $PREF{"groups_allowed_to_$action"}))
			{
				return 1 if user_is_member_of_group($PREF{logged_in_userid}, $group);
			}
			return 0;
		}
	}
}


# FC, VL
sub exit_with_access_denied
{
	my $target = shift;
	my $login_url = '';
	my $auto_redirect = '';
	if($PREF{integrate_with_userbase} =~ /yes/i || $PREF{integrate_with_existing_login_system} =~ /yes/i)
	{
		$login_url = $PREF{login_url};
		$auto_redirect = qq`<script type="text/javascript">location.href="$login_url" + '?whence=' + location.href;</script>\n`;
	}
	else
	{
		my $action = $PREF{internal_appname} =~ /visitorlog/i ? 'vllogin' : 'login';
		$login_url = "$PREF{here_login}?action=$action&amp;target=$target";
	}
	$login_url = "$PREF{protoprefix}$ENV{HTTP_HOST}$login_url" unless $login_url =~ /^https?:/;
	my $message = $PREF{member_is_logged_in} ? qq`Insufficient privileges to perform this action.` : qq`You must <a href="$login_url">log in</a> first.\n$auto_redirect`;
	start_html_output('Authentication Required', 'css', 'js');
	print	  qq`<h1>Authentication Required</h1>`
		. qq`\n<div style="margin: 30px;">$message</div>`
		. qq`\n`;
	finish_html_output('home', 'uploader', 'list');
	exit;
}


##############################################################################
### Dispatch-UB: #############################################################
##############################################################################


load_prefs();

   if($qs eq 'logout')							{ do_logout();					}
elsif($qs eq 'logoutall')						{ do_logout('all');				}
elsif($qs =~ /(?:^|&)action=loggedout&whence=(.*)(?:&|$)/)		{ show_loggedout_page($1);			} # note that the whence regex is .* not .*? because the value will likely contain ampersands that we want to keep.

elsif($qs =~ /createrandomadmin/ && $PREF{allow_random_admin_account_creation} =~ /yes/i) { create_random_admin_account_and_exit();	}

elsif($qs =~ /(?:^|&)action=showusers(?:&|$)/)				{ showusers();					}
elsif($qs =~ /(?:^|&)action=newaccount(?:&|$)/)				{ print_user_form('user_signup');		}
elsif($qs =~ /(?:^|&)action=adduser(?:&|$)/)				{ print_user_form('added_by_admin');		}
elsif($qs =~ /(?:^|&)action=edituser&id=(\d+?)(?:&|$)/)			{ print_user_form('edit', $1);			}
elsif($qs =~ /(?:^|&)action=commitadduser(?:&|$)/)			{ process_new_account();			}
elsif($qs =~ /(?:^|&)action=commitedituser(?:&|$)/)			{ edit_user_account();				}
elsif($qs =~ /(?:^|&)action=verify&u=(\d+)&t=(\w+)(?:&|$)/)		{ do_email_verification($1,$2);			}
elsif($qs =~ /(?:^|&)action=approve_or_del&uid=(\d+)(?:&|$)/)		{ approve_or_delete_pending_account($1);	}
elsif($qs =~ /(?:^|&)action=approve_pending_acct&uid=(\d+)(?:&|$)/)	{ approve_or_delete_pending_account_stage2($1,'approve');	}
elsif($qs =~ /(?:^|&)action=delete_pending_acct&uid=(\d+)(?:&|$)/)	{ approve_or_delete_pending_account_stage2($1,'delete');	}

elsif($qs =~ /(?:^|&)action=deletecustomfield&id=(.+?)(?:&|$)/)		{ delete_custom_field($1);			}
elsif($qs =~ /(?:^|&)action=commitdeletecustomfield&id=(.+?)(?:&|$)/)	{ commit_delete_custom_field($1);		}

elsif($qs =~ /(?:^|&)action=deleteuser&id=(.+?)(?:&|$)/)		{ delete_user($1);				}
elsif($qs =~ /(?:^|&)action=commitdeleteuser&id=(.+?)(?:&|$)/)		{ commit_delete_user($1);			}

elsif($qs =~ /(?:^|&)action=showgroups(?:&|$)/)				{ showgroups();					}
elsif($qs =~ /(?:^|&)action=addgroup(?:&|$)/)				{ print_group_form('add');			}
elsif($qs =~ /(?:^|&)action=editgroup&id=(\d+)(?:&|$)/)			{ print_group_form('edit',$1);			}
elsif($qs =~ /(?:^|&)action=commitaddgroup(?:&|$)/)			{ process_new_group();				}
elsif($qs =~ /(?:^|&)action=commiteditgroup(?:&|$)/)			{ edit_group();					}

elsif($qs =~ /(?:^|&)action=deletegroup&id=(.+?)(?:&|$)/)		{ delete_group($1);				}
elsif($qs =~ /(?:^|&)action=commitdeletegroup&id=(.+?)(?:&|$)/)		{ commit_delete_group($1);			}

elsif($qs =~ /(?:^|&)action=validate(?:&|$)/)				{ do_login();					}
elsif($qs =~ /(?:^|&)action=chklogin(?:&|$)/)				{ check_login();				}
#elsif($qs =~ /(?:^|&)action=chpw(?:&|$)/)				{ chpw();					}
#elsif($qs =~ /(?:^|&)action=chpw2(?:&|$)/)				{ chpw2();					}

elsif($qs =~ /(?:^|&)action=addcustomfield(?:&|$)/)			{ add_or_edit_custom_fields('add');		}
elsif($qs =~ /(?:^|&)action=editcustomfield(?:&|$)/)			{ add_or_edit_custom_fields('edit');		}
elsif($qs =~ /(?:^|&)action=import(?:&|$)/)				{ import_users();				}

elsif($qs =~ /(?:^|&)action=pwreset1(?:&|$)/)				{ print_pwreset_page();				}
elsif($qs =~ /(?:^|&)action=pwreset2(?:&|$)/)				{ send_pwreset_email();				}
elsif($qs =~ /(?:^|&)action=pwreset3(?:&|$)/)				{ process_pwreset();				}

elsif($qs =~ /(?:^|&)action=test(?:&|$)/)				{ test_function();				}

elsif($qs =~ /(?:^|&)rslt=\d+(?:&|$)/)					{ show_results_page();				}
elsif($qs =~ /(?:^|&)phase=([es].+?)(?:&|$)/)				{ show_message($1);				}

elsif($qs =~ /(?:^|&)action=startpayment&type=(\d+)&uid=(\d+)(?:&|$)/)	{ print_payment_form($1,$2);			}
elsif($qs =~ /(?:^|&)action=confirmpayment(?:&|$)/)			{ print_payment_confirmation_page();		}

elsif($qs =~ /do_email_test/ && $PREF{enable_email_test} =~ /yes/i)	{ do_email_test();				}

elsif($qs =~ /(?:^|&)action=showipndata(?:&|$)/)			{ show_ipn_data();				}
elsif(!$qs && $ENV{REQUEST_METHOD} =~ /post/i)				{ do_paypal_ipn();				}

else
{
	if($PREF{admin_is_logged_in}   &&   $PREF{always_redirect_admins_to})
	{
		$PREF{always_redirect_admins_to} =~ s/%%username%%/$PREF{logged_in_username}/g;
		enc_redirect($PREF{always_redirect_admins_to});
	}
	elsif($PREF{member_is_logged_in}   &&   !$PREF{admin_is_logged_in}   &&   $PREF{always_redirect_members_to}) # need the !admin because admins are members too.
	{
		$PREF{always_redirect_members_to} =~ s/%%username%%/$PREF{logged_in_username}/g;
		enc_redirect($PREF{always_redirect_members_to});
	}
	else
	{
		if($PREF{member_is_logged_in})
		{
			$PREF{on_page} = 'mainmenu';
			start_html_output("<br><br>");
			print_login_landing_page();
			finish_html_output();
		}
		else
		{
			$PREF{on_page} = 'loginform';
			start_html_output();
			prompt_for_login();
			finish_html_output();
		}
	}
}