Problem or not? Incorrect integer value for column customer_notified

[swguy]

Right before the insert, do something like this:

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

[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

[mc12345678]

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.



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?



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.



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



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)



No.



No.



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

I was thinking that perhaps the "16" reoccurrences were like ipn updates that were failing and therefore notification of the initial refund was being tried again. But as well, can see the concern that possibly it is an attempted exploit... Maybe a way to filter to allow just one of the notifications to go through (small price or something) by some unique identifier to see the total result?

Or better yet, try installing lat9s mydebug backtrace plugin... The error messages should provide much more granularity of what section(s) of code are causing the error by identifying where the sql is that is "not correct".