MyBB Community Forums
[F] Duplicate session key in insert SQL with spider bot - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Community Archive (https://community.mybb.com/forum-106.html)
+--- Forum: Archived Forums (https://community.mybb.com/forum-143.html)
+---- Forum: Archived Development and Support (https://community.mybb.com/forum-155.html)
+----- Forum: Archived Bug Reports (https://community.mybb.com/forum-74.html)
+------ Forum: MyBB 1.4.2 (https://community.mybb.com/forum-100.html)
+------ Thread: [F] Duplicate session key in insert SQL with spider bot (/thread-38222.html)



[F] Duplicate session key in insert SQL with spider bot - koziolek - 2008-09-25

MyBB 1.4.2, PHP 5.2, MySQL 5.0

I can see multiple errors in error_log:
<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:
	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:
	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:
		$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:
		$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'];



RE: Duplicate session key in insert SQL with spider bot - Ryan Gordon - 2008-09-25

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


RE: Duplicate session key in insert SQL with spider bot - Yumi - 2008-09-27

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

Thanks!


RE: Duplicate session key in insert SQL with spider bot - koziolek - 2008-09-27

(2008-09-27, 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.


[F] Duplicate session key in insert SQL with spider bot - Ryan Gordon - 2008-09-29

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