csv import mysql error

Help for integrating the Laravel package
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
timothy_truckle
Posts: 10
Joined: 12 Mar 2018, 14:00

csv import mysql error

Post by timothy_truckle » 12 Mar 2018, 14:08

PHP 7.0.25-0ubuntu0.16.04.1 (cli)

I tried to import a csv with products:

php artisan aimeos:jobs product/import/csv

Number of apostrophes don't match:
SELECT mpro."id" AS "product.id", mpro."siteid" AS "product.siteid",
mpro."typeid" AS "product.typeid", mpro."code" AS "product.code",
mpro."label" AS "product.label", mpro."config" AS "product.config",
mpro."start" AS "product.datestart", mpro."end" AS "product.dateend",
mpro."status" AS "product.status", mpro."ctime" AS "product.ctime",
mpro."mtime" AS "product.mtime", mpro."editor" AS "product.editor",
mpro."target" AS "product.target"
FROM "mshop_product" AS mpro

WHERE ( mpro."siteid" IN (1) AND mpro."code" IN ('{','name: \"laravel/laravel\"','description: \"The Laravel Framework.\"','keywords: [\"framework\"','license: \"MIT\"','type: \"project\"','require: {','php: \">=7.0.0\"','aimeos/aimeos-laravel: \"~2017.10\"','aimeoscom/omnipay-sofort: \"^2.3\"','fideloper/proxy: \"~3.3\"','guzzlehttp/guzzle: \"^6.3\"','laravel/framework: \"5.5.*\"','laravel/tinker: \"~1.0\"','omnipay/paypal: \"~2.0\"','symfony/event-dispatcher: \"^2.8\"',' }','require-dev: {','filp/whoops: \"~2.0\"','fzaninotto/faker: \"~1.4\"','mockery/mockery: \"~1.0\"','phpunit/phpunit: \"~6.0\"','symfony/thanks: \"^1.0\"','autoload: {','classmap: [','database/seeds','database/factories',' ]','psr-4: {','App\\\\: \"app/\"',' }','autoload-dev: {','Tests\\\\: \"tests/\"','extra: {','laravel: {','dont-discover: [',' ]','scripts: {','post-root-package-install: [','@php -r \\file_exists(\'.env\') || copy(\'.env.example\'','post-create-project-cmd: [','@php artisan key:generate','post-autoload-dump: [','Illuminate\\\\Foundation\\\\ComposerScripts::postAutoloadDump','@php artisan package:discover','post-update-cmd: [','php artisan vendor:publish --tag=public --force','php artisan migrate','config: {','preferred-install: \"dist\"','sort-packages: true','optimize-autoloader: true','}') )
GROUP BY mpro."id", mpro."siteid", mpro."typeid", mpro."code",
mpro."label", mpro."config", mpro."start", mpro."end",
mpro."status", mpro."ctime", mpro."mtime", mpro."editor",
mpro."target"


LIMIT 53 OFFSET 0

What could be the error here? if i unquote in heidisql the query works. Seems to be a problem with quoting or connection configuration.

timothy_truckle
Posts: 10
Joined: 12 Mar 2018, 14:00

Re: csv import mysql error

Post by timothy_truckle » 12 Mar 2018, 14:30

Aimeos Version from composer.json: "aimeos/aimeos-laravel": "~2017.10"

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: csv import mysql error

Post by aimeos » 13 Mar 2018, 13:06

You didn't configure the directory that contains the CSV files:
https://aimeos.org/docs/Configuration/C ... v/location
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

timothy_truckle
Posts: 10
Joined: 12 Mar 2018, 14:00

Re: csv import mysql error

Post by timothy_truckle » 13 Mar 2018, 14:19

I think the issue is not with the path or csv, the query made by the importer is not properly quoted, a file import attempt is never made, if i remove the zip-file the output is the same:

#20 {main}Number of apostrophes don't match:

followed by the query:

SELECT mpro."id" AS "product.id", mpro."siteid" AS "product.siteid",
mpro."typeid" AS "product.typeid", mpro."code" AS "product.code",
mpro."label" AS "product.label", mpro."config" AS "product.config",
mpro."start" AS "product.datestart", mpro."end" AS "product.dateend",
mpro."status" AS "product.status", mpro."ctime" AS "product.ctime",
mpro."mtime" AS "product.mtime", mpro."editor" AS "product.editor",
mpro."target" AS "product.target"
FROM "mshop_product" AS mpro

