User tracking mod

mc12345678:

Yeah, took a second tofigure out, but look at the last character of your page description field that is just before the host ip address. Looks like this: 'Discovery and Exploration##-##Educational Insights You', well, the \ just before the single parenthesis is an escape code sequence, telling SQL to treat the ' as a character in the string rather than the end of a string. So the next ' becomes the end, the OFFICE_IP_TO_HOST_ADDRESS is becomes a misplaced statement and then finally the last ' is treated as the opening to a new string that is not properly finalized which causes the error you are seeing. I think that if you were to visit thatpage yourself, you would cause the same issue, it just happens that someone/something got there first.

Btw, that is an example also of what I was saying of identifying a malformed SQL statement, doing something about it, and letting the operator know it happened and hopefully in a way that would be unique to UT.

Basically the page description for that product should not end with a backwards slash. Not sure why it would, to begin with...

Thank you so much for looking into this. I think the problem is with the Zen Lightbox on our site. If you look at this page: http://www.clevershoppers.com//index.php?main_page=product_reviews&cPath=388_408&products_id=13478 you will notice the garbage on top of the product image. If I turn off Zen Lightbox the problem is not there. As you suggested I went to the actual product page: http://www.clevershoppers.com/index.php?main_page=product_info&products_id=13478 and I did not see any problem with the description when I view it normally as well as looking at the source. I will try to solve the Zen Lightbox issue first and then see if I see more errors in the log.

Well, I resolved my original issue by using the fix provided via the Zen Lightbox thread. Now I am dealing with something else and perhaps it doesn't have anything to do with User Tracking but since it's the mod revealing the problem I am starting here. Here is the error message:> [17-Nov-2013 04:41:17 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 'OFFICE_IP_TO_HOST_ADDRESS')' 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', '', '23.22.202.186', '1384681277', '1384681277', '/?main_page=product_info&cPath=12_170&products_id=79860', '/?main_page=product_info&cPath=12_170&products_id=79860', 'Animation - Theo (Teaching Children God's Word) God', 'OFFICE_IP_TO_HOST_ADDRESS') in /includes/classes/db/mysql/query_factory.php on line 120
Here is the link http://www.clevershoppers.com/index.php?main_page=product_info&products_id=79860 to the product in question. You will notice in the title (Theo (Teaching Children God's Word) God's Love DVD 2011) there are two apostrophes. I believe the ' in the title is what's causing the problem. It would appear User Tracking is not properly escaping the single quote (') in the title before trying to post it to the database. Zen Cart seems to handle this just fine on the site but not sure how to deal with this issue.

BlessIsaacola:

Well, I resolved my original issue by using the fix provided via the Zen Lightbox thread. Now I am dealing with something else and perhaps it doesn't have anything to do with User Tracking but since it's the mod revealing the problem I am starting here. Here is the error message:
Here is the link http://www.clevershoppers.com/index.php?main_page=product_info&products_id=79860 to the product in question. You will notice in the title (Theo (Teaching Children God's Word) God's Love DVD 2011) there are two apostrophes. I believe the ' in the title is what's causing the problem. It would appear User Tracking is not properly escaping the single quote (') in the title before trying to post it to the database. Zen Cart seems to handle this just fine on the site but not sure how to deal with this issue.

