Page 1 of 1

Unexpected double items in database

Posted: Mon Nov 05, 2007 3:03 pm
by WaldoMonster
The code below is the only place in the script where an INSERT is done for the configuration_session table.
Some how there get double records in the database with the same ip, user_agent, sign and seed except the creation time is different and sometimes the counter is different.
The strange thing is that most of the time there are no double items.
I have tried many things but nothing helped, I have tried out the script with:
  • PHP 5.23
  • PHP 5.24
  • PHP 5.25 (Nov 03, 2007 snapshot)
  • PHP 5.30 (Nov 03, 2007 snapshot)
  • Without PHP eAccelerator
  • Without PHP zLIB compression
  • MySQL 4.1.22
  • MySQL 5.0.45
Is there something wrong with the script?
Any suggestion?

Code: Select all

//  +------------------------------------------------------------------------+
//  | Logout                                                                 |
//  +------------------------------------------------------------------------+
function logout()
{
global $cfg, $db;

$sid            = cookie('netjukebox_sid');
$sign           = RandomKey(36);
$seed2          = RandomKey(64);

$query = mysqli_query($db, 'SELECT sid FROM configuration_session WHERE sid = "' . mysqli_real_escape_string($db, $sid) . '"');
if (mysqli_fetch_row($query))
    {
    // Update current session
    mysqli_query($db, 'UPDATE configuration_session SET
        logged_in       = 0,
        ip              = "' . mysqli_real_escape_string($db, $_SERVER['REMOTE_ADDR']) . '",
        user_agent      = "' . mysqli_real_escape_string($db, $_SERVER['HTTP_USER_AGENT']) . '",
        sign            = "' . mysqli_real_escape_string($db, $sign) . '",
        seed            = "' . mysqli_real_escape_string($db, $seed2) . '"
        WHERE sid       = "' . mysqli_real_escape_string($db, $sid) . '"');
    }
else
    {
    // Create new session
    $sid = RandomKey(36);

    mysqli_query($db, 'INSERT INTO configuration_session (logged_in, create_time, ip, user_agent, sid, sign, seed) VALUES (
        0,
        ' . (int) time() . ',
        "' . mysqli_real_escape_string($db, $_SERVER['REMOTE_ADDR']) . '",
        "' . mysqli_real_escape_string($db, $_SERVER['HTTP_USER_AGENT']) . '",
        "' . mysqli_real_escape_string($db, $sid) . '",
        "' . mysqli_real_escape_string($db, $sign) . '",
        "' . mysqli_real_escape_string($db, $seed2) . '")');

    setcookie('netjukebox_sid', $sid, time() + 3600 * 24 * 1095, '', '', '', true);
    @ob_flush();
    flush();
    }

// Rest of the login script...

Posted: Tue Nov 06, 2007 3:21 am
by CoderGoblin
mysqli_fetch_row returns false only if the query is wrong/invalid. If no rows are returned it is still true. You may want to look at mysqli_num_rows.

Posted: Tue Nov 06, 2007 12:35 pm
by WaldoMonster
I don't think that this is the problem, from the PHP manual:
mysqli_fetch_row() returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in result set
I have isolated the mysqli_fetch_row() part, but this is working perfectly:

Code: Select all

$sid = '6hqi3sicez2tiesittr5rk6whjann1f4alffx97w';
query = mysqli_query($db, 'SELECT sid FROM configuration_session WHERE sid = "' . mysqli_real_escape_string($db, $sid) . '"');
if (mysqli_fetch_row($query))   echo 'Found in database';
else                            echo 'NOT found in database';

Posted: Tue Nov 06, 2007 12:44 pm
by Weirdan
under some circumstances mozilla browsers are known to issue double requests. Check your access logs to make sure it's not this issue.

Posted: Tue Nov 06, 2007 1:01 pm
by WaldoMonster
Weirdan wrote:under some circumstances mozilla browsers are known to issue double requests. Check your access logs to make sure it's not this issue.
I will check that out as soon as there is a double item in the database again.
(I have cleared that section of the database)
Still strange even with a double request the $sign and $seed should be always different because it is randomly generated.
I will post the findings when it is available.

Posted: Wed Nov 07, 2007 4:32 am
by WaldoMonster
I have a double item in the session database again.
Every data is the same except the creation time:

Code: Select all

1194353942 => Tue, 06 Nov 2007 13:59:02 +0100
1194405220 => Wed, 07 Nov 2007 04:13:40 +0100
Strange that the first time is from a day before where there was no traffic from that ip address.

Say that there is generated a same sid as the day before from another user.
Still than that sid in the database will be updated and not duplicated.

What also is to see from the log file is that the first login attempt failed.
This can be because the default password has changed or another reason.

At the moment I have know idea what the problem is.
Is it somehow possible that the sequences of lets say 3*40 random numbers are the same as a day before?

Code: Select all

201.255.xxx.xxx - - [07/Nov/2007:04:13:40 +0100] "GET / HTTP/1.1" 200 2186 "http://pajhome.org.uk/crypt/md5/auth.html" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:41 +0100] "GET /skin/default/styles.css HTTP/1.1" 200 6799 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:41 +0100] "GET /javascript/initialize.js HTTP/1.1" 200 4803 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:42 +0100] "GET /javascript/overlib.js HTTP/1.1" 200 50734 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:47 +0100] "GET /javascript/overlib_cssstyle.js HTTP/1.1" 200 9007 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:48 +0100] "GET /javascript/sha1.js HTTP/1.1" 200 5752 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:49 +0100] "GET /skin/default/menu_media_on.gif HTTP/1.1" 200 1271 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:50 +0100] "GET /skin/default/menu_top.gif HTTP/1.1" 200 61 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:50 +0100] "GET /skin/default/menu_favorites_off.gif HTTP/1.1" 200 1485 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:50 +0100] "GET /skin/default/menu_top_left.gif HTTP/1.1" 200 63 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:50 +0100] "GET /skin/default/menu_playlist_off.gif HTTP/1.1" 200 1485 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_middle_left.gif HTTP/1.1" 200 169 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_config_off.gif HTTP/1.1" 200 1363 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_middle_right.gif HTTP/1.1" 200 46 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_top_right.gif HTTP/1.1" 200 48 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_netjukebox.gif HTTP/1.1" 200 1201 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:51 +0100] "GET /skin/default/menu_back.gif HTTP/1.1" 200 240 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:57 +0100] "GET /javascript.php?action=LoginStage1&username=anonymous&sign=fcfb7967e755cc7e55223571a37fe8a1be17a2a3 HTTP/1.1" 200 165 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:13:58 +0100] "POST /index.php HTTP/1.1" 200 2186 "http://live.netjukebox.nl/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:14:13 +0100] "GET /javascript.php?action=LoginStage1&username=anonymous&sign=c8b1bd4a3cca5e18628ef7ca8a364d98b2db30fb HTTP/1.1" 200 167 "http://live.netjukebox.nl/index.php" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:14:14 +0100] "POST /index.php HTTP/1.1" 200 2476 "http://live.netjukebox.nl/index.php" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
201.255.xxx.xxx - - [07/Nov/2007:04:14:15 +0100] "GET /javascript.php?action=SuggestView1&artist= HTTP/1.1" 200 55 "http://live.netjukebox.nl/index.php" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

Posted: Sat Nov 10, 2007 5:29 am
by WaldoMonster
The RandomKey() function is based on mt_rand.
When I based the RandomKey on uniqid there where no double items in the database.
I will test it on a longer period to be certain that this was the problem.

Posted: Sat Nov 10, 2007 6:40 am
by Weirdan
uniqid is based on current time while mt_rand is a mere random number generator. I'd suggest you to move back to uniqid solution.

Posted: Sun Nov 11, 2007 12:16 pm
by WaldoMonster
I will indeed go back to a uniqid based solution.
Thanks,

Willem