Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.3Mb
PDF (A4) - 33.3Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 144.9Kb
Man Pages (Zip) - 205.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  Security Issues with LOAD DATA LOCAL

Pre-General Availability Draft: 2017-09-19

6.1.6 Security Issues with LOAD DATA LOCAL

The LOAD DATA statement can load a file located on the server host, or, if the LOCAL keyword is specified, on the client host.

There are two potential security issues with the LOCAL version of LOAD DATA:

  • The transfer of the file from the client host to the server host is initiated by the MySQL server. 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. (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 LOAD DATA issues, clients should avoid using LOCAL. To avoid connecting to untrusted servers, clients can establish a secure connection and verify the server identity by connecting using the --ssl-mode=VERIFY_IDENTIFY option and the appropriate CA certificate.

To enable adminstrators and applications to manage the local data loading capability, LOCAL configuration works like this:

  • On the server side:

    • The local_infile system variable controls server-side LOCAL capability. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. 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 with local_infile disabled or enabled, respectively. local_infile can also be set at runtime.

  • On the client side:

    • The ENABLED_LOCAL_INFILE CMake option controls the compiled-in default LOCAL capability for the MySQL client library. Clients that make no explicit arrangements therefore have LOCAL capability disabled or enabled according to the ENABLED_LOCAL_INFILE setting specified at MySQL build time.

      By default, the client library in MySQL binary distributions is compiled with ENABLED_LOCAL_INFILE disabled. If you compile MySQL from source, configure it with ENABLED_LOCAL_INFILE disabled or enabled based on whether clients that make no explicit arrangements should have LOCAL capability disabled or enabled, respectively.

    • Client programs that use the C API can control load data loading explicitly by invoking mysql_options() to disable or enable the MYSQL_OPT_LOCAL_INFILE option. See Section 27.7.7.50, “mysql_options()”.

    • For the mysql client, local data loading is disabled by default. To disable or enable it explicitly, use the --local-infile=0 or --local-infile[=1] option.

    • For the mysqlimport client, local data loading is disabled by default. To disable or enable it explicitly, use the --local=0 or --local[=1] option.

    • If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add an local-infile option setting to that group. To prevent problems for programs that do not understand this option, specify it using the loose- prefix:

      [client]
      loose-local-infile=0

      or:

      [client]
      loose-local-infile=1
    • In all cases, successful use of a LOCAL load operation by a client also requires that the server permits it.

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

User Comments
  Posted by on August 21, 2003
This topic often leads to problems in combination with PHP. If you followed one of the many documents which tell you to compile PHP with "--with-mysql", you didn't link against the MySQL libraries, but you're using the internal MySQL client of PHP, which seems to cause trouble, even if the MySQL server is correctly built with "--enable-local-infile".

Recompiling PHP with "--with-mysql=/usr" solved the problem instantly, because it links against the original MySQL libraries with local file handling enabled. No further configuration needed.
  Posted by on October 14, 2003
For use in perl DBI scripts, adding an option at the end of the data source definition for DBI->connect fixes the LOAD DATA LOCAL problem in some situations...

use strict;
use DBI;
my $dsn = "DBI:mysql:mydb;mysql_local_infile=1"; <----
my $user = "me";
my $password = "secret";
my $dbh = DBI->connect($dsn,$user,$password);

Jeff

  Posted by Jimmy Zhang on February 17, 2004
if you are using latest version of phpMyAdmin and have problem uploading CSV, you can go to edit ldi_table.php

you can find "DATA LOCAL" and reach here:
...($local_option_selected ? ' checked="checked" ' : ''); ?>/><label for="radio_local_option_0">...DATA</label><br />
...(!$local_option_selected ? ' checked="checked" ' : ''); ?>/><label for="radio_local_option_1">...DATA LOCAL</label>

you can change them into
...(!$local_option_selected ? ' checked="checked" ' : ''); ?>/><label for="radio_local_option_0">...DATA</label><br />
...($local_option_selected ? ' checked="checked" ' : ''); ?>/><label for="radio_local_option_1">...DATA LOCAL</label>

to make ...DATA default instead of ...DATA LOCAL
in this way, you will be able to upload your CSV files smoothly
  Posted by Rob McMillin on March 13, 2004
I have to agree with the poster upthread. The documentation for this misfeature (which SHOULD BE REMOVED!) is awful. If you're so dense as to be concerned about somebody taking over your server, you have physical security issues and THAT is what needs to be resolved.
  Posted by Douglas Marsh on April 5, 2004
I think the problem is "LOCAL" is sometimes confusing... I'd suggest that the Syntax change to (instead of using or NOT using the word "LOCAL")...

LOAD DATA [SERVER/CLIENT] INFILE 'filename.txt' [... INTO ...];

  Posted by Cory Sytsma on July 15, 2004
If using phpMyAdmin from the same computer as your MySQL instance, you can easily get around this problem by changing the LOAD radio box to ...DATA, when using 'Insert data from a textfile into table' feature off the SQL tab. Modifying ldi_table.php, as mentioned above, will just change the default value selected here.

I don't have a lot of experience with this, but since I'm running phpMyAdmin from the same computer as my MySQL database, I'm assuming that's why this works.
  Posted by Emory Smith on September 26, 2004
for those using php and unable to LOAD DATA INFILE on a remote host (with only ftp access):

first try using phpMyAdmin - you will probably need to customize some variables in the .../phpMyAdmin/config.inc.php file (search repeatedly for "upload" in http://www.phpmyadmin.net/documentation/ to see which ones). i found this to work ok for small files (ie - table exports with no binary data).

attempting to load large files (for instance exporting binary data from a local database to remote one) in this manner proved hopeless in my case due to host provider's upload restrictions, timeouts and other issues mentioned above.

for those in the same boat, here's an alternate solution that i found to be both faster and more reliable than using phpMyAdmin:

1. format your INFILE so that you have one sql statement per line*
2. upload the INFILE to remote server (however you like) and make sure both the file and parent directory are readable by php
3. write a php script that opens a file and repeatedly
- - - a. reads a single line into a string**
- - - b. calls mysql_query($string);
4. upload your script (preferably to passwd protected dir) and invoke it with the path to your INFILE.

* beware -- if you are using OS X along with pre-4.3 version of php, you must convert all end-of-line characters in the INFILE from mac-style to unix style. you can do this with the "tr" command:

% tr '\015' '\012' < mac.txt > unix.txt

for php4.3 and later, line endings are no problem, just call

set_ini('auto-detect-line-endings',1);

as described at http://www.php.net/manual/en/ref.filesystem.php#ini.auto-detect-line-endings

** if youre not sure how you want to do this, try "fgets()" ... see the code example at http://www.php.net/manual/en/function.fgets.php
  Posted by David Weingart on December 23, 2004
You can use LOAD DATA LOCAL with recent versions of PHP without recompiling PHP.

Passing 128 (the value of the CLIENT_LOCAL_FILES constant) as the fifth parameter to mysql_connect () enables LOAD DATA LOCAL on the client side.

Example: $dbh = mysql_connect($server, $user, $pass, false, 128);

For PHP 4.3 and above.

  Posted by Peter Keane on September 12, 2005
Hi,

I was getting "the used command is not allowed with this mysql version". using mysql 4.1.11-standard-log. VB6 and MyODBC 3.51.06 on win2000. For this set up you need to add option 65536 Read parameters from the client and odbc groups from my.cnf.

Check this option in the MyOdbc options or an alternative is just to add this option to the connectionstring e.g. option=65536.

In the file c:\my.cnf (or where ever it is) add the following
[odbc]
local-infile=1

This sorted out the problem for me.

Peter Keane
  Posted by John Nagle on March 26, 2007
The standard configuration for shared and dedicated hosting today with the Plesk server administration system sets
"set-variable=local-infile=0" in "/etc/my.cnf". Thus, LOAD DATA LOCAL is disabled on such servers.

SHOW VARIABLES LIKE "local%";
will display the current setting of "local_infile".

Using the command line "mysql --local-infile=1" does not produce any error message when local_infile is OFF at the server end, even though "local_infile" is not thereby enabled.
  Posted by Geoffrey Transom on September 14, 2007
I agree completely with those who think there has been a 'negative improvement' recently.

My server has local_infile ON, and PHP is compiled with mysql... yet

LOAD DATA LOCAL INFILE [full file path, right from /home/.. ] INTO TABLE `TABLENAME` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

now produces different results depending on the client. It does not work in PHP scripts, nor does it work in mySQL Query browser... but it does work in phpMySQLAdmin. The same user/password was used for all attempts to get teh stupid thing to play like it used to.

It used to work just fine, and I have about a dozen scripts that use it. Simply trying to stick a CSV (on my own server) into an existing table (also on my own server) - and use a cron-job PHP script to do so rather than having to plod around doing it by hand in phpMySQLAdmin like a Dark Ages numpty.

Someone tell me what I am missing... tried giving it the relative path to the file, tried to absolute path, but NO JOY.

Cheers,

GT
  Posted by George S on January 14, 2008
I found this solution on another site.. in case the thread gets deleted I'll copy & paste it below.. Here is the original link:
http://forums.westhost.com/showthread.php?t=7010#6

edit my.cnf in /etc/:
----------------------------------------
[mysqld]
...(other stuff)
local-infile=1
[mysql]
...(other stuff)
local-infile=1

then shutdown, then start mysql:
----------------------------------------
shell> mysqladmin shutdown
shell> cd /usr/libexec
shell> mysqld
  Posted by Jorge Albarenque on July 29, 2013
I have some web forms on my intranet which uploaded a file to a database using this function. It broke after upgrading Debian to its Wheezy release.

In order to fix it I had to:
* Add local-infile=1 to the [mysqld] and [mysql] sections of my.cnf (as explained in the comments above)
* Use mysqli_real_connect function (check documentation).

The catch is that with that function you can explicitly enable the support for LOAD DATA LOCAL INFILE. For example (procedural style):

$link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($link, 'localhost', $username, $password, $database);

This did not require any recompiling or anything else, works straigh away on Debian Wheezy. Hope this helps!!
Sign Up Login You must be logged in to post a comment.