2012/04/18

Zend_Test_PHPUnit_DatabaseTestCase with multidb


we moved here http://radzserg.com/2012/04/18/zend_test_phpunit_databasetestcase-with-multidb/

Today I'll tell you how you can run tests for multiple databases with PHPUnit_DatabaseTestCase at the same time.

There are 2 ways. First is very simple you can use in your fixtures something like this
<second_db_scheme.accounts id="123"
It will work if only you have enough rights it's good for development/local db. But as a rule on even dev servers it won't work.

Second approach is to extend PHPUnitsetUp
I've changed some names you can use more clear names in your projects.
<?php

abstract class App_Test_PHPUnit_DatabaseTestCase extends Zend_Test_PHPUnit_DatabaseTestCase
{

    protected $_connectionMock;
    protected $_secondDbConnectionMock;

    protected $backupGlobalsBlacklist = array('application');  // btw this hack will speed up your tests

    /**
     * @return Zend_Test_PHPUnit_Db_Connection
     */
    protected function getConnection()
    {
        if ($this->_connectionMock == null) {
            $multiDb = Zend_Registry::get('multidb');

            $connection = $multiDb->getDb();

            $this->_connectionMock = $this->createZendDbConnection(
                $connection, ''
            );

            Zend_Db_Table_Abstract::setDefaultAdapter($connection);
        }
        return $this->_connectionMock;
    }

    protected function getSecondDbConnection()
    {
        if ($this->_dnsConnectionMock == null) {
            $multiDb = Zend_Registry::get('multidb');

            $connection = $multiDb->getDb('second_db');

            $this->_secondDbConnectionMock = $this->createZendDbConnection(
                $connection, ''
            );
        }

        return $this->_dnsConnectionMock;
    }

    protected function setUp()
    {
        parent::setUp();

        $this->databaseTester = NULL;

        $this->getDatabaseTester()->setSetUpOperation($this->getSetUpOperation());
        $this->getDatabaseTester()->setDataSet($this->getDataSet());
        $this->getDatabaseTester()->onSetUp();

        $secondDataSet = $this->getDataSetForSecondDb();
        if ($dnsDataSet) {
            // create data set for second db
            $secondDataTester = new PHPUnit_Extensions_Database_DefaultTester($this->getSecondDbConnection());
            $secondDataTester->setSetUpOperation($this->getSetUpOperation());
            $secondDataTester->setDataSet($secondDataSet);
            $secondDataTester->onSetUp();
        }

    }

    protected function getDataSetForSecondDb()
    {
        return null;
    }

}
 
as you see we just check does method getDataSetForSecondDb return data. You have to override it in child classes. If we get dataSet we will set up operations for second Db.

Everything is quite simple but in the fullness of time it made me to  look inside PHPUnit_* classes. So I hope I can save you time with that stuff. 

2012/04/07

Run Zend cli scripts (yii style)


we moved here http://radzserg.com/2012/04/07/run-zend-cli-scripts-yii-style/


yii has a nice yiic tool - cli for *nix so you can run scripts in this way

./yiic mynicescript --param1=2 --param2=2

this is really cool feature is essentially just a bootstrap for for running cli yii scripts(commands). 


It's weird that Zend Framework(ZF) doesn't have something similar. I propose you my variant for ZF


There are 2 classes (btw some features are similar with Extended Yii CConsoleCommand :):

<?php

/**
 *
 * Implements function to work from CLI
 * @author radzserg
 *
 */
abstract class App_Script_Abstract
{
    const VERBOSE_ERROR = 'error';
    const VERBOSE_INFO = 'info';

    protected $_verbose;
    
    /**
     *
     * Return class name
     * @return string
     */
    public function getName() {
        return get_class($this);
    }
    