WHERE ( mpro."siteid" IN (1) AND mpro."code" IN ('{','name: \"laravel/laravel\"','description: \"The Laravel Framework.\"','keywords: [\"framework\"','license: \"MIT\"','type: \"project\"','require: {','php: \">=7.0.0\"','aimeos/aimeos-laravel: \"~2017.10\"','aimeoscom/omnipay-sofort: \"^2.3\"','fideloper/proxy: \"~3.3\"','guzzlehttp/guzzle: \"^6.3\"','laravel/framework: \"5.5.*\"','laravel/tinker: \"~1.0\"','omnipay/paypal: \"~2.0\"','symfony/event-dispatcher: \"^2.8\"',' }','require-dev: {','filp/whoops: \"~2.0\"','fzaninotto/faker: \"~1.4\"','mockery/mockery: \"~1.0\"','phpunit/phpunit: \"~6.0\"','symfony/thanks: \"^1.0\"','autoload: {','classmap: [','database/seeds','database/factories',' ]','psr-4: {','App\\\\: \"app/\"',' }','autoload-dev: {','Tests\\\\: \"tests/\"','extra: {','laravel: {','dont-discover: [',' ]','scripts: {','post-root-package-install: [','@php -r \\file_exists(\'.env\') || copy(\'.env.example\'','post-create-project-cmd: [','@php artisan key:generate','post-autoload-dump: [','Illuminate\\\\Foundation\\\\ComposerScripts::postAutoloadDump','@php artisan package:discover','post-update-cmd: [','php artisan vendor:publish --tag=public --force','php artisan migrate','config: {','preferred-install: \"dist\"','sort-packages: true','optimize-autoloader: true','}') )
GROUP BY mpro."id", mpro."siteid", mpro."typeid", mpro."code",
mpro."label", mpro."config", mpro."start", mpro."end",
mpro."status", mpro."ctime", mpro."mtime", mpro."editor",
mpro."target"


LIMIT 53 OFFSET 0

-------------------------------------------------------------------------------------------
copy the query AS IS to heidisql(or whatever mysql client): does not work bc wrong quoting!
-------------------------------------------------------------------------------------------

Anyway here is the config and csv, the path is readable by user and www-data(webserver), the file is named import.zip and contains a handcrafted csv (one line):

Config:

config/shop.php
...
'controller' => [
'jobs' => [
'import' => [
'csv' => [
'location' => '/home/user/projects/shop/storage/tmp'
],
],
'product' => [
'import' => [
'csv' => [
'mapping' => array(
'item' => array(
0 => 'product.code', // e.g. unique EAN code
1 => 'product.label', // UTF-8 encoded text, also used as product name
2 => 'product.type', // type of the product, e.g. "default" or "selection"
3 => 'product.status', // enabled (1) or disabled (0)
),
'text' => array(
4 => 'text.type', // e.g. "short" for short description
5 => 'text.content', // UTF-8 encoded text
6 => 'text.type', // e.g. "long" for long description
7 => 'text.content', // UTF-8 encoded text
),
'media' => array(
8 => 'media.url', // relative URL of the product image on the server
),
'price' => array(
9 => 'price.currencyid', // three letter ISO currency code
10 => 'price.quantity', // the quantity the price (for block pricing)
11 => 'price.value', // price with decimals separated by a dot
12 => 'price.taxrate', // tax rate with decimals separated by a dot
),
'attribute' => array(
13 => 'attribute.code', // code of an attribute, will be created if not exists
14 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.
),
'product' => array(
15 => 'product.code', // e.g. EAN code of another product
16 => 'product.lists.type', // e.g. "suggestion" for suggested product
),
'property' => array(
17 => 'product.property.value', // arbitrary value for the corresponding type
18 => 'product.property.type', // e.g. "package-weight"
),
'catalog' => array(
19 => 'catalog.code', // e.g. Unique category code
20 => 'catalog.lists.type', // e.g. "promotion" for top seller products
),
),
],
],
],
...

CSV:

"1","30 Alte Hausschlachterezepte","default","1","short","30 leckere Hausschlachterrezepte","long","Inhaltsverzeichnis: 1. Delikateß - Leberwurst 2. Feine Leberwurst","/3/0/30-hausschlachte_1.jpg","EUR","1","3.7300","7.00","","","","","","","home",""

Any ideas? I am new to laravel and aimeos, please point me in the right direction.

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: csv import mysql error

Post by aimeos » 14 Mar 2018, 10:47

We have been able to reproduce the problem but are still looking for the root cause of it. The good news: It's nothing that can lead to an SQL injection because the exeception is thrown when building the SQL locally.

Nevertheless, your error is still that the location of the directory containing the CSV is incorrect. It's "controller/jobs/product/import/csv/location" not "controller/jobs/import/csv/location":
https://aimeos.org/docs/Configuration/C ... v/location
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: csv import mysql error

Post by aimeos » 14 Mar 2018, 13:56

OK, the problem has been fixed and new minor releases are available. Please run "composer update".
Thank you for reporting the issue!
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

timothy_truckle
Posts: 10
Joined: 12 Mar 2018, 14:00

Re: csv import mysql error

Post by timothy_truckle » 12 Jul 2018, 09:20

Thank you!
Shop is now up and running. You can see it in Action at https://www.wurstrezepte.org

I'd like to put the template (css/js etc) into a separate package, maybe someone else is interested in it. What would be the best way to do this? Thanks again and sorry for the late reply.

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: csv import mysql error

Post by aimeos » 12 Jul 2018, 12:14

Cool! :-)

You can create a new extension with your changes:
https://aimeos.org/developer/extensions ... _extension
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply