User tracking mod

[BlessIsaacola]

Here is my latest error:

[28-Nov-2013 23:43:25 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%20Science%20Club%20Microscope%20and%20Journal', 'Educational Insights Nancy B\' 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', 'fb6d3871016ffbebde95586b802f0414', '24.236.229.133', '1385700205', '1385700205', '/index.php?main_page=product_info&products_id=86402&gclid=CMaL_cSXibsCFclcMgod1Eg Apg', 'http://www.google.com/aclk?sa=l&ai=Cyn7UaBuYUtPBHIm2-QOijoGwAZ6CsMYD_sil-0HugcP8xQEIBhAHILlUKAhQ3K-m6P##########_AWDJ1qyJkKSIEKAB1Mar_QPIAQeqBCZP0Cblz5bDyrb-EQlFVMnZDBH2UaHp-4bpeMgbY-bt0Hxgh8aBL4AFkE7ABQWgBiaAB5S51AKQBwHgEqahzP2QlLS2Jw&sig=AOD64_1_SNzCkGTlRdatFOv qeL5RNJzEBw&ctype=5&ved=0CLsBELsX&adurl=http://www.clevershoppers.com/index.php%3Fmain_page%3Dproduct_info%26products_id%3D86402&rct=j&q=Nancy%20B's%2 0Science%20Club%20Microscope%20and%20Journal', 'Educational Insights Nancy B\'s Science Club Microscope and Act', 'OFFICE_IP_TO_HOST_ADDRESS') in /includes/classes/db/mysql/query_factory.php on line 120

[mc12345678]

Here is my latest error:

So, now trying to figure out a few things:1
1) How is it that the refer_url is longer than 253 or 254 characters?
2) The above is not supposed to occur in the user side of logging.
3) Which logging is currently active?

The problem lies in that there is an apostrophe that is not escaped in the referer_url. This is throwing off the SQL (assuming all content was copied and pasted). I do not know if this is some sort of limitation of the addslashes function. The email received based on the above post included an exclamation point in that variable that is not visible in the above unedited post. So I am not sure what was fully fed to the addslashes and of that what came back and why it was not truncated prior to going to the SQL statement. Had it been truncated as expected by the above information, then no error would have occurred in this case.
I also forget, have you for the purposes of posting on this forum been changing the IP address and entering OFFICE_IP_TO_HOST_ADDRESS or is that a result of the previous SQL fail?

[BlessIsaacola]

So, now trying to figure out a few things:1
1) How is it that the refer_url is longer than 253 or 254 characters?
2) The above is not supposed to occur in the user side of logging.
3) Which logging is currently active?

The problem lies in that there is an apostrophe that is not escaped in the referer_url. This is throwing off the SQL (assuming all content was copied and pasted). I do not know if this is some sort of limitation of the addslashes function. The email received based on the above post included an exclamation point in that variable that is not visible in the above unedited post. So I am not sure what was fully fed to the addslashes and of that what came back and why it was not truncated prior to going to the SQL statement. Had it been truncated as expected by the above information, then no error would have occurred in this case.
I also forget, have you for the purposes of posting on this forum been changing the IP address and entering OFFICE_IP_TO_HOST_ADDRESS or is that a result of the previous SQL fail?

No I am not changing anything except for the server path to admin (which I removed for security reasons). I know the url is long but I have seen this with Google Adwords that some of the referral url is ridiculously long and it also depends on the user setting what Google does with the url when sending the customer to our site. Again, I don't have any issues in the error log if I disable user tracking. User tracking seems to be the only mod at the moment that's triggering this error. IP to Host conversion is set to false on our site.

[mc12345678]

No I am not changing anything except for the server path to admin (which I removed for security reasons). I know the url is long but I have seen this with Google Adwords that some of the referral url is ridiculously long and it also depends on the user setting what Google does with the url when sending the customer to our site. Again, I don't have any issues in the error log if I disable user tracking. User tracking seems to be the only mod at the moment that's triggering this error. IP to Host conversion is set to false on our site.

