Clearing All Product Data In Magento

Today we just want to share a little script we use on a regular basis that we thought may come in handy for a few of you out there. This simply clears all product data from your Magento installation – very handy if you import your data on a regular basis and it needs refreshing or if you need to delete old demo data.

First connect to MySQL using the following in SSH:

mysql -u YOUR-USERNAME -p

Next you’ll be prompted for your password, just type it in and hit enter.

Now, connect to the Magento database:

use YOUR-MAGENTO-DATABASE-NAME;

Lastly, run the commands below to clear all product data.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `rating_option_vote`; 
TRUNCATE TABLE `rating_option_vote_aggregated`;
TRUNCATE TABLE `review`; 
TRUNCATE TABLE `review_detail`; 
TRUNCATE TABLE `review_entity_summary`; 
TRUNCATE TABLE `review_store`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

You’re all set! Navigate back to your Magento and give it a quick double check, you should find that all products have now gone.

If you need to do this on a regular basis then a cron job may be more fitting, just bung the above code in a .sql file and use a cron job similar to the below:

00 22 * * * PATH/TO/MYSQL MAGENTO-DATABASE-NAME < clear-products.sql

Simply swap in your MySQL path and Magento database name, upload the .sql file you created earlier to your root directory and you are good to go. This cron runs daily at 10PM so change that according to your own needs. For example mine would be the below which runs at 10PM every Friday:

00 22 * * 5 /usr/bin/mysql magento < clear-products.sql

Let us know below if you need anymore help and thanks for reading!

Magento Daily Maintenance Script

This is just a short(ish) post for anybody interested in setting up a little cron job to keep your Magento database a bit tidier. The cron job itself is quite simple, it truncates some ‘not so necessary’ tables in your Magento database (mostly logs) and then goes on to re-index everything in Magento as a final step.

However, one of the cron jobs also trunactes the core_url_rewrite table, which can lead to some 404 errors until everything is re-indexed (hence the last part of the cron).

You have been warned...

Before putting this into action on a production site it is always smart to give it a test first on a development version, just incase!

Okay, so here goes…

30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE core_url_rewrite"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_customer"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_visitor"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_visitor_info"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_url"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_url_info"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE log_quote"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE report_viewed_product_index"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE report_compared_product_index"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE report_event"
30 22 * * * /usr/bin/mysql magento -e "TRUNCATE TABLE catalog_compare_item"
45 22 * * * /usr/bin/php -f /var/www/shell/indexer.php reindexall

This is quite a simple set of cron jobs the purpose of which is to truncate various tables in your database and then re-index everything afterwards (as mentioned above). In this particular example all truncates happen at 22:30pm and then the re-index occurs at 22:45pm.

The script does have some variables that are application specific, i.e. your database might not be named ‘magento’, or your MySQL install may be in a different directory. This means that copying and pasting the above code into the crontab of your own server doesn’t necessarily mean it will start working automatically.

To help with this I will give a quick run down of each section in the code above so that it is easier to figure out what needs changing.

Choosing When The Cron Jobs Run.

30 22 * * *

Firstly, we have to specify the times/days that we want to run the cron jobs. The syntax for this is shown below:

Minute / Hour / Day of Month / Month / Day of Week

In crontab an asterisk (*) generally means all, so * in the month column would mean that the cron job runs every month.

To give an example, the code below would run at 15:00 every Monday:

#Minute / Hour / Day of Month / Month / Day of Week
00 15 * * 1

A handy tool for checking over your cron syntax and making sure everything will work as expected is Cron Checker.

Define Where MySQL is.

Next we have to define where MySQL is on the server using the below code:

/usr/bin/mysql

This may vary dependant on your set-up. If you don’t know where MySQL is hiding then try the two snippets below in SSH to see if you can get a better idea:

mysql -u YOUR_USERNAME_GOES_HERE -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

Or…

which mysql

Define Which Database You Want To Make Changes To.

Next you want to define which database you are referring to. In the example above I simply named it magento – again, yours may be something different. Once you find this make the changes accordingly.

Re-indexing Data After Truncating.

Lastly, I’ll just take a quick look at the final line in the crontab; the line that re-indexes everything in Magento after the previous cron jobs have finished running.

45 22 * * * /usr/bin/php -f /var/www/shell/indexer.php reindexall

Similar to how we defined MySQL’s  directory earlier we now have to do the same for php:

/usr/bin/php

We can find this with the code below:

which php

Then change your cron job so that it matches up.

Point the cron to your Magento directory.

And to finish it all up we simply need to make sure that the cron is pointing toward the Magento directory when running the re-indexing script. This particular Magento install is located in /var/www/ so we use the code:

/var/www/shell/indexer.php

If your Magento installation resides elsewhere then change this part of the cron job to that folder instead.


Now that’s all finished you can save your crontab and hopefully it should all start working. If you notice that it isn’t doing anything then take a look at your syslog (generally in /var/log/) and see if there are any errors just after the cron runs. Feel free to drop a comment below if you need help with any of this.


If you like this post, why not give us a follow on Twitter and stay up-to-date!

[twitter-follow username=”ArtistLabIOW”]

Magento Shoplift Bug (SUPEE-5344) & How To Fix It Once Infected

On February 9, 2015 Magento issued a patch for the Shoplift Bug (SUPEE-5344), a vulnerability that opens you up to potential remote code execution exploits.

Unfortunately, I have had to deal with two sites that weren’t patched in time and fell victim to the exploit. Looking round the web there was a lot of information letting people know the patching process and the risks of not patching as soon as possible, however, I couldn’t find much in the way of fixing an already infected site.

Obviously the best and safest way to solve this is by reverting to a safe copy and patching that, but if you land in a position like myself and don’t have access to such luxuries then below is a list of steps that might help you out.

Firstly, here are some common symptoms of the Shoplift Bug, many of which I saw occur:

  • Unauthorized admin users added under System -> Permissions -> Users
  • Passwords of current admin users changed
  • Current admin accounts deleted completely (how to fix)
  • Unauthorized .csv file exports in var/export/
  • New vouchers being created/used out of the blue
  • PHP files in media/ directory
  • Messed up permissions
  • New pages added, often with customer data inside

For more symptoms and to use Byte’s Shoplift Bug tester click here…

Now, down to the nitty gritty.

Below are the affected files I found in my ventures and some more compiled from around the web. I will point out though that one of the sites I checked seemed as if it had also fallen victim to a different exploit too (specifically SUPEE-1533 from Oct 3, 2014) so some of these may not be relevant in your situation. Certainly no harm in checking anyway though!

Changed files/directories:

  • index.php
  • js/index.php
  • js/ccard.js
  • lib/Varien/Db/Adapter/Pdo/Mysql.php
  • app/code/core/Mage/Cms/controllers/IndexController.php
  • downloader/cache.cfg
  • downloader/pearlib/php/PEAR/Command/Mage.php
  • downloader/pearlib/php/PEAR/Installer/Role/Mage.php
  • lib/Varien/Autoload.php

Newly added files/directories:

  • js/api.php
  • app/code/community/Magpleasure/
  • app/design/adminhtml/default/default/layout/filesystem.xml
  • app/design/adminhtml/default/default/template/filesystem/
  • app/etc/modules/Magpleasure_Filesystem.xml
  • downloader/.cache/community/File_System-1.0.0.tgz
  • js/editarea/
  • js/filesystem/
  • js/File_System-1.0.0.tgz
  • skin/adminhtml/default/default/filesystem/
  • var/importexport/*.csv (check any files here as I found a nasty code hidden inside a spreadsheet file in this location)
  • media/dhl/info.php
  • js/flash/FrontendHtml.phar.php
  • js/extjs/resources/images/magento/basic-dialog/MagentoLib.php

These are all affected files I have found so far but I’m sure many more could just as easily be edited. If you notice anything extra in your installations let me know in the comments below and I’ll get it added to the list too.

Tips to fix:

In your FTP client of choice there is usually an option to order files/directories by date modified. If you take a look at the files that have been edited recently chances are you can get an idea of when the exploit took place and fix any suspicious files edited around that date accordingly.

It is also helpful to have reference to what has been added to these files so that you don’t delete anything important. The best way to do this is if you have a clean copy for comparison. If this is not the case you should download a fresh copy of your Magento version and use that for comparison.

Also, on the note of clean copies (if you do have one) another option could be to export your recent customer/order information and inject it into an older, safer copy you have lying around.

And last but not least…

Here are a couple of things that you should do after everything looks fixed:

  • Change all admin passwords
  • Change database password
  • Install all patches (clear cache and recompile after)
  • Fix all permissions
  • Keep a close eye on your sites access log, user list and any affected files for a few weeks to make sure they aren’t hit again.

EDIT: Just found Check Point vulnerability analysis which is definitely worth a  read to get in the mind of an attacker using this exploit. Check it out here…