Problem or not? Incorrect integer value for column customer_notified

[robax]

Hello chaps

I'm trying to figure out if the following error in the debug log is a fault that I need to fix, or if I can ignore it.

[12-Mar-2015 14:11:06 Australia/Sydney] PHP Fatal error: 1366:Incorrect integer value: '' for column 'customer_notified' at row 1 ::
INSERT INTO orders_status_history (orders_id, orders_status_id, date_added, comments, customer_notified)
VALUES ('4286', '1', now(), 'PayPal status: Refunded @ 21:17:30 Mar 09, 2015 PDT Parent Trans ID:xxxxxxxxxxxxxx Trans ID:xxxxxxxxxxxxxx Amount: -161.75 AUD', '')
in C:\inetpub\wwwroot\xxxxxxxxxxxxxx\includes\classes\db\mysql\query_factory.php on line 120



It looks like it doesn't like the value to be empty, but I'm no PHP/MySQL expert so I don't really know.

I can't see any issues in the store admin. I only noticed this one in the logs. It appears to be a PayPal refund. Is this likely to be a problem or can I safely ignore it?

Thanks for any advice on this.
Rob

These are the details of my website:

Zen Cart 1.5.1
Database Patch Level: 1.5.1
v1.5.1 [2013-10-25 16:43:45] (Version Update 1.5.0->1.5.1)
v1.5.0 [2013-10-25 16:43:45] (Version Update 1.3.9->1.5.0)
v1.3.9b [2010-05-18 18:34:34] (Version Update 1.3.8->1.3.9b)
v1.3.8 [2008-07-30 02:07:56] (Fresh Installation)

It uses the PayPal Express checkout and has some unrelated plugins such as the one for image resizing and OZPost for shipping.

Other info that may or may not matter:
Server Host: xxxxxxxx Database Host: 127.0.0.1 (127.0.0.1)
Server OS: Windows NT SV8774 6.2 build 9200 (Unknow Windows version Standard Edition) i586 Database: MySQL 5.5.42
HTTP Server: Microsoft-IIS/8.5
PHP Version: 5.3.28 (Zend: 2.3.0) PHP Memory Limit: 128M PHP Safe Mode: Off
PHP File Uploads: On Max Size: 2M POST Max Size: 8M
Database Data Size: 73,089 kB Database Index Size: 3,227 kB

[RodG]

I'm trying to figure out if the following error in the debug log is a fault that I need to fix, or if I can ignore it.

This one shouldn't be ignored because it means you'll be losing some valuable information from your orders history.

Do you want the good news or the bad news first?

OK, the good news. This problem isn't unique to you. I've experienced (and fixed) the exact same issue.

The bad news. I can't find or recall exactly what I did to fix it. I've searched my code to see if I can see any changes I made for this, but didn't find anything (it may be one of those rare times I forgot to leave myself any comments in the code), but on the other hand, I think I may have fixed it by modifying the orders_status_history table structure so that it allows NULLS.

I have verified that this is indeed how my database is configured, but I don't know if that is/was the default setting, or the setting I ended up changing.

Hopefully someone else that has come across this issue has a more definitive answer.

Cheers
RodG

[robax]

Hi Rod

Yes, that makes good sense.

I've checked that table here in MySQL Workbench and can see the information in the screenshot for the column.

Strangely it says 'Yes' in the Nullable column. Do you recall if yours looks like this?



Regards
Rob

[swguy]

Right before the insert, do something like this:

if ($customer_notified === '') { 
   $customer_notified = -1;
}

[RodG]

I've checked that table here in MySQL Workbench and can see the information in the screenshot for the column.
Strangely it says 'Yes' in the Nullable column. Do you recall if yours looks like this?

Yes, that is identical to mine. (So much for that idea).

I've also checked to see if my fix was the same as that mentioned by swguy (which seems to ring a bell, and even looks like it would be the same kind of fix I'd apply myself), but I've done another search of my code and can't find anything along those lines either.

It was my intent to identify a similar change in my own code so that I could tell you exactly which file & place you need to add this fix. Alas, I can't even do that. :-(

Cheers
RodG

[robax]

Right before the insert, do something like this:

if ($customer_notified === '') { 
   $customer_notified = -1;
}

Thanks for this SWGuy

I've taken a wild guess after searching via the developer tools at where to put this and have inserted it in:
\admin\orders.php at line 175

