Sequel-ace: Cannot run load data local infile query

[rbalslev]

• Sequel Ace Version: 2.1.2, build 2060
• macOS Version: 10.15.6
• MySQL Version: 5.7.30

Description
A query with "load data local infile" works as expected in Sequel Pro (build 5446) but in Sequel Ace I get "The used command is not allowed with this MySQL version". They use same DB, connection configuration etc.

Steps To Reproduce

1. Execute the following query:

load data local infile '/Users/balslev15/Downloads/ProductData.txt' into table ps_product_temp
    fields terminated by ';'
    optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (@dummy, @externalID, @ean, @dummy, @name, @typeno, @manufacturer, @dummy, @unit, @dummy, @dummy, @dummy, @taxvalue, @dummy, @dummy,
    @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @mpv, @packagesize, @dummy, @dummy, @factor, @discountcode, @dummy,
    @dummy, moq2, @campaignstart, @campaignend, @description, @tags, @breadcrumb)
    set impID = 124, externalID = trim(@externalID), ean = trim(@ean), name = trim(@name), typeno = trim(@typeno),
        manufacturer = trim(@manufacturer), unit = trim(@unit), taxvalue = replace(@taxvalue, ',', '.'), mpv = replace(@mpv, ',', '.'),
        packagesize = replace(@packagesize, ',', '.'), factor = replace(@factor, ',', '.'), discountcode = trim(@discountcode),
        campaignstart = date(concat(substring(@campaignstart, 7, 4), '-', substring(@campaignstart, 4, 2), '-', left(@campaignstart, 2))),
        campaignend = date(concat(substring(@campaignend, 7, 4), '-', substring(@campaignend, 4, 2), '-', left(@campaignend, 2))),
        description = trim(@description), tags = trim(@tags), breadcrumb = trim(@breadcrumb);

Expected Behaviour
Data is loaded into table ps_product_temp

Is Issue Present in Latest Beta?
Additionally tested in 2.1.4-beta1, build 2068

Additional Context

create table ps_product_temp (
    impID int,
    externalID varchar(50),
    ean varchar(50),
    name varchar(255),
    typeno varchar(50),
    manufacturer varchar(100),
    unit varchar(50),
    taxvalue float,
    mpv float,
    packagesize float,
    factor float,
    discountcode varchar(50),
    moq2 varchar(50),
    campaignstart datetime,
    campaignend datetime,
    description longtext,
    tags text,
    breadcrumb text
);

[Jason-Morcos]

Sequel Ace is sandboxed (required by the App Store) and cannot access files in your filesystem unless you manually give access to them.
In order to be able to access that file (/Users/balslev15/Downloads/ProductData.txt), you'd need to go to Sequel Ace's preferences, navigate to the files tab, and add that file as a file that sequel ace has been granted access to.

[rbalslev]

Hi Jason
I saw that and preferences was set, see attached.

...And thanks a lot, by the way, for your work!

[Jason-Morcos]

Ahh, perhaps we are not connecting with allow infile set to true?
Any thoughts @Sequel-Ace/all?
https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version

[gboudreau]

Ahh, perhaps we are not connecting with allow infile set to true?
Any thoughts @Sequel-Ace/all?

Yes, that is probably it. I'll take a look when I'm back at my computer in an hour or two.

[Jason-Morcos]

Yes, that is probably it. I'll take a look when I'm back at my computer in an hour or two.

I think the question becomes if this is something we should allow only when a specific preference is enabled as opposed to always? It seems that allowing infile could be perhaps a minor security vulnerability?

Perhaps in the connection settings we could let users enable certain things? Like infile support
We could also add for the user to be able to set the safe mode stuff referenced in #44

[gboudreau]

Reproduced.
I get this error, on MySQL 8:

Loading local data is disabled; this must be enabled on both the client and server sides

On 5.7:

The used command is not allowed with this MySQL version

[gboudreau]

So we can enable local-infile easily in the code (see above commit).
The problem with that is that a rogue server could use that to read any file the client has access to (ref).
While sandboxing on Sequel Ace limits this, anyone connecting using SSH will give Sequel Ace access to his private SSH keys, and the rogue MySQL server could then read those files. Not cool!

I think the best option would be to use the very-recently-added MYSQL_OPT_LOAD_DATA_LOCAL_DIR option, instead of MYSQL_OPT_LOCAL_INFILE, which allows the client to define a folder from which the server can load files. Rogue servers could not read any files outside this folder.
We could allow the end-users to select that folder using a new button/selector, in the Files tab of the Preferences panel. Doing so would give Sequel Ace access to all files in that folder (using bookmarks), and we'd set the MYSQL_OPT_LOAD_DATA_LOCAL_DIR MySQL option to that folder, when connecting, thus allowing LOAD DATA LOCAL INFILE... to load any file from that folder.
(That option was added in libmysqlclient v8.0.21, so we'd need a minor bump of libmysqlclient, since we updated to 8.0.20 in #38).

Related note: I suck at UI in Xcode; if someone could add the required UI change, I could finish the PR to use the selected folder while connecting, and upgrade libmysqlclient.

[rbalslev]

One very impressed user here. Kudos to all of you!

[Jason-Morcos]

We have a WIP PR for this but someone would need to tinker/finish it out before we'd merge it through

247

[Jason-Morcos]

Reopening until 2.2.0 is actually released