Remove customer passwords from tickets

[Nick]

We've all had customers who are oblivious to the fact that they shouldn't give out their passwords to anyone, even the support desk - if an employee should have access to their details, they will. This results in their unencrypted passwords sitting in tickets, something which I mislike greatly.

 

If you don't like the idea of these unencrypted passwords sitting in your ticket database either, fret ye not: I've written a script which will go through all your support tickets and remove customer passwords, both WHMCS and hosting.

 

Copying the following script into a file in your WHMCS root directory and running it will go through your tickets, remove current passwords and save back into the database. It has been tested successfully on a test install of 3.6.0 beta and a production 3.5.1. Having said that, Back Up First!. A backed-up database is a happy database.

 

<?php

include("dbconnect.php");
include("includes/functions.php");

error_reporting(E_ALL);

$all = array(); //Our all-encompassing array for this script

$tickets = mysql_query("SELECT `userid`, `id`, `message` FROM `tbltickets`;");
//Pull all the tickets from the datbase (we'll get replies individually)

while($row = mysql_fetch_assoc($tickets))
{
   if((int)$row['userid'] != 0) // If this is not a client ticket, ignore it.
   {
       $all[$row['userid']][$row['id']] = $row;
       $all[$row['userid']][$row['id']]['replies'] = array();

       $rep = mysql_query("SELECT `id`, `message` FROM `tblticketreplies` WHERE `tid` = '" . $row['id'] ."';");
//Fetch replies from this ticket

       while($reps = mysql_fetch_assoc($rep))
       {
           $all[$row['userid']][$row['id']]['replies'][$reps['id']] = $reps['message'];
       }
   }
}
foreach($all as $key => $value) //Go through each user and pull passwords from the db, etc
{
   $all[$key]['passwords'] = array();
   $all[$key]['passwords-rep'] = array();
   $upass = mysql_fetch_assoc(mysql_query("SELECT `password` FROM `tblclients` WHERE `id` = " . $key . ";"));
//WHMCS client area password
   $all[$key]['passwords'][] = decrypt($upass['password']);
   $all[$key]['passwords-rep'][] = "[Removed client area password]";

   $hosting = mysql_query("SELECT `password` FROM `tblhosting` WHERE `userid` = " . $key . ";");
//All hosting passwords
   while($row = mysql_fetch_assoc($hosting))
   {
       $all[$key]['passwords'][] = decrypt($row['password']);
       $all[$key]['passwords-rep'][] = "[Removed account password]";
   }

   foreach($all[$key] as $ticketid => $details)
   {
       if((int)$ticketid != 0)
       {
           $all[$key][$ticketid]['message'] = str_ireplace($all[$key]['passwords'], $all[$key]['passwords-rep'], $details['message']);
           mysql_query("UPDATE `tbltickets` SET `message` = '" . mysql_real_escape_string($all[$key][$ticketid]['message']) . "' WHERE `id` = $ticketid;");
           foreach($all[$key][$ticketid]['replies'] as $replyid => $replymessage)
           {
               $all[$key][$ticketid]['replies'][$replyid] = str_ireplace($all[$key]['passwords'], $all[$key]['passwords-rep'], $replymessage);
               mysql_query("UPDATE `tblticketreplies` SET `message` = '" . mysql_real_escape_string($all[$key][$ticketid]['replies'][$replyid]) . "' WHERE `id` = " . $replyid . ";");
           }
       }
   }
}
?>

[JasonO]

Good contribution Nick