MySQL Bugs: #96450: mysql shell importTable requires local

Bug #96450	mysql shell importTable requires local_infile set
Submitted:	7 Aug 2019 13:44	Modified:	26 Aug 2020 23:23
Reporter:	Morgan Tocker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Document Store: MySQL Shell	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Via: https://mysqlserverteam.com/mysql-shell-8-0-17-whats-new/

The MySQL Shell requires local_infile=1. The MySQL manual has a security section on this here:

https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html

"In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access."

While this client is not vulnerable (I checked with #shell on MySQL community slack), the setting is only global so it creates surface area for unpatched clients to start using this feature in an insecure way.

How this feature works was recently the subject of:
https://www.percona.com/blog/2019/02/06/percona-responds-to-mysql-local-infile-security-is...

.. and described here:
https://mysqlserverteam.com/what-is-load-data-and-how-does-it-work/

While the vulnerability is in the client, having the server also refuse to use this RPC to clients is important because it encourages users to do something that is insecure.

How to repeat:
Follow the instructions here: https://elephantdolphin.blogspot.com/2019/08/parallel-table-importer-in-mysql-shell.html

Enable local_infile globally as suggested (oh noes!)

Suggested fix:
I am not convinced LOAD DATA is materially faster than a well-constructed bulk-insert, which would not require a setting change.

Not on the shell team, but it would be even better if the server protocol was extended to do a proper batched prepared statement send, and clients could do all this silly work.

Hello Morgan,

Thank you for the report and feedback.

regards,
Umesh

- MySQL CLI session

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.08 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table foo(id int, name varchar(41));
Query OK, 0 rows affected (0.01 sec)

- MySQL Shell session
bin/mysqlsh mysql://root@localhost:3333 --ssl-mode=DISABLED -- util import-table foo.csv --schema=test --table=foo
Please provide the password for 'root@localhost:3333':
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
ERROR: Util.importTable: Invalid preconditions

- MySQL CLI session

mysql> set local_infile=ON;
ERROR 1229 (HY000): Variable 'local_infile' is a GLOBAL variable and should be set with SET GLOBAL

As it is right now both  importTable and loadDump load data using LOAD DATA statements which require the local_infile to be enabled.

The security issues related to local_infile are resolved in the latest client, but this indeed does not prevent older or modified clients to still pose a problem.

I am going to close the issue as not a bug. The only way for shell to not require local_infile=1 is to either change the protocol or not use LOAD DATA anymore. We'll address that in a separate enhancement.