Search

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.";
    }
}

?>

Tequilaphp's Blog

Tech stuff

Searching strings in a database and files

Searching in files

Searching in the database