Merge branch 'release' into bug-807

Conflicts:
	includes/account.php
	includes/lib/account.php
	pages/account/16.php

21 files changed, 1663 insertions, 1074 deletions

diff --git a/pages/account/12.php b/pages/account/12.php
index 9058a07..f4428aa 100644
--- a/pages/account/12.php
+++ b/pages/account/12.php
@@ -19,7 +19,7 @@
 <form method="post" action="account.php">
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
   <tr>
-    <td colspan="8" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=12&amp;viewall=<?=!$viewall?>"><?=$viewall?_("Hide old certificates"):_("View all certificates")?></a></td>
+    <td colspan="8" class="title"><?=_("Domain Certificates")?> - <a href="account.php?id=12&amp;viewall=<?=intval(!$viewall)?>"><?=$viewall?_("Hide old certificates"):_("View all certificates")?></a></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
@@ -34,17 +34,20 @@
 	$query = "select UNIX_TIMESTAMP(`domaincerts`.`created`) as `created`,
 			UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
 			UNIX_TIMESTAMP(`domaincerts`.`expire`) as `expired`,
-			`domaincerts`.`expire` as `expires`, `revoked` as `revoke`,
-			UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id`,
+			`domaincerts`.`expire`,
+			`domaincerts`.`revoked` as `revoke`,
+			UNIX_TIMESTAMP(`revoked`) as `revoked`,
+			if (`domaincerts`.`expire`=0,CURRENT_TIMESTAMP(),`domaincerts`.`modified`) as `modified`,
+			`CN`, `domaincerts`.`serial`, `domaincerts`.`id` as `id`,
 			`domaincerts`.`description`
 			from `domaincerts`,`domains`
 			where `memid`='".intval($_SESSION['profile']['id'])."' and `domaincerts`.`domid`=`domains`.`id` ";
 	if($viewall != 1)
 	{
 		$query .= "AND `revoked`=0 AND `renewed`=0 ";
-		$query .= "HAVING `timeleft` > 0 ";
+		$query .= "HAVING `timeleft` > 0 or `expire` = 0 ";
 	}
-	$query .= "ORDER BY `domaincerts`.`modified` desc";
+	$query .= "ORDER BY `modified` desc";
 //echo $query."<br>\n";
 	$res = mysql_query($query);
 	if(mysql_num_rows($res) <= 0)
@@ -69,24 +72,24 @@
 ?>
   <tr>
 <? if($verified != _("Pending") && $verified != _("Revoked")) { ?>
-    <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"/></td>
+    <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=intval($row['id'])?>"/></td>
 <? } else if($verified != _("Revoked")) { ?>
-    <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"/></td>
+    <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=intval($row['id'])?>"/></td>
 <? } else { ?>
     <td class="DataTD">&nbsp;</td>
 <? } ?>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><a href="account.php?id=15&amp;cert=<?=$row['id']?>"><?=$row['CN']?></a></td>
+    <td class="DataTD"><a href="account.php?id=15&amp;cert=<?=intval($row['id'])?>"><?=htmlspecialchars($row['CN'])?></a></td>
     <td class="DataTD"><?=$row['serial']?></td>
     <td class="DataTD"><?=$row['revoke']?></td>
-    <td class="DataTD"><?=$row['expires']?></td>
-    <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
-    <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
+    <td class="DataTD"><?=$row['expire']?></td>
+    <td class="DataTD"><input name="comment_<?=intval($row['id'])?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
+    <td class="DataTD"><input type="checkbox" name="check_comment_<?=intval($row['id'])?>" /></td>
   </tr>
 <? } ?>
   <tr>
     <td class="DataTD" colspan="8">
-      <a href="account.php?id=12&amp;viewall=<?=!$viewall?>"><b><?=$viewall?_("Hide old certificates"):_("View all certificates")?></b></a>
+      <a href="account.php?id=12&amp;viewall=<?=intval(!$viewall)?>"><b><?=$viewall?_("Hide old certificates"):_("View all certificates")?></b></a>
     </td>
   </tr>
   <tr>
@@ -104,6 +107,6 @@
     <td class="DataTD" colspan="8"><?=_("From here you can delete pending requests, or revoke valid certificates.")?></td>
   </tr>
 </table>
-<input type="hidden" name="oldid" value="<?=$id?>"/>
+<input type="hidden" name="oldid" value="<?=intval($id)?>"/>
 <input type="hidden" name="csrf" value="<?=make_csrf('srvcerchange')?>"/>
 </form>
diff --git a/pages/account/13.php b/pages/account/13.php
index 08f325d..080e277 100644
--- a/pages/account/13.php
+++ b/pages/account/13.php
@@ -52,22 +52,22 @@
 <? if($_SESSION['profile']['points'] == 0) { ?>
   <tr>
     <td class="DataTD" width="125"><?=_("First Name")?>: </td>
-    <td class="DataTD" width="125"><input type="text" name="fname" value="<?=$user['fname']?>"></td>
+    <td class="DataTD" width="125"><input type="text" name="fname" value="<?=sanitizeHTML($user['fname'])?>"></td>
   </tr>
   <tr>
     <td class="DataTD" valign="top"><?=_("Middle Name(s)")?><br>
       (<?=_("optional")?>)
     </td>
-    <td class="DataTD"><input type="text" name="mname" value="<?=$user['mname']?>"></td>
+    <td class="DataTD"><input type="text" name="mname" value="<?=sanitizeHTML($user['mname'])?>"></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Last Name")?>: </td>
-    <td class="DataTD"><input type="text" name="lname" value="<?=$user['lname']?>"></td>
+    <td class="DataTD"><input type="text" name="lname" value="<?=sanitizeHTML($user['lname'])?>"></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Suffix")?><br>
       (<?=_("optional")?>)</td>
-    <td class="DataTD"><input type="text" name="suffix" value="<?=$user['suffix']?>"></td>
+    <td class="DataTD"><input type="text" name="suffix" value="<?=sanitizeHTML($user['suffix'])?>"></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Date of Birth")?><br>
@@ -100,22 +100,22 @@
 <? } else { ?>
   <tr>
     <td class="DataTD" width="125"><?=_("First Name")?>: </td>
-    <td class="DataTD" width="125"><?=$user['fname']?></td>
+    <td class="DataTD" width="125"><?=sanitizeHTML($user['fname'])?></td>
   </tr>
   <tr>
     <td class="DataTD" valign="top"><?=_("Middle Name(s)")?><br>
       (<?=_("optional")?>)
     </td>
-    <td class="DataTD"><?=$user['mname']?></td>
+    <td class="DataTD"><?=sanitizeHTML($user['mname'])?></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Last Name")?>: </td>
-    <td class="DataTD"><?=$user['lname']?></td>
+    <td class="DataTD"><?=sanitizeHTML($user['lname'])?></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Suffix")?><br>
       (<?=_("optional")?>)</td>
-    <td class="DataTD"><?=$user['suffix']?></td>
+    <td class="DataTD"><?=sanitizeHTML($user['suffix'])?></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Date of Birth")?><br>
@@ -124,7 +124,10 @@
   </tr>
 <? } ?>
   <tr>
-    <td colspan="2" class="title"><a href="account.php?id=13&amp;showdetails=<?=!$showdetails?>"><?=_("View secret question & answers and OTP phrases")?></a></td>
+    <td colspan="2" class="title"><a href="account.php?id=59&amp;oldid=13&amp;userid=<?=intval($_SESSION['profile']['id'])?>"><?=_('Show account history')?></a></td>
+  </tr>
+  <tr>
+    <td colspan="2" class="title"><a href="account.php?id=13&amp;showdetails=<?=intval(!$showdetails)?>"><?=_("View secret question & answers and OTP phrases")?></a></td>
   </tr>
   <? if($showdetails){ ?>
   <tr>
@@ -167,5 +170,5 @@
   </tr>
 </table>
 <input type="hidden" name="csrf" value="<?=make_csrf('perschange')?>" />
-<input type="hidden" name="oldid" value="<?=$id?>">
+<input type="hidden" name="oldid" value="<?=intval($id)?>">
 </form>
diff --git a/pages/account/16.php b/pages/account/16.php
index 28aa614..8783bc5 100644
--- a/pages/account/16.php
+++ b/pages/account/16.php
@@ -48,7 +48,7 @@ if (array_key_exists('emails',$_SESSION['_config']) && is_array($_SESSION['_conf
   </tr>
   <tr>
     <td class="DataTD"><label for="OU"><?=_("Department")?></label></td>
-    <td class="DataTD"><input type="text" id="OU" name="OU" value="<?=array_key_exists('OU',$_SESSION['_config'])?($_SESSION['_config']['OU']):''?>"/></td>
+    <td class="DataTD"><input type="text" id="OU" name="OU" value="<?=array_key_exists('OU',$_SESSION['_config'])?(sanitizeHTML($_SESSION['_config']['OU'])):''?>"/></td>
   </tr>
 
   <tr name="expertoff" style="display:none">
diff --git a/pages/account/18.php b/pages/account/18.php
index 9ab13b2..ca0a3c8 100644
--- a/pages/account/18.php
+++ b/pages/account/18.php
@@ -79,6 +79,7 @@ $status = array_key_exists('status',$_SESSION['_config']) ? intval($_SESSION['_c
       UNIX_TIMESTAMP(`oemail`.`expire`) as `expired`,
       `oemail`.`expire` as `expires`, `oemail`.`revoked` as `revoke`,
       UNIX_TIMESTAMP(`oemail`.`revoked`) as `revoked`,
+      if (`oemail`.`expire`=0,CURRENT_TIMESTAMP(),`oemail`.`modified`) as `modified`,
       `oemail`.`CN`, `oemail`.`serial`, `oemail`.`id`,
       `oemail`.`description`, `oemail`.`ou`, `orginfo`.`O`
       from `orgemailcerts` as `oemail`, `org`, `orginfo`
@@ -92,17 +93,17 @@ $status = array_key_exists('status',$_SESSION['_config']) ? intval($_SESSION['_c
   if(0==$status)
   {
     $query .= "AND `oemail`.`revoked`=0 AND `oemail`.`renewed`=0 ";
-    $query .= "HAVING `timeleft` > 0 AND `revoked`=0 ";
+    $query .= "HAVING `timeleft` > 0 AND `revoked`=0 or `expires` = 0 ";
   }
   switch ($sorting){
     case 0:
-      $query .= "ORDER BY `orginfo`.`O`, `oemail`.`expire` desc";
+      $query .= "ORDER BY `orginfo`.`O`, `modified` desc";
       break;
     case 1:
-      $query .= "ORDER BY `orginfo`.`O`, `oemail`.`ou`, `oemail`.`expire` desc";
+      $query .= "ORDER BY `orginfo`.`O`, `oemail`.`ou`, `modified` desc";
       break;
     case 2:
-      $query .= "ORDER BY `orginfo`.`O`, `oemail`.`CN`, `oemail`.`expire` desc";
+      $query .= "ORDER BY `orginfo`.`O`, `oemail`.`CN`, `modified` desc";
       break;
   }
   $res = mysql_query($query);
diff --git a/pages/account/21.php b/pages/account/21.php
index 75827fb..c5832b9 100644
--- a/pages/account/21.php
+++ b/pages/account/21.php
@@ -41,7 +41,7 @@ if (is_array($_SESSION['_config']['altrows'])) {
 }
 
 echo _("Organisation"), ": {$org['O']}<br>\n";
-echo _("Org. Unit"), ": {$_SESSION['_config']['OU']}<br>\n";
+echo _("Org. Unit"), ": ", sanitizeHTML($_SESSION['_config']['OU']), "<br>\n";
 echo _("Location"), ": {$org['L']}<br>\n";
 echo _("State/Province"), ": {$org['ST']}<br>\n";
 echo _("Country"), ": {$org['C']}<br>\n";
@@ -52,7 +52,7 @@ echo _("Country"), ": {$org['C']}<br>\n";
 		<input type="submit" name="process" value="<?=_("Submit")?>">
 		<input type="hidden" name="oldid" value="<?=$id?>">
 	</p>
-	
+
 	<?
 	if ($_SESSION['profile']['admin'] == 1) {
 		?>
diff --git a/pages/account/22.php b/pages/account/22.php
index 0413da0..7b7db2d 100644
--- a/pages/account/22.php
+++ b/pages/account/22.php
@@ -78,7 +78,9 @@ $status = array_key_exists('dstatus',$_SESSION['_config']) ? intval($_SESSION['_
       UNIX_TIMESTAMP(`orgdomaincerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
       UNIX_TIMESTAMP(`orgdomaincerts`.`expire`) as `expired`,
       `orgdomaincerts`.`expire` as `expires`, `revoked` as `revoke`,
-      UNIX_TIMESTAMP(`revoked`) as `revoked`, `CN`,
+      UNIX_TIMESTAMP(`orgdomaincerts`.`revoked`) as `revoked`,
+      if (`orgdomaincerts`.`expire`=0,CURRENT_TIMESTAMP(),`orgdomaincerts`.`modified`) as `modified`,
+       `CN`,
       `orgdomaincerts`.`serial`,
       `orgdomaincerts`.`id` as `id`,
       `orgdomaincerts`.`description`, `orginfo`.`O`
@@ -94,14 +96,14 @@ $status = array_key_exists('dstatus',$_SESSION['_config']) ? intval($_SESSION['_
     if(0==$status)
     {
       $query .= "AND `revoked`=0 AND `renewed`=0 ";
-      $query .= "HAVING `timeleft` > 0 ";
+      $query .= "HAVING `timeleft` > 0 or `expires` = 0 ";
     }
     switch ($sorting){
       case 0:
-        $query .= "ORDER BY `orginfo`.`O`, `orgdomaincerts`.`expire` desc";
+        $query .= "ORDER BY `orginfo`.`O`, `modified` desc";
         break;
       case 1:
-        $query .= "ORDER BY `orginfo`.`O`, `orgdomaincerts`.`CN`, `orgdomaincerts`.`expire` desc";
+        $query .= "ORDER BY `orginfo`.`O`, `orgdomaincerts`.`CN`, `modified` desc";
         break;
     }
 
diff --git a/pages/account/43.php b/pages/account/43.php
index 53b24d3..c889ce3 100644
--- a/pages/account/43.php
+++ b/pages/account/43.php
@@ -14,30 +14,30 @@
     You should have received a copy of the GNU General Public License
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
-*/ ?>
-<?
+*/
+
 include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
 
+$ticketno='';
+$ticketvalidation=FALSE;
 
-  if(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0)
-  {
-    $assurance = mysql_escape_string(intval($_REQUEST['assurance']));
-    $row = 0;
-    $res = mysql_query("select `to` from `notary` where `id`='$assurance'");
-    if ($res) {
-      $row = mysql_fetch_assoc($res);
-    }
-    mysql_query("delete from `notary` where `id`='$assurance'");
-    if ($row) {
-      fix_assurer_flag($row['to']);
-    }
-  }
+if (isset($_SESSION['ticketno'])) {
+    $ticketno = $_SESSION['ticketno'];
+    $ticketvalidation = valid_ticket_number($ticketno);
+}
+if (isset($_SESSION['ticketmsg'])) {
+    $ticketmsg = $_SESSION['ticketmsg'];
+} else {
+    $ticketmsg = '';
+}
 
-  if(intval(array_key_exists('userid',$_REQUEST)?$_REQUEST['userid']:0) <= 0)
-  {
+
+// search for an account by email search, if more than one is found display list to choose
+if(intval(array_key_exists('userid',$_REQUEST)?$_REQUEST['userid']:0) <= 0)
+{
     $_REQUEST['userid'] = 0;
 
-    $emailsearch = $email = mysql_escape_string(stripslashes($_REQUEST['email']));
+    $emailsearch = $email = mysql_real_escape_string(stripslashes($_REQUEST['email']));
 
     //Disabled to speed up the queries
     //if(!strstr($email, "%"))
@@ -45,877 +45,1036 @@ include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
 
     // bug-975 ted+uli changes --- begin
     if(preg_match("/^[0-9]+$/", $email)) {
-      // $email consists of digits only ==> search for IDs
-      // Be defensive here (outer join) if primary mail is not listed in email table
-      $query = "select `users`.`id` as `id`, `email`.`email` as `email`
-          from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
-          where (`email`.`id`='$email' or `users`.`id`='$email')
-            and `users`.`deleted`=0
-          group by `users`.`id` limit 100";
+        // $email consists of digits only ==> search for IDs
+        // Be defensive here (outer join) if primary mail is not listed in email table
+        $query = "select `users`.`id` as `id`, `email`.`email` as `email`
+            from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
+            where (`email`.`id`='$email' or `users`.`id`='$email')
+                and `users`.`deleted`=0
+            group by `users`.`id` limit 100";
     } else {
-      // $email contains non-digits ==> search for mail addresses
-      // Be defensive here (outer join) if primary mail is not listed in email table
-      $query = "select `users`.`id` as `id`, `email`.`email` as `email`
-          from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
-          where (`email`.`email` like '$emailsearch'
-                 or `users`.`email` like '$emailsearch')
-            and `users`.`deleted`=0
-          group by `users`.`id` limit 100";
+        // $email contains non-digits ==> search for mail addresses
+        // Be defensive here (outer join) if primary mail is not listed in email table
+        $query = "select `users`.`id` as `id`, `email`.`email` as `email`
+            from `users` left outer join `email` on (`users`.`id`=`email`.`memid`)
+            where (`email`.`email` like '$emailsearch'
+                    or `users`.`email` like '$emailsearch')
+                and `users`.`deleted`=0
+            group by `users`.`id` limit 100";
     }
     // bug-975 ted+uli changes --- end
     $res = mysql_query($query);
-    if(mysql_num_rows($res) > 1) { ?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="5" class="title"><?=_("Select Specific Account Details")?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("User ID")?></td>
-    <td class="DataTD"><?=_("Email")?></td>
-  </tr>
-<?
-  while($row = mysql_fetch_assoc($res))
-  { ?>
-  <tr>
-    <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=intval($row['id'])?></a></td>
-    <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=sanitizeHTML($row['email'])?></a></td>
-  </tr>
-<? } if(mysql_num_rows($res) >= 100) { ?>
-  <tr>
-    <td class="DataTD" colspan="2"><?=_("Only the first 100 rows are displayed.")?></td>
-  </tr>
-<? } else { ?>
-  <tr>
-    <td class="DataTD" colspan="2"><? printf(_("%s rows displayed."), mysql_num_rows($res)); ?></td>
-  </tr>
-<? } ?>
-</table><br><br>
-<?    } elseif(mysql_num_rows($res) == 1) {
-      $row = mysql_fetch_assoc($res);
-      $_REQUEST['userid'] = $row['id'];
-    } else {
-      printf(_("No users found matching %s"), sanitizeHTML($email));
-    }
-  }
-
-  if(intval($_REQUEST['userid']) > 0)
-  {
-    $userid = intval($_REQUEST['userid']);
-    $query = "select * from `users` where `users`.`id`='$userid' and `users`.`deleted`=0";
-    $res = mysql_query($query);
-    if(mysql_num_rows($res) <= 0)
-    {
-      echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");
-    } else {
-      $row = mysql_fetch_assoc($res);
-      $query = "select sum(`points`) as `points` from `notary` where `to`='".intval($row['id'])."'";
-      $dres = mysql_query($query);
-      $drow = mysql_fetch_assoc($dres);
-      $alerts = mysql_fetch_assoc(mysql_query("select * from `alerts` where `memid`='".intval($row['id'])."'"));
+    if(mysql_num_rows($res) > 1) {
 ?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="5" class="title"><? printf(_("%s's Account Details"), sanitizeHTML($row['email'])); ?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Email")?>:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['email'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("First Name")?>:</td>
-    <td class="DataTD"><form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to modify this DOB and/or last name?")?>')) return false;">
-  <input type="hidden" name="csrf" value="<?=make_csrf('admchangepers')?>" />
-  <input type="text" name="fname" value="<?=sanitizeHTML($row['fname'])?>"></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Middle Name")?>:</td>
-    <td class="DataTD"><input type="text" name="mname" value="<?=sanitizeHTML($row['mname'])?>"></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Last Name")?>:</td>
-    <td class="DataTD">  <input type="hidden" name="oldid" value="43">
-  <input type="hidden" name="action" value="updatedob">
-  <input type="hidden" name="userid" value="<?=intval($userid)?>">
-  <input type="text" name="lname" value="<?=sanitizeHTML($row['lname'])?>"></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Suffix")?>:</td>
-    <td class="DataTD"><input type="text" name="suffix" value="<?=sanitizeHTML($row['suffix'])?>"></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Date of Birth")?>:</td>
-    <td class="DataTD">
-<?
-  $year = intval(substr($row['dob'], 0, 4));
-  $month = intval(substr($row['dob'], 5, 2));
-  $day = intval(substr($row['dob'], 8, 2));
-  ?><nobr><select name="day">
+        <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+            <tr>
+                <td colspan="5" class="title"><?=_("Select Specific Account Details")?></td>
+            </tr>
+            <tr>
+                <td class="DataTD"><?=_("User ID")?></td>
+                <td class="DataTD"><?=_("Email")?></td>
+            </tr>
 <?
-        for($i = 1; $i <= 31; $i++)
+        while($row = mysql_fetch_assoc($res))
         {
-                echo "<option";
-                if($day == $i)
-                    echo " selected='selected'";
-                echo ">$i</option>";
-        }
 ?>
-    </select>
-    <select name="month">
+            <tr>
+                <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=intval($row['id'])?></a></td>
+                <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>"><?=sanitizeHTML($row['email'])?></a></td>
+            </tr>
 <?
-        for($i = 1; $i <= 12; $i++)
-        {
-                echo "<option value='$i'";
-                if($month == $i)
-                        echo " selected='selected'";
-                echo ">".ucwords(strftime("%B", mktime(0,0,0,$i,1,date("Y"))))."</option>";
         }
+
+        if(mysql_num_rows($res) >= 100) {
 ?>
-    </select>
-    <input type="text" name="year" value="<?=$year?>" size="4">
-    <input type="submit" value="Go"></form></nobr></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("CCA accepted")?>:</td>
-    <td class="DataTD"><a href="account.php?id=57&amp;userid=<?=intval($row['id'])?>"><?=intval(get_user_agreement_status($row['id'])) ? _("Yes") : _("No") ?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Trainings")?>:</td>
-    <td class="DataTD"><a href="account.php?id=55&amp;userid=<?=intval($row['id'])?>">show</a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Is Assurer")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;assurer=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admsetassuret')?>"><?=$row['assurer']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Blocked Assurer")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;assurer_blocked=<?=intval($row['id'])?>"><?=$row['assurer_blocked']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Account Locking")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;locked=<?=$row['id']?>&amp;csrf=<?=make_csrf('admactlock')?>"><?=$row['locked']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Code Signing")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;codesign=<?=$row['id']?>&amp;csrf=<?=make_csrf('admcodesign')?>"><?=$row['codesign']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Org Assurer")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;orgadmin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admorgadmin')?>"><?=$row['orgadmin']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("TTP Admin")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;ttpadmin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admttpadmin')?>"><?=$row['ttpadmin']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Location Admin")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;locadmin=<?=$row['id']?>"><?=$row['locadmin']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Admin")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;admin=<?=$row['id']?>&amp;csrf=<?=make_csrf('admsetadmin')?>"><?=$row['admin']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Ad Admin")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;adadmin=<?=$row['id']?>"><?=$row['adadmin']?></a> (0 = none, 1 = submit, 2 = approve)</td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Tverify Account")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;tverify=<?=$row['id']?>"><?=$row['tverify']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("General Announcements")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;general=<?=$row['id']?>"><?=$alerts['general']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Country Announcements")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;country=<?=$row['id']?>"><?=$alerts['country']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Regional Announcements")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;regional=<?=$row['id']?>"><?=$alerts['regional']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Within 200km Announcements")?>:</td>
-    <td class="DataTD"><a href="account.php?id=43&amp;radius=<?=$row['id']?>"><?=$alerts['radius']?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Change Password")?>:</td>
-    <td class="DataTD"><a href="account.php?id=44&amp;userid=<?=$row['id']?>"><?=_("Change Password")?></a></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Delete Account")?>:</td>
-    <td class="DataTD"><a href="account.php?id=50&amp;userid=<?=$row['id']?>&amp;csrf=<?=make_csrf('admdelaccount')?>"><?=_("Delete Account")?></a></td>
-  </tr>
+            <tr>
+                <td class="DataTD" colspan="2"><?=_("Only the first 100 rows are displayed.")?></td>
+            </tr>
 <?
-  // This is intensionally a $_GET for audit purposes. DO NOT CHANGE!!!
-  if(array_key_exists('showlostpw',$_GET) && $_GET['showlostpw'] == "yes") {
+        } else {
 ?>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - Q1:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['Q1'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - A1:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['A1'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - Q2:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['Q2'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - A2:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['A2'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - Q3:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['Q3'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - A3:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['A3'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - Q4:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['Q4'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - A4:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['A4'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - Q5:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['Q5'])?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><?=_("Lost Password")?> - A5:</td>
-    <td class="DataTD"><?=sanitizeHTML($row['A5'])?></td>
-  </tr>
-<? } else { ?>
-  <tr>
-    <td class="DataTD" colspan="2"><a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;showlostpw=yes"><?=_("Show Lost Password Details")?></a></td>
-  </tr>
-<? } ?>
-  <tr>
-    <td class="DataTD"><?=_("Assurance Points")?>:</td>
-    <td class="DataTD"><?=intval($drow['points'])?></td>
-  </tr>
-</table>
-<br><?
-  $query = "select * from `email` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''
-      and `email`!='".mysql_escape_string($row['email'])."'";
-  $dres = mysql_query($query);
-  if(mysql_num_rows($dres) > 0) { ?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="5" class="title"><?=_("Alternate Verified Email Addresses")?></td>
-  </tr><?
-  $rc = mysql_num_rows($dres);
-  while($drow = mysql_fetch_assoc($dres))
-  { ?>
-  <tr>
-    <td class="DataTD"><?=_("Secondary Emails")?>:</td>
-    <td class="DataTD"><?=sanitizeHTML($drow['email'])?></td>
-  </tr>
-<? } ?>
-</table>
-<br><? } ?>
+            <tr>
+                <td class="DataTD" colspan="2"><? printf(_("%s rows displayed."), mysql_num_rows($res)); ?></td>
+            </tr>
 <?
-  $query = "select * from `domains` where `memid`='".intval($row['id'])."' and `deleted`=0 and `hash`=''";
-  $dres = mysql_query($query);
-  if(mysql_num_rows($dres) > 0) { ?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="5" class="title"><?=_("Verified Domains")?></td>
-  </tr><?
-  $rc = mysql_num_rows($dres);
-  while($drow = mysql_fetch_assoc($dres))
-  { ?>
-  <tr>
-    <td class="DataTD"><?=_("Domain")?>:</td>
-    <td class="DataTD"><?=sanitizeHTML($drow['domain'])?></td>
-  </tr>
-<? } ?>
-</table>
-<br>
-<? } ?>
-<? //  Begin - Debug infos ?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="2" class="title"><?=_("Account State")?></td>
-  </tr>
-
-<?
-  // ---  bug-975 begin ---
-  //  potential db inconsistency like in a20110804.1
-  //    Admin console -> don't list user account
-  //    User login -> impossible
-  //    Assurer, assure someone -> user displayed
-  /*  regular user account search with regular settings
-
-    --- Admin Console find user query
-    $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
-        where `users`.`id`=`email`.`memid` and
-        (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and
-        `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0
-        group by `users`.`id` limit 100";
-     => requirements
-       1.  email.hash = ''
-       2.  email.deleted = 0
-       3.  users.deleted = 0
-       4.  email.email = primary-email       (???) or'd
-      not covered by admin console find user routine, but may block users login
-       5.  users.verified = 0|1
-      further "special settings"
-       6.  users.locked  (setting displayed in display form)
-       7.  users.assurer_blocked   (setting displayed in display form)
-
-    --- User login user query
-    select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or
-						`password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0
-		=> requirements
-       1. users.verified = 1
-       2. users.deleted = 0
-       3. users.locked = 0
-       4. users.email = primary-email
-
-    --- Assurer, assure someone find user query
-    select * from `users` where `email`='".mysql_escape_string(stripslashes($_POST['email']))."'
-           and `deleted`=0
-		=> requirements
-       1. users.deleted = 0
-       2. users.email = primary-email
-                                     Admin      User        Assurer
-      bit                            Console    Login       assure someone
-
-       1.  email.hash = ''            Yes        No           No
-       2.  email.deleted = 0          Yes        No           No
-       3.  users.deleted = 0          Yes        Yes          Yes
-       4.  users.verified = 1         No         Yes          No
-       5.  users.locked = 0           No         Yes          No
-       6.  users.email = prim-email   No         Yes          Yes
-       7.  email.email = prim-email   Yes        No           No
-
-    full usable account needs all 7 requirements fulfilled
-    so if one setting isn't set/cleared there is an inconsistency either way
-    if eg email.email is not avail, admin console cannot open user info
-    but user can login and assurer can display user info
-    if user verified is not set to 1, admin console displays user record
-    but user cannot login, but assurer can search for the user and the data displays
-
-    consistency check:
-    1. search primary-email in users.email
-    2. search primary-email in email.email
-    3. userid = email.memid
-    4. check settings from table 1. - 5.
-
-   */
-
-  $inconsistency = 0;
-  $inconsistencydisp = "";
-  $inccause = "";
-   // current userid  intval($row['id'])
-  $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked`
-      from `users` where `id`='".intval($row['id'])."' ";
-  $dres = mysql_query($query);
-  $drow = mysql_fetch_assoc($dres);
-  $uemail    = $drow['uemail'];
-  $udeleted  = $drow['udeleted'];
-  $uverified = $drow['verified'];
-  $ulocked   = $drow['locked'];
-
-  $query = "select `hash`, `email` as `eemail` from `email`
-      where `memid`='".intval($row['id'])."' and
-      `email` ='".$uemail."' and
-      `deleted` = 0";
-  $dres = mysql_query($query);
-  if ($drow = mysql_fetch_assoc($dres)) {
-    $drow['edeleted'] = 0;
-  } else {
-  	// try if there are deleted entries
-    $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email`
-        where `memid`='".intval($row['id'])."' and
-        `email` ='".$uemail."'";
-    $dres = mysql_query($query);
-    $drow = mysql_fetch_assoc($dres);
-  }
-
-  if ($drow) {
-    $eemail    = $drow['eemail'];
-    $edeleted  = $drow['edeleted'];
-    $ehash     = $drow['hash'];
-    if ($udeleted!=0) {
-      $inconsistency += 1;
-      $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted");
-    }
-    if ($uverified!=1) {
-      $inconsistency += 2;
-      $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set");
-    }
-    if ($ulocked!=0) {
-      $inconsistency += 4;
-      $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set");
-    }
-    if ($edeleted!=0) {
-      $inconsistency += 8;
-      $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted");
-    }
-    if ($ehash!='') {
-      $inconsistency += 16;
-      $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset");
-    }
-  } else {
-    $inconsistency = 32;
-    $inccause = _("Prim. email, Email record doesn't exist");
-  }
-  if ($inconsistency>0) {
-     // $inconsistencydisp = _("Yes");
-?>
-  <tr>
-    <td class="DataTD"><?=_("Account inconsistency")?>:</td>
-    <td class="DataTD"><?=$inccause?><br>code: <?=$inconsistency?></td>
-  </tr>
-  <tr>
-    <td colspan="2" class="DataTD" style="max-width: 75ex">
-      <?=_("Account inconsistency can cause problems in daily account ".
-      "operations and needs to be fixed manually through arbitration/critical ".
-      "team.")?>
-     </td>
-  </tr>
-<? }
-
-  // ---  bug-975 end ---
+        }
 ?>
-</table>
-<br>
+        </table><br><br>
 <?
- //  End - Debug infos
-?>
+    } elseif(mysql_num_rows($res) == 1) {
+        $row = mysql_fetch_assoc($res);
+        $_REQUEST['userid'] = $row['id'];
+    } else {
+        printf(_("No users found matching %s"), sanitizeHTML($email));
+    }
+}
+
+// display user information for given user id
+if(intval($_REQUEST['userid']) > 0) {
+    $userid = intval($_REQUEST['userid']);
+    $res =get_user_data($userid);
+    if(mysql_num_rows($res) <= 0) {
+        echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");
+    } else {
+        $row = mysql_fetch_assoc($res);
+        $query = "select sum(`points`) as `points` from `notary` where `to`='".intval($row['id'])."' and `deleted` = 0";
+        $dres = mysql_query($query);
+        $drow = mysql_fetch_assoc($dres);
+        $alerts =get_alerts(intval($row['id']));
 
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-	<tr>
-		<td colspan="6" class="title"><?=_("Certificates")?></td>
-	</tr>
-
-	<tr>
-		<td class="DataTD"><?=_("Cert Type")?>:</td>
-		<td class="DataTD"><?=_("Total")?></td>
-		<td class="DataTD"><?=_("Valid")?></td>
-		<td class="DataTD"><?=_("Expired")?></td>
-		<td class="DataTD"><?=_("Revoked")?></td>
-		<td class="DataTD"><?=_("Latest Expire")?></td>
-	</tr>
-<!-- server certificates -->
-	<tr>
-		<td class="DataTD"><?=_("Server")?>:</td>
-	<?
-	$query = "select COUNT(*) as `total`,
-	                 MAX(`domaincerts`.`expire`) as `maxexpire`
-	          from `domains` inner join `domaincerts`
-	               on `domains`.`id` = `domaincerts`.`domid`
-	          where `domains`.`memid` = '".intval($row['id'])."' ";
-	$dres = mysql_query($query);
-	$drow = mysql_fetch_assoc($dres);
-	$total = $drow['total'];
-
-	$maxexpire = "0000-00-00 00:00:00";
-	if ($drow['maxexpire']) {
-		$maxexpire = $drow['maxexpire'];
-	}
-
-	if($total > 0) {
-		$query = "select COUNT(*) as `valid`
-		          from `domains` inner join `domaincerts`
-		               on `domains`.`id` = `domaincerts`.`domid`
-		          where `domains`.`memid` = '".intval($row['id'])."'
-		                and `revoked` = '0000-00-00 00:00:00'
-		                and `expire` > NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$valid = $drow['valid'];
-
-		$query = "select COUNT(*) as `expired`
-		          from `domains` inner join `domaincerts`
-		               on `domains`.`id` = `domaincerts`.`domid`
-		          where `domains`.`memid` = '".intval($row['id'])."'
-		                and `expire` <= NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$expired = $drow['expired'];
-
-		$query = "select COUNT(*) as `revoked`
-		          from `domains` inner join `domaincerts`
-		               on `domains`.`id` = `domaincerts`.`domid`
-		          where `domains`.`memid` = '".intval($row['id'])."'
-		                and `revoked` != '0000-00-00 00:00:00'";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$revoked = $drow['revoked'];
-		?>
-		<td class="DataTD"><?=intval($total)?></td>
-		<td class="DataTD"><?=intval($valid)?></td>
-		<td class="DataTD"><?=intval($expired)?></td>
-		<td class="DataTD"><?=intval($revoked)?></td>
-		<td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
-			substr($maxexpire, 0, 10) : _("Pending")?></td>
-		<?
-	} else { // $total > 0
-		?>
-		<td colspan="5" class="DataTD"><?=_("None")?></td>
-		<?
-	} ?>
-	</tr>
-<!-- client certificates -->
-	<tr>
-		<td class="DataTD"><?=_("Client")?>:</td>
-	<?
-	$query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
-	          from `emailcerts`
-	          where `memid` = '".intval($row['id'])."' ";
-	$dres = mysql_query($query);
-	$drow = mysql_fetch_assoc($dres);
-	$total = $drow['total'];
-
-	$maxexpire = "0000-00-00 00:00:00";
-	if ($drow['maxexpire']) {
-		$maxexpire = $drow['maxexpire'];
-	}
-
-	if($total > 0) {
-		$query = "select COUNT(*) as `valid`
-		          from `emailcerts`
-		          where `memid` = '".intval($row['id'])."'
-		                and `revoked` = '0000-00-00 00:00:00'
-		                and `expire` > NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$valid = $drow['valid'];
-
-		$query = "select COUNT(*) as `expired`
-		          from `emailcerts`
-		          where `memid` = '".intval($row['id'])."'
-		                and `expire` <= NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$expired = $drow['expired'];
-
-		$query = "select COUNT(*) as `revoked`
-		          from `emailcerts`
-		          where `memid` = '".intval($row['id'])."'
-		                and `revoked` != '0000-00-00 00:00:00'";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$revoked = $drow['revoked'];
-		?>
-		<td class="DataTD"><?=intval($total)?></td>
-		<td class="DataTD"><?=intval($valid)?></td>
-		<td class="DataTD"><?=intval($expired)?></td>
-		<td class="DataTD"><?=intval($revoked)?></td>
-		<td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
-			substr($maxexpire, 0, 10) : _("Pending")?></td>
-		<?
-	} else { // $total > 0
-		?>
-		<td colspan="5" class="DataTD"><?=_("None")?></td>
-		<?
-	} ?>
-	</tr>
-<!-- gpg certificates -->
-	<tr>
-		<td class="DataTD"><?=_("GPG")?>:</td>
-	<?
-	$query = "select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
-	          from `gpg`
-	          where `memid` = '".intval($row['id'])."' ";
-	$dres = mysql_query($query);
-	$drow = mysql_fetch_assoc($dres);
-	$total = $drow['total'];
-
-	$maxexpire = "0000-00-00 00:00:00";
-	if ($drow['maxexpire']) {
-		$maxexpire = $drow['maxexpire'];
-	}
-
-	if($total > 0) {
-		$query = "select COUNT(*) as `valid`
-		          from `gpg`
-		          where `memid` = '".intval($row['id'])."'
-		                and `expire` > NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$valid = $drow['valid'];
-
-		$query = "select COUNT(*) as `expired`
-			from `gpg`
-			where `memid` = '".intval($row['id'])."'
-			and `expire` <= NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$expired = $drow['expired'];
-
-		?>
-		<td class="DataTD"><?=intval($total)?></td>
-		<td class="DataTD"><?=intval($valid)?></td>
-		<td class="DataTD"><?=intval($expired)?></td>
-		<td class="DataTD"></td>
-		<td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
-			substr($maxexpire, 0, 10) : _("Pending")?></td>
-		<?
-	} else { // $total > 0
-		?>
-		<td colspan="5" class="DataTD"><?=_("None")?></td>
-		<?
-	} ?>
-	</tr>
-<!-- org server certificates -->
-	<tr>
-		<td class="DataTD"><a href="account.php?id=58&amp;userid=<?=intval($row['id'])?>"><?=_("Org Server")?></a>:</td>
-	<?
-	$query = "select COUNT(*) as `total`,
-	                 MAX(`orgcerts`.`expire`) as `maxexpire`
-	          from `orgdomaincerts` as `orgcerts` inner join `org`
-	                   on `orgcerts`.`orgid` = `org`.`orgid`
-	          where `org`.`memid` = '".intval($row['id'])."' ";
-	$dres = mysql_query($query);
-	$drow = mysql_fetch_assoc($dres);
-	$total = $drow['total'];
-
-	$maxexpire = "0000-00-00 00:00:00";
-	if ($drow['maxexpire']) {
-		$maxexpire = $drow['maxexpire'];
-	}
-
-	if($total > 0) {
-		$query = "select COUNT(*) as `valid`
-		          from `orgdomaincerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
-		                and `orgcerts`.`expire` > NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$valid = $drow['valid'];
-
-		$query = "select COUNT(*) as `expired`
-		          from `orgdomaincerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`expire` <= NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$expired = $drow['expired'];
-
-		$query = "select COUNT(*) as `revoked`
-		          from `orgdomaincerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$revoked = $drow['revoked'];
-		?>
-		<td class="DataTD"><?=intval($total)?></td>
-		<td class="DataTD"><?=intval($valid)?></td>
-		<td class="DataTD"><?=intval($expired)?></td>
-		<td class="DataTD"><?=intval($revoked)?></td>
-		<td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
-			substr($maxexpire, 0, 10) : _("Pending")?></td>
-		<?
-	} else { // $total > 0
-		?>
-		<td colspan="5" class="DataTD"><?=_("None")?></td>
-		<?
-	} ?>
-	</tr>
-<!-- org client certificates -->
-	<tr>
-		<td class="DataTD"><?=_("Org Client")?>:</td>
-	<?
-	$query = "select COUNT(*) as `total`,
-	                 MAX(`orgcerts`.`expire`) as `maxexpire`
-	          from `orgemailcerts` as `orgcerts` inner join `org`
-	                   on `orgcerts`.`orgid` = `org`.`orgid`
-	          where `org`.`memid` = '".intval($row['id'])."' ";
-	$dres = mysql_query($query);
-	$drow = mysql_fetch_assoc($dres);
-	$total = $drow['total'];
-
-	$maxexpire = "0000-00-00 00:00:00";
-	if ($drow['maxexpire']) {
-		$maxexpire = $drow['maxexpire'];
-	}
-
-	if($total > 0) {
-		$query = "select COUNT(*) as `valid`
-		          from `orgemailcerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
-		                and `orgcerts`.`expire` > NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$valid = $drow['valid'];
-
-		$query = "select COUNT(*) as `expired`
-		          from `orgemailcerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`expire` <= NOW()";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$expired = $drow['expired'];
-
-		$query = "select COUNT(*) as `revoked`
-		          from `orgemailcerts` as `orgcerts` inner join `org`
-		                   on `orgcerts`.`orgid` = `org`.`orgid`
-		          where `org`.`memid` = '".intval($row['id'])."'
-		                and `orgcerts`.`revoked` != '0000-00-00 00:00:00'";
-		$dres = mysql_query($query);
-		$drow = mysql_fetch_assoc($dres);
-		$revoked = $drow['revoked'];
-		?>
-		<td class="DataTD"><?=intval($total)?></td>
-		<td class="DataTD"><?=intval($valid)?></td>
-		<td class="DataTD"><?=intval($expired)?></td>
-		<td class="DataTD"><?=intval($revoked)?></td>
-		<td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?
-			substr($maxexpire, 0, 10) : _("Pending")?></td>
-		<?
-	} else { // $total > 0
-		?>
-		<td colspan="5" class="DataTD"><?=_("None")?></td>
-		<?
-	} ?>
-	</tr>
-	<tr>
-		<td colspan="6" class="title">
-			<form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to revoke all private certificates?")?>')) return false;">
-				<input type="hidden" name="action" value="revokecert">
-				<input type="hidden" name="oldid" value="43">
-				<input type="hidden" name="userid" value="<?=intval($userid)?>">
-				<input type="submit" value="<?=_('revoke certificates')?>">
-			</form>
-		</td>
-	</tr>
-</table>
-<br>
-
-
-<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto"><?=_("Show Assurances the user got")?></a>
- (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredto15"><?=_("New calculation")?></a>)
-<br />
-<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby"><?=_("Show Assurances the user gave")?></a>
- (<a href="account.php?id=43&amp;userid=<?=$row['id']?>&amp;shownotary=assuredby15"><?=_("New calculation")?></a>)
-<br />
+//display account data
 
-<?
-//  if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") {
+//deletes an assurance
+        if(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0 && $ticketvalidation == true)
+        {
+            if (!write_se_log($userid, $_SESSION['profile']['id'], 'SE assurance revoke', $ticketno)) {
+                $ticketmsg=_("Writing to the admin log failed. Can't continue.");
+            } else {
+                $assurance = intval($_REQUEST['assurance']);
+                $trow = 0;
+                $res = mysql_query("select `to` from `notary` where `id`='".intval($assurance)."' and `deleted` = 0");
+                if ($res) {
+                    $trow = mysql_fetch_assoc($res);
+                    if ($trow) {
+                        mysql_query("update `notary` set `deleted`=NOW() where `id`='".intval($assurance)."'");
+                        fix_assurer_flag($trow['to']);
+                    }
+                }
+            }
+        } elseif(array_key_exists('assurance',$_REQUEST) && $_REQUEST['assurance'] > 0 && $ticketvalidation == FALSE) {
+            $ticketmsg=_('No assurance revoked. Ticket number is missing!');
+        }
 
-function showassuredto()
-{
-?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="8" class="title"><?=_("Assurance Points")?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><b><?=_("ID")?></b></td>
-    <td class="DataTD"><b><?=_("Date")?></b></td>
-    <td class="DataTD"><b><?=_("Who")?></b></td>
-    <td class="DataTD"><b><?=_("Email")?></b></td>
-    <td class="DataTD"><b><?=_("Points")?></b></td>
-    <td class="DataTD"><b><?=_("Location")?></b></td>
-    <td class="DataTD"><b><?=_("Method")?></b></td>
-    <td class="DataTD"><b><?=_("Revoke")?></b></td>
-  </tr>
-<?
-  $query = "select * from `notary` where `to`='".intval($_GET['userid'])."'";
-  $dres = mysql_query($query);
-  $points = 0;
-  while($drow = mysql_fetch_assoc($dres))
-  {
-    $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($drow['from'])."'"));
-    $points += $drow['points'];
+//Ticket number
 ?>
-  <tr>
-    <td class="DataTD"><?=$drow['id']?></td>
-    <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td>
-    <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td>
-    <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
-    <td class="DataTD"><?=intval($drow['points'])?></td>
-    <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
-    <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
-    <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>&amp;assurance=<?=intval($drow['id'])?>&amp;csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
-  </tr>
-<? } ?>
-  <tr>
-    <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
-    <td class="DataTD"><?=$points?></td>
-    <td class="DataTD" colspan="3">&nbsp;</td>
-  </tr>
-</table>
-<? } ?>
 
+<form method="post" action="account.php?id=43&userid=<?=intval($_REQUEST['userid'])?>">
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="2" class="title"><?=_('Ticket handling') ?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_('Ticket no')?>:</td>
+            <td class="DataTD"><input type="text" name="ticketno" value="<?=sanitizeHTML($ticketno)?>"/></td>
+        </tr>
+        <tr>
+            <td colspan="2" class="DataTDError"><?=$ticketmsg?></td><?php $_SESSION['ticketmsg']='' ?>
+        </tr>
+        <tr>
+            <td colspan="2" ><input type="submit" value="<?=_('Set ticket number') ?>"></td>
+        </tr>
+    </table>
+</form>
+<br/>
+
+
+<!-- display data table -->
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="5" class="title"><? printf(_("%s's Account Details"), sanitizeHTML($row['email'])); ?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Email")?>:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['email'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("First Name")?>:</td>
+            <td class="DataTD"><form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to modify this DOB and/or last name?")?>')) return false;">
+                <input type="hidden" name="csrf" value="<?=make_csrf('admchangepers')?>" />
+                <input type="text" name="fname" value="<?=sanitizeHTML($row['fname'])?>">
+            </td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Middle Name")?>:</td>
+            <td class="DataTD"><input type="text" name="mname" value="<?=sanitizeHTML($row['mname'])?>"></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Last Name")?>:</td>
+            <td class="DataTD">  <input type="hidden" name="oldid" value="43">
+                <input type="hidden" name="action" value="updatedob">
+                <input type="hidden" name="userid" value="<?=intval($userid)?>">
+                <input type="text" name="lname" value="<?=sanitizeHTML($row['lname'])?>">
+            </td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Suffix")?>:</td>
+            <td class="DataTD"><input type="text" name="suffix" value="<?=sanitizeHTML($row['suffix'])?>"></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Date of Birth")?>:</td>
+            <td class="DataTD">
+                <?
+                $year = intval(substr($row['dob'], 0, 4));
+                $month = intval(substr($row['dob'], 5, 2));
+                $day = intval(substr($row['dob'], 8, 2));
+    ?>
+                <nobr>
+                        <select name="day">
+    <?
+                for($i = 1; $i <= 31; $i++) {
+                    echo "<option";
+                    if($day == $i) {
+                        echo " selected='selected'";
+                    }
+                    echo ">$i</option>";
+                }
+    ?>
+                        </select>
+                        <select name="month">
+    <?
+                for($i = 1; $i <= 12; $i++) {
+                    echo "<option value='$i'";
+                    if($month == $i)
+                            echo " selected='selected'";
+                    echo ">".ucwords(strftime("%B", mktime(0,0,0,$i,1,date("Y"))))."</option>";
+                }
+    ?>
+                        </select>
+                        <input type="text" name="year" value="<?=$year?>" size="4">
+                        <input type="submit" value="Go">
+                        <input type="hidden" name="ticketno" value="<?=sanitizeHTML($ticketno)?>"/>
+                    </form>
+                </nobr>
+            </td>
+        </tr>
+
+    <? // list of flags ?>
+        <tr>
+            <td class="DataTD"><?=_("CCA accepted")?>:</td>
+            <td class="DataTD"><a href="account.php?id=57&amp;userid=<?=intval($row['id'])?>"><?=intval(get_user_agreement_status($row['id'], 'CCA')) ? _("Yes") : _("No") ?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Trainings")?>:</td>
+            <td class="DataTD"><a href="account.php?id=55&amp;userid=<?=intval($row['id'])?>">show</a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Is Assurer")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;assurer=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admsetassuret')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['assurer'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Blocked Assurer")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;assurer_blocked=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['assurer_blocked'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Account Locking")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;locked=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admactlock')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['locked'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Code Signing")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;codesign=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admcodesign')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['codesign'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Org Assurer")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;orgadmin=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admorgadmin')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['orgadmin'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("TTP Admin")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;ttpadmin=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admttpadmin')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['ttpadmin'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Location Admin")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;locadmin=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=$row['locadmin']?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Admin")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;admin=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admsetadmin')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['admin'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Ad Admin")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;adadmin=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['adadmin'])?></a> (0 = none, 1 = submit, 2 = approve)</td>
+        </tr>
+    <!-- presently not needed
+        <tr>
+            <td class="DataTD"><?=_("Tverify Account")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;tverify=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($row['tverify'])?></a></td>
+        </tr>
+    -->
+        <tr>
+            <td class="DataTD"><?=_("General Announcements")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;general=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($alerts['general'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Country Announcements")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;country=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($alerts['country'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Regional Announcements")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;regional=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($alerts['regional'])?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Within 200km Announcements")?>:</td>
+            <td class="DataTD"><a href="account.php?id=43&amp;radius=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=intval($alerts['radius'])?></a></td>
+        </tr>
+    <? //change password, view secret questions and delete account section ?>
+        <tr>
+            <td class="DataTD"><?=_("Change Password")?>:</td>
+            <td class="DataTD"><a href="account.php?id=44&amp;userid=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Change Password")?></a></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Delete Account")?>:</td>
+            <td class="DataTD"><a href="account.php?id=50&amp;userid=<?=intval($row['id'])?>&amp;csrf=<?=make_csrf('admdelaccount')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Delete Account")?></a></td>
+        </tr>
+    <?
+                // This is intensionally a $_GET for audit purposes. DO NOT CHANGE!!!
+                if(array_key_exists('showlostpw',$_GET) && $_GET['showlostpw'] == "yes" && $ticketvalidation==true) {
+                    if (!write_se_log($userid, $_SESSION['profile']['id'], 'SE view lost password information', $ticketno)) {
+    ?>
+        <tr>
+            <td class="DataTD" colspan="2"><?=_("Writing to the admin log failed. Can't continue.")?></td>
+        </tr>
+        <tr>
+            <td class="DataTD" colspan="2"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;showlostpw=yes&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Lost Password Details")?></a></td>
+        </tr>
+    <?
+                    } else {
+    ?>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - Q1:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['Q1'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - A1:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['A1'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - Q2:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['Q2'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - A2:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['A2'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - Q3:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['Q3'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - A3:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['A3'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - Q4:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['Q4'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - A4:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['A4'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - Q5:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['Q5'])?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Lost Password")?> - A5:</td>
+            <td class="DataTD"><?=sanitizeHTML($row['A5'])?></td>
+        </tr>
+    <?
+                    }
+                } elseif (array_key_exists('showlostpw',$_GET) && $_GET['showlostpw'] == "yes" && $ticketvalidation==false) {
+    ?>
+        <tr>
+            <td class="DataTD" colspan="2"><?=_('No access granted. Ticket number is missing')?></td>
+        </tr>
+        <tr>
+            <td class="DataTD" colspan="2"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;showlostpw=yes&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Lost Password Details")?></a></td>
+        </tr>
+    <?
+                } else {
+                    ?>
+        <tr>
+            <td class="DataTD" colspan="2"><a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;showlostpw=yes&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Lost Password Details")?></a></td>
+        </tr>
+    <?                }
+
+    // list assurance points
+    ?>
+        <tr>
+            <td class="DataTD"><?=_("Assurance Points")?>:</td>
+            <td class="DataTD"><?=intval($drow['points'])?></td>
+        </tr>
+    <?
+    // show account history
+    ?>
+        <tr>
+            <td class="DataTD" colspan="2"><a href="account.php?id=59&amp;oldid=43&amp;userid=<?=intval($row['id'])?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_('Show account history')?></a></td>
+        </tr>
+    </table>
+    <br/>
+    <?
+    //list secondary email addresses
+                $dres = get_email_addresses(intval($row['id']),$row['email']);
+                if(mysql_num_rows($dres) > 0) {
+    ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="5" class="title"><?=_("Alternate Verified Email Addresses")?></td>
+        </tr>
+    <?
+                    while($drow = mysql_fetch_assoc($dres)) {
+    ?>
+        <tr>
+            <td class="DataTD"><?=_("Secondary Emails")?>:</td>
+            <td class="DataTD"><?=sanitizeHTML($drow['email'])?></td>
+        </tr>
+    <?
+                    }
+    ?>
+    </table>
+    <br/>
+    <?
+                }
+
+    // list of domains
+                $dres=get_domains(intval($row['id']));
+                if(mysql_num_rows($dres) > 0) {
+    ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="5" class="title"><?=_("Verified Domains")?></td>
+        </tr>
+    <?
+                    while($drow = mysql_fetch_assoc($dres)) {
+    ?>
+        <tr>
+            <td class="DataTD"><?=_("Domain")?>:</td>
+            <td class="DataTD"><?=sanitizeHTML($drow['domain'])?></td>
+        </tr>
+    <?
+                    }
+    ?>
+    </table>
+    <br/>
+    <?
+                }
+    ?>
+    <? //  Begin - Debug infos ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="2" class="title"><?=_("Account State")?></td>
+        </tr>
+
+    <?
+                // ---  bug-975 begin ---
+                //  potential db inconsistency like in a20110804.1
+                //    Admin console -> don't list user account
+                //    User login -> impossible
+                //    Assurer, assure someone -> user displayed
+                /*  regular user account search with regular settings
+
+                --- Admin Console find user query
+                $query = "select `users`.`id` as `id`, `email`.`email` as `email` from `users`,`email`
+                    where `users`.`id`=`email`.`memid` and
+                    (`email`.`email` like '$emailsearch' or `email`.`id`='$email' or `users`.`id`='$email') and
+                    `email`.`hash`='' and `email`.`deleted`=0 and `users`.`deleted`=0
+                    group by `users`.`id` limit 100";
+                 => requirements
+                   1.  email.hash = ''
+                   2.  email.deleted = 0
+                   3.  users.deleted = 0
+                   4.  email.email = primary-email       (???) or'd
+                  not covered by admin console find user routine, but may block users login
+                   5.  users.verified = 0|1
+                  further "special settings"
+                   6.  users.locked  (setting displayed in display form)
+                   7.  users.assurer_blocked   (setting displayed in display form)
+
+                --- User login user query
+                select * from `users` where `email`='$email' and (`password`=old_password('$pword') or `password`=sha1('$pword') or
+                    `password`=password('$pword')) and `verified`=1 and `deleted`=0 and `locked`=0
+                 => requirements
+                   1. users.verified = 1
+                   2. users.deleted = 0
+                   3. users.locked = 0
+                   4. users.email = primary-email
+
+                --- Assurer, assure someone find user query
+                select * from `users` where `email`='".mysql_real_escape_string(stripslashes($_POST['email']))."'
+                    and `deleted`=0
+                 => requirements
+                   1. users.deleted = 0
+                   2. users.email = primary-email
+
+                                                 Admin      User        Assurer
+                  bit                            Console    Login       assure someone
+
+                   1.  email.hash = ''            Yes        No           No
+                   2.  email.deleted = 0          Yes        No           No
+                   3.  users.deleted = 0          Yes        Yes          Yes
+                   4.  users.verified = 1         No         Yes          No
+                   5.  users.locked = 0           No         Yes          No
+                   6.  users.email = prim-email   No         Yes          Yes
+                   7.  email.email = prim-email   Yes        No           No
+
+                full usable account needs all 7 requirements fulfilled
+                so if one setting isn't set/cleared there is an inconsistency either way
+                if eg email.email is not avail, admin console cannot open user info
+                but user can login and assurer can display user info
+                if user verified is not set to 1, admin console displays user record
+                but user cannot login, but assurer can search for the user and the data displays
+
+                consistency check:
+                1. search primary-email in users.email
+                2. search primary-email in email.email
+                3. userid = email.memid
+                4. check settings from table 1. - 5.
+
+                */
+
+                $inconsistency = 0;
+                $inconsistencydisp = "";
+                $inccause = "";
+
+                // current userid  intval($row['id'])
+                $query = "select `email` as `uemail`, `deleted` as `udeleted`, `verified`, `locked`
+                    from `users` where `id`='".intval($row['id'])."' ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $uemail    = $drow['uemail'];
+                $udeleted  = $drow['udeleted'];
+                $uverified = $drow['verified'];
+                $ulocked   = $drow['locked'];
+
+                $query = "select `hash`, `email` as `eemail` from `email`
+                    where `memid`='".intval($row['id'])."' and
+                        `email` ='".$uemail."' and
+                        `deleted` = 0";
+                $dres = mysql_query($query);
+                if ($drow = mysql_fetch_assoc($dres)) {
+                    $drow['edeleted'] = 0;
+                } else {
+                    // try if there are deleted entries
+                    $query = "select `hash`, `deleted` as `edeleted`, `email` as `eemail` from `email`
+                        where `memid`='".intval($row['id'])."' and
+                            `email` ='".$uemail."'";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                }
+
+                if ($drow) {
+                    $eemail    = $drow['eemail'];
+                    $edeleted  = $drow['edeleted'];
+                    $ehash     = $drow['hash'];
+                    if ($udeleted!=0) {
+                        $inconsistency += 1;
+                        $inccause .= (empty($inccause)?"":"<br>")._("Users record set to deleted");
+                    }
+                    if ($uverified!=1) {
+                        $inconsistency += 2;
+                        $inccause .= (empty($inccause)?"":"<br>")._("Users record verified not set");
+                    }
+                    if ($ulocked!=0) {
+                        $inconsistency += 4;
+                        $inccause .= (empty($inccause)?"":"<br>")._("Users record locked set");
+                    }
+                    if ($edeleted!=0) {
+                        $inconsistency += 8;
+                        $inccause .= (empty($inccause)?"":"<br>")._("Email record set deleted");
+                    }
+                    if ($ehash!='') {
+                        $inconsistency += 16;
+                        $inccause .= (empty($inccause)?"":"<br>")._("Email record hash not unset");
+                    }
+                } else {
+                    $inconsistency = 32;
+                    $inccause = _("Prim. email, Email record doesn't exist");
+                }
+                if ($inconsistency>0) {
+                    // $inconsistencydisp = _("Yes");
+    ?>
+        <tr>
+            <td class="DataTD"><?=_("Account inconsistency")?>:</td>
+            <td class="DataTD"><?=$inccause?><br>code: <?=intval($inconsistency)?></td>
+        </tr>
+        <tr>
+            <td colspan="2" class="DataTD" style="max-width: 75ex;">
+                <?=_("Account inconsistency can cause problems in daily account operations and needs to be fixed manually through arbitration/critical team.")?>
+            </td>
+        </tr>
+    <?
+                }
+
+                // ---  bug-975 end ---
+    ?>
+    </table>
+    <br />
+    <?
+    //  End - Debug infos
+
+    // certificate overview
+    ?>
+
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="6" class="title"><?=_("Certificates")?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><?=_("Cert Type")?>:</td>
+            <td class="DataTD"><?=_("Total")?></td>
+            <td class="DataTD"><?=_("Valid")?></td>
+            <td class="DataTD"><?=_("Expired")?></td>
+            <td class="DataTD"><?=_("Revoked")?></td>
+            <td class="DataTD"><?=_("Latest Expire")?></td>
+        </tr>
+        <!-- server certificates -->
+        <tr>
+            <td class="DataTD"><?=_("Server")?>:</td>
+    <?
+                $query = "
+                    select COUNT(*) as `total`,
+                        MAX(`domaincerts`.`expire`) as `maxexpire`
+                    from `domains` inner join `domaincerts`
+                        on `domains`.`id` = `domaincerts`.`domid`
+                    where `domains`.`memid` = '".intval($row['id'])."'
+                    ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $total = $drow['total'];
+
+                $maxexpire = "0000-00-00 00:00:00";
+                if ($drow['maxexpire']) {
+                    $maxexpire = $drow['maxexpire'];
+                }
+
+                if($total > 0) {
+                    $query = "
+                        select COUNT(*) as `valid`
+                        from `domains` inner join `domaincerts`
+                            on `domains`.`id` = `domaincerts`.`domid`
+                        where `domains`.`memid` = '".intval($row['id'])."'
+                            and `revoked` = '0000-00-00 00:00:00'
+                            and `expire` > NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $valid = $drow['valid'];
+
+                    $query = "
+                        select COUNT(*) as `expired`
+                        from `domains` inner join `domaincerts`
+                            on `domains`.`id` = `domaincerts`.`domid`
+                        where `domains`.`memid` = '".intval($row['id'])."'
+                            and `expire` <= NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $expired = $drow['expired'];
+
+                    $query = "
+                        select COUNT(*) as `revoked`
+                        from `domains` inner join `domaincerts`
+                            on `domains`.`id` = `domaincerts`.`domid`
+                        where `domains`.`memid` = '".intval($row['id'])."'
+                            and `revoked` != '0000-00-00 00:00:00'
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $revoked = $drow['revoked'];
+    ?>
+            <td class="DataTD"><?=intval($total)?></td>
+            <td class="DataTD"><?=intval($valid)?></td>
+            <td class="DataTD"><?=intval($expired)?></td>
+            <td class="DataTD"><?=intval($revoked)?></td>
+            <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
+    <?
+                } else { // $total > 0
+    ?>
+            <td colspan="5" class="DataTD"><?=_("None")?></td>
+    <?
+                }
+    ?>
+        </tr>
+        <!-- client certificates -->
+        <tr>
+            <td class="DataTD"><?=_("Client")?>:</td>
+    <?
+                $query = "
+                    select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
+                    from `emailcerts`
+                    where `memid` = '".intval($row['id'])."'
+                    ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $total = $drow['total'];
+
+                $maxexpire = "0000-00-00 00:00:00";
+                if ($drow['maxexpire']) {
+                    $maxexpire = $drow['maxexpire'];
+                }
+
+                if($total > 0) {
+                    $query = "
+                        select COUNT(*) as `valid`
+                        from `emailcerts`
+                        where `memid` = '".intval($row['id'])."'
+                            and `revoked` = '0000-00-00 00:00:00'
+                            and `expire` > NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $valid = $drow['valid'];
+
+                    $query = "
+                        select COUNT(*) as `expired`
+                        from `emailcerts`
+                        where `memid` = '".intval($row['id'])."'
+                            and `expire` <= NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $expired = $drow['expired'];
+
+                    $query = "
+                        select COUNT(*) as `revoked`
+                        from `emailcerts`
+                        where `memid` = '".intval($row['id'])."'
+                            and `revoked` != '0000-00-00 00:00:00'
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $revoked = $drow['revoked'];
+    ?>
+            <td class="DataTD"><?=intval($total)?></td>
+            <td class="DataTD"><?=intval($valid)?></td>
+            <td class="DataTD"><?=intval($expired)?></td>
+            <td class="DataTD"><?=intval($revoked)?></td>
+            <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
+    <?
+                } else { // $total > 0
+    ?>
+            <td colspan="5" class="DataTD"><?=_("None")?></td>
+    <?
+                }
+    ?>
+        </tr>
+        <!-- gpg certificates -->
+        <tr>
+            <td class="DataTD"><?=_("GPG")?>:</td>
+    <?
+                $query = "
+                    select COUNT(*) as `total`, MAX(`expire`) as `maxexpire`
+                    from `gpg`
+                    where `memid` = '".intval($row['id'])."'
+                    ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $total = $drow['total'];
+
+                $maxexpire = "0000-00-00 00:00:00";
+                if ($drow['maxexpire']) {
+                    $maxexpire = $drow['maxexpire'];
+                }
+
+                if($total > 0) {
+                    $query = "
+                        select COUNT(*) as `valid`
+                        from `gpg`
+                        where `memid` = '".intval($row['id'])."'
+                            and `expire` > NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $valid = $drow['valid'];
+
+                    $query = "
+                        select COUNT(*) as `expired`
+                        from `gpg`
+                        where `memid` = '".intval($row['id'])."'
+                            and `expire` <= NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $expired = $drow['expired'];
+    ?>
+            <td class="DataTD"><?=intval($total)?></td>
+            <td class="DataTD"><?=intval($valid)?></td>
+            <td class="DataTD"><?=intval($expired)?></td>
+            <td class="DataTD"></td>
+            <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
+    <?
+                } else { // $total > 0
+    ?>
+            <td colspan="5" class="DataTD"><?=_("None")?></td>
+    <?
+                }
+    ?>
+        </tr>
+        <!-- org server certificates -->
+        <tr>
+            <td class="DataTD"><a href="account.php?id=58&amp;userid=<?=intval($row['id'])?>"><?=_("Org Server")?></a>:</td>
+    <?
+                $query = "
+                    select COUNT(*) as `total`,
+                        MAX(`orgcerts`.`expire`) as `maxexpire`
+                    from `orgdomaincerts` as `orgcerts` inner join `org`
+                        on `orgcerts`.`orgid` = `org`.`orgid`
+                    where `org`.`memid` = '".intval($row['id'])."'
+                    ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $total = $drow['total'];
+
+                $maxexpire = "0000-00-00 00:00:00";
+                if ($drow['maxexpire']) {
+                    $maxexpire = $drow['maxexpire'];
+                }
+
+                if($total > 0) {
+                    $query = "
+                        select COUNT(*) as `valid`
+                        from `orgdomaincerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
+                            and `orgcerts`.`expire` > NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $valid = $drow['valid'];
+
+                    $query = "
+                        select COUNT(*) as `expired`
+                        from `orgdomaincerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`expire` <= NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $expired = $drow['expired'];
+
+                    $query = "
+                        select COUNT(*) as `revoked`
+                        from `orgdomaincerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`revoked` != '0000-00-00 00:00:00'
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $revoked = $drow['revoked'];
+    ?>
+            <td class="DataTD"><?=intval($total)?></td>
+            <td class="DataTD"><?=intval($valid)?></td>
+            <td class="DataTD"><?=intval($expired)?></td>
+            <td class="DataTD"><?=intval($revoked)?></td>
+            <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
+    <?
+                } else { // $total > 0
+    ?>
+            <td colspan="5" class="DataTD"><?=_("None")?></td>
+    <?
+                }
+    ?>
+        </tr>
+        <!-- org client certificates -->
+        <tr>
+            <td class="DataTD"><?=_("Org Client")?>:</td>
+    <?
+                $query = "
+                    select COUNT(*) as `total`,
+                        MAX(`orgcerts`.`expire`) as `maxexpire`
+                    from `orgemailcerts` as `orgcerts` inner join `org`
+                        on `orgcerts`.`orgid` = `org`.`orgid`
+                    where `org`.`memid` = '".intval($row['id'])."'
+                    ";
+                $dres = mysql_query($query);
+                $drow = mysql_fetch_assoc($dres);
+                $total = $drow['total'];
+
+                $maxexpire = "0000-00-00 00:00:00";
+                if ($drow['maxexpire']) {
+                    $maxexpire = $drow['maxexpire'];
+                }
+
+                if($total > 0) {
+                    $query = "
+                        select COUNT(*) as `valid`
+                        from `orgemailcerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`revoked` = '0000-00-00 00:00:00'
+                            and `orgcerts`.`expire` > NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $valid = $drow['valid'];
+
+                    $query = "
+                        select COUNT(*) as `expired`
+                        from `orgemailcerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`expire` <= NOW()
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $expired = $drow['expired'];
+
+                    $query = "
+                        select COUNT(*) as `revoked`
+                        from `orgemailcerts` as `orgcerts` inner join `org`
+                            on `orgcerts`.`orgid` = `org`.`orgid`
+                        where `org`.`memid` = '".intval($row['id'])."'
+                            and `orgcerts`.`revoked` != '0000-00-00 00:00:00'
+                        ";
+                    $dres = mysql_query($query);
+                    $drow = mysql_fetch_assoc($dres);
+                    $revoked = $drow['revoked'];
+    ?>
+            <td class="DataTD"><?=intval($total)?></td>
+            <td class="DataTD"><?=intval($valid)?></td>
+            <td class="DataTD"><?=intval($expired)?></td>
+            <td class="DataTD"><?=intval($revoked)?></td>
+            <td class="DataTD"><?=($maxexpire != "0000-00-00 00:00:00")?substr($maxexpire, 0, 10) : _("Pending")?></td>
+    <?
+                } else { // $total > 0
+    ?>
+            <td colspan="5" class="DataTD"><?=_("None")?></td>
+    <?
+                }
+    ?>
+        </tr>
+        <tr>
+            <td colspan="6" class="title">
+                <form method="post" action="account.php" onSubmit="if(!confirm('<?=_("Are you sure you want to revoke all private certificates?")?>')) return false;">
+                    <input type="hidden" name="action" value="revokecert">
+                    <input type="hidden" name="oldid" value="43">
+                    <input type="hidden" name="userid" value="<?=intval($userid)?>">
+                    <input type="submit" value="<?=_('revoke certificates')?>">
+                    <input type="hidden" name="ticketno" value="<?=sanitizeHTML($ticketno)?>"/>
+                </form>
+            </td>
+        </tr>
+    </table>
+    <br />
+    <? // list assurances ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td class="DataTD">
+                <a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;shownotary=assuredto&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Assurances the user got")?></a>
+                (<a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;shownotary=assuredto15&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("New calculation")?></a>)
+            </td>
+        </tr>
+        <tr>
+            <td class="DataTD">
+                <a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;shownotary=assuredby&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("Show Assurances the user gave")?></a>
+                (<a href="account.php?id=43&amp;userid=<?=intval($row['id'])?>&amp;shownotary=assuredby15&amp;ticketno=<?=sanitizeHTML($ticketno)?>"><?=_("New calculation")?></a>)
+            </td>
+        </tr>
+    </table>
+    <?
+    //  if(array_key_exists('assuredto',$_GET) && $_GET['assuredto'] == "yes") {
+
+
+    function showassuredto($ticketno)
+    {
+    ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="8" class="title"><?=_("Assurance Points")?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><b><?=_("ID")?></b></td>
+            <td class="DataTD"><b><?=_("Date")?></b></td>
+            <td class="DataTD"><b><?=_("Who")?></b></td>
+            <td class="DataTD"><b><?=_("Email")?></b></td>
+            <td class="DataTD"><b><?=_("Points")?></b></td>
+            <td class="DataTD"><b><?=_("Location")?></b></td>
+            <td class="DataTD"><b><?=_("Method")?></b></td>
+            <td class="DataTD"><b><?=_("Revoke")?></b></td>
+        </tr>
+    <?
+        $query = "select * from `notary` where `to`='".intval($_GET['userid'])."'  and `deleted` = 0";
+        $dres = mysql_query($query);
+        $points = 0;
+        while($drow = mysql_fetch_assoc($dres)) {
+            $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($drow['from'])."'"));
+            $points += $drow['points'];
+    ?>
+        <tr>
+            <td class="DataTD"><?=$drow['id']?></td>
+            <td class="DataTD"><?=sanitizeHTML($drow['date'])?></td>
+            <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['fname'])." ".sanitizeHTML($fromuser['lname'])?></td>
+            <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['from'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
+            <td class="DataTD"><?=intval($drow['points'])?></td>
+            <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
+            <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
+            <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>&amp;assurance=<?=intval($drow['id'])?>&amp;csrf=<?=make_csrf('admdelassurance')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),intval($drow['id']))?>');"><?=_("Revoke")?></a></td>
+        </tr>
+    <?
+        }
+    ?>
+        <tr>
+            <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
+            <td class="DataTD"><?=intval($points)?></td>
+            <td class="DataTD" colspan="3">&nbsp;</td>
+        </tr>
+    </table>
+    <?
+    }
+
+    function showassuredby($ticketno)
+    {
+    ?>
+    <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+        <tr>
+            <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td>
+        </tr>
+        <tr>
+            <td class="DataTD"><b><?=_("ID")?></b></td>
+            <td class="DataTD"><b><?=_("Date")?></b></td>
+            <td class="DataTD"><b><?=_("Who")?></b></td>
+            <td class="DataTD"><b><?=_("Email")?></b></td>
+            <td class="DataTD"><b><?=_("Points")?></b></td>
+            <td class="DataTD"><b><?=_("Location")?></b></td>
+            <td class="DataTD"><b><?=_("Method")?></b></td>
+            <td class="DataTD"><b><?=_("Revoke")?></b></td>
+        </tr>
+    <?
+        $query = "select * from `notary` where `from`='".intval($_GET['userid'])."' and `deleted` = 0";
+        $dres = mysql_query($query);
+        $points = 0;
+        while($drow = mysql_fetch_assoc($dres)) {
+            $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($drow['to'])."'"));
+            $points += intval($drow['points']);
+    ?>
+        <tr>
+            <td class="DataTD"><?=intval($drow['id'])?></td>
+            <td class="DataTD"><?=$drow['date']?></td>
+            <td class="DataTD"><a href="wot.php?id=9&userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['fname']." ".$fromuser['lname'])?></td>
+            <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
+            <td class="DataTD"><?=intval($drow['points'])?></td>
+            <td class="DataTD"><?=sanitizeHTML($drow['location'])?></td>
+            <td class="DataTD"><?=sanitizeHTML($drow['method'])?></td>
+            <td class="DataTD"><a href="account.php?id=43&userid=<?=intval($drow['from'])?>&assurance=<?=intval($drow['id'])?>&amp;csrf=<?=make_csrf('admdelassurance')?>&amp;ticketno=<?=sanitizeHTML($ticketno)?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),intval($drow['id']))?>');"><?=_("Revoke")?></a></td>
+        </tr>
+    <?
+        }
+    ?>
+        <tr>
+            <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
+            <td class="DataTD"><?=intval($points)?></td>
+            <td class="DataTD" colspan="3">&nbsp;</td>
+        </tr>
+    </table>
+    <?} ?>
+<br/><br/>
 <?
-function showassuredby()
-{
-?>
-<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr>
-    <td colspan="8" class="title"><?=_("Assurance Points The User Issued")?></td>
-  </tr>
-  <tr>
-    <td class="DataTD"><b><?=_("ID")?></b></td>
-    <td class="DataTD"><b><?=_("Date")?></b></td>
-    <td class="DataTD"><b><?=_("Who")?></b></td>
-    <td class="DataTD"><b><?=_("Email")?></b></td>
-    <td class="DataTD"><b><?=_("Points")?></b></td>
-    <td class="DataTD"><b><?=_("Location")?></b></td>
-    <td class="DataTD"><b><?=_("Method")?></b></td>
-    <td class="DataTD"><b><?=_("Revoke")?></b></td>
-  </tr>
-<?
-  $query = "select * from `notary` where `from`='".intval($_GET['userid'])."'";
-  $dres = mysql_query($query);
-  $points = 0;
-  while($drow = mysql_fetch_assoc($dres))
-  {
-    $fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".$drow['to']."'"));
-    $points += $drow['points'];
-?>
-  <tr>
-    <td class="DataTD"><?=$drow['id']?></td>
-    <td class="DataTD"><?=$drow['date']?></td>
-    <td class="DataTD"><a href="wot.php?id=9&userid=<?=$drow['to']?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td>
-    <td class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($drow['to'])?>"><?=sanitizeHTML($fromuser['email'])?></a></td>
-    <td class="DataTD"><?=$drow['points']?></td>
-    <td class="DataTD"><?=$drow['location']?></td>
-    <td class="DataTD"><?=$drow['method']?></td>
-    <td class="DataTD"><a href="account.php?id=43&userid=<?=$drow['from']?>&assurance=<?=$drow['id']?>&amp;csrf=<?=make_csrf('admdelassurance')?>" onclick="return confirm('<?=sprintf(_("Are you sure you want to revoke the assurance with ID &quot;%s&quot;?"),$drow['id'])?>');"><?=_("Revoke")?></a></td>
-  </tr>
-<? } ?>
-  <tr>
-    <td class="DataTD" colspan="4"><b><?=_("Total Points")?>:</b></td>
-    <td class="DataTD"><?=$points?></td>
-    <td class="DataTD" colspan="3">&nbsp;</td>
-  </tr>
-</table>
-<? } ?>
-<br><br>
-<? } }
+} }
 
 if(isset($_GET['shownotary'])) {
     switch($_GET['shownotary']) {
         case 'assuredto':
-            showassuredto();
+            showassuredto($ticketno);
             break;
         case 'assuredby':
-            showassuredby();
+            showassuredby($ticketno);
             break;
         case 'assuredto15':
-            output_received_assurances(intval($_GET['userid']),1);
+            output_received_assurances(intval($_GET['userid']),1,$ticketno);
             break;
         case 'assuredby15':
-            output_given_assurances(intval($_GET['userid']),1);
+            output_given_assurances(intval($_GET['userid']),1, $ticketno);
             break;
     }
 }
diff --git a/pages/account/44.php b/pages/account/44.php
index fd34612..718f0e0 100644
--- a/pages/account/44.php
+++ b/pages/account/44.php
@@ -14,8 +14,28 @@
     You should have received a copy of the GNU General Public License
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
-*/ ?>
-<? if(array_key_exists('error',$_SESSION['_config']) && $_SESSION['_config']['error'] != "") { ?><div color="orange">ERROR: <?=$_SESSION['_config']['error']?></div><? unset($_SESSION['_config']['error']); } ?>
+*/
+
+if(array_key_exists('error',$_SESSION['_config']) && $_SESSION['_config']['error'] != "") {
+    ?>
+    <div style="color: orange;">ERROR: <?=$_SESSION['_config']['error']?></div>
+    <?
+    unset($_SESSION['_config']['error']);
+}
+
+$ticketno = "";
+if (array_key_exists('ticketno', $_SESSION)) {
+    $ticketno = $_SESSION['ticketno'];
+}
+
+if (!valid_ticket_number($ticketno)) {
+    printf(_("I'm sorry, you did not enter a ticket number! %s You cannot reset the password."), '<br/>');
+    echo '<br/><a href="account.php?id=43&amp;userid='.intval($_REQUEST['userid']).'">'._('Back to previous page.').'</a>';
+    showfooter();
+    exit;
+ }
+?>
+
 <form method="post" action="account.php">
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
   <tr>
@@ -34,5 +54,6 @@
   </tr>
 </table>
 <input type="hidden" name="userid" value="<?=intval($_REQUEST['userid'])?>">
-<input type="hidden" name="oldid" value="<?=$id?>">
+<input type="hidden" name="oldid" value="<?=intval($id)?>">
+<input type="hidden" name="ticketno" value="<?=sanitizeHTML($ticketno)?>"/>
 </form>
diff --git a/pages/account/5.php b/pages/account/5.php
index 934ca0c..efed0ab 100644
--- a/pages/account/5.php
+++ b/pages/account/5.php
@@ -19,7 +19,7 @@
 <form method="post" action="account.php">
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
   <tr>
-    <td colspan="10" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=5&amp;viewall=<?=!$viewall?>"><?=$viewall?_("Hide old certificates"):_("View all certificates")?></a></td>
+    <td colspan="10" class="title"><?=_("Client Certificates")?> - <a href="account.php?id=5&amp;viewall=<?=intval(!$viewall)?>"><?=$viewall?_("Hide old certificates"):_("View all certificates")?></a></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Renew/Revoke/Delete")?></td>
@@ -34,23 +34,24 @@
 	$query = "select UNIX_TIMESTAMP(`emailcerts`.`created`) as `created`,
 			UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
 			UNIX_TIMESTAMP(`emailcerts`.`expire`) as `expired`,
-			`emailcerts`.`expire` as `expires`,
+			`emailcerts`.`expire`,
 			`emailcerts`.`revoked` as `revoke`,
 			UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`,
+			if (`emailcerts`.`expire`=0,CURRENT_TIMESTAMP(),`emailcerts`.`modified`) as `modified` ,
 			`emailcerts`.`id`,
 			`emailcerts`.`CN`,
 			`emailcerts`.`serial`,
 			`emailcerts`.`disablelogin` as `disablelogin`,
 			`emailcerts`.`description`
 			from `emailcerts`
-			where `emailcerts`.`memid`='".$_SESSION['profile']['id']."'
+			where `emailcerts`.`memid`='".intval($_SESSION['profile']['id'])."'
 			";
 	if($viewall != 1)
 		$query .= " AND `revoked`=0 AND `renewed`=0 ";
 	$query .= " GROUP BY `emailcerts`.`id` ";
 	if($viewall != 1)
-                $query .= " HAVING `timeleft` > 0 ";
-	$query .= " ORDER BY `emailcerts`.`modified` desc";
+		$query .= " HAVING `timeleft` > 0 or `expire` = 0 ";
+	$query .= " ORDER BY `modified` desc";
 // echo $query."<br>\n";
 	$res = mysql_query($query);
 	if(mysql_num_rows($res) <= 0)
@@ -75,32 +76,32 @@
 ?>
   <tr>
 <? if($verified != _("Pending") && $verified != _("Revoked")) { ?>
-    <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=$row['id']?>"></td>
+    <td class="DataTD"><input type="checkbox" name="revokeid[]" value="<?=intval($row['id'])?>"></td>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><a href="account.php?id=6&amp;cert=<?=$row['id']?>"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></a></td>
+    <td class="DataTD"><a href="account.php?id=6&amp;cert=<?=intval($row['id'])?>"><?=(trim($row['CN'])=="" ? _("empty") : sanitizeHTML($row['CN']))?></a></td>
 <? } else if($verified != _("Revoked")) { ?>
-    <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=$row['id']?>"></td>
+    <td class="DataTD"><input type="checkbox" name="delid[]" value="<?=intval($row['id'])?>"></td>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
+    <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : sanitizeHTML($row['CN']))?></td>
 <? } else { ?>
     <td class="DataTD">&nbsp;</td>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
+    <td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : sanitizeHTML($row['CN']))?></td>
 <? } ?>
-    <td class="DataTD"><?=$row['serial']?></td>
-    <td class="DataTD"><?=$row['revoke']?></td>
-    <td class="DataTD"><?=$row['expires']?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['serial'])?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['revoke'])?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['expire'])?></td>
     <td class="DataTD">
-      <input type="checkbox" name="disablelogin_<?=$row['id']?>" value="1" <?=$row['disablelogin']?"":'checked="checked"'?>/>
-      <input type="hidden" name="cert_<?=$row['id']?>" value="1" />
+      <input type="checkbox" name="disablelogin_<?=intval($row['id'])?>" value="1" <?=$row['disablelogin']?"":'checked="checked"'?>/>
+      <input type="hidden" name="cert_<?=intval($row['id'])?>" value="1" />
     </td>
-    <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
-    <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
+    <td class="DataTD"><input name="comment_<?=intval($row['id'])?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
+    <td class="DataTD"><input type="checkbox" name="check_comment_<?=intval($row['id'])?>" /></td>
   </tr>
     <? } ?>
   <tr>
     <td class="DataTD" colspan="9">
-      <a href="account.php?id=5&amp;viewall=<?=!$viewall?>"><b><?=$viewall?_("Hide old certificates"):_("View all certificates")?></b></a>
+      <a href="account.php?id=5&amp;viewall=<?=intval(!$viewall)?>"><b><?=$viewall?_("Hide old certificates"):_("View all certificates")?></b></a>
     </td>
   </tr>
 
@@ -119,7 +120,7 @@
   </tr>
 <? } ?>
 </table>
-<input type="hidden" name="oldid" value="<?=$id?>" />
+<input type="hidden" name="oldid" value="<?=intval($id)?>" />
 <input type="hidden" name="csrf" value="<?=make_csrf('clicerchange')?>" />
 </form>
 <p><?=_("From here you can delete pending requests, or revoke valid certificates.")?></p>
diff --git a/pages/account/52.php b/pages/account/52.php
index 77a3bae..6c00c26 100644
--- a/pages/account/52.php
+++ b/pages/account/52.php
@@ -14,45 +14,51 @@
     You should have received a copy of the GNU General Public License
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
-*/ ?>
-<? if($_SESSION['profile']['tverify'] <= 0) { echo _("You don't have access to this area."); } else { ?>
-<?
+*/
+
+if($_SESSION['profile']['tverify'] <= 0) {
+    echo _("You don't have access to this area.");
+} else {
 	$uid = intval($_GET['uid']);
-	$query = "select * from `tverify` where `id`='$uid' and `modified`=0";
+	$query = "select * from `tverify` where `id`='".intval($uid)."' and `modified`=0";
 	$res = mysql_query($query);
-	if(mysql_num_rows($res) > 0)
-	{
+	if(mysql_num_rows($res) > 0) {
 		$row = mysql_fetch_assoc($res);
 		$memid = intval($row['memid']);
 
-		$query2 = "select * from `tverify-vote` where `tverify`='$uid' and `memid`='".intval($_SESSION['profile']['id'])."'";
-                $rc2 = mysql_num_rows(mysql_query($query2));
-		if($rc2 > 0)
-		{
+		$query2 = "select * from `tverify-vote` where `tverify`='".intval($uid)."' and `memid`='".intval($_SESSION['profile']['id'])."'";
+		$rc2 = mysql_num_rows(mysql_query($query2));
+		if($rc2 > 0) {
 			showheader(_("My CAcert.org Account!"));
 			echo _("You have already voted on this request.");
 			showfooter();
 			exit;
 		}
 
-		$query = "select sum(`points`) as `points` from `notary` where `to`='$memid'";
+		$query = "select sum(`points`) as `points` from `notary` where `to`='".intval($memid)."' and `deleted` = 0";
 		$notary = mysql_fetch_assoc(mysql_query($query));
-		$query = "select * from `users` where `id`='$memid'";
+		$query = "select * from `users` where `id`='".intval($memid)."'";
 		$user = mysql_fetch_assoc(mysql_query($query));
 		$tobe = 50 - $notary['points'];
-		if($row['URL'] != '' && $row['photoid'] != '')
+		if($row['URL'] != '' && $row['photoid'] != '') {
 			$tobe = 150 - $notary['points'];
-		else if($row['URL'] != '')
+		} else if($row['URL'] != '') {
 			$tobe = 90 - $notary['points'];
-		if(intval($tobe) <= 0)
+		}
+		if(intval($tobe) <= 0) {
 			$tobe = 0;
+		}
 ?>
 <?=_("Request Details")?>:<br>
-<?=_("Name on file")?>: <?=$user['fname']." ".$user['mname']." ".$user['lname']." ".$user['suffix']?><br>
-<?=_("Primary email address")?>: <?=$user['email']." (".$user['id'].")"?><br>
-<?=_("Certificate Subject")?>: <?=$row['CN']?><br>
-<? if($row['URL'] != '') { ?><?=_("Notary URL")?>: <a href="<?=$row['URL']?>"><?=$row['URL']?></a><br><? } ?>
-<? if($row['photoid'] != '') { ?><?=_("Photo ID URL")?>: <a href="/account.php?id=51&amp;photoid=<?=intval($row['id'])?>"><?=_("Here")?></a><br><? } ?>
+<?=_("Name on file")?>: <?=sanitizeHTML($user['fname']." ".$user['mname']." ".$user['lname']." ".$user['suffix'])?><br>
+<?=_("Primary email address")?>: <?=sanitizeHTML($user['email'])." (".intval($user['id']).")"?><br>
+<?=_("Certificate Subject")?>: <?=sanitizeHTML($row['CN'])?><br>
+<?		if($row['URL'] != '') { ?>
+<?=_("Notary URL")?>: <a href="<?=$row['URL']?>"><?=$row['URL']?></a><br>
+<?		} ?>
+<?		if($row['photoid'] != '') { ?>
+<?=_("Photo ID URL")?>: <a href="/account.php?id=51&amp;photoid=<?=intval($row['id'])?>"><?=_("Here")?></a><br>
+<?		} ?>
 <?=_("Current Points")?>: <?=intval($notary['points'])?><br>
 <?=_("Potential Points")?>: <?=intval($tobe)?><br>
 <?=_("Date of Birth")?>: <?=$user['dob']?> (YYYY-MM-DD)<br>
@@ -63,40 +69,37 @@
 <input type="submit" name="agree" value="<?=_("I agree with this Application")?>">
 <input type="submit" name="disagree" value="<?=_("I don't agree with this Application")?>">
 <input type="hidden" name="oldid" value="<?=intval($_GET['id'])?>">
-<input type="hidden" name="uid" value="<?=$uid?>">
+<input type="hidden" name="uid" value="<?=intval($uid)?>">
 </form>
-<? } else { 
-	$query = "select * from `tverify` where `id`='$uid' and `modified`=1";
-	$res = mysql_query($query);
-	if(mysql_num_rows($res) > 0)
-	{
-		echo _("This UID has already been voted on.")."<br/>";
+<?
 	} else {
-		if($uid) echo _("Unable to locate a valid request for that UID.")."<br/>";
-	}
-	
-	// Search for open requests:
-	$query = "select * from `tverify` where `modified`=0";
-	$res = mysql_query($query);
-	if(mysql_num_rows($res) > 0)
-	{
-		echo "<br/>"._("The following requests are still open:")."<br/><ul>";
-		while($row = mysql_fetch_assoc($res))
-		{
-			$uid=intval($row['id']);	
-			$query3 = "select * from `tverify-vote` where `tverify`='$uid' and `memid`='".intval($_SESSION['profile']['id'])."'";
-                	$rc3 = mysql_num_rows(mysql_query($query3));
-			if($rc3 <= 0)
-		        {
-				echo "<li><a href='account.php?id=52&amp;uid=".intval($row['id'])."'>".intval($row['id'])."</a></li>\n";
+		$query = "select * from `tverify` where `id`='".intval($uid)."' and `modified`=1";
+		$res = mysql_query($query);
+		if(mysql_num_rows($res) > 0) {
+			echo _("This UID has already been voted on.")."<br/>";
+		} else {
+			if($uid) echo _("Unable to locate a valid request for that UID.")."<br/>";
+		}
+
+		// Search for open requests:
+		$query = "select * from `tverify` where `modified`=0";
+		$res = mysql_query($query);
+		if(mysql_num_rows($res) > 0) {
+			echo "<br/>"._("The following requests are still open:")."<br/><ul>";
+			while($row = mysql_fetch_assoc($res)) {
+				$uid=intval($row['id']);
+				$query3 = "select * from `tverify-vote` where `tverify`='".intval($uid)."' and `memid`='".intval($_SESSION['profile']['id'])."'";
+				$rc3 = mysql_num_rows(mysql_query($query3));
+				if($rc3 <= 0)
+				{
+					echo "<li><a href='account.php?id=52&amp;uid=".intval($row['id'])."'>".intval($row['id'])."</a></li>\n";
+				}
 			}
-		}	
-		echo "</ul>\n<br>\n";
-	}
-	else
-	{
-		echo "<br/>"._("There are no pending requests where you haven't voted yet.");
+			echo "</ul>\n<br>\n";
+		} else {
+			echo "<br/>"._("There are no pending requests where you haven't voted yet.");
+		}
 	}
+}
 
-
- } } ?>
+?>
diff --git a/pages/account/55.php b/pages/account/55.php
index ec401a0..6793a71 100644
--- a/pages/account/55.php
+++ b/pages/account/55.php
@@ -23,7 +23,7 @@
   <tr>

     <td colspan="5" class="title"><?=_("Your passed Tests")?></td>

   </tr>

-  <tr> 

+  <tr>

     <td class="DataTD"><?=_("The list of tests you did pass at").' <a href="https://cats.cacert.org/">https://cats.cacert.org/</a>'?></td>

   </tr>

 </table>

@@ -34,7 +34,7 @@
     $res = mysql_query($query);

     if(mysql_num_rows($res) <= 0)

     {

-      echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");

+      echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");

     } else {

       $row = mysql_fetch_assoc($res);

     }

@@ -58,11 +58,11 @@
 <?

         $query = "SELECT `CP`.`pass_date`, `CT`.`type_text`, `CV`.`test_text` ".

                  " FROM `cats_passed` AS CP, `cats_variant` AS CV, `cats_type` AS CT ".

-                 " WHERE `CP`.`variant_id`=`CV`.`id` AND `CV`.`type_id`=`CT`.`id` AND `CP`.`user_id` ='".(int)$user_id."'".

+                 " WHERE `CP`.`variant_id`=`CV`.`id` AND `CV`.`type_id`=`CT`.`id` AND `CP`.`user_id` ='".intval($user_id)."'".

                  " ORDER BY `CP`.`pass_date`";

-                 

+

         $res = mysql_query($query);

-        

+

         $HaveTest=0;

         while($row = mysql_fetch_array($res, MYSQL_NUM))

         {

@@ -71,23 +71,24 @@
           }

 ?>

   <tr>

-    <td class="DataTD"><?=$row[0]?></td>

-    <td class="DataTD"><?=$row[1]?></td>

-    <td class="DataTD"><?=$row[2]?></td>

+    <td class="DataTD"><?=sanitizeHTML($row[0])?></td>

+    <td class="DataTD"><?=sanitizeHTML($row[1])?></td>

+    <td class="DataTD"><?=sanitizeHTML($row[2])?></td>

   </tr>

-<?      } 

+<?      }

 ?>

 </table>

 <br>

 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">

-  <tr> 

+  <tr>

 <?

       if ($_SESSION['profile']['admin'] == 1 && array_key_exists('userid',$_REQUEST) && intval($_REQUEST['userid']) > 0) {

 ?>

-    <tr><td colspan="3" class="DataTD"><a href="account.php?id=43&amp;userid=<?=$user_id ?>">back</a></td></tr>

-<?    } else {

+    <tr><td colspan="3" class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($user_id)?>">back</a></td></tr>

+<?

+    } else {

         $query = 'SELECT `u`.id, `u`.`assurer`, SUM(`points`) FROM `users` AS `u`, `notary` AS `n` '.

-                 '  WHERE `u`.`id` = \''.(int)intval($_SESSION['profile']['id']).'\' AND `n`.`to` = `u`.`id` AND `expire` < now() '.

+                 '  WHERE `u`.`id` = \''.(int)intval($_SESSION['profile']['id']).'\' AND `n`.`to` = `u`.`id` AND `expire` < now() and  and `n`.`deleted` = 0'.
                  '  GROUP BY `u`.id, `u`.`assurer`';

         $res = mysql_query($query);

         if (!$res) {

diff --git a/pages/account/57.php b/pages/account/57.php
index 76eee27..9db7ccf 100644
--- a/pages/account/57.php
+++ b/pages/account/57.php
@@ -17,18 +17,18 @@
 */ ?>
 <?
   include_once($_SESSION['_config']['filepath'].'/includes/notary.inc.php');
-  
+
   if ($_SESSION['profile']['admin'] != 1 || !array_key_exists('userid',$_REQUEST) || intval($_REQUEST['userid']) < 1) {
 
   echo _('You do not have access to this page');
 
-  } else {  
+  } else {
     $user_id = intval($_REQUEST['userid']);
     $query = "select * from `users` where `id`='$user_id' and `users`.`deleted`=0";
     $res = mysql_query($query);
     if(mysql_num_rows($res) <= 0)
     {
-      echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");
+      echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");
     } else {
       $row = mysql_fetch_assoc($res);
 ?>
@@ -38,7 +38,7 @@
   </tr>
 </table>
 
-  
+
 <br>
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
   <tr>
@@ -48,21 +48,21 @@
     <td class="DataTD"><b><?=_('Type')?></b></td>
   </tr>
 <?
-  $data=get_first_user_agreement($user_id,1);
+  $data=get_first_user_agreement($user_id, 'CCA', 1);
   if (!isset($data['active'])){
       $type='';
-    }else{
+  }else{
       $type=_('active');
-    }
+  }
 ?>
   <tr>
     <td class="DataTD"><?=_('First active CCA')?></td>
-    <td class="DataTD"><?=$data['date']?></td>
-    <td class="DataTD"><?=$data['method']?></td>
+    <td class="DataTD"><?=isset($data['date'])?$data['date']:''?></td>
+    <td class="DataTD"><?=isset($data['method'])?$data['method']:''?></td>
     <td class="DataTD"><?=$type?></td>
   </tr>
 <?
-  $data=get_first_user_agreement($user_id,0);
+  $data=get_first_user_agreement($user_id, 'CCA', 0);
   if (!isset($data['active'])){
       $type="";
     }else{
@@ -71,35 +71,35 @@
 ?>
   <tr>
     <td class="DataTD"><?=_('First passive CCA')?></td>
-    <td class="DataTD"><?=$data['date']?></td>
-    <td class="DataTD"><?=$data['method']?></td>
+    <td class="DataTD"><?=isset($data['date'])?$data['date']:''?></td>
+    <td class="DataTD"><?=isset($data['method'])?$data['method']:''?></td>
     <td class="DataTD"><?=$type?></td>
   </tr>
 <?
-  $data=get_last_user_agreement($user_id);
+  $data=get_last_user_agreement($user_id, 'CCA');
   if (!isset($data['active'])){
-      $type="";
-    }elseif($data['active']==1){
-      $type=_('active');
-    }else{
-      $type=_('passive');
-    }
+    $type="";
+  }elseif($data['active']==1){
+    $type=_('active');
+  }else{
+    $type=_('passive');
+  }
 ?>
   <tr>
     <td class="DataTD"><?=_('Last CCA')?></td>
-    <td class="DataTD"><?=$data['date']?></td>
-    <td class="DataTD"><?=$data['method']?></td>
+    <td class="DataTD"><?=isset($data['date'])?$data['date']:''?></td>
+    <td class="DataTD"><?=isset($data['method'])?$data['method']:''?></td>
     <td class="DataTD"><?=$type?></td>
   </tr>
 </table>
 <br>
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
-  <tr> 
+  <tr>
 <?
       if ($_SESSION['profile']['admin'] == 1 && array_key_exists('userid',$_REQUEST) && intval($_REQUEST['userid']) > 0) {
 ?>
-    <tr><td colspan="3" class="DataTD"><a href="account.php?id=43&amp;userid=<?=$user_id ?>">back</a></td></tr>
-<?    } 
+    <tr><td colspan="3" class="DataTD"><a href="account.php?id=43&amp;userid=<?=intval($user_id)?>">back</a></td></tr>
+<?    }
 ?>  </table>
 <?
   }
diff --git a/pages/account/58.php b/pages/account/58.php
index 1f6b1a0..af26b70 100644
--- a/pages/account/58.php
+++ b/pages/account/58.php
@@ -23,7 +23,7 @@ if ($_SESSION['profile']['admin'] != 1 || !array_key_exists('userid',$_REQUEST)
 	$query = "select `users`.`fname`, `users`.`mname`, `users`.`lname` from `users` where `id`='$user_id' and `users`.`deleted`=0";
 	$res = mysql_query($query);
 	if(mysql_num_rows($res) != 1){
-		echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");
+		echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");
 	} else {
 		if ($row = mysql_fetch_assoc($res)){
 			$username=sanitizeHTML($row['fname']).' '.sanitizeHTML($row['mname']).' '.sanitizeHTML($row['lname']);
@@ -54,7 +54,7 @@ if ($_SESSION['profile']['admin'] != 1 || !array_key_exists('userid',$_REQUEST)
 			}
 			?></table>
 <?		}else{
-				echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are a foot!");
+				echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");
 		}
 	}
 }
diff --git a/pages/account/59.php b/pages/account/59.php
new file mode 100644
index 0000000..1c73ae5
--- /dev/null
+++ b/pages/account/59.php
@@ -0,0 +1,385 @@
+<?/*
+       LibreSSL - CAcert web application
+       Copyright (C) 2004-2008    CAcert Inc.
+
+       This program is free software; you can redistribute it and/or modify
+       it under the terms of the GNU General Public License as published by
+       the Free Software Foundation; version 2 of the License.
+
+       This program is distributed in the hope that it will be useful,
+       but WITHOUT ANY WARRANTY; without even the implied warranty of
+       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.    See the
+       GNU General Public License for more details.
+
+       You should have received a copy of the GNU General Public License
+       along with this program; if not, write to the Free Software
+       Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA    02110-1301    USA
+*/
+include_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
+
+
+$userid = intval($_REQUEST['userid']);
+
+$res = get_user_data($userid);
+if (mysql_num_rows($res) <= 0)
+{
+    echo _("I'm sorry, the user you were looking for seems to have disappeared! Bad things are afoot!");
+    exit;
+}
+
+$user = mysql_fetch_assoc($res);
+
+$fname = $user['fname'];
+$mname = $user['mname'];
+$lname = $user['lname'];
+$suffix = $user['suffix'];
+$dob = $user['dob'];
+$username = $fname." ".$mname." ".$lname." ".$suffix;
+$email = $user['email'];
+$alerts =get_alerts($userid);
+
+$ticketno = "";
+if (array_key_exists('ticketno', $_SESSION)) {
+    $ticketno = $_SESSION['ticketno'];
+}
+
+$oldid = 0;
+if (array_key_exists('oldid', $_REQUEST)) {
+    $oldid = intval($_REQUEST['oldid']);
+}
+
+// Support Engineer access restrictions
+$support=0;
+if ($userid != $_SESSION['profile']['id']) {
+    // Check if support engineer
+    if (array_key_exists('admin', $_SESSION['profile']) &&
+        $_SESSION['profile']['admin'] != 0)
+    {
+        $support=$_SESSION['profile']['admin'];
+
+    } else {
+        echo _("You do not have access to this page.");
+        showfooter();
+        exit;
+    }
+
+    if (!valid_ticket_number($ticketno)) {
+        printf(_("I'm sorry, you did not enter a ticket number! %s Support is not allowed to view the account history without a ticket number."), '<br/>');
+        echo '<br/><a href="account.php?id=43&amp;userid='.intval($userid).'">'. _('Back to previous page.') .'</a>';
+        showfooter();
+        exit;
+    }
+
+    if (!write_se_log($userid, $_SESSION['profile']['id'], 'SE View account history', $ticketno)) {
+        echo _("Writing to the admin log failed. Can't continue.");
+        echo '<br/><a href="account.php?id=43&amp;userid='.intval($userid).'">'. _('Back to previous page.') .'</a>';
+        showfooter();
+        exit;
+    }
+}
+
+// Account details
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="2" class="title"><?printf(_('Account history of %s'),$username)?></td>
+    </tr>
+    <tr>
+        <td colspan="2" class="title"><?=_('User actions')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_('User name')?></td>
+        <td class="DataTD"><?=sanitizeHTML($username)?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_('Date of Birth')?></td>
+        <td class="DataTD"><?=sanitizeHTML($dob)?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Is Assurer")?>:</td>
+        <td class="DataTD"><?= ($user['assurer']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Blocked Assurer")?>:</td>
+        <td class="DataTD"><?= ($user['assurer_blocked']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Account Locking")?>:</td>
+        <td class="DataTD"><?= ($user['locked']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Code Signing")?>:</td>
+        <td class="DataTD"><?= ($user['codesign']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Org Assurer")?>:</td>
+        <td class="DataTD"><?= ($user['orgadmin']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("TTP Admin")?>:</td>
+        <td class="DataTD"><?= $user['ttpadmin']._(' - 0 = none, 1 = TTP Admin, 2 = TTP TOPUP admin')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Location Admin")?>:</td>
+        <td class="DataTD"><?= ($user['locadmin']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Admin")?>:</td>
+        <td class="DataTD"><?= ($user['admin']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Ad Admin")?>:</td>
+        <td class="DataTD"><?= $user['adadmin']._(' - 0 = none, 1 = submit, 2 = approve')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("General Announcements")?>:</td>
+        <td class="DataTD"><?= ($alerts['general']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+          <td class="DataTD"><?=_("Country Announcements")?>:</td>
+          <td class="DataTD"><?= ($alerts['country']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Regional Announcements")?>:</td>
+        <td class="DataTD"><?= ($alerts['regional']==0)? _('No'):_('Yes')?></td>
+    </tr>
+    <tr>
+        <td class="DataTD"><?=_("Within 200km Announcements")?>:</td>
+        <td class="DataTD"><?= ($alerts['radius']==0)? _('No'):_('Yes')?></td>
+    </tr>
+</table>
+<br/>
+<?
+
+// Email addresses
+$dres = get_email_addresses($userid,'',1);
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="3" class="title"><?=_('Email addresses')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_log_email_header();
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_log_email($drow,$email);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="3" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+<?
+
+// Domains
+$dres = get_domains($userid, 1);
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="3" class="title"><?=_('Domains')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_log_domains_header();
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+          output_log_domains($drow);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="3" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+
+<?
+// Trainings
+$dres = get_training_results($userid);
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="3" class="title"><?=_('Trainings')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_log_training_header();
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_log_training($drow);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="3" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+
+<?
+// User Agreements
+$dres = get_user_agreements($userid);
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="4" class="title"><?=_('User agreements')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_log_agreement_header();
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_log_agreement($drow);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="4" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+
+<?
+// Client Certificates
+$dres = get_client_certs($userid, 1);
+$colspan=8;
+if (1 == $support) {
+    $colspan=6;
+}
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="<?=$colspan?>" class="title"><?=_('Client certificates')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_client_cert_header($support);
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_client_cert($drow,$support);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="<?=$colspan?>" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+
+<?
+// Server Certificates
+$dres = get_server_certs($userid,1);
+$colspan = 7;
+if (1 == $support) {
+    $colspan = 5;
+}
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="<?=$colspan?>" class="title"><?=_('Server certificates')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_server_certs_header($support);
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_server_certs($drow,$support);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="<?=$colspan?>" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+</table>
+<br/>
+
+<?
+// GPG Certificates
+$dres = get_gpg_certs($userid,1);
+$colspan = 6;
+if (1 == $support) {
+    $colspan = 4;
+}
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="<?=$colspan?>" class="title"><?=_('GPG/PGP certificates')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_gpg_certs_header($support);
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+        output_gpg_certs($drow, $support);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="<?=$colspan?>" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}?>
+</table>
+<br/>
+
+<?
+
+output_given_assurances($userid, $support, $ticketno, 1);
+?><br/><?
+
+output_received_assurances($userid, $support, $ticketno, 1);
+?><br/><?
+
+$dres = get_se_log($userid);
+$colspan = 2;
+if (1 == $support) {
+    $colspan = 4;
+}
+?>
+<table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
+    <tr>
+        <td colspan="<?=$colspan?>" class="title"><?=_('Admin log')?></td>
+    </tr>
+<?
+if (mysql_num_rows($dres) > 0) {
+    output_log_se_header($support);
+    while ($drow = mysql_fetch_assoc($dres))
+    {
+       output_log_se($drow,$support);
+    }
+} else {
+    ?>
+    <tr>
+        <td colspan="<?=$colspan?>" ><?=_('no entry available')?></td>
+    </tr>
+    <?
+}
+?>
+<tr>
+    <td colspan="<?=$colspan?>" >
+        <a href="account.php?id=<?=$oldid?intval($oldid):($support?43:13)?>&amp;userid=<?=intval($userid)?>"><?= _('Back to previous page.')?></a>
+    </td>
+</tr>
+
+</table>
diff --git a/pages/account/6.php b/pages/account/6.php
index 8455499..305fccb 100644
--- a/pages/account/6.php
+++ b/pages/account/6.php
@@ -25,7 +25,7 @@ if(array_key_exists('cert',$_REQUEST)) {
 $query = "select UNIX_TIMESTAMP(`emailcerts`.`created`) as `created`,
 			UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP() as `timeleft`,
 			UNIX_TIMESTAMP(`emailcerts`.`expire`) as `expired`,
-			`emailcerts`.`expire` as `expires`,
+			`emailcerts`.`expire`,
 			`emailcerts`.`revoked` as `revoke`,
 			UNIX_TIMESTAMP(`emailcerts`.`revoked`) as `revoked`,
 			`emailcerts`.`id`,
@@ -137,11 +137,11 @@ if (array_key_exists('format', $_REQUEST)) {
 	</tr>
 	<tr>
 		<td class="DataTD"><?=_("Email Address")?></td>
-		<td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : $row['CN'])?></td>
+		<td class="DataTD"><?=(trim($row['CN'])=="" ? _("empty") : sanitizeHTML($row['CN']))?></td>
 	</tr>
 	<tr>
 		<td class="DataTD"><?=_("SerialNumber")?></td>
-		<td class="DataTD"><?=$row['serial']?></td>
+		<td class="DataTD"><?=sanitizeHTML($row['serial'])?></td>
 	</tr>
 	<tr>
 		<td class="DataTD"><?=_("Revoked")?></td>
@@ -149,7 +149,7 @@ if (array_key_exists('format', $_REQUEST)) {
 	</tr>
 	<tr>
 		<td class="DataTD"><?=_("Expires")?></td>
-		<td class="DataTD"><?=$row['expires']?></td>
+		<td class="DataTD"><?=$row['expire']?></td>
 	</tr>
 	<tr>
 		<td class="DataTD"><?=_("Login")?></td>
diff --git a/pages/gpg/2.php b/pages/gpg/2.php
index cc8a872..84e11d2 100644
--- a/pages/gpg/2.php
+++ b/pages/gpg/2.php
@@ -30,7 +30,7 @@
 	$query = "select UNIX_TIMESTAMP(`issued`) as `issued`,
 			UNIX_TIMESTAMP(`expire`) - UNIX_TIMESTAMP() as `timeleft`,
 			UNIX_TIMESTAMP(`expire`) as `expired`,
-			`expire` as `expires`, `id`, `level`,
+			`expire`, `id`, `level`,
 			`email`,`keyid`,`description` from `gpg` where `memid`='".intval($_SESSION['profile']['id'])."'
 			ORDER BY `issued` desc";
 	$res = mysql_query($query);
@@ -43,6 +43,7 @@
 <? } else {
 	while($row = mysql_fetch_assoc($res))
 	{
+		$verified = '';
 		if($row['timeleft'] > 0)
 			$verified = _("Valid");
 		if($row['timeleft'] < 0)
@@ -53,18 +54,18 @@
   <tr>
 <? if($verified == _("Valid")) { ?>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
+    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=intval($row['id'])?>"><?=sanitizeHTML($row['email'])?></a></td>
 <? } else if($verified == _("Pending")) { ?>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><?=$row['email']?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['email'])?></td>
 <? } else { ?>
     <td class="DataTD"><?=$verified?></td>
-    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['email']?></a></td>
+    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=intval($row['id'])?>"><?=sanitizeHTML($row['email'])?></a></td>
 <? } ?>
-    <td class="DataTD"><?=$row['expires']?></td>
-    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=$row['id']?>"><?=$row['keyid']?></a></td>
-    <td class="DataTD"><input name="comment_<?=$row['id']?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
-    <td class="DataTD"><input type="checkbox" name="check_comment_<?=$row['id']?>" /></td>
+    <td class="DataTD"><?=$row['expire']?></td>
+    <td class="DataTD"><a href="gpg.php?id=3&amp;cert=<?=intval($row['id'])?>"><?=sanitizeHTML($row['keyid'])?></a></td>
+    <td class="DataTD"><input name="comment_<?=intval($row['id'])?>" type="text" value="<?=htmlspecialchars($row['description'])?>" /></td>
+    <td class="DataTD"><input type="checkbox" name="check_comment_<?=intval($row['id'])?>" /></td>
   </tr>
 <? } ?>
 <? } ?>
@@ -77,5 +78,5 @@
     <td class="DataTD" colspan="6"><input type="submit" name="change" value="<?=_("Change settings")?>" /> </td>
   </tr>
 </table>
-<input type="hidden" name="oldid" value="<?=$id?>" />
+<input type="hidden" name="oldid" value="<?=intval($id)?>" />
 </form>
diff --git a/pages/index/1.php b/pages/index/1.php
index 4f0ca83..3315d69 100644
--- a/pages/index/1.php
+++ b/pages/index/1.php
@@ -35,7 +35,7 @@
 
   <tr>
     <td class="DataTD" width="125"><?=_("First Name")?>: </td>
-    <td class="DataTD" width="125"><input type="text" name="fname" value="<?=array_key_exists('fname',$_REQUEST)?sanitizeHTML($_REQUEST['fname']):""?>" autocomplete="off"></td>
+    <td class="DataTD" width="125"><input type="text" name="fname" size="30" value="<?=array_key_exists('fname',$_REQUEST)?sanitizeHTML($_REQUEST['fname']):""?>" autocomplete="off"></td>
     <td rowspan="4" class="DataTD" width="125"><? printf(_("Help on Names %sin the wiki%s"),'<a href="//wiki.cacert.org/FAQ/HowToEnterNamesInJoinForm" target="_blank">','</a>')?></td>
   </tr>
 
@@ -43,18 +43,18 @@
     <td class="DataTD" valign="top"><?=_("Middle Name(s)")?><br>
       (<?=_("optional")?>)
     </td>
-    <td class="DataTD"><input type="text" name="mname" value="<?=array_key_exists('mname',$_REQUEST)?sanitizeHTML($_REQUEST['mname']):""?>" autocomplete="off"></td>
+    <td class="DataTD"><input type="text" name="mname" size="30" value="<?=array_key_exists('mname',$_REQUEST)?sanitizeHTML($_REQUEST['mname']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
     <td class="DataTD"><?=_("Last Name")?>: </td>
-    <td class="DataTD"><input type="text" name="lname" value="<?=array_key_exists('lname',$_REQUEST)?sanitizeHTML($_REQUEST['lname']):""?>" autocomplete="off"></td>
+    <td class="DataTD"><input type="text" name="lname" size="30" value="<?=array_key_exists('lname',$_REQUEST)?sanitizeHTML($_REQUEST['lname']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
     <td class="DataTD"><?=_("Suffix")?><br>
       (<?=_("optional")?>)</td>
-    <td class="DataTD"><input type="text" name="suffix" value="<?=array_key_exists('suffix',$_REQUEST)?sanitizeHTML($_REQUEST['suffix']):""?>" autocomplete="off"><br><?=sprintf(_("Please only write Name Suffixes into this field."))?></td>
+    <td class="DataTD"><input type="text" name="suffix" size="30" value="<?=array_key_exists('suffix',$_REQUEST)?sanitizeHTML($_REQUEST['suffix']):""?>" autocomplete="off"><br><?=sprintf(_("Please only write Name Suffixes into this field."))?></td>
   </tr>
 
   <tr>
@@ -89,18 +89,18 @@
 
   <tr>
     <td class="DataTD"><?=_("Email Address")?>: </td>
-    <td class="DataTD"><input type="text" name="email" value="<?=array_key_exists('email',$_REQUEST)?sanitizeHTML($_REQUEST['email']):""?>" autocomplete="off"></td>
+    <td class="DataTD"><input type="text" name="email" size="30" value="<?=array_key_exists('email',$_REQUEST)?sanitizeHTML($_REQUEST['email']):""?>" autocomplete="off"></td>
     <td class="DataTD"><?=_("I own or am authorised to control this email address")?></td>
   </tr>
 
   <tr>
     <td class="DataTD"><?=_("Pass Phrase")?><font color="red">*</font>: </td>
-    <td class="DataTD"><input type="password" name="pword1" autocomplete="off"></td>
+    <td class="DataTD"><input type="password" name="pword1" size="30" autocomplete="off"></td>
     <td class="DataTD" rowspan="2">&nbsp;</td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("Pass Phrase Again")?><font color="red">*</font>: </td>
-    <td class="DataTD"><input type="password" name="pword2" autocomplete="off"></td>
+    <td class="DataTD"><input type="password" name="pword2" size="30" autocomplete="off"></td>
   </tr>
 
   <tr>
@@ -112,29 +112,39 @@
   </tr>
 
   <tr>
-    <td class="DataTD">1)&nbsp;<input type="text" name="Q1" size="15" value="<?=array_key_exists('Q1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q1']):""?>"></td>
-    <td class="DataTD"><input type="text" name="A1" value="<?=array_key_exists('A1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A1']):""?>" autocomplete="off"></td>
-    <td class="DataTD" rowspan="5">&nbsp;</td>
+    <td class="DataTD">&nbsp;</td>
+    <td class="DataTD"><?=_("Question")?></td>
+    <td class="DataTD"><?=_("Answer")?></td>
+  </tr>
+
+  <tr>
+    <td class="DataTD">1)</td>
+    <td class="DataTD"><input type="text" name="Q1" size="30" value="<?=array_key_exists('Q1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q1']):""?>"></td>
+    <td class="DataTD"><input type="text" name="A1" size="30" value="<?=array_key_exists('A1',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A1']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
-    <td class="DataTD">2)&nbsp;<input type="text" name="Q2" size="15" value="<?=array_key_exists('Q2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q2']):""?>"></td>
-    <td class="DataTD"><input type="text" name="A2" value="<?=array_key_exists('A2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A2']):""?>" autocomplete="off"></td>
+    <td class="DataTD">2)</td>
+    <td class="DataTD"><input type="text" name="Q2" size="30" value="<?=array_key_exists('Q2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q2']):""?>"></td>
+    <td class="DataTD"><input type="text" name="A2" size="30" value="<?=array_key_exists('A2',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A2']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
-    <td class="DataTD">3)&nbsp;<input type="text" name="Q3" size="15" value="<?=array_key_exists('Q3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q3']):""?>"></td>
-    <td class="DataTD"><input type="text" name="A3" value="<?=array_key_exists('A3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A3']):""?>" autocomplete="off"></td>
+    <td class="DataTD">3)</td>
+    <td class="DataTD"><input type="text" name="Q3" size="30" value="<?=array_key_exists('Q3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q3']):""?>"></td>
+    <td class="DataTD"><input type="text" name="A3" size="30"value="<?=array_key_exists('A3',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A3']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
-    <td class="DataTD">4)&nbsp;<input type="text" name="Q4" size="15" value="<?=array_key_exists('Q4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q4']):""?>"></td>
-    <td class="DataTD"><input type="text" name="A4" value="<?=array_key_exists('A4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A4']):""?>" autcomplete="off"></td>
+    <td class="DataTD">4)</td>
+    <td class="DataTD"><input type="text" name="Q4" size="30"" value="<?=array_key_exists('Q4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q4']):""?>"></td>
+    <td class="DataTD"><input type="text" name="A4" size="30" value="<?=array_key_exists('A4',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A4']):""?>" autcomplete="off"></td>
   </tr>
 
   <tr>
-  <td class="DataTD">5)&nbsp;<input type="text" name="Q5" size="15" value="<?=array_key_exists('Q5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q5']):""?>"></td>
-    <td class="DataTD"><input type="text" name="A5" value="<?=array_key_exists('A5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A5']):""?>" autocomplete="off"></td>
+  <td class="DataTD">5)</td>
+    <td class="DataTD"><input type="text" name="Q5" size="30" value="<?=array_key_exists('Q5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['Q5']):""?>"></td>
+    <td class="DataTD"><input type="text" name="A5" size="30" value="<?=array_key_exists('A5',$_SESSION['signup'])?sanitizeHTML($_SESSION['signup']['A5']):""?>" autocomplete="off"></td>
   </tr>
 
   <tr>
diff --git a/pages/wot/1.php b/pages/wot/1.php
index a45b5df..9047f27 100644
--- a/pages/wot/1.php
+++ b/pages/wot/1.php
@@ -14,9 +14,9 @@
     You should have received a copy of the GNU General Public License
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
-*/ ?>
-<?
-        $res=mysql_fetch_assoc(mysql_query("select sum(acount) as summe from countries"));
+*/
+
+	$res=mysql_fetch_assoc(mysql_query("select sum(acount) as summe from countries"));
 	$total1 =$res['summe'];
 
 	$locid=array_key_exists('locid',$_REQUEST)?intval($_REQUEST['locid']):0;
@@ -91,7 +91,7 @@
 	{
 	$query = "select *, `users`.`id` as `id` from `users`,`notary` where `listme`='1' and
 			`ccid`='".$ccid."' and `regid`='".$regid."' and
-			`locid`='".$locid."' and `users`.`id`=`notary`.`to`
+			`locid`='".$locid."' and `users`.`id`=`notary`.`to` and `notary`.`deleted`=0
 			group by `notary`.`to` HAVING SUM(`points`) >= 100 order by `points` desc";
 	$list = mysql_query($query);
 	if(mysql_num_rows($list) > 0)
@@ -104,19 +104,19 @@
     <td class="title"><?=_("Contact Details")?></td>
     <td class="title"><?=_("Email Assurer")?></td>
     <td class="title"><?=_("Assurer Challenge")?></td>
-
   </tr>
-<? while($row = mysql_fetch_assoc($list)) { ?>
+
+<?		while($row = mysql_fetch_assoc($list)) { ?>
   <tr>
-    <td class="DataTD" width="100"><nobr><?=$row['fname']?> <?=substr($row['lname'], 0, 1)?></nobr></td>
+    <td class="DataTD" width="100"><nobr><?=sanitizeHTML($row['fname'])?> <?=substr($row['lname'], 0, 1)?>.</nobr></td>
     <td class="DataTD"><?=maxpoints($row['id'])?></td>
-    <td class="DataTD"><?=$row['contactinfo']?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['contactinfo'])?></td>
     <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($row['id'])?>"><?=_("Email Me")?></a></td>
     <td class="DataTD"><?=$row['assurer']?_("Yes"):("<font color=\"#ff0000\">"._("Not yet!")."</font>")?></td>
-
   </tr>
-<? } 
-        }
+<?
+		}
+	}
 ?>
 </table>
 <br>
diff --git a/pages/wot/10.php b/pages/wot/10.php
index bc76a86..b5e146c 100644
--- a/pages/wot/10.php
+++ b/pages/wot/10.php
@@ -24,7 +24,7 @@
     <td colspan="5" class="title"><?=_("Assurer Ranking")?></td>
   </tr>
   <tr>
-<?
+<?// the rank calculation is not adjusted to the new deletion method
 	$query = "SELECT `users`. *, count(*) AS `list` FROM `users`, `notary`
 			WHERE `users`.`id` = `notary`.`from` AND `notary`.`from` != `notary`.`to`
 			AND `from`='".intval($_SESSION['profile']['id'])."' GROUP BY `notary`.`from`";
@@ -36,8 +36,8 @@
 			WHERE `users`.`id` = `notary`.`from` AND `notary`.`from` != `notary`.`to`
 			GROUP BY `notary`.`from` HAVING count(*) > '$rc' ORDER BY `notary`.`when` DESC";
 */
-	$query = "SELECT count(*) AS `list` FROM `users` 
-			inner join `notary` on `users`.`id` = `notary`.`from` 
+	$query = "SELECT count(*) AS `list` FROM `users`
+			inner join `notary` on `users`.`id` = `notary`.`from`
 			GROUP BY `notary`.`from` HAVING count(*) > '$rc'";
 
 	$rank = mysql_num_rows(mysql_query($query)) + 1;
@@ -64,18 +64,18 @@
     <td class="DataTD"><b><?=_("Method")?></b></td>
   </tr>
 <?
-	$query = "select * from `notary` where `to`='".intval($_SESSION['profile']['id'])."'";
+	$query = "select `id`, `date`, `from`, `points`, `location`, `method` from `notary` where `to`='".intval($_SESSION['profile']['id'])."' and `deleted`=0";
 	$res = mysql_query($query);
 	while($row = mysql_fetch_assoc($res))
 	{
-		$fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($row['from'])."'"));
+		$fromuser = mysql_fetch_assoc(mysql_query("select `fname`, `lname` from `users` where `id`='".intval($row['from'])."'"));
 ?>
   <tr>
-    <td class="DataTD"><?=$row['id']?></td>
+    <td class="DataTD"><?=intval($row['id'])?></td>
     <td class="DataTD"><?=$row['date']?></td>
-    <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($row['from'])?>"><?=$fromuser['fname']." ".$fromuser['lname']?></td>
-    <td class="DataTD"><?=$row['points']?></td>
-    <td class="DataTD"><?=$row['location']?></td>
+    <td class="DataTD"><a href="wot.php?id=9&amp;userid=<?=intval($row['from'])?>"><?=sanitizeHTML(trim($fromuser['fname']." ".$fromuser['lname']))?></td>
+    <td class="DataTD"><?=intval($row['points'])?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['location'])?></td>
     <td class="DataTD"><?=_(sprintf("%s", $row['method']))?></td>
   </tr>
 <?
@@ -114,30 +114,30 @@ if ($thawte)
   </tr>
 <?
 	$points = 0;
-	$query = "select * from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `to`!='".intval($_SESSION['profile']['id'])."'";
+	$query = "select `id`, `date`, `points`, `to`, `location`, `method` from `notary` where `from`='".intval($_SESSION['profile']['id'])."' and `to`!='".intval($_SESSION['profile']['id'])."'  and `deleted`=0" ;
 	$res = mysql_query($query);
 	while($row = mysql_fetch_assoc($res))
 	{
-		$fromuser = mysql_fetch_assoc(mysql_query("select * from `users` where `id`='".intval($row['to'])."'"));
-		$points += $row['points'];
+		$fromuser = mysql_fetch_assoc(mysql_query("select `fname`, `lname` from `users` where `id`='".intval($row['to'])."'"));
+		$points += intval($row['points']);
 		$name = trim($fromuser['fname']." ".$fromuser['lname']);
 		if($name == "")
 			$name = _("Deleted before Verification");
 		else
-			$name = "<a href='wot.php?id=9&amp;userid=".intval($row['to'])."'>$name</a>";
+			$name = "<a href='wot.php?id=9&amp;userid=".intval($row['to'])."'>".sanitizeHTML($name)."</a>";
 ?>
   <tr>
     <td class="DataTD"><?=intval($row['id'])?></td>
     <td class="DataTD"><?=$row['date']?></td>
     <td class="DataTD"><?=$name?></td>
     <td class="DataTD"><?=intval($row['points'])?></td>
-    <td class="DataTD"><?=$row['location']?></td>
+    <td class="DataTD"><?=sanitizeHTML($row['location'])?></td>
     <td class="DataTD"><?=$row['method']==""?"":_(sprintf("%s", $row['method']))?></td>
   </tr>
 <? } ?>
   <tr>
     <td class="DataTD" colspan="3"><b><?=_("Total Points Issued")?>:</b></td>
-    <td class="DataTD"><?=$points?></td>
+    <td class="DataTD"><?=intval($points)?></td>
     <td class="DataTD" colspan="2">&nbsp;</td>
   </tr>
 </table>
diff --git a/pages/wot/15.php b/pages/wot/15.php
index cca2702..c1f3e0f 100644
--- a/pages/wot/15.php
+++ b/pages/wot/15.php
@@ -14,7 +14,7 @@
     You should have received a copy of the GNU General Public License
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
-*/ 
+*/
 
 	require_once($_SESSION['_config']['filepath']."/includes/notary.inc.php");
 
diff --git a/pages/wot/9.php b/pages/wot/9.php
index bfa7a98..a8b9413 100644
--- a/pages/wot/9.php
+++ b/pages/wot/9.php
@@ -15,9 +15,9 @@
     along with this program; if not, write to the Free Software
     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 */
-	
+
 	require_once($_SESSION['_config']['filepath'].'/includes/lib/l10n.php');
-	
+
 
 	$res = mysql_query("select * from `users` where `id`='".intval($_REQUEST['userid'])."' and `listme`='1'");
 	if(mysql_num_rows($res) <= 0)
@@ -28,9 +28,8 @@
 		$user = mysql_fetch_array($res);
 		$userlang = $user['language'];
 		$points = mysql_num_rows(mysql_query("select sum(`points`) as `total` from `notary`
-				where `to`='".$user['id']."' group by `to` HAVING SUM(`points`) > 0"));
-		if($points <= 0)
-		{
+				where `to`='".intval($user['id'])."' and `deleted`=0 group by `to` HAVING SUM(`points`) > 0"));
+		if($points <= 0) {
 			echo _("Sorry, I was unable to locate that user.");
 		} else {
 
@@ -38,31 +37,31 @@
 ?>
 <? if($_SESSION['_config']['error'] != "") { ?><font color="#ff0000" size="+1">ERROR: <?=$_SESSION['_config']['error']?></font><? unset($_SESSION['_config']['error']); } ?>
 <form method="post" action="wot.php">
-<input type="hidden" name="userid" value="<?=$user['id']?>">
+<input type="hidden" name="userid" value="<?=intval($user['id'])?>">
 <table align="center" valign="middle" border="0" cellspacing="0" cellpadding="0" class="wrapper">
   <tr>
     <td colspan="2" class="title"><?=_("Contact Assurer")?></td>
   </tr>
   <tr>
     <td class="DataTD"><?=_("To")?>:</td>
-    <td class="DataTD" align="left"><?=$user['fname']?> <?=substr($user['lname'], 0, 1)?></td>
+    <td class="DataTD" align="left"><?=sanitizeHTML(trim($user['fname'].' '.substr($user['lname'], 0, 1)))?></td>
   </tr>
 <? if($userlang != "") { ?>
   <tr>
     <td class="DataTD"><?=_("Language")?>:</td>
-    <td class="DataTD" align="left"><? printf(_("%s prefers to be contacted in %s"), $user['fname'], L10n::$translations[$userlang]) ?></td>
+    <td class="DataTD" align="left"><? printf(_("%s prefers to be contacted in %s"), sanitizeHTML($user['fname']), L10n::$translations[$userlang]) ?></td>
   </tr>
 <? } ?>
 <?
-	$query = "select * from `addlang` where `userid`='".$user['id']."'";
+	$query = "select * from `addlang` where `userid`='".intval($user['id'])."'";
 	$res = mysql_query($query);
 	while($row = mysql_fetch_assoc($res))
 	{
-		$lang = mysql_fetch_assoc(mysql_query("select * from `languages` where `locale`='${row['lang']}'"));
+		$lang = mysql_fetch_assoc(mysql_query("select * from `languages` where `locale`='".mysql_real_escape_string($row['lang'])."'"));
 ?>
   <tr>
     <td class="DataTD"><?=_("Additional Language")?>:</td>
-    <td class="DataTD" align="left"><? printf(_("%s will also accept email in %s - %s"), $user['fname'], $lang['lang'], $lang['country']) ?></td>
+    <td class="DataTD" align="left"><? printf(_("%s will also accept email in %s - %s"), sanitizeHTML($user['fname']), sanitizeHTML($lang['lang']), sanitizeHTML($lang['country'])) ?></td>
   </tr>
 <? } ?>
   <tr>
@@ -79,7 +78,7 @@
 </table>
 <input type="hidden" name="pageid" value="<?=$_SESSION['_config']['pagehash']?>">
 <input type="hidden" name="userid" value="<?=intval($_REQUEST['userid'])?>">
-<input type="hidden" name="oldid" value="<?=$id?>">
+<input type="hidden" name="oldid" value="<?=intval($id)?>">
 </form>
 <p>[ <a href='javascript:history.go(-1)'><?=_("Go Back")?></a> ]</p>
 <? } } ?>