So the field that is currently causing the problem is the page description (which I thought would be different than the page title, but I can look further into that). You are correct that UT doesn't (yet) filter the data to remove the converted ' (made into ' ) prior to posting to the UT table database. Typically I have seen that only the last_page_url or refferer_page_url would throw that type of error because of attempts by malicious visitors to inject SQL code. It looks like the review/change will need to include the characters of other fields as well, to include some form of substitution of the last character in the string as applicable.

BlessIsaacola:

Can you please tell me if this update is suppose to fix the issue where User Shopping Cart (when viewing user tracking) is the same across multiple visitors? Thanks!

mc12345678:

While aware and familiar with the results of an item being in the cart causing visual chaos, I have not tackled that one yet. I realize that there is some incorrect logic that predates my changes to the plug-in, but if I remember correctly that area of the code is integrated with the area that does the logging of the visit, which is why I have put a bit of a precedence on squaring away the logging/capturing of data first. I once had a bot add something like 4 million dollars of items to it's cart and while looking at UT you could imagine my surprise that a single "user" wanted to buy that much from our little organization. Looked into it, and ah well just some computer adding stuff to the cart that it wasn't going to buy.

Anyways, as much as I would like to straighten the cart issue out, I need to stick with the plan to address capture of the data first which may lead to an easy fix on the cart issue.

So was thinking about the above thiss morning. I have a few ideas associated with it.

1. Sessions have a limited life. As such, UT as currently constructed is only setup to reoprt carts that are active.
2. Storing information related to all carts created would utilize more database capability which could be an issue forfor some users having a limited capacity. Enabling this capability would require an additional table in the database (to maintain correct table form) and also a control to prevent populating the table. Considering the default would be not to populate the table and that the table would be created in the code of usage rather than the install/upgrade sql.
3. Current carts are available through SQL inspection. UT apparently uses the version of cart identification similar to who's online in that it presents all current carts whenever/wherever cart information is displayed. At the point of display, there is sufficient information available to pull only the cart associated with that session. This information is currently not being used to provide just that cart info. Adding in the code to pull the current cart content for an active cart should be available from who's online with modification to apply only to the current session of the record being displayed.
4. Logic/display of cart information would need to consider gathering "all" info from 2 and current information of 3, but also take into account that on each page load the contents of the cart for the session being tracked such that as long as the cart's session is active the tracked cart is updated to reflect the current contents in both thetable of 2 and the active display of 3. Once the cart's session is inactive/has expired, then the data displayed identifies that this is basically an abandoned cart. This is not to take the place of any abandoned cart plugin, though may duplicate effort. This method would increase traffic withthe database, more so if option 2 above is active.
5. This may be possible to partially implement without correcting the tracking method of spiders/users as described above as it would affect only the display of session/cart information associated with existing variables and data that is readily available. Subsequent mod to address the tracking/identification of spiders may affect this area; however, that would just be part of updating the code to address any other changes/ensure continuity.

Ideas?

mc12345678:

So the field that is currently causing the problem is the page description (which I thought would be different than the page title, but I can look further into that). You are correct that UT doesn't (yet) filter the data to remove the converted ' (made into ' ) prior to posting to the UT table database. Typically I have seen that only the last_page_url or refferer_page_url would throw that type of error because of attempts by malicious visitors to inject SQL code. It looks like the review/change will need to include the characters of other fields as well, to include some form of substitution of the last character in the string as applicable.
Thank you for your response. I think the main issue is dealing with special characters like apostrophe which can actually happen in several places. Like I said, Zen Cart handles the situation just fine. For now, I am leaving the mod installed because the value outweighs the inconvenience of deleting the log entries. I will also consider fixing the special characters where I can to minimize my issue. The only problem is when I have used html entities in the titles before, it resulted in other problems.

BlessIsaacola:

Thank you for your response. I think the main issue is dealing with special characters like apostrophe which can actually happen in several places. Like I said, Zen Cart handles the situation just fine. For now, I am leaving the mod installed because the value outweighs the inconvenience of deleting the log entries. I will also consider fixing the special characters where I can to minimize my issue. The only problem is when I have used html entities in the titles before, it resulted in other problems.

As usual, continued to consider this issue. Will need to look at how the variable is used downstream, what was it page_key?, but I'm thinking that it would be okay to truncate it by one character if the last character is . See ZC is properly formatting the string to prevent a problem, but if remember correctly the field is set to a specific length upon construction of the table. I'm not sure why it is the length that it is, but, also thinking that it's okay to be as short as it is. I will have to assess that before making a change to the table length, but if there is no effect on the program by truncating that and other fields that could potentially have an escaped character, then the fix will be to check the last character in the string if it is \ then delete it. So, generally speaking there is no reason for you to modify the content as you have described, this plug-in should be modified to support standard ZC functionality.

I do think that part of the reason that it is as "short" as it is, is because UT is collecting additional data and storing it without using reference to the rest of the database as the goal is to report exactly what was attempted instead of what the ZC database contains. So, this increases the amount of information in the database and increasing the field length may gain little in functionality/information, but will increase database size.

will this mod help in tracking who is doing what at my zc 1.5.1 site? Reason I am asking this is because I notice some really strange behaviors on my site. I can see some ip's in my "Whos Online" page in admin, that some ip's are able to put ridiculous quantities of an item in their shopping cart such as 5000000000 units will I have less than a 100 units in stock in the quantity of that one item.

Does zencart prevent a larger then inventory quantity be added to the shopping cart? it seems like some of these ips' are trying to block my traffic. I want to find out who they are, what exactly are they doing, and be able to block them.

any help is appreciated.

twi:

will this mod help in tracking who is doing what at my zc 1.5.1 site? Reason I am asking this is because I notice some really strange behaviors on my site. I can see some ip's in my "Whos Online" page in admin, that some ip's are able to put ridiculous quantities of an item in their shopping cart such as 5000000000 units will I have less than a 100 units in stock in the quantity of that one item.

Does zencart prevent a larger then inventory quantity be added to the shopping cart? it seems like some of these ips' are trying to block my traffic. I want to find out who they are, what exactly are they doing, and be able to block them.

any help is appreciated.

So to try to answer, it will let you know what pages have successfully (and completely) loaded (I.e., the footer must be processed), therefore it will show you to what page(s) a session has successfully reached.

Regarding the tracking of what entity is doing this, well, there is a small caveat still at this time. The coding for identifying which records to add to the database already does some filtering as written; however, there were some types of records that were not filtered (hence the added hide spiders functionality). It is planned to overhaul that process so that the show spiders option would show all visitor traffic regardless of being a spider or not (which is more than likely what you have/had going on).

ZC does offer the capability of preventing the more than current quantity from being added to cart capability (or at least from checkout); however, that issue is something I would recommend to bring up in a separate forum/thread. There are multiple settings/ways to "notify" the user/visitor of attempting to add to the cart more quantity than in stock or to prevent that from happening. This plugin though will more than likely provide the information (IP address) of the individual(s) attempting to perform that action. All-in-all it seems to be an attempt to locate security weaknesses that could potentially be exploited. Afterall, if they actually pay for that quantity of product, worst case is that they get a refund for the product not in stock and you make a lot of money otherwise. :)

mc12345678:

So to try to answer, it will let you know what pages have successfully (and completely) loaded (I.e., the footer must be processed), therefore it will show you to what page(s) a session has successfully reached.

Regarding the tracking of what entity is doing this, well, there is a small caveat still at this time. The coding for identifying which records to add to the database already does some filtering as written; however, there were some types of records that were not filtered (hence the added hide spiders functionality). It is planned to overhaul that process so that the show spiders option would show all visitor traffic regardless of being a spider or not (which is more than likely what you have/had going on).

ZC does offer the capability of preventing the more than current quantity from being added to cart capability (or at least from checkout); however, that issue is something I would recommend to bring up in a separate forum/thread. There are multiple settings/ways to "notify" the user/visitor of attempting to add to the cart more quantity than in stock or to prevent that from happening. This plugin though will more than likely provide the information (IP address) of the individual(s) attempting to perform that action. All-in-all it seems to be an attempt to locate security weaknesses that could potentially be exploited. Afterall, if they actually pay for that quantity of product, worst case is that they get a refund for the product not in stock and you make a lot of money otherwise. :)

