4.2.4 Вопросы безопасности, относящиеся к команде LOAD DATA LOCAL

Чтобы решить проблемы безопасности, которые могут возникнуть при использовании команды LOAD DATA LOCAL,в MySQL 3.23.49 и MySQL 4.0.2 были добавлены новые опции.

При поддержке этой команды могут возникнуть две проблемы:

Первая: поскольку чтение файла инициируется сервером, теоретически имеется возможность создать ``доработанный'' при помощи патча сервер MySQL, способный читать любые файлы на клиентской машине, к которой текущий пользователь имеет доступ для чтения, в то время, когда клиент направляет запрос к таблице.

Вторая: в веб-среде, в которой подсоединение клиентов осуществляется с веб-сервера, пользователь может использовать команду LOAD DATA LOCAL для чтения любых файлов, к которым процесс веб-сервера имеет доступ для чтения (если предположить, что пользователь может выполнять на сервере SQL любые команды).

Эти проблемы решаются с помощью двух следующих исправлений:

Если вы конфигурируете MySQL без опции --enable-local-infile, то команда LOAD DATA LOCAL будет запрещена для всех клиентов, если, конечно, они не будут вызывать mysql_options (... MYSQL_OPT_LOCAL_INFILE, 0). Обратитесь к разделу See section 8.4.3.159 mysql_options().

В случае клиента mysql, LOAD DATA LOCAL может быть разблокирована заданием опции --local-infile[=1] или заблокирована с помощью опции --local-infile=0.

По умолчанию все MySQL-клиенты и библиотеки компилируются с опцией --enable-local-infile для обеспечения совместимости с MySQL 3.23.48 и более старыми версиями.

Блокировку всех команд LOAD DATA LOCAL на MySQL-сервере можно осуществить путем запуска mysqld с опцией --local-infile=0.

В случае, если команда LOAD DATA LOCAL INFILE заблокирована на сервере или клиенте, вы получите следующее сообщение об ошибке (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: '[email protected]' (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

Add your own comment.