#348 (2.2.3) LOAD INFILE when LOCAL disabled

[Jakub Formela]

Hello
I'm using Mysql 3.23.49
in that version LOCAL keyword
in command LOAD DATA INFILE 'file.txt'
is not allowed, so it's time to fix it

i.e. copy file from client mashine to server
and then run LOAD... command but without LOCAL
keyword

waiting for reply

JF

kuba@3net.pl

[Marc Delisle]

Logged In: YES
user_id=210714

Some MySQL doc about this:
http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html

[Marc Delisle]

Logged In: YES
user_id=210714

We could modify the "insert data from textfile" dialog to
add the option for this alternate mode of LOAD, and add
warnings that "this will only work if the user has FILE
privilege, and if the file is located in the database
directory, or readable by all".

Of course the Browse button would not work :)

[Jakub Formela]

Logged In: YES
user_id=486764

I can see another solution:
copying file from client directory to
server temp directory, and then
using mysql command LOAD DATA INFILE (witjout LOCAL) from
that temp directory on server mashine

[Robin Johnson]

Logged In: YES
user_id=30201

Has this been resolved?

[Nobody/Anonymous]

Logged In: NO

Couldn't you setup an option whereby they upload the file
from their computer to the "tmp" directory, you change the
permission on it to be world readable, then use "LOAD DATA
INFILE" to load it in, then delete the tmp file? This would get
around the Mysql 3.23.49 security problem with LOAD DATA
LOCAL.

The only other option would be to use the mysql_options
possibly:
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 8.4.3.159 mysql_options().
--

Just some thoughts to correct this problem.

[Marc Delisle]

Logged In: YES
user_id=210714

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

So if the sysadmin can completely disable LOAD DATA LOCAL.

[Nobody/Anonymous]

Logged In: NO

To make this feature work with phpMyAdmin 2.2.6 I had to do the
following changes within ldi_check.php:

- remove LOCAL from the query string:

$query = 'LOAD DATA INFILE \'' . $textfile . '\'';

- make sure the uploaded file is readable by all:

chmod($textfile, 0777);

(this line is added just before the block that executes the query)

Pages at mysql.com and this site are misleading on why this specific
feature does not work in phpMyAdmin. The problem does not lie with the
mysql --local-inline settings, but with the fact that phpMyAdmin 2.2.6
uses the LOCAL keyword while actually uploading the file on the server!

Hope this helps.

Francois.

francois1 at mac dot com

[Marc Delisle]

Logged In: YES
user_id=210714

The main reason why we are using the LOCAL keyword is
explained in the MySQL manual:

"On the other hand, you do not need the FILE privilege to
load local files."

So we wanted users without the FILE privilege to be able to
upload their datafiles.

And using LOAD DATA LOCAL INFILE works, even if the file is
on the server (except for the case introduced with MySQL
3.23.49).

[Nobody/Anonymous]

Logged In: NO

OK, it's easier to understand the motivation once you shed some light
on it :)

But still, with the regular phpMyAdmin 2.2.6 and MySQL 3.23.49 and
3.23.51 distributions I *cannot* get this feature to work even if I make
sure everything is done and authorized by the book (Sun Solaris 2.8,
SPARC).

What exactly is "the case introduced with MySQL
3.23.49" and how to get around it?

Thanks,
F

[Marc Delisle]

Logged In: YES
user_id=210714

What has been introduced in 3.23.49 is explained in
http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html

and the workaround... well, this bug report is still open,
we still have to choose the best solution for the maximum
number of environments.

[Nobody/Anonymous]

Logged In: NO

Am I being too simple minded to add a function to import
CSV table pasted into text box, much like the sql dumps
allow?

With an optional record range, and even fields, for them
LARGE tables. At least I would be able to get done what I
need to.
I don't want to sound... un-gratefull. BUT!
There should be a link to these forums or something to
make it easier to find out why this wont work.

I'm pretty "savvy" in my searching, but there are a TON of
submissions about this, and practically nothing saying how
to fix. It took me an hour going through all the posts before
finding the "local" work around. (which by the way is pretty
simple, but adds some inconvenience for me, headaches for
my client)My sole purpose for phpMyAdmin is for JUST
THAT. If some one can point me in a direction for another
soution for JUST importing and exporting table data...
PLEASE reply here.

TIA

[Nobody/Anonymous]

Logged In: NO

I know there is way to at least import a SQL file, A
purchased an off the shelf shopping cart that did it.
So I have to Imagine that there IS a way, Probably by
reading the file, converting to sql statement, then applying
to table. Maybe inefficient ?? .. or a BI*CH to accomplish? I
wish I was a programmer, I know I could at least get where
I need to be.
But I'm not seeing much light at the end of the "Train
Wrecked Tunnel"

[Nobody/Anonymous]

Logged In: NO

This issue is fixed with MySQL 3.23.52 and phpMyAdmin 2.3.0

[Nobody/Anonymous]

Logged In: NO

Change the line in ldi_check.php, works fine for me.

[Marc Delisle]

Logged In: YES
user_id=210714

To the person who said "this issue is fixed with MySQL
3.23.52 and phpMyAdmin 2.3.0": it depends. If mysqld is
started with
--local-infile=0
you get:
The used command is not allowed with this MySQL version

[Marc Delisle]

Logged In: YES
user_id=210714

To the person who said "Change the line in ldi_check.php",
the suggested patch in ldi_check (removing LOCAL, adding
chmod 777) requires that you have FILE privileges to work.

[Marc Delisle]

Logged In: YES
user_id=210714

From the forum:
By: kattare ( Ethan Burnside )
Small FYI - Maybe save you some time?
2002-10-17 12:59 If you compile php against mysql client
libs 3.23.49 or higher and you are connecting to a mysql
server that is 3.23.48 or lower, the CSV import that does a
LOAD DATA LOCAL call will bomb with a "Can't stat
'/tmp/phptmpfile' (errcode 2)". As it turns out, it was
looking for the file on the SERVER! (ie, ignoring the
'LOCAL' portion of the 'LOAD DATA LOCAL' command.) It looks
like the mysql guys really didn't think this one through. On
the client side the compiled in libs don't call the
/etc/my.cnf file to get the 'load-local=1' config setting
that would be necessary to fix it.

The fix was to download the 3.23.48 source and compile the
libs. Then replace the newer libs with the older ones.

This problem had been nagging us for literally months.

Cheers,

~Ethan Burnside
Kattare Internet Services

[Marc Delisle]

Logged In: YES
user_id=210714

Maybe we should align with MySQL guidelines: stop using the
LOCAL trick and require users to have FILE privilege?

And we could add a small checkbox
( ) LOCAL
on the import form, in case they don't have FILE priv and
they want to try the LOCAL trick.

[Remi COLLET]

Logged In: YES
user_id=652301

Why not check in file ldi_check.php if serveur is "localhost".
If that case, word 'LOCAL' can be deleted.

This simple solution will solve the problem for most of us
who use the
same serveur for MySQL et phpMyAdmin

Cordialy.