MySQL Tables Search

The software that comes with this article is specialized.

You won't need it, unless you do, in which case it will be a mighty handy tool to have around.

In essence, MySQL Tables Search looks for one or more terms in all columns of one or more MySQL tables. It quickly finds the matches and tells you where they are.

MySQL Tables Search was developed because of something that happened last week.

A Serious Hacking Incident

A client's website was hacked and, quite frankly, destroyed. The MySQL database wasn't compromised, but the website files were.

The hacker had access permissions like they would have with full FTP access.

I watched changes being made as they happened. When I deleted their files, the files reappeared.

At first, it was assumed they somehow had FTP access. The FTP password was changed, but the hacker never noticed and just kept on contaminating files and adding new ones.

With the hacker's direct access to all the website files, none could be trusted anymore. I knew that once the hacker's access was determined and blocked, every file of the website would need to be scanned to reverse any contamination.

But the hacker removed that project from my to-do list by deleting the entire website.

Because the FTP password was changed (by me, personally), I knew the hacker wasn't getting in that way. Therefore, it pretty much had to be a security breach in some software on the server.

Parts of the site were put back up, but not all, trying to determine where the hacker was getting in. Sure enough, the hacker came back and continued the nefarious activities.

While the hacker was rummaging around the site, I deleted the forum.

Suddenly, silence. No more hacker.

The rest of the site was uploaded from backups and went back live.

Understandably, the client is highly disgusted with the forum software and will not support that company ever again.

So it fell to me to extract the forum posts, threads and authors from the forum MySQL tables. They would then be published on the website (and, perhaps, imported into new forum software later on).

The forum software that was removed had used 233 MySQL tables. Forum posts were in one table. Users in another. Thread information in still another. But I wasn't certain that I found all the connections for a successful export.

Thus, MySQL Tables Search was born. It was used to search for matching id values across tables, values that might reside in any column of any of the tables. It was also used to search for certain phrases found in posts to help me manually verify I had my table interrelationships correctly mapped.

MySQL Tables Search is now available to you. It can search through any number of MySQL tables you specify, within all of their columns, for one or more terms you specify.

MySQL Tables Search

The source code of the MySQL Tables Search software is in the code box. It is a PHP script.

Customization notes follow the source code. When customization is done and you are ready to use it, the PHP script is uploaded to your server and its URL typed into your browser.

<?php
/*
   MySQL Tables Search
   Version 1.0
   August 16, 2020

   Will Bontrager Software LLC
   https://www.willmaster.com/
*/

/* *** *** *** *** */
/* Customizations */

// Words/Phrases to search for, one per line, placed between 
//   the two lines containing WORDS_PHRASES

$WordPhrases = <<<WORDS_PHRASES
winter
cold spell
summer
hot spell
autumn
fall
spring
WORDS_PHRASES;


// Match type, field value is either exact match or search term 
//   may be anywhere within field. (Use true or false as value.)

$ValueMustBeExactMatch = false;


// The output file, where to put any matches found. If left 
//   blank, matches are printed in the browser window.

$OutputFileName = 'testing.txt';


// List of tables to search, separated with space and/or new line, 
//   placed between the two lines containing TABLES_TO_SEARCH

$TablesToSearch = <<<TABLES_TO_SEARCH
temp_one
temp_two
TABLES_TO_SEARCH;


// MySQL connection data

$MySQL_hostname = 'localhost';
$MySQL_database = 'forum';
$MySQL_username = 'me';
$MySQL_password = 'dfDER##$Fee';


/* End of customization */
/* *** *** *** *** *** */