Thanks for the prompt reply. I would be ecstatic I they actually want to buy that amount added to the cart. It is in the ten of millions of dollars when they attempt to do that. they would add ridiculous quantities to a handful of products. Of course they are not able to checkout since I have initiated the prevent checkout if quantity is greater than inventory setting in admin. But it really bothers me that they are actually able to put the quantities in the cart. I though that zc prevent it from doing so even though an insufficient quantity warning in read is displayed.

To make thing simple. Is there a way to block certain/specific IP's? I have read something about htaccess but don't understand a thing how to do it. any suggestions?

twi:

Thanks for the prompt reply. I would be ecstatic I they actually want to buy that amount added to the cart. It is in the ten of millions of dollars when they attempt to do that. they would add ridiculous quantities to a handful of products. Of course they are not able to checkout since I have initiated the prevent checkout if quantity is greater than inventory setting in admin. But it really bothers me that they are actually able to put the quantities in the cart. I though that zc prevent it from doing so even though an insufficient quantity warning in read is displayed.

To make thing simple. Is there a way to block certain/specific IP's? I have read something about htaccess but don't understand a thing how to do it. any suggestions?

It's not difficult; however, I am not on a device that would allow me to look up my current htaccess file (easily). I've done that after I received one after another contact us inquiries, and other obnoxious actions on my site. Ultimately I didn't need anything from that group of servers as there is no way that their business could be gained for our organization. Anyways, it's not difficult;however, if you go for the onsey twosy, you may find that they will try from a different direction. Unfortunately it's a part of being able to be accessed by anyone in the world. Bound to have a few people/computers trying to do something obnoxious.

