Searching strings in a database and files

I always find cumbersome to work or maintain someone else systems, to make a small changes you normally lose hours trying to find out where that little string or that link that you have to modify.

Searching in files

For searching in files you can sometimes download all the site and simply use netbeans or other tool to search in files.

If you are lucky and you are in Linux you can SSH to the server and use the system tools. Check this fantastic link on how to do it

http://www.liamdelahunty.com/tips/linux_find_string_files.php

Searching in the database

Now what happens when you discover that what you are looking for is contained in the database?

Sometimes you can just browse the database content or dump the database and search as file but this can be hard with big databases.

So I decided for a very simple solution, create a PHP script that will check for a string in every available string field in all tables.  It will echo the name of the tables that have matches or you can go further and echo the ID’s or even further and echo the whole content of the row.

Hope someone else find it useful!

(This script is v.0.2, I updated after Andrew commented on the wrong sql construction) Please let me know of you find bugs.

<?php
/**
* Simple tool to search in a mysql database in all string fields
*
* Browse to search.php?term=yourterm[detailed=1/2]
* searchdb.php?action=search&term=yourterm[&detailed=1/2][&in=table1,table2,...][&notin=table1,table2,...]
*
* To use this class set $dbase to the database you want to use, 
* You might also need to modify line 70 to enter your own server info
* (I left that info hardcoded as it failed to use this info using variables in one server)
*
* If you need it, would be easy to add a web interface, just create one field for every parameter and send the form via GET or change the safeget function to read the POST or REQUEST var instead 
*
* Please let me know of any errors
*
* @author itzco 
* @version 0.2
*/

error_reporting(E_ALL);
set_time_limit(120);
// 1) SET YOUR DB NAME HERE
$dbase = 'yourdbnamehere';

if (empty($_GET)) {
    echo "Please enter a method (or action=help)";
    die();
}

echo "<pre>";
$method =safeget('action');


switch($method) {
    case 'getdbpwd':
    // This method to get the user/pwd when it's set in php.ini
        $hk = new db();
        $tmp = $hk->getdata();
        echo 'User: '.$tmp['user'].'Pwd:'. $tmp['pwd'];
        break;
    case 'search':
        $finder = new dbsearch($dbase);
        $finder->exclude(safeget('notin'));
        $finder->useonly(safeget('in'));
        $finder->find(safeget('term'),safeget('detailed'));
        break;
    case 'help':

        echo "<br>Methods:getdbpwd/search<p>getdbpwd<br>?action=getdbpwd&domain=xxx.domain.nl</p>";
        echo "<p>search<br>?action=search&term=<i>yourterm</i>[&detailed=1/2][&in=table1,table2,...][&notin=table1,table2,...]</p>";
        echo "<p>While in/notin are not mutually exclusive setting one is enough";
        break;
}
function safeget($key,$default=false)
{
    return isset($_GET[$key]) ? $_GET[$key] : $default;
}

class db {
    private $link;
    public function getdata() {
        return array('user'=>ini_get('mysql.default_user'), 'pwd' =>ini_get('mysql.default_password'));

    }
    public function connect($dbase)
    {
        $user = ini_get('mysql.default_user');
        $pwd  = ini_get('mysql.default_password');
        if ($user & $pwd)
        {
           $this->link = @mysql_connect();
        }
        else
        {
            echo  '<br>Warning: ini_get user and pwd are not set';
            // 2) Enter here your user pwd and server if required
            $this->link = @mysql_connect('localhost', 'root', 'root');
        }
        if ($this->link === FALSE)
    {
            die ('Not able to connect to the database');
        }
    else
    {
            $db_selected = mysql_select_db ($dbase, $this->link);
            if (!$db_selected) {
                die ('Can\'t use database : ' . mysql_error());
            }
    }

    }
    function query($query)
    {

        if (false === ($this->res = mysql_query($query, $this->link))) {
            echo sprintf('MySQL error #%d: %s.', mysql_errno(), mysql_error());
        } else {
            $this->error = null;
            $result = array();
            while ($row = mysql_fetch_assoc($this->res)) {
                $result[] = $row;
            }
            return $result;
        }
    }
}



