Category Archives: Uncategorized

Where I was last 3 months

3 months have passed since I wrote last time here. During this time I changed the 2 projects and  worked with many new technologies. Unfortunately I’m still really busy and just don’t have time and effort to write here 🙂 But still I have a very mall update.

Also I helped to consult LAMP group in softserve university for about last 3 months and they have graduated yesterday. Congrats guys. Hope it was fine.

c61e8f8613ee11e3b6c622000a1f92d1_7

Yii through Mysql perfomance

Today I’d like to tell about interesting question which caused me to think and to do some research.

Let’s look at example from official guide
http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through
Another nice relation type, moreover you can build it recursively. Group has users through roles. Group has comments through users, through roles. Very flexible and convenient.

But does anybody look inside built queries. Yii will build query like this. Even let’s imagine how I will build such query.

SELECT *
FROM role
    JOIN user ON user.id = role.user_id
    JOIN comment ON user.id = comment.user_id
WHERE  role.group_id = 123

And now let’s see what we get with yii

SELECT comment.*
FROM comment
    LEFT JOIN user ON user.id = comment.user_id
    LEFT JOIN role ON role.user_id = user.id
WHERE  role.group_id = 2870

It happens because yii build query recursively go down from first relation. A few WHY from my side.

  • why LEFT join
  • I expected to see everything viceversa
  • If user can belong to few roles – final result won’t be unique, i.e. we need to add DISTINCT. Yii will filter it by themself.

I’m really not a pro DB developer. So perhaps there are a lot of errors in my reasoning.

All in all let’s see… In terms of SQL this means that we take ALL rows from comment then trying to join user then trying to join role. Then will apply where condition to received data snapshot. Plus some internal server JOIN optimization. I used only mysql, so unfortunately can’t provide feedback about other servers. I thought so 🙂

Ok then I decieded to check my assumption. I created tables, fill some data. They have from mln – 6 mln rows, each one. And started to EXPLAIN

EXPLAIN SELECT *
FROM role
	JOIN user ON user.id = role.user_id
	JOIN comment ON user.id = comment.user_id
WHERE  role.group_id = 2897;

explain_human_queries

EXPLAIN SELECT comment.*
FROM comment
    LEFT JOIN user ON user.id = comment.user_id
    LEFT JOIN role ON role.user_id = user.id
WHERE  role.group_id = 2897

explain_yii_query

Heh… Mysql is smart enough to start from role table in both cases. Take Five. I think in most cases this will work. And you’ll get almost the same performance.

But some issues is still relevant
– why LEFT join
– is it smart to get unique data using yii
– does join server optimization will work for all supported db servers

Thoughts ?

Attempt to use ArrayObject as unified array

The problem was following in different part of my application we have to pass array of DNS. The problem that in one place it looks like that

array(‘ns1’ => ‘ns1.namserver.com’, ‘ns2’ => ‘ns2.nameserver.com’,’addr1′ => ‘222.22.2.2’, ‘addr2’ => ‘22.22.222.22’, .. ‘ns6’ => ”)
in other
array(1 => array(‘ns’ => ‘ns1.nameserver.com’, ‘addr’ = ‘22.22.22.22’), 2 => array(…), .. 6 => array(..) )
and others variations.

and it was quite confusing cause every time you have to check which variant of array you will get. It has been already beig volume of code using this. And I didn’t want to change it. So I started to think about class that could replace array. I want to implement __get __set start working with that mess and after half and hour I finally came to ArrayObject SPL class.

It seemed to me a very good solution. Cause all I’ve done was that

<?php

/**
 * Structure class to unify nses array
 * User: radzserg
 * Date: 6/26/12
 */
class Struct_Nses extends ArrayObject
{

    public function offsetSet($name, $value)
    {
        $this->_checkName($name);
        return parent::offsetSet($name, $value);
    }

    private function _checkName($name)
    {
        $availableNames = array('ns1', 'ns2', 'ns3', 'ns4', 'ns5', 'ns6',
            'addr1', 'addr2', 'addr3', 'addr4', 'addr5', 'addr6',
        );
        if (!in_array($name, $availableNames)) {
            throw new Exception("Undefined name {$name} for Struct_Nses");
        }
    }

}

It was an array that can check keys. Also I can check other errors like Stuct_Nses at least should have 2 nses, check ns format and others.

And everything was good until I ran tests. Epic fail 🙂 This is an object not an array. All operations like this

$nses = Struct_Nses()

$nses[‘ns1’] = ‘ns1.nameserver.com’;

$nses += array(‘ns2’ => ‘ns2.domainclub.com’) or array_merge($nses, array(‘ns2.domainclub.com’))

fail. Unfortunately I have to refuse of this cause I have to change a lot of important and working code.

But this is really cool feature and I think I’ll use such approach in future.

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)


/**
 * 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

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 logs2012-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))

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))

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))

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))

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/