set_time_limit(300);
mb_regex_encoding('UTF-8');
mb_internal_encoding('UTF-8');
ini_set('display_errors',1);
error_reporting(E_ALL);
if( ! ini_get('date.timezone') ) { date_default_timezone_set('UTC'); }
$MTS_Global = array();
$MTS_Global['MySQL'] = false;
$MTS_Global['MySQL_Tables'] = array();
$MTS_Global['OutputFileName'] = preg_match('/\w/',$OutputFileName) ? trim($OutputFileName) : false;
$MTS_Global['token_exact'] = ($ValueMustBeExactMatch==true or $ValueMustBeExactMatch=='true' or $ValueMustBeExactMatch>0) ? true : false;
$MTS_Global['token_match'] = array();
$MTS_Global['token_in'] = array();
foreach( preg_split('/[\r\n]+/',trim($WordPhrases)) as $line )
{
   $line = trim($line);
   $MTS_Global['token_in'][] = $line;
   $match = preg_quote($line,'/');
   if(! $MTS_Global['token_exact'])
   {
      $match = preg_replace('/\s+/',"\\s+",$match);
      //$match = "[\\A\\b]{$match}[\\b\\Z]";
      $match = "\\b{$match}\\b";
   }
   $MTS_Global['token_match'][] = $match;
}
$MTS_Global['token_count'] = count($MTS_Global['token_match']);
$MTS_Global['MySQL_Tables'] = preg_split('/\s+/',trim($TablesToSearch));
$line = "Tables to look in:\n".print_r($MTS_Global['MySQL_Tables'],true)."\n\n";
if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
else { echo $line; }
$line = "Words/Phrases to look for:\n".print_r($MTS_Global['token_in'],true)."\n\n";
if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
else { echo $line; }
$MTS_Global['MySQL'] = new MySQLclass( array( 'hostname'=>$MySQL_hostname, 'username'=>$MySQL_username, 'password'=>$MySQL_password, 'database'=>$MySQL_database ) );
if( count($MTS_Global['MySQL']->error) ) { MTS_FatalError($MTS_Global['MySQL']->error); }
MTS_DoSearch();
echo 'DONE';
exit;

function MTS_FatalError($err)
{
   $s = '';
   if( is_array($err) ) { $s = implode("\n\n",$err); }
   else { $s = $err; }
   echo "<pre>$s</pre>";
   exit;
}

function MTS_DoSearch()
{
   global $MTS_Global;
   if( ! $MTS_Global['OutputFileName'] ) { echo '<pre>'; }
   foreach( $MTS_Global['MySQL_Tables'] as $table )
   {
      $successArray = array( 'cols' => array( '*' ), 'tablename' => $table );
      $success = $MTS_Global['MySQL']->SelectRecordData( $successArray );
      if( $success )
      {
         $matchInTable = false;
         $line = "\n\nTable: $table\n";
         if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
         else { echo $line; }
         while( $row = $MTS_Global['MySQL']->GetRow() )
         {
            $hasMatch = false;
            for( $i=0; $i<$MTS_Global['token_count']; $i++ )
            {
               foreach( $row as $k => $v )
               {
                  $thisMatch = false;
                  if($MTS_Global['token_exact'])
                  {
                     if(preg_match('/^'.$MTS_Global['token_match'][$i].'$/',$v)) { $thisMatch = true; }
                  }
                  else
                  {
                     if(preg_match('/'.$MTS_Global['token_match'][$i].'/i',$v)) { $thisMatch = true; }
                  }
                  if( $thisMatch )
                  {
                     $matchInTable = true;
                     $hasMatch = true;
                     $line = "Column \"$k\" matches with: {$MTS_Global['token_in'][$i]}\n";
                     if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
                     else { echo $line; }
                  }
               } // foreach
            } // for
            if( $hasMatch )
            {
               $line = "Match row content:\n".print_r($row,true)."\n";
               if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
               else { echo $line; }
            }
         } // while
         $MTS_Global['MySQL']->FreeResult();
         if( ! $matchInTable )
         {
            $line = "No match in table $table\n";
            if( $MTS_Global['OutputFileName'] ) { file_put_contents($MTS_Global['OutputFileName'],$line,FILE_APPEND); }
            else { echo $line; }
         }
      } // if
   } // foreach
   if( ! $MTS_Global['OutputFileName'] ) { echo '<pre>'; }
}

class MySQLclass
{
   private $MySQLi;
   private $result;
   public $error;

