4.2.4 Security issues with LOAD DATA LOCAL

In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with possible security issues when it comes to LOAD DATA LOCAL.

There are two possible problems with supporting this command:

As the reading of the file is initiated from the server, one could theoretically create a patched MySQL server that could read any file on the client machine that the current user has read access to, when the client issues a query against the table.

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 a user could run any command against the SQL server).

There are two separate fixes for this:

If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client. See section 9.1.3.39 mysql_options().

For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option --local-infile[=1], or disabled with --local-infile=0.

By default, all MySQL clients and libraries are compiled with --enable-local-infile, to be compatible with MySQL 3.23.48 and before.

One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0.

In the case that LOAD DATA LOCAL INFILE is disabled in the server or the client, you will get the error message (1148):

The used command is not allowed with this MySQL version

User Comments

There are all kinds of catches to this change. You
will not necessarily get the error message (1148):

The used command is not allowed with this MySQL
version

if attempting to use the "load data local" syntax with
the local clients not configured MySQL with --enable-
local-infile. If you are contacting a 3.23.47 server
on one host from a 3.23.49 client on another host,
for example, you may get an error message like one
of the following two instead:

ERROR 13: Can't get stat of '/tmp/file' (Errcode: 2)

or

ERROR 1105: File './dbname/file' not found
(Errcode: 2)

Depending on whether you specified a relative or
absolute path.

Rerunning the exact same commands, but starting
the mysql command line program with the --local-
infile flag fixes this.

You might also get this kind of error message, too:

ERROR 1045: Access denied for
user: 'testuser@10.1.1.94' (Using password: YES)

But again, simply adding the --local-infile flag to the
mysql command line program fixes it.

I just wasted 3 hours of my time tracking down this
annoying security "improvement" -- thanks but no
thanks, MySQL developers. This was poorly thought
out and poorly implemented.

The idea of disabling LOAD DATA LOCAL as
a "security fix" is a stupid kluge that solves
nothing. The claimed hole (that a user can use a
CGI or PHP script to use LOAD DATA LOCAL to
read any data that the httpd user (nobody) can
read), has NOTHING to do with mysql. Even if
you "close this hole" by disabling LOAD DATA
LOCAL, the user's CGI or PHP script can simply
fopen() any file s/he wants--without bothering to
go through mysql--and send its contents back to
his browser. Everybody knows this hole exists;
that's why httpd is run as UID nobody, to give it
minimum privileges. If you don't trust someone
with even this privilege, you shouldn't give them
the right to upload and execute CGI/PHP scripts
on your server!!! There are lots of problems
with web server security holes (e.g. the fact that
you have to put the username and password for
connecting to the MySQL database in the clear in
a nobody-readable file (your PHP script).
However, this "fix" doesn't solve anything!

In fact, disabling LOAD DATA LOCAL tends to
push people towards LOAD DATA on the server
side, one of the most egregiously terrible
security holes in MySQL.

LOAD LOCAL INFILE generates Error 1148 just as it
should when issued from mysql, it works just fine
when you use mysqlGUI even with
load-local-infile=0

Perhaps there should also be a work-around for the
security issue with mysql_options(...
MYSQL_OPT_LOCAL_INFILE, 0)?
Following the link don't give much..

how to correct the error " The used command is not
allowed with this MySQL version"
when use command " load data local.....

I am still not able to import data. I even tried
simplifying by creating a one field data file and a one
field table and I still get this error 1148. I have a
window2000 Pro and MySql 4.0.4-beta-max-nt. Any
recommendations?

Just because PHP/CGI scripts have the same risk as
mysql with this feature enabled, doesn't mean mysql
should ignore it. What about people who don't even
have PHP/CGI scripts enabled on the server?

So the question is : how to import data (textfile)
in the mysql database? must I go back to 3.xx.?
I'm running 4.05beta

From mysql-max-4.0.8-gamma-sun-solaris2.8-sparc.tar.gz
installation, running the mysqld server without option
(ie. local-infile variable is "ON"):

1) a LOAD DATA LOCAL INFILE command executed with the
mysql from the same installation and without any option,
(ie. local-infile variable display "FALSE") is accepted, whereas it should be refused.
The same abnormal result is given with the mysql option --local-infile=0 (to confirm the "FALSE" value).

