The LOAD DATA statement loads a data file into a table. The statement can load a file located on the server host, or, if the LOCAL keyword is specified, on the client host.
The LOCAL version of LOAD DATA has two potential security issues:
Because LOAD DATA LOCAL is an SQL statement, parsing occurs on the server side, and transfer of the file from the client host to the server host is initiated by the MySQL server, which tells the client the file named in the statement. In theory, a patched server could tell the client program to transfer a file of the server's choosing rather than the file named in the statement. Such a server could access any file on the client host to which the client user has read access. (A patched server could in fact reply with a file-transfer request to any statement, not just LOAD DATA LOCAL, so a more fundamental issue is that clients should not connect to untrusted servers.)
In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not a remote program being run by users who connect to the Web server.
To avoid connecting to untrusted servers, clients can establish a secure connection and verify the server identity by connecting using the --ssl-mode=VERIFY_IDENTITY option and the appropriate CA certificate.
To avoid LOAD DATA issues, clients should avoid using LOCAL unless proper client-side precautions have been taken.
For control over local data loading, MySQL permits the capability to be enabled or disabled. In addition, as of MySQL 8.0.21, MySQL enables clients to restrict local data loading operations to files located in a designated directory.
Adminstrators and applications can configure whether to permit local data loading as follows:
On the server side:
The local_infile system variable controls server-side LOCALcapability. Depending on thelocal_infile setting, the server refuses or permits local data loading by clients that request local data loading.
By default,local_infile is disabled. To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld withlocal_infile disabled or enabled.local_infile can also be set at runtime.
On the client side:
The ENABLED_LOCAL_INFILECMake option controls the compiled-in default LOCAL capability for the MySQL client library (seeSection 2.9.7, “MySQL Source-Configuration Options”). Clients that make no explicit arrangements therefore haveLOCAL capability disabled or enabled according to theENABLED_LOCAL_INFILEsetting specified at MySQL build time.
By default, the client library in MySQL binary distributions is compiled withENABLED_LOCAL_INFILEdisabled. If you compile MySQL from source, configure it with ENABLED_LOCAL_INFILEdisabled or enabled based on whether clients that make no explicit arrangements should haveLOCAL capability disabled or enabled.
For client programs that use the C API, local data loading capability is determined by the default compiled into the MySQL client library. To enable or disable it explicitly, invoke themysql_options() C API function to disable or enable theMYSQL_OPT_LOCAL_INFILE option. SeeSection 28.7.6.50, “mysql_options()”.
For the mysql client, local data loading capability is determined by the default compiled into the MySQL client library. To disable or enable it explicitly, use the--local-infile=0 or--local-infile[=1] option.
For the mysqlimport client, local data loading is not used by default. To disable or enable it explicitly, use the--local=0 or--local[=1] option.
If you useLOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add a local-infileoption setting to that group. To prevent problems for programs that do not understand this option, specify it using theloose-prefix:
[client]loose-local-infile=0or:
[client]loose-local-infile=1In all cases, successful use of aLOCAL load operation by a client also requires that the server permits local loading.
If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue aLOAD DATA LOCAL statement receives the following error message:
ERROR 3950 (42000): Loading local data is disabled; this must beenabled on both the client and server sideAs of MySQL 8.0.21, the MySQL client library enables client applications to restrict local data loading operations to files located in a designated directory. Certain MySQL client programs take advantage of this capability.
Client programs that use the C API can control which files to permit for load data loading using theMYSQL_OPT_LOCAL_INFILE andMYSQL_OPT_LOAD_DATA_LOCAL_DIR options of themysql_options() C API function (see Section 28.7.6.50, “mysql_options()”).
The effect of MYSQL_OPT_LOAD_DATA_LOCAL_DIRdepends on whether LOCAL data loading is enabled or disabled:
If LOCAL data loading is enabled, either by default in the MySQL client library or by explicitly enabling MYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option has no effect.
If LOCAL data loading is disabled, either by default in the MySQL client library or by explicitly disabling MYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option can be used to designate a permitted directory for locally loaded files. In this case, LOCAL data loading is permitted but restricted to files located in the designated directory. Interpretation of theMYSQL_OPT_LOAD_DATA_LOCAL_DIR value is as follows:
If the value is the null pointer (the default), it names no directory, with the result that no files are permitted for LOCAL data loading.
If the value is a directory path name,LOCAL data loading is permitted but restricted to files located in the named directory. Comparison of the directory path name and the path name of files to be loaded is case-sensitive regardless of the case-sensitivity of the underlying file system.
MySQL client programs use the precedingmysql_options() options as follows:
The mysql client has a--load-data-local-dir option that takes a directory path or an empty string.mysql uses the option value to set theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option (with an empty string setting the value to the null pointer). The effect of--load-data-local-dir depends on whether LOCAL data loading is enabled:
When --load-data-local-dirapplies, the option value designates the directory in which local data files must be located. Comparison of the directory path name and the path name of files to be loaded is case-sensitive regardless of the case-sensitivity of the underlying file system. If the option value is the empty string, it names no directory, with the result that no files are permitted for local data loading.
If LOCAL data loading is enabled, either by default in the MySQL client library or by specifying--local-infile[=1], the--load-data-local-diroption is ignored.
If LOCAL data loading is disabled, either by default in the MySQL client library or by specifying--local-infile=0, the--load-data-local-diroption applies.
mysqlimport setsMYSQL_OPT_LOAD_DATA_LOCAL_DIR for each file that it processes so that the directory containing the file is the permitted local loading directory.
For data loading operations corresponding toLOAD DATA statements,mysqlbinlog extracts the files from the binary log events, writes them as temporary ffiles to the local file system, and writesLOAD DATA LOCAL statements to cause the files to be loaded. By default, mysqlbinlog writes these temporary files to an operating system-specific directory. The --local-load option can be used to explicitly specify the directory wheremysqlbinlog should prepare local temporary files.
Because other processes can write files to the default system-specific directory, it is advisable to specify the--local-load option tomysqlbinlog to designate a different directory for data files, and then designate that same directory by specifying the--load-data-local-dir option to mysql when processing the output frommysqlbinlog.
联系客服
