User tracking mod

[BlessIsaacola]

I implemented this code change from previous post:

/* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

    $wo_last_page_url = substr($wo_last_page_url, 0, 253);
    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($wo_last_page_url, -1), '\\') !== false) {
        $wo_last_page_url = substr($wo_last_page_url, 0, -1);    
    }

    $referer_url = substr($referer_url, 0, 253);

    while (strpos(substr($referer_url, -1), '\\') !== false) {
        $referer_url = substr($referer_url, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/ 

Monitoring the log file and will report back if anything shows up. So far, so good. Thanks for the update.

[mc12345678]

I implemented this code change from previous post:

/* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

    $wo_last_page_url = substr($wo_last_page_url, 0, 253);
    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($wo_last_page_url, -1), '\\') !== false) {
        $wo_last_page_url = substr($wo_last_page_url, 0, -1);    
    }

    $referer_url = substr($referer_url, 0, 253);

    while (strpos(substr($referer_url, -1), '\\') !== false) {
        $referer_url = substr($referer_url, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/ 

Monitoring the log file and will report back if anything shows up. So far, so good. Thanks for the update.

Thanks for your patience, and my apologies for the mixup that I thought both $wo_last_page_url and $referer_url were being truncated, when it was only the first one that actually was and really the second one is the one more likely to be long. (internal links are not likely to be 254 characters, not to say that they can't be.)

Hopefully the "lost" data does not cause heartache, otherwise will have to implement a way to capture the entire string, probably in parts so that it can be formatted properly and then stored in the database. A thought is to split the URL up into parts short enough that if a majority of the characters needed to be escaped that the resulting string would still be parseable to add the slashes, then recombine the entire string to go into the table. Thing is at the moment, the table is setup with a finite length for the string, and therefore would either need the string length increased, or to split the referrer_url field off into it's own table so that the page load associated with referer_url can effectively have any length of string desired. (Remember, these things take up space on the server and each read/write to the database may also be counted by the host, so this all would be something of a concern, but doable.)

And thank you for being the guinea pig.. :) At least the one reporting back on the issues. :)

Once you have had some good runtime, I will post the code with the above incorporated, also will post the two versions that were discussed earlier, one with a new geoIP.dat and geoIP.inc file with possibly new smaller flags, and one that just has the code and a reference to how to get/where to store/how to name the additional data that is captured in the other file. Still trying to think of a name for the files, maybe one that has the word full and one that has CodeOnly or something like that... Still working on that part. :)

[mc12345678]

To prevent the early demise of the previous edit, see below. As I said it is unlikely for an internal link to be greater than 254 characters; however, the original code went a step further and considered 128 as the maximum (see the declaration for the table and the current table length for the last url field). This is one of the downfalls of hard coding something that is dependent on something else. I'd like to implement a define that will the first time pull from the database, and thenon the next run through don't load it again as it would be defined. That would then be used to truncate the strings to the length of the table's field(s).

Towards the end of YOURSTORE/includes/functions/extra_functions/user_tracking.php

have the code look something like this:

    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

    $wo_last_page_url = substr($wo_last_page_url, 0, 127);
    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($wo_last_page_url, -1), '\\') !== false) {
        $wo_last_page_url = substr($wo_last_page_url, 0, -1);    
    }

    $referer_url = substr($referer_url, 0, 253);

    while (strpos(substr($referer_url, -1), '\\') !== false) {
        $referer_url = substr($referer_url, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

[BlessIsaacola]

Thanks for your patience, and my apologies for the mixup that I thought both $wo_last_page_url and $referer_url were being truncated, when it was only the first one that actually was and really the second one is the one more likely to be long. (internal links are not likely to be 254 characters, not to say that they can't be.)

Hopefully the "lost" data does not cause heartache, otherwise will have to implement a way to capture the entire string, probably in parts so that it can be formatted properly and then stored in the database. A thought is to split the URL up into parts short enough that if a majority of the characters needed to be escaped that the resulting string would still be parseable to add the slashes, then recombine the entire string to go into the table. Thing is at the moment, the table is setup with a finite length for the string, and therefore would either need the string length increased, or to split the referrer_url field off into it's own table so that the page load associated with referer_url can effectively have any length of string desired. (Remember, these things take up space on the server and each read/write to the database may also be counted by the host, so this all would be something of a concern, but doable.)

And thank you for being the guinea pig.. :) At least the one reporting back on the issues. :)

Once you have had some good runtime, I will post the code with the above incorporated, also will post the two versions that were discussed earlier, one with a new geoIP.dat and geoIP.inc file with possibly new smaller flags, and one that just has the code and a reference to how to get/where to store/how to name the additional data that is captured in the other file. Still trying to think of a name for the files, maybe one that has the word full and one that has CodeOnly or something like that... Still working on that part. :)

Thank you so much for all your work on this. I didn't experience any issue until later today. This is what was in the log:

[03-Dec-2013 12:17:46 America/New_York] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's+Science+Club%3A+Moonscope+%26+Sky+Gazers+Activity+Journal&qscrl=1&tbm=shop&tb s' at line 1 :: insert into user_tracking (customer_id, full_name, session_id, ip_address, time_entry, time_last_click, last_page_url, referer_url, page_desc, customers_host_address) values ('0', 'Guest', 'af49e4a11455e29d1459dfe2f76dff34', '98.254.12.98', '1386091066', '1386091066', '/index.php?main_page=product_info&products_id=86403&gclid=CObKws3HlLsCFUtp7Aodgl0 AGQ', 'https://www.google.com/webhp?sourceid=toolbar-instant&hl=en&ion=1&qscrl=1&rlz=1T4ADFA_enUS478US479#hl=en&q=Nancy+B's+Science+C lub%3A+Moonscope+%26+Sky+Gazers+Activity+Journal&qscrl=1&tbm=shop&tbs=vw:l', 'Nancy B\'s Science Club MoonScope and Star Gazer\'s Activity Jo', 'OFFICE_IP_TO_HOST_ADDRESS') in /includes/classes/db/mysql/query_factory.php on line 120

IMHO, I think you should keep the mod into one package. This thing have been around for ages and nobody has complain about the size being an issue. Unless someone is on dial up I don't really think the size is that big of an issue. It will simply end up causing more headache which is not worth the marginal gain of small file size.

[BlessIsaacola]

To prevent the early demise of the previous edit, see below. As I said it is unlikely for an internal link to be greater than 254 characters; however, the original code went a step further and considered 128 as the maximum (see the declaration for the table and the current table length for the last url field). This is one of the downfalls of hard coding something that is dependent on something else. I'd like to implement a define that will the first time pull from the database, and thenon the next run through don't load it again as it would be defined. That would then be used to truncate the strings to the length of the table's field(s).

Towards the end of YOURSTORE/includes/functions/extra_functions/user_tracking.php

have the code look something like this:

    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

    $wo_last_page_url = substr($wo_last_page_url, 0, 127);
    /* Start - User tracking v1.4.3b modification*/
    while (strpos(substr($wo_last_page_url, -1), '\\') !== false) {
        $wo_last_page_url = substr($wo_last_page_url, 0, -1);    
    }

    $referer_url = substr($referer_url, 0, 253);

    while (strpos(substr($referer_url, -1), '\\') !== false) {
        $referer_url = substr($referer_url, 0, -1);    
    }
    /* End - User tracking v1.4.3b modification*/