We had one day over 10000 hits from a single ip. Want to talk about somebody trying to shut down someone. It didn't take us offline but wholly cow!

mc12345678:

It's not difficult; however, I am not on a device that would allow me to look up my current htaccess file (easily). I've done that after I received one after another contact us inquiries, and other obnoxious actions on my site. Ultimately I didn't need anything from that group of servers as there is no way that their business could be gained for our organization. Anyways, it's not difficult;however, if you go for the onsey twosy, you may find that they will try from a different direction. Unfortunately it's a part of being able to be accessed by anyone in the world. Bound to have a few people/computers trying to do something obnoxious.

We had one day over 10000 hits from a single ip. Want to talk about somebody trying to shut down someone. It didn't take us offline but wholly cow!

Thank you again for the thorough help and insight in this situation. If you do have time later or a few days later, maybe you can send me some links of shoot me some pm concerning how to do it in the htaccess file would be great. Thanks in advance.

Here is the latest error message: > [22-Nov-2013 03:11:00 America/New_York] PHP Warning: reset() expects parameter 1 to be array, null given in /adminfolder/user_tracking.php on line 301 I think I am going to disabled this mod temporarily until after Christmas season. I want to make sure I can focus on any error that's genuine in the log and this mod is just filling it up. I will definitely continue to monitor the progress on this though. Thanks!

BlessIsaacola:

Here is the latest error message: I think I am going to disabled this mod temporarily until after Christmas season. I want to make sure I can focus on any error that's genuine in the log and this mod is just filling it up. I will definitely continue to monitor the progress on this though. Thanks!

Please advise, at that point of using UT to look at the history, were there no records returned/shown?
(For those reviewing this thread, that error iappears from the newly added functionality of displaying the number of visits at the beginning of the display of visitors.)
Odd thing to say/think, but almost glad that there is a problem, that way any additional changes can be incorporated with a fix.looking at adding a check for the last character of strings before sending to SQL and other character sequences specifically in the URL info just to prevent additional errors/warnings can add a flag later, then also to address a zero "volume" return on the array of visitors. Hopefully can submit soon.

mc12345678:

Please advise, at that point of using UT to look at the history, were there no records returned/shown?
(For those reviewing this thread, that error iappears from the newly added functionality of displaying the number of visits at the beginning of the display of visitors.)
Odd thing to say/think, but almost glad that there is a problem, that way any additional changes can be incorporated with a fix.looking at adding a check for the last character of strings before sending to SQL and other character sequences specifically in the URL info just to prevent additional errors/warnings can add a flag later, then also to address a zero "volume" return on the array of visitors. Hopefully can submit soon.

It is definitely possible that this happened after I disabled the Mod on the Admin side. I did not remove the tracking code in the footer just set it "User Tracking Visitor" to False. I guess I will disable on the Admin as well as remove the tracking code from the footer to avoid any potential issue.

Thanks!

BlessIsaacola:

It is definitely possible that this happened after I disabled the Mod on the Admin side. I did not remove the tracking code in the footer just set it "User Tracking Visitor" to False. I guess I will disable on the Admin as well as remove the tracking code from the footer to avoid any potential issue.

Thanks!

May have been before or after disabling tracking of visitors. I was able to reproduce the error by moving to tomorrow's date. (Obviously no data available for tomorrow.)

So no, it does not just randomly generate that reset warning. It is a missed check on my part. Should not have it try to cycle through the "array" if there is nothing in that array, thus I would not have to reset it. The result will still be zero, but the "array" will not be sent through an operation that could cause a warning. At this time removing the footer code will not prevent that warning from being logged. Not going to the admin/user_tracking.php page when there is no data or to a date that has no data will.

Thanks for the update and clarification. I appreciate all your support.

[16-Nov-2013 08:44:35 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 'OFFICE_IP_TO_HOST_ADDRESS')' 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', '', '157.55.32.84', '1384609475', '1384609475', '/index.php?main_page=product_reviews&cPath=388_408&products_id=13478', '/index.php?main_page=product_reviews&cPath=388_408&products_id=13478', 'Discovery and Exploration - Educational Insights You', 'OFFICE_IP_TO_HOST_ADDRESS') in /includes/classes/db/mysql/query_factory.php on line 120

BlessIsaacola:

Any idea what this error is? The ip address belongs to MSN searchbot. Thanks!