2) a LOAD DATA LOCAL INFILE command executed with a mysql
from a 4.05-beta-OSF1 installation, with option
--local-infile=1 (local-infile variable display "TRUE")
is refused (ERROR 1148) whereas it should be accepted.

This is a mysql client option at least when connecting to mysqld-max 3.23.52. When I connect to my mysql-prompt if I want to be able to use a text file for database input I simply start mysql with..

bash$>mysql --local-infile=1 -u myuser -p

I can then use:
LOAD LOCAL DATA INFILE "stupidfile.txt" INTO TABLE mytable;

to populate the database.

This is working for me...

When I try to run the following SQL query



LOAD DATA INFILE '/var/tmp/php0EtQpo' INTO TABLE `table1` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

I get the following result

MySQL said:


Access denied for user: 'userid@localhost' (Using password: YES)

----

my datadir is /var/db/mysql/

What is the best way to upload from server?

I have 4.0.5a-beta-max running on linux. I am really confused with the above discussion whether to make LOCAL infile true or false or whether to start teh daemom mysqld or mysql

I am getting Can't get stat of error.
I would like to know if I have 4.0.5a-beta-max running on linux and if the mysql has been installed with default settings and its running.

How can I load a file? do I have to restart my daemon with --local-infile=0 option? or do I have to simply start the mysql --local-infile=0 and not the daemon. (well I tried this it did not work)

PLEASE any help on this will be WORSHIPPED!!
Thanks
Jin

i ran mysqld-nt --local-infile=1,

then tried LOAD DATA LOCAL INFILE,

it didn't work, when i check the configs with the
winmysqladmin and did a report it should LOCAL_INFILE=ON

this is wierd because i swore i was able to load data from a file before and didn't have to turn this on from the other machine i was running XP, it just doesn't seem to work on W2k

I imported data successfuly setting --local-infile to 0 !!!

It seems that it works in the oposite way. Call that security :)

i ran mysqld normally (local-infile = 1 by default) and then used the "-L" option on mysqlimport and it worked... without the "-L" option no combination of local-infile (on mysqld-nt or mysql) settings would work...

This section of the manual seems a bit unclear, as a new user of mysql, but a long time user of other RDBMS system, I can not even figure out how to enable the Load Data Local File capabilities. Even following some of the advice in the comment section only partially worked. As a new user of mysql I find this feature and the supporting documentation to be of great concern. After what good is a RDBMS that can not load data into a newly created table other then manually one record at a time. I would also suggest that some example of how to implement the suggested fix to make the process clearer, this would be good especially for new users.

I'm a MySQL newbie, but I did figure out how to load a local file in MySQL 4.0.12-nt...

I edited the my.ini file in my Windows directory... Under the heading:
[mysqld]
I added:
set-variable=local-infile=0

Then re-started mySQL. This seems backwards to me; I thought it should be set to 1, but it works like that...

Uh!!!!! I've been down for 1 month now because of this upload problem. This is very bad for me. I've proposed a mysql, php and apache solution to solve our integration needs on a national level to upper management. Because of the cost savings and extensive functionality of this solution, I have them listening.
But because of this security fix and the fact that I cannot upload files on my server any longer all development has stopped and I'm now looking at Postgres instead. I really can't believe that the brains at mysql have implemented such a bad solution. What were they thinking. Basically, I feel that they will be prone to implement the same type of solutions causing even more grief in the future.
The documentation is confusing. There were no examples with exact syntax and the proposed solution hasn't work even after consulting several resources. I don't need these types of hiccups at any level in the my development phase. Please bring this functionality back with a much better security solution