That makes it look like this:

  

		  if ($customer_notified === '') { 
		  $customer_notified = -1;
		  }

          $db->Execute("insert into " . TABLE_ORDERS_STATUS_HISTORY . "
                      (orders_id, orders_status_id, date_added, customer_notified, comments)
                      values ('" . (int)$oID . "',
                      '" . zen_db_input($status) . "',
                      now(),
                      '" . zen_db_input($customer_notified) . "',
                      '" . zen_db_input($comments)  . "')");
          $order_updated = true;
        }

Does that look about right?

Thanks for a look!
Regards
Rob

[swguy]

You betcha! Give it a whirl!

(My guess wrt the root cause of this issue would be a failed merge of a mod to the admin/orders.php file - this doesn't happen in a new download. But this workaround should suffice for now.)

[robax]

Excellent.. I'll make a note to watch this file on the next upgrade.
Thanks heaps for this!

And thanks for your comments too Rod.
Rob

[robax]

hmm poop

I'm still seeing this error in the logs. It seems to be related to PayPal refunds.

[20-Mar-2015 12:33:42 Australia/Sydney] PHP Fatal error: 1366:Incorrect integer value: '' for column 'customer_notified' at row 1 :: INSERT INTO orders_status_history (orders_id, orders_status_id, date_added, comments, customer_notified) VALUES ('4125', '1', now(), 'PayPal status: Refunded @ 18:32:39 Mar 19, 2015 PDT
Parent Trans ID:xxxxxxxxxxxxxxxx
Trans ID:xxxxxxxxxxxxxxxxxxxx
Amount: -9.25 AUD', '') in C:\...............\includes\classes\db\mysql\query_factory.php on line 120


It looks like the error is recorded a number of times in a day and then it stops. Like it's on a schedule.

In fact, I can see rows on the PayPal IPN tab within Zen Cart that correspond to the errors.

It looks like there are about 16 entries for each refund. I would guess that PayPal limits the number of times it sends these.

It does look like the refunds work at PayPal, but are not recorded in the cart because I can't see any notes on the order saying so.

This store has been running for several years and the owner (my sister) has never said anything is wrong, but she might not know, and if the refunds are going back to the customer then that's fine.


So this time I downloaded a fresh ZC 1.5.1 zip and compared the file system. Everything appears to be intact, with only the OZPost module and various language changes showing up in WinMerge.

It is the case that my db is upgraded from as early as ZC 1.3.8.

So.. could this be something to do with upgrades over time, or possibly a PayPal configuration issue? I'm assuming that if it records the error as above, then it must be coming back to the right place from PayPal.. otherwise it'd record nothing at all.

Any idea are appreciated.
Regards
Rob

[RodG]

hmm poop
I'm still seeing this error in the logs. It seems to be related to PayPal refunds.

That 'fix' that swguy gave... You'll need to apply the same fix to other parts of the code that contain the

$db->Execute("insert into " . TABLE_ORDERS_STATUS_HISTORY

code call.

It looks like the error is recorded a number of times in a day and then it stops. Like it's on a schedule.

Just to be perfectly clear here, are you saying that these events DO NOT coincide with you (the store admin) submitting a PayPal Refund request?

It looks like there are about 16 entries for each refund. I would guess that PayPal limits the number of times it sends these.

It does look like the refunds work at PayPal, but are not recorded in the cart because I can't see any notes on the order saying so.

If you don't know anything about trying to provide PayPal refunds, this is starting to now look like you are seeing a possible hack/exploit attempt.

This store has been running for several years and the owner (my sister) has never said anything is wrong, but she might not know, and if the refunds are going back to the customer then that's fine.

But if those refunds shouldn't be going to *any* customer, then that's not fine.

So this time I downloaded a fresh ZC 1.5.1 zip and compared the file system. Everything appears to be intact, with only the OZPost module and various language changes showing up in WinMerge.

This will be the case if the V1.5.1 code has this bug/problem (which may or may not be fixed in V1.5.4)

It is the case that my db is upgraded from as early as ZC 1.3.8.

So.. could this be something to do with upgrades over time,

No.

or possibly a PayPal configuration issue?

No.

I'm assuming that if it records the error as above, then it must be coming back to the right place from PayPal.. otherwise it'd record nothing at all.

I had initially assumed that the events being logged would coincide with PP refunds being given, but based on these latest comments I'm assuming that this is probably some kind of exploit attempt, and for me, that changes things a lot. So much so that it is theoretically possible you may not even want to fix this bug on the basis that the fix may turn the failed exploit into a successful one.

As for the error being triggered and logged, my thoughts are that this is caused with one of the PHP/MySQL updates that have become a lot more restrictive in what rules can be 'broken' with no ill effect (such as the ability to use a NULL where an INT is expected). IOW, developers now need to take a little bit more care about validating user inputs before using them.

Cheers
RodG