   function __construct()
   {
      $this->error = array();
      $th = func_get_args();
      if( count($th) )
      {
         foreach( $th[0] as $k => $v ) { $this->$k = $v; }
      }
      $this->MySQLi = new mysqli( $this->hostname, $this->username, $this->password, $this->database );
      if( $this->MySQLi->connect_errno )
      {
         $this->error[] = 'Unable to connect to the database server (error number ' . $this->MySQLi->connect_errno . '):<br> ' . $this->MySQLi->connect_error;
      }
      if( ! $this->MySQLi->set_charset('utf8') )
      {
         $this->error[] = $this->AppendErrorNotationToErrorMessage('Error loading character set utf8');
      }
      return $this->error;
   } # function __construct()

   function __destruct() { $this->MySQLi->close(); }

   public function ErrorMessage() { return $this->error; }

   public function SelectRecordData($arr)
   {
      $this->error = array();
      if( empty($arr['tablename']) )
      {
         $this->AppendErrorNotationToErrorMessage('Table name is required.');
         return false;
      }
      $sql = 'SELECT ';
      if( isset($arr['cols']) )
      {
         if( is_array($arr['cols']) ) { $sql .= implode(',',$arr['cols']) . ' '; }
         else { $sql .= implode(',',preg_split('/[, ]+/',$arr['cols'])) . ' '; }
      }
      else { $sql .= '* '; }
      $sql .= "FROM {$arr['tablename']} ";
      if( isset($arr['where']) ) { $sql .= "WHERE {$arr['where']} "; }
      if( isset($arr['order']) ) { $sql .= "ORDER BY {$arr['order']} "; }
      $limit = isset($arr['limit']) ? preg_replace('/[^\d\,]+/','',$arr['limit']) : 0;
      $offset = isset($arr['offset']) ? intval($arr['offset']) : 0;
      if( $offset and (! $limit ) ) { $limit = 9999999; }
      if( $limit ) { $sql .= "LIMIT $limit "; }
      if( $offset ) { $sql .= "OFFSET $offset "; }
      if( ($this->result = @mysqli_query($this->MySQLi,$sql)) )
      {
         $this->numRowsInResult = mysqli_num_rows($this->result);
         return true;
      }
      $this->AppendErrorNotationToErrorMessage('Selection failed.');
      return false;
   }

   public function GetRow()
   {
      if( ($row = mysqli_fetch_assoc($this->result)) ) { return $row; }
      return array();
   }

   public function FreeResult() { $this->result->close(); }
}
?>

Customizations —

There are five places to customize.

1. Two lines contain WORDS_PHRASES within them.

   Between those two lines, list the words or phrases to search for. Put each on a line by itself.

2. $ValueMustBeExactMatch = false;

   Either leave false as is or replace it with true to specify the word or phrase must be an exact match.

   When true is specified, a match occurs only if the entire word or phrase is the exact content of a column in a table.

   When false is specified, the search term being looked for may be anywhere within the table and does not have to be the entire content of a column.

3. $OutputFileName = 'testing.txt';

   Any matches found may be written to a file on the server. If that is what you want, update testing.txt with your preferred file name.

   When this is blank, matches are published in the browser window instead of in a file.

4. Two lines contain TABLES_TO_SEARCH within them.

   Between those two lines, list the names of the tables to look through during the search for your specified words and phrases. Put each table name on a line by itself.

5. The MySQL database credentials:

   $MySQL_hostname = 'localhost';
   $MySQL_database = 'forum';
   $MySQL_username = 'me';
   $MySQL_password = 'dfDER##$Fee';
   

   Replace the values for the MySQL hostname, database name, username, and password to the ones that will connect with your MySQL database.

Customization has now been completed. Name the PHP script tablesearch.php or other *.php file name.

When ready to use tablesearch.php, upload it to your server and type its URL into your browser.

It will look through the tables you specified and find any instances of the words or phrases you specified. It will publish information about where it found the matches either in your browser window or in a file name you specified.

(This article first appeared with an issue of the Possibilities newsletter.)

Will Bontrager