Wondering if you could add the following code to: yourstore/includes/functions/extra_functions/user_tracking.php ~line 69 (after the assignment of $page_desc = substr($page_desc, 0, 63); but before the $db->execute that inserts the record... Then test accessing the product (assuming that the record still has the same description) and see if still get the same issue... I'll see if I can't reproduce prevent reproduction of the error by forcing a comparison with that final result; however, some of the errors described seem to be "random" or at least it seems to take very specific conditions to cause a problem.

    while (strpos(right($page_desc, 1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }

mc12345678:

Wondering if you could add the following code to: yourstore/includes/functions/extra_functions/user_tracking.php ~line 69 (after the assignment of $page_desc = substr($page_desc, 0, 63); but before the $db->execute that inserts the record... Then test accessing the product (assuming that the record still has the same description) and see if still get the same issue... I'll see if I can't reproduce prevent reproduction of the error by forcing a comparison with that final result; however, some of the errors described seem to be "random" or at least it seems to take very specific conditions to cause a problem.

while (strpos(right($page_desc, 1), '\\') !== false) {
    $page_desc = substr($page_desc, 0, -1);    
}

Yeah, need to code in the correct language.  PHP doesn't have a "right(" function...Code should be: 
Code should be:

```
    while (strpos(substr($page_desc, -1), '\\') !== false) {
        $page_desc = substr($page_desc, 0, -1);    
    }

Submitted version 1.4.3b with the following updates:

1. Corrected a PHP warning that appears if there are no visits tracked for the date being reviewed.
2. Corrected an issue that had the possibility of generating an error log: If a product's description included a character that would be translated by use of an escape character as part of a SQL command, (eg: he's would be changed to he's) then, if that escape character landed in just the right position, it would end up as the last character in the string prior to a single quote as part of the SQL statement. This would then cause an error in the execution of the SQL statement and an error log to be generated. Resolution was to remove the last character if that last character is a . This issue has been resolved for: page description, last page URI, and referrer URI.
3. Updated the button display of spiders, such that if option 3 is chosen in system setup that a message appears.

UPDATING INSTRUCTIONS:
For SQL statemenst: same instructions as applicable from the 11/10/2013 update.

Files updated from 1.4.3 or 1.4.3a are:
YOUR_ADMIN\user_tracking.php
YOUR_ADMIN\includes\lanaguages\english\user_tracking.php
includes\functions\extra_functions\user_tracking.php

For those that would just want the code update, here are basically the changes (functional changes) (compared to version 1.4.3), I am leaving out the additional words portion of the update at this time.

In YOUR_ADMIN/user_tracking.php

find:

 $listed = 0;
  if ($results)
  while (($ut = each($user_tracking)) && !$user_tracking->EOF /*($listed++ < CONFIG_USER_TRACKING_SESSION_LIMIT)*/)

replace with (Change in red):

  $listed = 0;
  if ($results && is_array($user_tracking) == true)
  while (($ut = each($user_tracking)) && !$user_tracking->EOF /*($listed++ < CONFIG_USER_TRACKING_SESSION_LIMIT)*/)

Find:

  reset($user_tracking);
    //End of v1.4.3 14 of 15 
  if ($results)
  while (($ut = each($user_tracking)) && ($listed++ < CONFIG_USER_TRACKING_SESSION_LIMIT))

replace with (Change in red):

      //End of v1.4.3 14 of 15 
  
  if ($results && is_array($user_tracking) == true) {
    /* Begin v1.4.3b  (Moved statement to within test) */
        reset($user_tracking);
    /* End v1.4.3b */
while (($ut = each($user_tracking)) && ($listed++ < CONFIG_USER_TRACKING_SESSION_LIMIT))

and

Find:

// End User Tracking - Spider Mod 6 of 7
 }

Replace with (Change in red):

// End User Tracking - Spider Mod 6 of 7
 }}

in \includes\functions\extra_functions\user_tracking.php

at approximately line 70 (after $page_desc asssignment) but before $db->Execute add:

    /* 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*/

then after $wo_last_page_url assignment and before $db->Execute:

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

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

These last two changes need to be before the $db->Execute command.
No indicators have been added. This all will simply clean up the data going to be processed via SQL so that it won't generate a warning when no data is present for the data reviewed, and also will prevent SQL injection of creating a ' unintentionally.
Tada done...

No, did not get to next set of features to be added.