    /**
     * Get params from cli
     * myscript.php --param1=value --param2=value
     * @return array
     */
    public function getCliParams() {
        $params = isset($_SERVER['argv']) ? $_SERVER['argv'] : array();
        $resultParams = array();
        foreach ($params as $paramPair) {
            if (strpos($paramPair, '--') !== false && strpos($paramPair, '--') == 0) {
                $count = 1;
                $paramPair = str_replace('--', '', $paramPair, $count);
                $paramPair = explode('=', $paramPair, 2);
                $key = isset($paramPair[0]) ? $paramPair[0] : null;
                $value = isset($paramPair[1]) ? $paramPair[1] : '';
                if ($key) {
                    $resultParams[$key] = $value;
                }
            }
        }
        return $resultParams;
    }

    /**
     * Verbose info
     * @param $message
     * @param null $type
     */
    public function verbose($message, $type = null)
    {
        if ($this->_verbose === NULL) {
            $cliParams = $this->getCliParams();
            $this->_verbose = isset($cliParams['verbose']) ? true : false;
        }
        if ($this->_verbose) {
            if ($type == self::VERBOSE_ERROR) {
                // message in red
                echo date('H:i:s ') . "\033[31;1m" . $message . "\033[0m\n";
            } elseif ($type == self::VERBOSE_INFO) {
                // message in green
                echo date('H:i:s ') . "\033[32;1m" . $message . "\033[0m\n";
            } else {
                echo date('H:i:s ') . $message . "\n";    
            }
        }
    }
}
<?php

class App_Script_ScriptRunner extends App_Script_Abstract
{

    /**
     *
     * Run script
     */
    public function run()
    {
        $script = $this->_getScriptObject();
        $params = $this->getCliParams();
        if (isset($params['help'])) {
            $script->help();
        } else {
            $script->execute($params);    
        }
    }
    
    
    /**
     *
     * Return script nam that have to be run
     * Mandatory format
     *
     * cron_runner.php scriptName [--param1=value --param2=value]
     * @return string
     */
    public function getScriptName()
    {
        return isset($_SERVER['argv'][1]) ? $_SERVER['argv'][1] : NULL;
    }

    
    /**
     *
     * Return script object instance of App_Exception_System
     * @return App_Script_Abstract
     * @throws App_Exception_System
     */
 protected function _getScriptObject()
 {
     $scriptName = $this->getScriptName();
        if (!$scriptName) {
            throw new App_Exception_System("Cron name is not defined. Server argv " . print_r($_SERVER['argv'], true));
        }
        
        $scriptName = "App_Script_Command_" . ucfirst($scriptName);
        $script = new $scriptName;
        
        return $script;
 }
    
}
Second class App_Script_ScriptRunner is a descendant class and you can extend it as you want. As you see in my example I extended _getScriptObject my scrips are in /App/Script/Command perhaps just /scripts is better place. For example  I use another one App_Script_CronRunner to run cron scripts that additionally adds some stat about script execution.

And finally you have to add entry script for CLI. I put it in /scripts folder.


/**
 * Run CLI scripts
 *
 * php cron_runner.php myCommandName --param1=value --param2=value
 * command should be located in App/Script/Command/
 */

$envention = file_get_contents(dirname(__FILE__) . '/cli_envention');
if (!$envention) {
    throw new Exception("CLI envention is not defined");
}
define('APPLICATION_ENV', $envention);
define('ROOT_PATH', realpath(dirname(__FILE__) . '/..'));

define('APPLICATION_PATH', ROOT_PATH . '/application');

set_include_path(implode(PATH_SEPARATOR, array(
    realpath(ROOT_PATH . '/library'),
    get_include_path(),
)));

if ('development' == APPLICATION_ENV) {
    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', 1);
}

require_once 'App/Application/Console.php';
// Create application, bootstrap, and run
$application = new App_Application_Console(
    APPLICATION_ENV,
    APPLICATION_PATH . '/configs/application.ini'
);
$application->bootstrap();

$scriptRunner = new App_Script_ScriptRunner();
$scriptRunner->run();

As you see it's very similar to index.php The last hack that I use here is App_Application_Console it's full copy of Zend_Application but uses his own Bootstrap class.

That's it now you can run your scripts in this way.

cd /scripts
php script_runner.php haveFun --param1=123 --verbose


2012/03/31

I forgot to remove debug code and updated last changes to production


we moved here http://radzserg.com/2012/03/31/i-forgot-to-remove-debug-code-and-updated-last-changes-to-production/


Sometimes you need to debug some code and you do something like this

if ($service->canBeUpdated) {
    // $service->canBeUpdated returns false 
    // and you need to debug this place 
}
 
// and you do simple (very simple :) stub
if (true || $service->canBeUpdated()) {

}


:D ooh everything works and you can easily debug this place. You've finished with that task and did some other tasks and finally updated everything to live.

Oh crap.. I forgot to remove debug code and updated last changes to production (or some customer complains that smth is wrong and this is even worst)

So I use this simple component in order to prevent such situations. Because because to be honest I faced with such problems not one time.



<?php

/**
 *
 * User: radzserg
 * Date: 3/6/12
 */
 
class App_Debug 
{

    /**
     * @static
     * @param $val
     * @return bool
     */
    public static function alwaysTrueForDebug($val)
    {
        if (APPLICATION_ENV == 'development') {
            return true;
        } else {
            return $val;
        }
    }

    public static function setVarForDebug(&$var, $value)
    {
        if (APPLICATION_ENV == 'development') {
            $var = $value;
        }
    }

}
// Now I can do it in this way 
if (App_Debug::alwaysTrueForDebug($service->canBeUpdated)) {
// or
App_Debug::setVarForDebug($user['state'], 'active') 

// first expression will be true and $user['state'] will be set 
// if only APPLICATION_ENV == 'development'


2012/03/30

Create copy of db strucutre. Simple yii command


we moved here http://radzserg.com/2012/03/30/create-copy-of-db-strucutre-simple-yii-command/

Continue to publish some of components that I usually use in different projects. Today I'll tell you about how easily create copy of db structure from production scheme to test db.

As usual I don't care about test DB until you starts your test and you get smth like table {my_funny_new_table} doesn't exist or smth like undefined field for {my_funny_table}. And you remember that you haven't updated structure for test DB.

So I use this simple command in my yii projects (and similar in not yii projects)

<?php

/**
 * Create test db
 * @author radzserg
 *
 */
class CreateTestDbCommand extends CConsoleCommand
{
    public function run($args)
    {
        echo "Creating test DB\n\n";

        $mainConfig = require dirname(__FILE__) . '/../config/main.php';
        $testConfig = require dirname(__FILE__) . '/../config/test.php';

        $mainDbConfig = $mainConfig['components']['db'];
        $testDbConfig = $testConfig['components']['db'];

        $m = array();
        preg_match('~mysql:host=(.+);dbname=(.+)~is', $testDbConfig['connectionString'], $m);
        $testDbConfig['host'] = $m[1];
        $testDbConfig['dbname'] = $m[2];

        $m = array();
        preg_match('~mysql:host=(.+);dbname=(.+)~is', $mainDbConfig['connectionString'], $m);
        $mainDbConfig['host'] = $m[1];
        $mainDbConfig['dbname'] = $m[2];

        $dumpPath = ROOT_PATH . '/protected/data/schema.sql';

        $mysqlTestDbCreator = new MysqlTestDbCreater($mainDbConfig, $testDbConfig, $dumpPath);
        $mysqlTestDbCreator->run();

        echo "Testing Db was successfully created\n\n";
    }
}

/**
 * Create test db as copy of real db
 *
 */
class MysqlTestDbCreater
{
    private $_productionConfig = NULL;
    private $_testConfig = NULL;
    private $_dumpPath = NULL;

    public function __construct($productionConfig, $testConfig, $dumpPath)
    {
        $this->_productionConfig = $productionConfig;
        $this->_testConfig = $testConfig;
        $this->_dumpPath = $dumpPath;
    }

    public function run()
    {
        $this->_makeDump();
        $this->_createTestDb();
    }

    protected function _createTestDb()
    {
        $config = $this->_testConfig;
        $loginOptions = "--user={$config['username']} --password={$config['password']} "
            . "--host={$config['host']}";

        // truncate database
        $command = "echo \"SET FOREIGN_KEY_CHECKS = 0;\"; mysqldump {$loginOptions} --add-drop-table --no-data {$config['dbname']} | grep ^DROP";
        $delete_commands = array();
        $return = false;
        exec($command, $delete_commands, $return);
        mysql_connect($config['host'], $config['username'], $config['password']);
        mysql_select_db($config['dbname']);
        foreach ($delete_commands as $command) {
            mysql_query($command);
        }    
            
        $command = "mysql {$config['dbname']} {$loginOptions} < "  . $this->_dumpPath;
        
        $output = array();
        $return = false;
        exec($command, $output, $return);
        $output = implode("\n", $output);
        echo $output; 
    }

    protected function _makeDump()
    {
        $config = $this->_productionConfig;
        $loginOptions = "--user={$config['username']} --password={$config['password']} "
            . "--host={$config['host']} {$config['dbname']}";
        $command = "mysqldump $loginOptions --opt --no-data > " . $this->_dumpPath;
  
        $output = array();
        $return = false;
        exec($command, $output, $return);
        $output = implode("\n", $output);
  echo $output;
    }
} 
good testing

2012/03/22

SQL Injections by binary search

we moved here http://radzserg.com/2012/03/22/sql-injections-by-binary-search/


Yesterday someone tried to hack one of the projects I'm working on. Yesterday I was busy all the day. When I started to work it was about 4 PM. And what did I see in error logs

2012-03-21T04:20:27-05:00 CRIT (2): Exception information:
Message: CURL error (http://myproject.com/path_to_api.php): The requested URL returned error: 500
Stack trace:
....
Request Parameters:
Array
(
    [id] => 11874362999999.9' or Length((select distinct column_name from `information_schema`.columns where table_schema=0x123(I changed real name) and table_name=0x321(I changed real name) limit 0,1))<8 and 'x'='x
)

At first I did not even gave special importance to this. I thought somebody try to check sql injection to urls. I knew that all he can see it's just an error page. I started to check logs.

Then I found such query
[id] => 11874362999999.9' or ascii(substring((select concat(system_settings.val,0x5e,system_settings.code,0x5e,system_settings.id) from {our_scheme_name}.system_settings limit 17,1),19,1))<118 and 'x'='x

And it looks really scary, because hacker already knew our DB structure. (Then I understood he has known it in first query, I was confusing by encoded names i.e. 0x123 hexadecimal number) I've immediately blocked him by IP. And started investigation.

All such errors was from one page, it became clear that we have a hole here. I started to check code, we did a api call. I went thought this call and what did I find in one very old class?

'select * from some_table where id=\''.$id.'\';

classic... :(

So how did it work? He added his query but he got only error page, without any valuable info. And let's see how it works

select *
from some_table
where id= 11874362999999.9'
    or Length((select distinct column_name from `information_schema`.columns where table_schema=0x123(I changed real name) and table_name=0x321(I changed real name) limit 0,1))<8 and 'x'='x'


if his query was succeed he got standard page. If it was failed he got error message. Then he use binary search, look at that query

ascii(substring((select concat(system_settings.val,0x5e,system_settings.code,0x5e,system_settings.id) from {our_scheme_name}.system_settings limit 17,1),19,1))<118

He picked up the values ​​character by character by binary search. Initially he got DB structure(looks like he used some automatic tool) then he started works with tables, making more clever requests.

To get DB structure you need to perform thousands of queries.

Fortunately we stopped him soon and closed the hole and he couldn't get valuable info. But it was very unpleasant.

But be careful! Those freaks don't sleep. Follow the classic simple rules.
- Do not use this shit  'select * from some_table where id=\''.$id.'\';
- Do not store passwords as plain text
- Analyze suspicious user behavior (I'm gonna wright about that last 2 months :))


And finally, I found this cheatsheet - read it even if you are familiar with most of the examples, in order to refresh the memories.
http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/