WL#13168: Client library safe LOAD DATA LOCAL INFILE paths/directories

Affects: Server-8.0   —   Status: Complete

LOAD DATA INFILE is an SQL command. Thus it can only reliably be parsed on the server. Thus the server returns the name to read to the client. But this means a rogue server can pass any file for the client to read and send over the wire. Even one different from the file supplied by the client.

To solve this issue the client needs to know the name of the file to send before sending the command so it can match it with the one returned by the server.

This worklog will add a new mysql_options() option for client apps to set that name.

Similarly to secure-file-priv this can be:

  • null pointer, meaning nothing is allowed
  • a directory meaning only the files in that directory and its sub-directories are allowed

There will also be a corresponding mysql/mysqltest command line option to set the new mysql_options() option. The mysqlimport utility and the shell equivalent will also be made to set the name properly for each file they consume. mysqldump --tab option stores the files into a single directory so for it to work the directory name that was given to dump should be specified to mysql too.

This would fix the usability issue with LOAD DATA INFILE at the client side without compromising on the security aspects.

  • FR1: a new mysql_options()/mysql_get_option() option called MYSQL_OPT_LOAD_DATA_LOCAL_DIR taking a string or a null pointer
    • FR1.1 if set to null pointer this means no exceptions: all LOAD DATA are as per MYSQL_OPT_LOCAL_INFILE
    • FR1.2. if set to a directory path all files in this directory will be accepted and LOAD DATA will proceed even when MYSQL_OPT_LOCAL_INFILE is on.
    • FR1.3. the option will have an immediate effect on the next query executed after it's set
    • FR1.4. The option can be set at any time during the lifetime of the MYSQL handle.
    • FR1.5: The comparison between the file name supplied and the directory supplied will be done via a case sensitive comparison regardless of the underlying file system's case sensitivity
  • FR2: a new command line option to mysql called --load-data-local-dir taking a directory path (passed directly) or an empty string (passed as a null pointer, the default) that passes its value to MYSQL_OPT_LOAD_DATA_LOCAL_DIR.
    • FR2.1. If the option is specified and mysql_options() fails the mysql execution will stop with an error
  • FR3: make mysqlimport set MYSQL_OPT_LOAD_DATA_LOCAL_DIR for each file it processes.
    • FR 3.1.: mysqlimport will not fail if the option cannot be set (e.g. older driver)
  • FR4: if MYSQL_OPT_LOCAL_INFILE is enabled MYSQL_OPT_LOAD_DATA_LOCAL_DIR has no effect and all LOAD DATA are allowed
  • NF5: the default state (no new option specified) is backward compatible with the current libmysql/mysql/mysqlimport behavior
  • NF6: replication will not be affected since LOAD DATA LOCAL is transformed either into series or row events or into LOAD DATA
  • NF7: this whole worklog touches only on the client side. No changes to the server.
  • NF8: After this change the client will always set the CLIENT_LOCAL_FILES capabilities flag in the client reply network packet. It might or might not work, depending on the value of the MYSQL_OPT_LOAD_DATA_LOCAL_DIR and whether the file requested is within it. But, to comply with FR1.3 (and not allow setting MYSQL_OPT_LOAD_DATA_LOCAL_DIR before connection) we need to tell the server that we support LOAD DATA LOCAL INFILE and later decide at runtime if we actually support it or not.

The following interface changes will be made:

  • a new mysql_options()/mysql_get_option() option MYSQL_OPT_LOAD_DATA_LOCAL_DIR
  • a new command line option for mysql --load-data-local-dir (string, default empty)

The new option will be stored as a string into MYSQL->options.extension that's always allocated hence binary backward compatible (clients compiled against an older libmysql will still work).

The following changed behavior will happen:

  1. if MYSQL_OPT_LOCAL_INFILE (default) and one sets MYSQL_OPT_LOAD_DATA_LOCAL_DIR to non-empty all files in this directory and its subdirectories will still be accepted as valid requests from the server and LOAD DATA LOCAL INFILE with these will work.
  2. the mysqlimport will set MYSQL_OPT_LOAD_DATA_LOCAL_DIR for the files it processes.

Note that for this option to have an effect the server needs to enable local-infile. This is safe since the risk is on the client and it is up to the client to deal with the risk

Effects on mysqlbinlog

mysqlbinlog will extract the files from the binlog events, put them on the local file system and then produce a bunch of LOAD DATA LOCAL INFILE to load these files back.

By default mysqlbinlog writes these to the designated OS temporary directory. But since other files could end up there it's advisable that people using mysqlbinlog would also use the https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html#option_mysqlbinlog_local-load option to store these files in a designated directory instead. And then pass on this directory to the mysql's --load-data-local-dir option.



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