So what I mean by the referer_url being so long is that just before the SQL is run, that particular variable is truncated to 253 characters (or possibly 254, I haven't looked at how that assignment occurs); however, the value shown in the above error indicates that referer_url is about 460 characters long.

The only way that could happen if the program is installed as written/provided is if the logging of the admin side is active and somehow it is logging an admin's activity where they are not logged in... (Try to explain that one... :) ) That's why I question the length of the url and how it is so long.

[BlessIsaacola]

So what I mean by the referer_url being so long is that just before the SQL is run, that particular variable is truncated to 253 characters (or possibly 254, I haven't looked at how that assignment occurs); however, the value shown in the above error indicates that referer_url is about 460 characters long.

The only way that could happen if the program is installed as written/provided is if the logging of the admin side is active and somehow it is logging an admin's activity where they are not logged in... (Try to explain that one... :) ) That's why I question the length of the url and how it is so long.

Hmm, I do not have the mod enable on the admin side. Not exactly sure how you came to the conclusion that this was triggered on the admin side.

[mc12345678]

Hmm, I do not have the mod enable on the admin side. Not exactly sure how you came to the conclusion that this was triggered on the admin side.

Review of the code shows that the admin version of the functions does not truncate the referer_uri (I would say because the admin side is considered something of a closed loop), so that led me to believe the information was provided through the admin. Of course that was also based on the belief that all files are of the version previously posted (with the corrections made because I messed up).

Basically, the insert is done in only two files, in one it is supposed to truncate the text, in the other it doesn't yours was not truncated, leading me to understand that the info came from the other or a file(s) are not up-to-date.

[BlessIsaacola]

Review of the code shows that the admin version of the functions does not truncate the referer_uri (I would say because the admin side is considered something of a closed loop), so that led me to believe the information was provided through the admin. Of course that was also based on the belief that all files are of the version previously posted (with the corrections made because I messed up).

Basically, the insert is done in only two files, in one it is supposed to truncate the text, in the other it doesn't yours was not truncated, leading me to understand that the info came from the other or a file(s) are not up-to-date.

I will wait until your latest version is available in the download section and see if that resolve the issue. If not, I will uninstall and pick this up next year. I am lost as to why this would be an issue on the admin side. Like I said, the url is from a Google Adwords ads and it makes no sense that the landing page for a Google Adwords will trigger this on the admin side. Thanks!

[mc12345678]

I will wait until your latest version is available in the download section and see if that resolve the issue. If not, I will uninstall and pick this up next year. I am lost as to why this would be an issue on the admin side. Like I said, the url is from a Google Adwords ads and it makes no sense that the landing page for a Google Adwords will trigger this on the admin side. Thanks!

Well, the unfortunate thing is that because this plug-in counts on external influence in order to cause an action, unless someone else is able to identify similar issues and willing to continue testing/able to identify what is "going on" it will be difficult to resolve this. My site is not presenting these issues, and I suspect that others are waiting for you and I to "work this out". May need to insert something to help identify the path taken to document how this issue is arising.

There's no reason the truncation can't be added into the admin side, but the admin side shouldn't be activated for normal users. (If there is a problem in the code, obviously would like to resolve it.)

[Woodymon]

Like I said, the url is from a Google Adwords ads and it makes no sense that the landing page for a Google Adwords will trigger this on the admin side. Thanks!

Indeed the Google Adwords referral URLs come from the catalog side. It could not be any other way. The Adwords referral URL strings I observe are always very lengthy. Not sure why MC believes otherwise.

I have to ask where in the "tracking" process does this mod "record" a "hit" (access request/attempt)?

Does every hit that appear in the user tracking database also appear in the server access log?

[mc12345678]

Indeed the Google Adwords referral URLs come from the catalog side. It could not be any other way. The Adwords referral URL strings I observe are always very lengthy. Not sure why MC believes otherwise.

I have to ask where in the "tracking" process does this mod "record" a "hit" (access request/attempt)?

Does every hit that appear in the user tracking database also appear in the server access log?

Please note, I did not indicate that the URI that is presented to UT is a surprise to be long. It is that UT is supposed to truncate that URI prior to storing it in the database. It is the storing routine (or lack of it successfully storing) that is causing blessisaacola the problem, because an apostrophe appears in the original uri that is not being escaped (assume because the original text length is greater than 254 characters and is only increased in the function that adds slashes (addslashes($stringtext)). That apostrophe is beyond the end of the 254 character limit imposed, and the string length being presented to the database was 400+ characters, when I thought it should be no more than 254.

Apparently I was wrong... The last_page_url is truncated, but the referrer url appears was not... *Head bowed in shame* It was when I went back to try to copy and paste the code in here, that I figured it out...

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

In his case, I was trying to figure out why a string greater than 254 characters was being presented to the SQL when I thought there was a specific function present to prevent a string greater than 254 characters from being provided. (THERE WASN"T, BUT IT IS PROVIDED ABOVE)

In regards to the question of tracking process. It is after the footer has loaded, then the data is parsed, if it passes internal filters and checks of UT, then it is logged.

Regarding the comparison of UT database with the server access log: All successful loads of the footer that make it through the internal filters/checks and then are logged are expected to show up in the server access log, unless there has been some tom foolery that gave UT bad information (which in some cases is attempted).

A significant difference between the two is that the server access log will also more than likely also show every css file, image and a multitude of other objects that were requested (including the robots.txt file where one doesn't exist).

Thanks for asking more about it, made me think it through more thoroughly and because I was working on a number of other things on my site, I have all my tools open to use... I just didn't really have the sample data to test it and have been hoping to get some other feedback.