Hi,all. I added "set-variable=local-infile=0" in [mysqld] of my.ini. But in vain.
I'm using mysql4.0.12 and mysqld-max-nt on XP home.

Mahesh,
why don't you check the file permissions and privileges for the user to insert into table1 and to use the file for loading.(see next section 4.2.5)

(Or)
copy the file from the server to your local file and use LOCAL in your query, probably with file extensions.

I think this should work!

" I edited the my.ini file in my Windows directory... Under the heading:
[mysqld]
I added:
set-variable=local-infile=0"

This did the trick for me also.
I'm running XP Pro, the my.ini file is in the WINNT folder.
I can now import csv / txt files.

Good find.

Also, if you're running WinXP, and you are having issues with stability and viewing phpinfo.php with Internet Exploder, the fix lies in downloading the updated afd.sys file if you can find it on the interweb somewhere.
The version I found that worked was: 5.1.2600.1106
It comes in SP1 supposedly, but I'm hesitant about 133+megs and dialup.

HOW TO UPLOAD DATA TO A REMOTE DATABASE
Finally! I spend several hours trying to figure out how that LOAD DATA works. Here is a step-by-step instructions to save your time. My machine runs Windows XP and MySQL 3.23, remote server had Windows 2000 and MySQL 4 (anyway, you should be able to do that with any OS, hopefully)

1. Go to Start / Run / cmd.exe
2. Change directory to MySQL Bin directory (cd mysql\bin)
3. Type the following:
mysql --local-infile -h www.remotehost.net -u username -p

Note: it is important to note that you must start MySQL command line utility with --local-infile option to be able to use Load Data Local Infile command

4. When prompted for password, type it in
5. MySQL will connect to remote host and show you something similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 107 to server version: 4.0.12-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

type: USE databasename;

6. Now if you have a text file with records that you want to upload to the server, type the following:
load data local infile "c:\\temp\\myfile.txt" into table tblRemoteTable;

MySQL will take the file on your disk, in this case C:\Temp\MyFile.txt and upload it to the table in remote database

7. After the upload is done, MySQL will display the following:
Query OK, 3460 rows affected (14 min 26.88 sec)
Records: 3460 Deleted: 0 Skipped: 0 Warnings: 3460

As you can see, it took me about 14 minutes to upload 3460 records, because of my slow connection. Anyway, i hope that it helps.

Levon Levonian

I now no why I have wasted half a day. I am running the server on my own computer and don't need the word "LOCAL" when executing "LOAD DATA INFILE". The other screw up is using two back slashes after c: (ie. c:\\myfile.txt).

Example:
LOAD DATA INFILE "c:\\myfile.txt" INTO TABLE mytable;

" I edited the my.ini file in my Windows directory... Under the heading:
[mysqld]
I added:
set-variable=local-infile=0"

This did the trick for me also.
I'm running XP Pro, the my.ini file is in the WINNT folder.
I can now import csv / txt files.

Good find.
----------------
Sorry, but I can't agree with this. I tried a lot settings for "local-infile" but it doesn't work in the way I need.

It drives me mad. Can anyone help to fix this?

I tried "set-variable=local-infile=0" with no luck. I'm on suse 7.2 and suse 8.1 running mysql 3.23.52

CAnyone know how to fix this? Thanks
Christian

Ok, I finally got it. I'm using Linux, and did the following:

I went back to my source directory, re-ran the "./configure" script with the additional option "--enable-local-infile". Then, "make", "make install". Don't redo all the installation steps (like mysql_create_db), or you might clobber databases and such from your existing installation.

Modify the startup script, and add this switch to safe_mysqld: "--local-infile=1" (e.g. "mysqld_safe --local-infile=1 &").

When running the client, you don't need any special switches. "mysql -u db -p <password>" was good enough for me, and LOAD DATA LOCAL INFILE worked.

(edit: should also note I'm using mysql-4.0.13)

Add your own comment.