The LOAD DATA
statement can load a
file that is located on the server host, or it can load a file
that is located on the client host when the
LOCAL
keyword is specified.
There are two potential security issues with supporting the
LOCAL
version of LOAD
DATA
statements:
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.
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 command
against the SQL server). In this environment, the client with
respect to the MySQL server actually is the Web server, not
the remote program being run by the user who connects to the
Web server.
To deal with these problems, we changed how
LOAD DATA
LOCAL
is handled as of MySQL 3.23.49 and MySQL 4.0.2
(4.0.13 on Windows):
By default, all MySQL clients and libraries in binary
distributions are compiled with the
--enable-local-infile
option, to be
compatible with MySQL 3.23.48 and before.
If you build MySQL from source but do not invoke
configure with the
--enable-local-infile
option,
LOAD DATA
LOCAL
cannot be used by any client unless it is
written explicitly to invoke
mysql_options(...
MYSQL_OPT_LOCAL_INFILE, 0)
. See
Section 20.9.3.49, “mysql_options()
”.
You can disable all
LOAD DATA
LOCAL
commands from the server side by starting
mysqld with the
--local-infile=0
option.
For the mysql command-line client, enable
LOAD DATA
LOCAL
by specifying the
--local-infile[=1]
option, or
disable it with the
--local-infile=0
option. For
mysqlimport, local data file loading is off
by default; enable it with the
--local
or
-L
option. In any case, successful use of a
local load operation requires that the server is enabled to
allow it.
If you use LOAD
DATA LOCAL
in Perl scripts or other programs that
read the [client]
group from option files,
you can add the local-infile=1
option to
that group. However, to keep this from causing problems for
programs that do not understand
local-infile
, specify it using the
loose-
prefix:
[client] loose-local-infile=1
If LOAD DATA
LOCAL
is disabled, either in the server or the
client, a client that attempts to issue such a statement
receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version
MySQL Enterprise.
Security advisors notify subscribers to the MySQL Enterprise
Monitor whenever a server is started with the
--local-infile
option enabled.
For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
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.
In a word...LAME!
The lack of usable examples for overcoming this "security improvement"is INEXCUSABLE! Especially given how widely this function is used....
I lost 4 hours and 1 client because a simple DB import via PHP that used to work no longer does.. The IRS manual makes more sense than the docs for "LOAD DATA [LOCAL]"
Let me elaborate what took forever to find out..(*NIX)
1=ON; 0=OFF; LOCAL != PHP;
LOAD DATA INFILE '/full_path_to_file/your_file.txt' INTO TABLE your_table
You MUST have a compiled MySQL with '--enable-local-infile '
You MUST have compiled PHP using the full path to MySQL, otherwise it will use it's internal handlers, which don't work with the "new" LOAD DATA.
--with-mysql=/usr/local/mysql (assuming your MySQL is located here)
You MUST start the MySQL daemon with the option '--local-infile=1'
Given the $time I lost fighting this I could have paid to buy a LOCKED SAFE to put my server in for for security... (Or bought a SQL license...<harsh>)
Next time folks, think features/performance/compatibility when "upgrading"..
Just when we started to convince users that the open-source approach was viable....
Don't get me wrong, I love this stuff...
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
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
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.
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 ...];
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.
You guys screwed up with the LOAD security enhancement. It reminds me of the bone-head move the PHP guys did with register-globals = off by default crap. Piss poor move.
Anyways, here is a workaround for those that are trying to import to a remote server:
upload file to remote server. use the following statement:
LOAD DATA LOCAL INFILE '/path/to/your/file.csv' INTO TABLE `your_table` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
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
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.
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
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.
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
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
Add your own comment.