Please Help SQL error ... Repair in process after Hack! NO

Hi Guys i am using 1.3.8a after upgrading from 1.3.7 after being hacked.. I am now having a few issues though.. Most of which i have sorted.

The main one is that when i look at the orders page in admin. I cannot update the order status? and i get an error message at the top of the page that says...

1054 Unknown column 'zen_order_id' in 'where clause'
[select * from zen_paypal where zen_order_id = '624' LIMIT 1]

Any help to sort this out would be gratefully recieved!

KR

Dan

To add insult to injury we are not using paypal as our processing service??? I really dont know whats going on!

I can make it pull of a result when i change the code to..

select * from zen_paypal where order_id = '624' LIMIT 1

should this code not read

select * from zen_orders where orders_id ='624' LIMIT 1?

That was it would be from the orders list not the paypal one? if so how do i change it? Or even better just fix it!

This sounds like your database is not upgraded ... the field:
zen_order_id

is renamed in the Upgrade of the database ...

You think it will be best to run through the database upgrade part of the ruitine again?

Or is someone able to help me with the needed sql to change the column?

Everything alse seems fine :-/

Backup your database ...

Then run the Upgrade Database again ...

You should see checkbox(es) for each upgrade that needs to be done ...

It is best to do these one at a time so if you have more than one checked, leave the first one checked alone and uncheck any below it and Upgrade ... then repeat until all are displayed unchecked ...

but i see loads of check boxes... all unchecked but lots of them.......... Dont know what to do?

What PayPal Payment method are you using?

Thats part of the problem i am not using paypal at all?! Only using a service called SecureHosting....? If that helps?!

In you Tools ... Server Info ... you should see something like:

Zen Cart 1.3.8a
Patch: 1::
Database Patch Level: 1.3.8
v1.3.8 [2009-07-21 10:35:09] (Fresh Installation)
v1.3.8 [2009-07-21 10:35:09] (Fresh Installation)

What do you see?

Hi thanks for your help with this!

** Zen Cart 1.3.8a**

Patch: 1::

 ** Database Patch Level:  1.3.8**

v1.3.8 [2009-06-29 19:27:17] (Version Update 1.3.7->1.3.8)
v1.3.7 [2009-06-29 19:27:11] (Version Update 1.3.6->1.3.7)
v1.3.6 [2009-06-29 19:27:00] (Version Update 1.3.5->1.3.6)
v1.3.5 [2006-09-16 20:18:05] (Fresh Installation)

Any further help much appreciated!

When you fixed your files from the Hacker issues ... are you sure you didn't down grade any of the files on your site?

For example, if you go to Tools ... Developers Tool Kit ... and in the bottom input box put:
zen_order_id

then search on the Catalog/Admin ... what comes up?

And on the files listed, what do their version ID lines at the top of those files, on the server, read?

I have no recolection of doing such a thing... Here are my results...

catalog/admin/includes/languages/english/paypal.php
Line #29 : define('TEXT_SORT_ZEN_ORDER_ID_DESC', 'Order ID (high - low), PayPal Order Received');

Line #30 : define('TEXT_SORT_ZEN_ORDER_ID', 'Order ID (low - high), PayPal Order Received');

catalog/admin/paypal.php
Line #14 : array('id' => '2', 'text' => TEXT_SORT_ZEN_ORDER_ID_DESC),

Line #15 : array('id' => '3', 'text'=> TEXT_SORT_ZEN_ORDER_ID),

catalog/includes/modules/payment/linkpoint_api.php
Line #531 : if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'zen_order_id')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default '0'");

Line #601 : if ($sniffer->field_exists(TABLE_LINKPOINT_API, 'zen_order_id')) $db->Execute("ALTER TABLE " . TABLE_LINKPOINT_API . " CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default '0'");

catalog/includes/modules/payment/paypal.php
Line #449 : if ($sniffer->field_exists(TABLE_PAYPAL, 'zen_order_id')) $db->Execute("ALTER TABLE " . TABLE_PAYPAL . " CHANGE COLUMN zen_order_id order_id int(11) NOT NULL default '0'");

Line #626 : $db->Execute("ALTER TABLE " . TABLE_PAYPAL . " CHANGE zen_order_id order_id int(11) NOT NULL default '0'");
**
catalog/includes/modules/payment/paypaldp.php**
Line #1631 : $db->Execute("ALTER TABLE " . TABLE_PAYPAL . " CHANGE zen_order_id order_id int(11) NOT NULL default '0'");

catalog/includes/modules/payment/paypalwpp.php
Line #2952 : $db->Execute("ALTER TABLE " . TABLE_PAYPAL . " CHANGE zen_order_id order_id int(11) NOT NULL default '0'");
catalog/includes/modules/payment/securehosting.php
Line #145 : $sql = "select * from " . TABLE_PAYPAL . " where zen_order_id = '" . $zf_order_id . "' LIMIT 1";

Line #155 : $sql = "INSERT INTO " . TABLE_PAYPAL . " (zen_order_id,txn_id,payment_date,payer_id,reason_code) VALUES ('".$insert_id."','".$_GET['transactionreference']."','".$_GET['transactiondate']." ".$_GET['transactiontime']."','".$_GET['cv2avsresult']."','Success')";
doh.... i hope this means something to you!!

tHANKS!

Your file:
catalog/includes/modules/payment/securehosting.php

looks to be using older commands for the old structure to the database table:
paypal

If you look, in phpMyAdmin, at the table structure for:
paypal

you will see the field zen_order_id has been changed to the new field name:
order_id

You need to fix your payment module:
catalog/includes/modules/payment/securehosting.php

to use the new field name ...
order_id

where you see the references to the old field name:
zen_order_id

Ok Thankyou i will try that later!

Ajeh, you are a complete legend!! That worked perfedtly and now its all up and running....

:clap:

Thanks Very very very very much

Dan

Sorry it took a bit to work this out, but I had to know more about what was really wrong before telling you a solution to this or risk breaking other things ...

Thanks for the update that this is now working once you updated your Payment Module ... :smile: