I am currently using an import/export module for my OpenCart ecommerce store.
The module works by exporting the OpenCart category and product database to an excel sheet which can then be edited and imported. We decided to use this module to bulk import our products.
The first 953 products import perfectly before I receive the following error:
Notice: Error: 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-NutRageous-Smaller.jpg',1,0.825,0,'2012-11-04 12:56:45','2013-12-01 14:19:41',' at line 1
Error No: 1064
INSERT INTO `oc_product` (`product_id`,`quantity`,`sku`,`upc`,`ean`,`jan`,`isbn`,`mpn`,`location`,`stock_status_id`,`model`,`manufacturer_id`,`image`,`shipping`,`price`,`points`,`date_added`,`date_modified`,`date_available`,`weight`,`weight_class_id`,`status`,`tax_class_id`,`viewed`,`length`,`width`,`height`,`length_class_id`,`sort_order`,`subtract`,`minimum`) VALUES (1060,0,'','','','','','','',5,'Reeses_Nutrageous_Bars_51g',22,'data/Reese's-NutRageous-Smaller.jpg',1,0.825,0,'2012-11-04 12:56:45','2013-12-01 14:19:41','2012-11-03',0.05,1,1,9,1930,1,2,3,'2','1','1','1'); in /var/www/vhosts/localhost.localdomain/americanfizz.info/system/database/mysql.php on line 50
I don't believe this is an issue with the formatting of the spreadsheet as I have tried removing the "offending" product but it always fails when it reaches line 954.
At first I had issues importing due to the size of our database but I have resolved this by temporarily adjusting memory_limit in php.ini to -1 whilst the import takes place. For reference purposes, the import script I am using can be found here: http://www.opencart.com/index.php?route=extension/extension/info&extension_id=17
The error is quite obvious (I suppose you aren't asking about that):
near 's-NutRageous-Smaller.jpg'
22,'data/Reese's-NutRageous-Smaller.jpg'
^ ^ ^
| | |
\ Start | \ End
\ Faux end
I'd say that the piece of software you are using to generate these queries is failing to use prepared statements or to escape input; that makes it too buggy to be usable.
Edit: Out of curiosity, I had a quick look at the code and found this at upload/admin/model/tool/export.php:
$sql = "INSERT INTO `".DB_PREFIX."product` (`product_id`,`quantity`,`sku`,`upc`,`ean`,`jan`,`isbn`,`mpn`,`location`,";
$sql .= "`stock_status_id`,`model`,`manufacturer_id`,`image`,`shipping`,`price`,`points`,`date_added`,`date_modified`,`date_available`,`weight`,`weight_class_id`,`status`,";
$sql .= "`tax_class_id`,`viewed`,`length`,`width`,`height`,`length_class_id`,`sort_order`,`subtract`,`minimum`) VALUES ";
$sql .= "($productId,$quantity,'$sku','$upc','$ean','$jan','$isbn','$mpn','$location',";
$sql .= "$stockStatusId,'$model',$manufacturerId,'$imageName',$shipping,$price,$points,";
$sql .= ($dateAdded=='NOW()') ? "$dateAdded," : "'$dateAdded',";
$sql .= ($dateModified=='NOW()') ? "$dateModified," : "'$dateModified',";
$sql .= ($dateAvailable=='NOW()') ? "$dateAvailable," : "'$dateAvailable',";
$sql .= "$weight,$weightClassId,$status,";
$sql .= "$taxClassId,$viewed,$length,$width,$height,'$lengthClassId','$sort_order','$subtract','$minimum');";
Going up the tack trace, this is where e.g. $imageName comes from:
$imageName = $this->getCell($data,$i,$j++);
... where getCell() is a function that reads the string from Excel. To sum up: ugly code that's calling for a SQL injection attack.