[F] Duplicate session key in insert SQL with spider bot

[koziolek]

MyBB 1.4.2, PHP 5.2, MySQL 5.0

I can see multiple errors in error_log:

Code:

<error>
        <dateline>1222353771</dateline>
        <script></script>
        <line>0</line>
        <type>20</type>
        <friendly_type>MyBB SQL Error</friendly_type>
        <message>SQL Error: 1062 - Duplicate entry 'bot=1' for key 1
Query:
                        INSERT
                        INTO mybb_sessions (`sid`,`time`,`ip`,`location`,`userag
ent`,`location1`,`location2`,`nopermission`)
                        VALUES ('bot=1','1222353771','77.114.172.216','/showthread.php?tid=48623','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; Google
Wireless Transcoder;)','20','48623','0')
                </message>
</error>

<error>
        <dateline>1222353990</dateline>
        <script></script>
        <line>0</line>
        <type>20</type>
        <friendly_type>MyBB SQL Error</friendly_type>
        <message>SQL Error: 1062 - Duplicate entry 'bot=1' for key 1
Query:
                        INSERT
                        INTO mybb_sessions (`sid`,`time`,`ip`,`location`,`useragent`,`location1`,`location2`,`nopermission`​)
                        VALUES ('bot=1','1222353990','66.249.66.99','/showthread.php?tid=111496&amp;pid=837618','Mediapartners-Google','18','111496','0')
                </message>
</error>

It should not happen, because in session->create_session() we have a delete_query()... but it happens. I cannot reproduce it even when simulating GoogleBot with spoofed useragent.

---

Update:
Just reproduced with Firefox + spoofed agent (Alexa) + many tabs + many reloads. It happens on high load - multiple requests from one bot. Probably between the delete_query() in create_session() and insert_query() other thread inserts his own bot record.


Possible solution for MySQL - use INSERT IGNORE or REPLACE. In file db_mysql.php FIND:

PHP Code:

    function insert_query($table, $array)
    {
        if(!is_array($array))
        {
            return false;
        }
        $fields = "`".implode("`,`", array_keys($array))."`";
        $values = implode("','", $array);
        $this->write_query("
            INSERT 
            INTO {$this->table_prefix}{$table} (".$fields.") 
            VALUES ('".$values."')
        ");
        return $this->insert_id();
    } 


REPLACE WITH:

PHP Code:

    function insert_query($table, $array, $ignore = FALSE)
    {
        if(!is_array($array))
        {
            return false;
        }
        $fields = "`".implode("`,`", array_keys($array))."`";
        $values = implode("','", $array);
        // KK: multiple_bots_race_condition_in_inserting_sessions_bug
        $this->write_query("
            INSERT " . ($ignore ? ' IGNORE ' : '') . "
            INTO {$this->table_prefix}{$table} (".$fields.") 
            VALUES ('".$values."')
        ");
        return $this->insert_id();
    } 


In file class_session.php FIND:

PHP Code:

        $onlinedata['location1'] = intval($speciallocs['1']);
        $onlinedata['location2'] = intval($speciallocs['2']);
        $onlinedata['nopermission'] = 0;
        $db->insert_query("sessions", $onlinedata);
        $this->sid = $onlinedata['sid'];
        $this->uid = $onlinedata['uid']; 


REPLACE WITH:

PHP Code:

        $onlinedata['location1'] = intval($speciallocs['1']);
        $onlinedata['location2'] = intval($speciallocs['2']);
        $onlinedata['nopermission'] = 0;
        // KK: multiple_bots_race_condition_in_inserting_sessions_bug
        $db->insert_query("sessions", $onlinedata, TRUE);
        $this->sid = $onlinedata['sid'];
        $this->uid = $onlinedata['uid']; 


[Ryan Gordon]

using IGNORE isn't a very good fix. I'm not sure if it's supported in either MySQL versions either. Might want to look into REPLACE to fix it

[Yumi]

Could you try this instead?
In inc/class_session.php, find $db->insert_query and replace with $db->replace_query

Thanks!

[koziolek]

(09-27-2008 01:05 PM)ZiNgA BuRgA Wrote:  Could you try this instead?
In inc/class_session.php, find $db->insert_query and replace with $db->replace_query

Sure. It should resolve this problem (in a cleaner way - it do not require changing DB class - I did not saw replace_query() method). Thanks.

[Ryan Gordon]

Thank you for your bug report.

This bug has been fixed in our internal code repository. Please note that the problem will not be fixed here until these forums are updated.

With regards,
MyBB Group