class dbsearch
{
    private $db;
    private $targettypes = array('varchar','char','text','tinytext','mediumtext');
    private $dbname;
    private $include=array();
    private $exclude=array();
    public function __construct($dbase)
    {
        $this->db = new db();
        $this->db->connect($dbase);
        $this->dbname = $dbase;
    }
    public function exclude($tables)
    {
        if (!$tables) return;
        $this->exclude = explode(',',$tables);
    }
    public function useonly($tables)
    {
        if (!$tables) return;
        $this->include = explode(',',$tables);
    }
    public function find($string, $detailed=false)
    {
        if (!$string || strlen($string)<3)
        {
            die ("<br>Please enter a search term (3 chars min)");
        }
        $tables = $this->db->query('SHOW TABLES');

        $tblfn = 'Tables_in_'.$this->dbname;
        foreach ($tables as $tablerow)
        {
            $table = $tablerow[$tblfn];
            $use = ($this->include ? in_array($table, $this->include) : true);
            $notuse = ($this->exclude ? in_array($table, $this->exclude) : false);
            if ($use && !$notuse)
            {
            $qryfields = array();
            $key='';

            echo ".";
            $msql = 'SHOW FIELDS IN '. $table;
            //echo "<br>$msql<br>";
            $fields = $this->db->query($msql);
            // use char,varchar and text tinytext
            //print_r($fields);
            foreach($fields as $field)
            {
                $mysqltype = preg_replace('/\s*\(\d+\)\s*/','', $field['Type']);
        $mysqltype = preg_replace('/\s*\(\d+,\d+\)\s*/','', $mysqltype);
                //echo $mysqltype."<br>";
                if ($field['Key'] == 'PRI' && !$key)
                {
                    $key = $field['Field'];
                }
                if (in_array($mysqltype, $this->targettypes ))
                {
                    $qryfields[] = '`'.$field['Field']."` like '%$string%'";
                }
            }
            //echo "<br> PRI: $key";print_r($qryfields);
            if ($qryfields)
            {
                //print_r($table);
                $mSql = "SELECT count(*) as e FROM  `$table` WHERE " . implode(' OR ', $qryfields);
                //echo $mSql."<br>";
                $result = $this->db->query($mSql);
                if ($result[0]['e'] >0)
                {
                    echo "<br>$table (".$result[0]['e'].')';
                    if ($detailed)
                    {
                         $mSql = "SELECT ".($detailed==1 ? $key : '*')." FROM  `$table` WHERE " . implode(' OR ', $qryfields);
                         echo '<div style="border:1px solid #ccc; height:200px;overflow:auto;">';
                         print_r($this->db->query($mSql));
                         echo "</div>";
                    }
                }

            }
        }//if
        }
        echo "Done.";
    }
}

?>

8 thoughts on “Searching strings in a database and files

  1. On this line:
    $mSql = “SELECT count(*) as e FROM `$table` WHERE ” . implode(‘ AND ‘, $qryfields);

    and this line:
    $mSql = “SELECT “.($detailed==1 ? $key : ‘*’).” FROM `$table` WHERE ” . implode(‘ AND ‘, $qryfields);

    The ‘AND’ should be an ‘OR’ if you want to check that any one of the fields contains the search term and not all of them.

    • Andrew

      Thanks for the correction you are completely right, I noticed some days after posting when I was unable to find anything :)

      I updated this class with other options I’ll fix the original post now and add the latest!

      Thanks again!

    • Yes, it’s possible to show the columns and their content also, try adding to the request:

      &detailed=1
      OR
      &detailed=2

      However showing what columns of each record were a match is not currently possible but wouldn’t be hard to implement in PHP

  2. There is small mistake in line 49. It should be:

    echo “Methods:getdbpwd/searchgetdbpwd?action=getdbpwd&domain=xxx.domain.nl”;

    …so tags are closed.

    Besides script looks like is not searching in whole database. I used this for searching in WP database and it found string in “wp_posts” table, but not in “wp_postmeta”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s