I applied the above and will report back if I experienced any issues. Thanks!

[gilby]

Thanks for your patience, and my apologies for the mixup that I thought both $wo_last_page_url and $referer_url were being truncated, when it was only the first one that actually was and really the second one is the one more likely to be long. (internal links are not likely to be 254 characters, not to say that they can't be.)

Hopefully the "lost" data does not cause heartache, otherwise will have to implement a way to capture the entire string, probably in parts so that it can be formatted properly and then stored in the database. A thought is to split the URL up into parts short enough that if a majority of the characters needed to be escaped that the resulting string would still be parseable to add the slashes, then recombine the entire string to go into the table. Thing is at the moment, the table is setup with a finite length for the string, and therefore would either need the string length increased, or to split the referrer_url field off into it's own table so that the page load associated with referer_url can effectively have any length of string desired. (Remember, these things take up space on the server and each read/write to the database may also be counted by the host, so this all would be something of a concern, but doable.)

I wonder if changing from "varchar(256)" to "text" would work?
It would certainly sort out url length problems.
And as this is not accessed a lot, any slowdown on access would probably not be noticeable
Just thinking out loud here!

[mc12345678]

I wonder if changing from "varchar(256)" to "text" would work?
It would certainly sort out url length problems.
And as this is not accessed a lot, any slowdown on access would probably not be noticeable
Just thinking out loud here!

I'm sorry that I'm not in front of a computer to do a knowledgeable search for the use of varchar(256), but I am assuming that is in reference to the table construct. Ie, if the table had a "limitless" text field then wouldn't have to worry about truncation. While this is true, it would go against one of the factors originally considered for this plugin: to maintain the referencing uri/last page looked at while minimizing database storage volume. For those that don't have a concern about database storage capacity a change such as this would allow maintaining the uri unchanged: however, also wouldn't directly correct the current problem at hand.

The current problem is cleaning up the information so that when sent to SQL doesn't include an unescaped single quote. Apparently addslashes() when provided the original information did not correct the ' that led up to +B' I began a search for a more appropriate function(s) to handle this situation. Expansion of the data to include a string instead of varchar. Trying not to give a short answer by truncating the string at the end of the host URI but provide some information that might still be relevant.

[mc12345678]

Sorry for what will become a long post... I have submitted version 1.5 of User Tracking... This version includes a rewrite of the data capture portion of UT. This is the portion that Blessisaacola has been describing as an issue. This does not address the other portion of data capture desired to be changed, but specifically the potential of information being supplied to UT that would cause errors to be captured. While I could possibly list individual changes made, the majority of the functional changes are in the catalog side of the program. First is the list of information submitted tonight for review (probably too late to be posted this weekend).

The below code partially addresses item one below; however, it is the most significant revision and should be implemented at one's earliest convenience. Items 2 and 3 can be found using links previously posted in this forum. Item 4 was simply to bring the code more into ZC standard.

Updated 12/08/2013 Version 1.5
1. Corrected a long standing issue with capturing data from the URL. The URLs are still truncated; however, they are now sent through for cleansing using Zen Carts db class prior to being sent to the SQL. This should reduce the occurrences of/prevent the SQL statement failure.
2. Incorporated a new GeoIP.dat file (recent as of: Dec-05-2013. Similar updates can be obtained from: http://geolite.maxmind.com/download/...y/GeoIP.dat.gz.
3. Incorporated a new geoip.inc file to accompany the GeoIP.dat file. The geoip.inc file can be obtained from: https://raw.github.com/maxmind/geoip.../src/geoip.inc.
4. Incorporated zen_href_link to generate links.

UPDATING INSTRUCTIONS:
For SQL statements: If updating from version 1.4.2, then: Use the UPDATE_VER.sql after any other SQL statements (This will update the version number of User Tracking)
otherwise same instructions as applicable from the 11/10/2013 update.

Files Updated from Version 1.4.4:
includes/functions/extra_functions/user_tracking.php
YOUR_ADMIN/user_tracking.php
YOUR_ADMIN/user_tracking_config.php
YOUR_ADMIN/includes/GeoIP.dat
YOUR_ADMIN/includes/geoip.inc
YOUR_ADMIN/includes/functions/extra_functions/user_tracking.php

Added UPDATE_VER.sql

Below is the entire code of shop/includes/functions/extra_functions/user_tracking.php which should be used to replace the previous version of this function. Update of the other files is not considered quite as crucial. Do not copy this code as written to any other file, the admin version of this is different.

<?php
//
// +----------------------------------------------------------------------+
// |zen-cart Open Source E-commerce                                       |
// +----------------------------------------------------------------------+
// | Copyright (c) 2003 The zen-cart developers                           |
// |                                                                      |
// | http://www.zen-cart.com/index.php                                    |
// |                                                                      |
// | Portions Copyright (c) 2003 osCommerce                               |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.0 of the GPL license,       |
// | that is bundled with this package in the file LICENSE, and is        |
// | available through the world-wide-web at the following url:           |
// | http://www.zen-cart.com/license/2_0.txt.                             |
// | If you did not receive a copy of the zen-cart license and are unable |
// | to obtain it through the world-wide-web, please send a note to       |
// | [email protected] so we can mail you a copy immediately.          |
// +----------------------------------------------------------------------+
//  $Id: usertracking 2004-12-1 [email protected] http://open-operations.com
function zen_update_user_tracking()
  {
    global $db;
    global $customer_id, $languages_id, $_GET;
    
    foreach(explode(",", CONFIG_USER_TRACKING_EXCLUDED) as $skip_ip) {
    $skip_tracking[trim($skip_ip)] = 1;
    }
    if ($_SESSION['customer_id']) {
      $wo_customer_id = $customer_id;
    $customer = $db->Execute("select customers_firstname, customers_lastname from " . TABLE_CUSTOMERS . " where customers_id = '" . $_SESSION['customer_id'] . "'");
    $wo_full_name = $db->prepare_input($customer->fields['customers_firstname'] . ' ' . $customer->fields['customers_lastname']);
    }
    else {
    $wo_customer_id = '';
    $wo_full_name = $db->prepare_input('Guest');
    }
    $wo_session_id = $db->prepare_input(zen_session_id());
    $wo_ip_address = getenv('REMOTE_ADDR');
    $wo_last_page_url = addslashes(getenv('REQUEST_URI'));
    $referer_url = ($_SERVER['HTTP_REFERER'] == '') ?  $wo_last_page_url : $_SERVER['HTTP_REFERER'];
        if (($_GET['products_id'] || $_GET['cPath'])) {
                if ($_GET['cPath'] && ZEN_CONFIG_SHOW_USER_TRACKING_CATEGORY == 'true') {   // JTD:12/04/06 - Woody feature request
                        $cPath = $_GET['cPath'];
                        $cPath_array = zen_parse_category_path($cPath);
                        $cPath = implode('_', $cPath_array);
                        $current_category_id = $cPath_array[(sizeof($cPath_array)-1)];
                        $page_desc_values = $db->Execute("select categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where categories_id = '" . $current_category_id . "'");
                        $page_desc = $db->prepare_input($page_desc_values->fields['categories_name'] . '&nbsp;-&nbsp;');
                }
        if ($_GET['products_id']) {
                $page_desc_values = $db->Execute("select products_name from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . $_GET['products_id'] . "' and language_id = '" . $_SESSION['languages_id'] . "'");
                $page_desc .= $db->prepare_input($page_desc_values->fields['products_name']);
            }
        }
        else {
                $page_desc = $db->prepare_input(HEADING_TITLE);
                if ($page_desc == "HEADING_TITLE")
                        $page_desc = $db->prepare_input(NAVBAR_TITLE);
        }
        $current_time = $db->prepare_input(time());
       $current_time = $current_time;
    if ($skip_tracking[$wo_ip_address] != 1) {
    // JTD:05/15/06 - Query bug fixes for mySQL 5.x
        $wo_ip_address = $db->prepare_input($wo_ip_address);
        
        $cust_id = $_SESSION['customer_id'];
        if ($cust_id == NULL) {
            $cust_id = 0;
        }

    $cust_id = $db->prepare_input($cust_id);
    
     $customers_host_address = $_SESSION['customers_host_address']; // JTD:11/27/06 - added host address support
    $customers_host_address = $db->prepare_input($customers_host_address);

    $page_desc = substr($page_desc, 0, 63);
    /* Start - User tracking v1.4.3b modification*/
    /*while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }*/
    /* End - User tracking v1.4.3b modification*/
    $wo_last_page_url = $db->prepare_input($wo_last_page_url);
    
    $wo_last_page_url = substr($wo_last_page_url, 0, 125);
    /* Start - User tracking v1.4.3b modification*/
    /*while (strpos(substr($wo_last_page_url, -1), '\\') !== false) {
        $wo_last_page_url = substr($wo_last_page_url, 0, -1);    
    }*/

    $referer_url = $db->prepare_input($referer_url);

    $referer_url = substr($referer_url, 0, 253);

    /*while (strpos(substr($referer_url, -1), '\\') !== false) {
        $referer_url = substr($referer_url, 0, -1);    
    }*/
    $user_track_array = array();
    $user_track_array[] = array('fieldName'=>'customer_id', 'value'=>$cust_id, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'full_name', 'value'=>$wo_full_name, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'session_id', 'value'=>$wo_session_id, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'ip_address', 'value'=>$wo_ip_address, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'time_entry', 'value'=>$current_time, 'type'=>'date');
    $user_track_array[] = array('fieldName'=>'time_last_click', 'value'=>$current_time, 'type'=>'date');
    $user_track_array[] = array('fieldName'=>'last_page_url', 'value'=>$wo_last_page_url, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'referer_url', 'value'=>$referer_url, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'page_desc', 'value'=>$page_desc, 'type'=>'string');
    $user_track_array[] = array('fieldName'=>'customers_host_address', 'value'=>$customers_host_address, 'type'=>'string');
    
    /* End - User tracking v1.4.3b modification*/
    $db->perform(TABLE_USER_TRACKING, $user_track_array);
//    $db->Execute("insert into " . TABLE_USER_TRACKING . " (customer_id, full_name, session_id, ip_address, time_entry, time_last_click, last_page_url, referer_url, page_desc, customers_host_address) values ('" . $cust_id . "', '" . $wo_full_name . "', '" . $wo_session_id . "', '" . $wo_ip_address . "', '" . $current_time . "', '" . $current_time . "', '" . $wo_last_page_url . "', '" . $referer_url . "', '" . $page_desc . "', '" . $customers_host_address . "')");
    }
  }
?>

[mc12345678]

And just when I thought that the plugin was working properly through and through I received the following in a private message:

After a few complaints from some of our customers who installed your latest version, your latest release seems to fail on PHP 5.4. Not sure the issue but the tracker works but for some reason the config page is blank. We have not had a chance to look into this deeply to figure the reason yet, even though all tables are inserted into the database as they should but maybe you may want to just in case it is something out of the ordinary.

I have identified the following "quick" fix for any affected by it. My understanding is that the configuration area does not work on a PHP 5.4 system, but everything else about the plug-in does. In further review of the files and the history of their generation, it appears that developers thought that a unique configuration file was necessary instead of the ZenCart default configuration system/path. Unfortunately, the differences were observed in the latest review/update, but were not incorporated because there was no reported issue with it and the observed behavior was that after making the first selection the configuration file seemed to work correctly. At any rate, the following SQL will revert the User Tracking Config selection to the Configuration area and will default to using the ZC file used for typical configuration. A future update installation file will correct/address this appropriately.

Running the following SQL inside of the admin area after installation should restore configuration functionality for those that have been receiving a "blank" screen when selecting the User Tracking Config menu option. The effect is that the User Tracking Config menu option will be moved to the Configuration menu area and that the ZC default configuration menu routine will be used instead of a User Tracking Configuration menu routine.

SELECT @UserTrackgID := configuration_group_id 
FROM configuration_group where configuration_group_title LIKE '%User Tracking Config%';

UPDATE admin_pages SET `page_params`= CONCAT('gID=', @UserTrackgID), `menu_key`='configuration', `main_page`='FILENAME_CONFIGURATION' WHERE `page_key`='UserTrackingConfig';

[mc12345678]

A new update is expected soon. I've incorporated IP Blocker for Zen Cart 1.5.0+ into the code of user tracking. Have also cleaned up the html a little, and changed most of the gets into posts. Works a little differently than before, but still works, with ot without IP Blocker. Trying to see if can still track access to the special blocked response page or similar.think just need to add the notifier code to the appropriate file(s)/location(s)