Unexpected double items in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Unexpected double items in database

Post 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...
Last edited by WaldoMonster on Tue Nov 06, 2007 7:27 pm, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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';
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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)"
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

I will indeed go back to a uniqid based solution.
Thanks,

Willem
Post Reply